[oracle] 범죄가 가장 많이 발생하는 요일, 범죄 동기 출력 / UNPIVOT / RANK

Q1. 요일별 범죄현황 CSV - 범죄별 가장 많이 발생하는 요일 출력하기

1. 테이블 생성

-- CSV 파일 저장할 테이블 생성
CREATE TABLE CRIME_DAY
( CRIME_TYPE  VARCHAR2(50),
  SUN_CNT   NUMBER(10),
  MON_CNT   NUMBER(10),
  TUE_CNT   NUMBER(10),
  WED_CNT   NUMBER(10),
  THU_CNT   NUMBER(10),
  FRI_CNT   NUMBER(10),
  SAT_CNT   NUMBER(10),
  UNKNOWN_CNT  NUMBER(10) );

 

2. 요일출력이 용이하도록 UNPIVOT문으로 요일컬럼을 행으로 출력하는 테이블 생성

CREATE TABLE CRIME_DAY_UNPIVOT
 AS
 SELECT *
   FROM CRIME_DAY
   UNPIVOT ( CNT FOR DAY_CNT  IN ( SUN_CNT, MON_CNT, TUE_CNT, WED_CNT, THU_CNT, FRI_CNT, SAT_CNT) );

 

• UNPIVOT : 열(칼럼)을 행으로 바꿔줌

SELECT *
FROM ( 대상테이블 or 서브쿼리 ) AS tab
UNPIVOT ( 집계값칼럼명  FOR  대상칼럼명  IN ( UNPIVOT할 칼럼명,  ... )  ;

 

3. '절도'가 가장 많이 발생하는 요일 출력

-- crime_type을 '절도'로 제한하고 rank함수로 건수(CNT)가 가장 많은순으로 순위 부여
SELECT *
  FROM (SELECT DAY_CNT, CNT, RANK() OVER (ORDER BY CNT DESC) RNK
           FROM CRIME_DAY_UNPIVOT
           WHERE TRIM(CRIME_TYPE)='절도'
       )
WHERE  RNK = 1; # 순위 1위만 출력

RANK : 1등이 2명이면 그 다음 순위는 ‘3’등이라고 출력됨 (1, 1, 3 .. )

DENSE_RANK : 1등이 2명이면 그 다음 순위는 ‘2’등이라고 출력됨 (1, 1, 2, 3 ..)

 

 

---

Q2. 범죄 동기 CSV - 범죄동기별 가장 큰 범죄유형 출력

 

1. 테이블 생성

-- CSV 파일 저장할 테이블 생성
create table crime_cause
(
범죄유형  varchar2(30),
생계형  number(10),
유흥 number(10),
도박 number(10),
허영심 number(10),
복수  number(10),
해고  number(10),
징벌 number(10),
가정불화  number(10),
호기심 number(10),
유혹  number(10),
사고   number(10),
불만   number(10),
부주의   number(10),
기타   number(10)  );

-- 범죄 동기 컬럼을 행으로 출력하는 테이블 생성
CREATE  TABLE  CRIME_CAUSE2
AS
SELECT *
 FROM CRIME_CAUSE
 UNPIVOT ( CNT FOR TERM IN (생계형, 유흥, 도박, 허영심, 복수, 해고, 징벌, 가정불화, 호기심, 유혹, 사고, 불만, 부주의, 기타));

-- 범죄동기가 '가정불화'인 범죄유형과 건수 확인
SELECT 범죄유형, CNT
FROM CRIME_CAUSE2
WHERE TERM = '가정불화'; 

 

Q2-1. 범죄 동기가 '가정불화' 인것중에 가장 많은 건수는?

SELECT 범죄유형,CNT
  FROM CRIME_CAUSE2
  WHERE CNT = ( SELECT MAX(CNT)
                FROM CRIME_CAUSE2
                WHERE TERM='가정불화' ); 

 

Q2-2. ‘방화’범죄의 가장 큰 원인(범죄동기)은?

SELECT TERM AS 원인
  FROM CRIME_CAUSE2
  WHERE CNT = ( SELECT MAX(CNT)
                          FROM CRIME_CAUSE2
                          WHERE 범죄유형='방화')
   AND 범죄유형='방화'; -- 다른 범죄유형이 중복되지 않도록 다시한번 조건 추가