SQL 스터디 8주차! (종료)
1. OVER절
- 분석함수를 사용하기 위해 분석 대상을 지정할 때 사용
- 분석함수의 대상을 정하는 역할을 한다.
- 대부분의 분석함수는 OVER절과 같이 상요한다.
- OVER()와 같이 괄호 안에 아무런 옵션을 주지 않으면 조회된 결과 전체가 분석대상이다.
- 분석함수 종류
COUNTSUMMINMAX- OVER 절이 있으면 분석함수, 없으면 집계함수RANKLAGLEAD
예시
1
2
3
4
5
SELECT T1.ORD_SEQ, T1.CUS_ID, T1.ORD_DT
, COUNT(*) OVER() ALL_CNT
FROM T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170301', 'YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170302', 'YYYYMMDD');
분석대상
분석의 대상이 되는 것은, 분석함수를 제외한 SQL이 완료된 결과이다.
SELECT SQL이 처리되는 과정은 보통
- FROM 절에서 대상 테이블들을 선택하고,
- WHERE절에서 1번 대상 중에 조회할 데이터를 선택하고,
- GROUP BY절에서 2번까지 수행된 결과를 그룹화하고,
- HAVING절에서 3번까지 수행된 결과 중 최종 조회될 데이터를 선택하는 것이다.
이 SELECT SQL이 수행된 결과의 집합을 분석함수가 처리한다.
활용법
- 분석함수를 사용하기 이전에도 에러가 없는 SQL만 실행 가능하다.
- 분석함수와
GROUP BY가 동시에 사용될 때는GROUP BY에 명시된 컬럼이나SUM처럼 집계 함수를 사용한 결과만 분석함수로 분석할 수 있다.
1
2
3
4
5
6
7
8
9
SELECT T1.CUS_ID
, COUNT(*) BY_CUS_ORD_CNT -- 집계함수: GROUP BY의 CUS_ID별 집계를 수행하는 고객별 주문건수
, COUNT(*) OVER() ALL_CUST_CNT -- 분석함수: 조회된 고객수. 분석 대상의 데이터 건수를 센다.
, SUM(COUNT(*)) OVER() ALL_ORD_CNT -- 분석함수: 고객별 주문건수에 대한 합. COUNT: CUST_ID별 집계함수, SUM OVER : COUNT(*)에 대한 분석함수
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('20170302', 'YYYYMMDD')
GROUP BY T1.CUS_ID;
OVER-PARTITION BY
- 로우 별로 분석 대상을 다르게 지정 하는 방법
PARTITION BY T1.CUS_ID: 분석대상은 해당 로우의 CUS_ID 값과 같은 값을 가진 로우들PARTITION BY에 정의된 컬럼 값에 따라 칸막이를 만들어서 분석한다고 보면 된다.
1
2
3
4
5
6
7
8
9
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
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('20170302', 'YYYYMMDD')
GROUP BY T1.CUS_ID, TO_CHAR(T1.ORD_DT, 'YYYYMM')
ORDER BY T1.CUS_ID, TO_CHAR(T1.ORD_DT, 'YYYYMM');
OVER-ORDER BY
ORDER BY를 분석함수의 OVER절 안에 사용하면 로우별로ORDER BY에 따라 분석 대상이 다르게 정해진다.ORDER BY를 기준으로 자신보다 먼저 조회된 데이터가 분석 대상이 된다.
1
2
3
4
5
6
7
8
9
SELECT T1.CUS_ID, TO_CHAR(T1.ORD_DT, 'YYYYMM') ORD_YM
, SUM(T1.ORD_AMT) ORD_AMT
, SUM(SUM(T1.ORD_AMT)) OVER (ORDER BY TO_CHAR(T1.ORD_DT, 'YYYYMM')) 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('20170302', 'YYYYMMDD')
GROUP BY T1.CUS_ID, TO_CHAR(T1.ORD_DT, 'YYYYMM')
ORDER BY T1.CUS_ID, TO_CHAR(T1.ORD_DT, 'YYYYMM');
PARTITION BY와ORDER BY를 동시에 사용할 때는PARTITION BY가 먼저 와야된다.
2. 기타 분석함수
순위 분석함수 RANK, DENSE_RANK
OVER절 안에ORDER BY를 필수적으로 사용해야 한다.ORDER BY에 따라 순위가 정해진다.
1
2
3
4
5
SELECT T1.CUS_ID
, SUM(T1.ORD_AMT) ORD_AMT
, RANK() OVER(ORDER BY SUM(T1.ORD_AMT) DESC) RNK
FROM T_ORD T1
GROUP BY T1.CUS_ID;
ROW_NUMBER
- 조회 결과에 줄 번호를 부여하는 분석함수
- 중복된 순위를 보내지 않는다.
- ROWNUM과 다름. ROWNUM이 성능 면에서 유리하다.
- ROW_NUMBER로 OVER절(PARTITION BY, ORDER BY)에 세밀한 줄 번호를 부여할 수 있다.
LAG, LEAD
- OVER절에 ORDER BY를 사용했을 때, 순서가 있는 분석함수에서 사용
- LAG : 자신의 이전 값을 가져옴
- LEAD : 자신의 이후 값을 가져옴
3. 분석함수를 대신하려면
서브쿼리, 인라인뷰, 셀프 조인으로 대체할 수 있다.
reference
SQL BOOSTER
