[SQL] SQL Booster - 분석함수
2024, Jun 10
목차
9.1 OVER절
9.1.1 OVER절 이해하기
함수
- COUNT, SUM, MIN, MAX ⇒ OVER절이 있으면 분석함수, 없으면 집계함수
- RANK, LAG, LEAD
- OVER절
-
OVER()는 분석함수 뒤에서 분석 대상을 지정하는 기능을 함
- OVER() ← ()옵션 안주면 조회가 완료된 결과 전체가 분석 대상
- () 안에 ‘PARTITION BY’나 ‘ORDER BY’를 사용하면 각 로우마다 분석 대상을 다르게 설정할 수 있음
9.1.2 분석 대상
SELECT SQL 처리 개념
- FROM 절
- WHERE 절
- GROUP BY 절
- HAVING 절
→ OVER 절의 분석 대상은 위 과정이 모두 종료된 결과 집합이다.
GROUP BY가 포함된 SQL에서 주의할 점
- 분석함수와 GROUP BY가 동시에 사용될 때는, GROUP BY에 적은 컬럼 OR 집계함수 사용 결과만 분석함수로 분석 가능
9.1.3 OVER - PARTITION BY
- OVER절 안에 사용하는 구문으로, 정의된 컬럼 값에 따라 칸막이를 만들어서 분석한다.
소계 구하기
- ROLLUP과의 차이로는 ROLLUP은 소계를 로우로 추가하지만, 분석함수는 소계를 컬럼으로 추가
ROLLUP 사용
SELECT T1.CUS_ID
,TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
,SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170601','YYYYMMDD')
GROUP BY
ROLLUP(T1.CUS_ID,TO_CHAR(T1.ORD_DT,'YYYYMM'))
ORDER BY T1.CUS_ID,TO_CHAR(T1.ORD_DT,'YYYYMM');
PARTITION BY 사용
SELECT T1.CUS_ID
,TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
,SUM(T1.ORD_AMT) ORD_AMT
,SUM(SUM(T1.ORD_AMT))
OVER(PARTITION BY T1.CUS_ID) BY_CUST_AMT
,SUM(SUM(T1.ORD_AMT)) OVER() ALL_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170601','YYYYMMDD')
GROUP BY T1.CUS_ID,TO_CHAR(T1.ORD_DT,'YYYYMM')
ORDER BY T1.CUS_ID,TO_CHAR(T1.ORD_DT,'YYYYMM');
사용 범위
- 각 로우별 전체 대비 비율
- 소계 대비 비율
9.1.4 OVER - ORDER BY
- OVER절 안에 사용하는 구문으로, 현재 데이터까지의 누적 합계를 구할 때 유용하다.
- PARTITION BY와 같이 쓰면, 누적금액과 총 금액을 알 때 사용할 수 있다.
- 주의 - PARTITION BY가 앞에 와야함
SELECT T1.CUS_ID ,TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
,SUM(T1.ORD_AMT) ORD_AMT
,SUM(SUM(T1.ORD_AMT)) OVER(PARTITION BY T1.CUS_ID) BY_CUST_AMT
,SUM(SUM(T1.ORD_AMT)) OVER(PARTITION BY T1.CUS_ID ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM'))
BY_CUS_ORD_YM_SUM
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170601','YYYYMMDD')
GROUP BY T1.CUS_ID ,TO_CHAR(T1.ORD_DT,'YYYYMM')
ORDER BY T1.CUS_ID ,TO_CHAR(T1.ORD_DT,'YYYYMM');
9.2 기타 분석함수
9.2.1 순위 분석함수
- RANK, DENSE_RANK
- RANK → 동률있으면 건너뛰고 순위 매김
- DENSE_RANK → 동률있어도 연속해서 순위 매김
- ORDER BY를 필수적으로 사용해야 한다.
9.2.2 ROW_NUMBER
- 조회 결과에 줄 번호를 부여하는 분석함수
- 중복된 순위를 내보내지 않는다.
- 주의
- ROWNUM을 대체하는 기능이 아님. ROWNUM이 성능면에서 유리함
- PARTITION BY랑 사용한 쿼리 (3,4월 주문에 대한 월별 고객 순위)
SELECT TO_CHAR(ORD.ORD_DT, 'YYYYMM') ORD_YM
, ORD.CUS_ID
, SUM(ORD.ORD_AMT) ORD_AMT
, ROW_NUMBER() OVER(PARTITION BY TO_CHAR(ORD.ORD_DT, 'YYYYMM') ORDER BY SUM(ORD.ORD_AMT) DESC) BY_YM_RANK
FROM T_ORD ORD
WHERE ORD.ORD_DT >= TO_DATE('20170301', 'YYYYMMDD')
AND ORD.ORD_DT < TO_DATE('20170501', 'YYYYMMDD')
GROUP BY TO_CHAR (ORD.ORD_DT, 'YYYYMM')
, ORD.CUS_ID;
9.2.3 LAG, LEAD
- LAG : 자신의 이전 값
- LEAD : 자신의 이후 값
- offset : 몇 로우 전/후로 갈건지
- 만약 없으면, NULL
9.3 분석함수를 대신하기
9.3.1 분석함수를 대신하는 방법
- 성능 면에서는 분석함수가 가장 유리할 수 있다는 점