Re: 문제760. (오늘의 마지막 문제)절도가 가장 많이 일어나는 요일과 건수, 순위를 출력하시오

[19기_이승원]

create table crime_week
(
crime_big_type     varchar2(20),
crime_small_type   varchar2(60),
sun                number(10),
mon                number(10),
tues                number(10),
wed                number(10),
thur                number(10),
fri                number(10),
sat                number(10)
);
 

create table crime_week_pivot
(
crime_big_type     varchar2(20),
crime_small_type   varchar2(60),
week_day           varchar2(20),
crime_cnt          number(10) );

select * from crime_week_pivot;


INSERT ALL
INTO CRIME_WEEK_PIVOT VALUES(CRIME_BIG_TYPE, CRIME_SMALL_TYPE,'SUN', SUN)
INTO CRIME_WEEK_PIVOT VALUES(CRIME_BIG_TYPE, CRIME_SMALL_TYPE,'MON', MON)
INTO CRIME_WEEK_PIVOT VALUES(CRIME_BIG_TYPE, CRIME_SMALL_TYPE,'TUES', TUES)
INTO CRIME_WEEK_PIVOT VALUES(CRIME_BIG_TYPE, CRIME_SMALL_TYPE,'WED', WED)
INTO CRIME_WEEK_PIVOT VALUES(CRIME_BIG_TYPE, CRIME_SMALL_TYPE,'THUR', THUR)
INTO CRIME_WEEK_PIVOT VALUES(CRIME_BIG_TYPE, CRIME_SMALL_TYPE,'FRI', FRI)
INTO CRIME_WEEK_PIVOT VALUES(CRIME_BIG_TYPE, CRIME_SMALL_TYPE,'SAT', SAT)
SELECT *
FROM CRIME_WEEK;

 

 

 

SELECT WEEK_DAY, CRIME_CNT, DENSE_RANK() OVER (ORDER BY CRIME_CNT DESC) AS DENSE_RANK
FROM CRIME_WEEK_PIVOT
WHERE CRIME_SMALL_TYPE='절도';