SQL BOOSTER - 분석함수

 

1. 분석함수란?

• 테이블의 데이터를 특정 컬럼을 기준으로 행들을 그룹화하여 결과를 조회하는 함수
• GROUP BY와 함께 사용하는 집계함수와 비슷해 보이지만, 실제로는 조금 다른 기능을 수행한다. 

2. OVER절

 

분석함수에서 분석 대상을 지정하는 역할을 수행한다. 기본 개념은 다음과 같다.

1. 분석함수의 분석 대상을 정하는 역할
2. 대부분의 분석함수는 OVER절과 함께 사용된다.
3. ()안에 아무런 옵션이 없다면, 조회된 결과 전체가 분석 대상이다.

2.1 OVER절의 기본 사용법 

 

SQL BOOSTER

위 SQL의 결과는 3월 1일의 주문 로우 데이터를 보여주면서, 마지막 컬럼에 주문 총 건수를 추가했다.

3월 1일의 총 주문건수는 5개이므로, 모두 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로 표현할 수 있다. OVER절이 처리되는 과정은 다음과 같다.

SQL BOOSTER

이를 잘 살펴보면, 분석함수를 사용하기 전 조회결과가 분석 대상이 되었음을 알 수 있다.

OVER절 안에 PARTITION BY나 ORDER BY를 사용하면 각 로우마다 분석 대상을 다르게 설정할 수 있다.

좀 더 나아가면 WINDOING 절까지 사용해 분석 대상을 더욱더 세밀하게 조정 할 수 있다. 

 

3. 분석 대상 

 

분석함수를 익히기 위해서는 분석 대상의 개념이 매우 중요하다. 분석 대상을 이해해야 분석함수를 정확히 사용할 수 있다.

OVER절의 분석 대상은 FROM,WHERE,GROUP BY, HAVING이 모두 종료된 결과이다. 

 

GROUP BY가 있는 것과 없는 분석함수의 차이를 알아보자

--GROUP BY가 없음
SELECT T1.ORD_SEQ, T1.CUS_ID, COUNT(*) OVER() ALL_CNT
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003')
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
ORDER BY T1.ORD_SEQ;

--GROUP BY가 있음
SELECT T1.ORD_SEQ, T1.CUS_ID, COUNT(*) OVER() ALL_CNT
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003')
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
GROUP BY T1.ORD_SEQ;

SQL BOOSTER

수행 결과로 확인할 수 있듯이 분석함수 수행전까지 SQL의 수행결과가 분석대상이다!

 

3.1 집계함수 결과를 분석 대상으로

 

--문제 발생 SQL -> T1.ORD_AMT는 GROUP BY에 포함되지 않았음으로, 분석함수 실행전에 조회될 수 없음
SELECT T1.CUS_ID, 
SUM(T1.ORD_AMT) OVER() ALL_CNT
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003')
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
GROUP BY T1.CUS_ID;

--문제 발생하지 않는 SQL -> SUM(T1.ORD_AMT)의 결과를 분석대상으로 삼음 (T1.CUS_ID별 SUM(T1.ORD_AMT)를 분석)
SELECT T1.CUS_ID, 
SUM(SUM(T1.ORD_AMT)) OVER() ALL_CNT
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003')
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
GROUP BY T1.CUS_ID;

 

GROUP BY시 분석함수에서 흔히 저지르는 실수는 GROUP BY에 없는 컬럼을 분석함수에서 분석 대상으로 지정한다는 것이다.

이를 집계함수로 한번 감싼 결과를 분석대상으로 삼음으로 해결할 수 있다. 

 

즉 GROUP BY와 분석함수를 동시에 사용 시 분석 대상은 다음만 가능하다.

• GROUP BY에 명시된 컬럼
• 집계함수를 사용한 결과

 

3.2 분석함수와 집계함수의 차이

SELECT T1.CUS_ID, 
COUNT(*) BY_CUS_ORD_CNT, --집계 : 고객별 주문
COUNT(*) OVER() ALL_CUST_CNT, -- 분석 : 조회된 고객 수 
SUM(COUNT(*)) OVER() ALL_ORD_CNT -- 분석: 고객별 주문건수에 합

FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003')
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
GROUP BY T1.CUS_ID;

SQL BOOSTER

결과를 위와 같이 확인할 수 있다. 이를 통해 집계함수와 분석함수의 결과는 테이블 전체(GROUP BY) VS 특정 분석 대상으로 결과를 처리하는 가에 따라 달렸다. 

 

분석 대상의 개념을 정리

• 분석함수는 분석 대상에 대해서만 수행된다.
• 분석 대상이란 분석함수를 제외한 SQL의 결과다.
• OVER() 안에 아무런 옵션을 정의하지 않으면 조회된 SQL 결과 전체가 대상이다.
• GROUP BY가 포함된 SQL에서 분석함수를 사용할 경우에는 GROUP BY에 명시된 컬럼 혹은 집계함수를 사용한 결과에만 분석함수를 먹을 수 있다. 

---

 

4. OVER-PARTITION BY

 

PARTITION BY는 분석 함수 수행전 SQL결과에 대해 로우 별로 분석 대상을 다르게 지정할 수 있게 해준다.

예를들어 PARTITION BY T1.CUS_ID라고 정의하면, 해당 로우의 CUS_ID값과 같은 값을 가진 로우들이 분석 대상이 된다.

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','CUS0003')
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');

 

SQL BOOSTER

1. 분석 전 SELECT 결과를 받는다. 
2. OVER 절의 PARTITION BY에 따라 해당 결과에 분석 대상을 로우 단위로 분리한다.
3. 분석 함수를 실행한다.

PARTITION BY는 여러 컬럼을 지정할 수도 있으며, 하나의 SELECT 절에서 분석함수별로 다르게 지정할 수도 있다.

 

SELECT T1.CUS_ID
, TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM,T1.ORD_ST
, SUM(T1.ORD_AMT) ORD_AMT
, SUM(SUM(ORD_AMT)) OVER (PARTITION BY T1.CUS_ID) BY_CUST_AMT
, SUM(SUM(ORD_AMT)) OVER (PARTITION BY T1.ORD_ST) BY_ORD_ST_AMT
, SUM(SUM(ORD_AMT)) OVER (PARTITION BY CUS_ID, TO_CHAR(T1.ORD_DT,'YYYYMM')) BY_CUST_YM_AMT

FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0002','CUS0003')
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'),T1.ORD_ST
ORDER BY  T1.CUS_ID , TO_CHAR(T1.ORD_DT, 'YYYYMM'),T1.ORD_ST;

• 첫번째 분석함수는 CUS_ID별로 분석 
• 두번째 분석함수는 ORD_ST별로 분석 
• 세번째 분석함수는 CUS_ID와 주문년월별 분석

 

일반적으로 데이터의 소계를 구하기 위해 ROLLUP을 사용하지만, PARTITION BY도 소계를 구할 수 있다. 

ROLLUP은 소계를 로우에 추가하고, 부석함수는 소계를 컬럼으로 추가한다는 차이점이 있다. 

 

SQL BOOSTER

또한, 각 로우 별로 전체 대비 비율이나 소계 대비 비율을 칼럼으로 표시해야할 때 분석함수를 사용할 수 있다.

SELECT T1.CUS_ID
       ,TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
       ,SUM(T1.ORD_AMT) ORD_AMT
       ,ROUND(SUM(T1.ORD_AMT) / (SUM(SUM(T1.ORD_AMT)) OVER(PARTITION BY T1.CUS_ID)) *100.00,2) ORD_AMT_RT_BY_CUST
       ,ROUDN(SUM(T1.ORD_AMT) / (SUM(SUM(T1.ORD_AMT)).OVER()) * 100.00,2) ORD_AMT_RT_BY_ALL_AMT
       FROM T_ORD T1
       WHERE T1.CUS_ID IN ('CUS_0002','CUS_0003');

 

---

 

5. OVER - ORDER BY

 

OVER절 안에 ORDER BY를 사용하면 분석 대상이 다르게 정해진다. 이러한 특징을 이용해 누적 합계를 구할 때 유용하게 사용할 수 있다.

 

*분석대상을 점진적으로 늘려가는 느낌이다!

 

SELECT 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,'YYYMM')) ORD_YM_SUM
FROM T_ORD T1

--.. 이하 생략

 

ORDER BY를 통해 월별 누적 합계를 구하고 있다. 

SQL BOOSTER

 

ORDER BY를 통해 분석 대상이 점차 늘어가는 것을 확인할 수 있다. 

결과 데이터가 주문년월 순서로 출력되어 있고, 각 로우별로 주문년월이 자신보다 작거나 같은 로우가 분석 대상이 되고 있다.

 

첫번째 로우는 201703이고, 201703보다 작거나 같은 로우는 자신 뿐이다. 따라서 분석 대상이 자기 자신이다.

두번째 부터는 201704이고, 201704보다 작거나 같은 로우가 분석대상이다. 이런식으로 조금씩 늘려가며 누적합계를 구한다.  

 

PARTITION BY와 ORDER BY를 동시에 사용할 수 있다. 다만, 파티션의 마지막 컬럼과 ORDER BY 사이에 콤마를 사용하지 말아라!

OVER(PARTITION BY T1.CUS_ID,T1.ORD_ST ORDER BY T1.ORD_AMT)

 

---

6. 기타 분석함수 

 

6.1 RANK 함수 

 

- RANK와 DENSE_RANK 분석함수를 사용하면 순위를 구할 수 있다. 

- ROW_NUMBER보다 ROWNUM이 성능 면에서 유리한 경우가 많다. 따라서 쉽게 처리할 수 있다면 굳이 ROW_NUMBER를 사용하지 않는 것이 좋다. 

 

6.2 LAG,LEAD

 

LAG는 자신의 이전 값을, LEAD는 자신의 이후 값을 가져오는 분석함수다. 이때 자신보다 몇건 이전이나 이후의 값을 가져올 지 결정할 수 있다. 

SQL BOOSTER

• LAG(컬럼명,offset) OVER ([PARTITION BY] ORDER BY)
• LEAD(컬럼명,offset) OVER ([PARTITION BY] ORDER BY)
• offset: 현재 로우에서 몇 로우 이전 또는 몇 로우 이후를 뜻한다.

자신의 이전이나, 이후의 값을 가져온다는 뜻은 데이터에 순서가 있다는 뜻이다.

따라서 over절에 order by를 사용해야 사용할 수 있는 분석함수이다.

 

SELECT T1.CUS_ID
      ,SUM(T1.ORD_AMT) ORD_AMT
      ,ROW_NUMBER() OVER(ORDER BY SUM(T1.ORD_AMT) DESC) RNK
      ,LAG(T1.CUS_ID,1) OVER(ORDER BY SUM(T1.ORD_AMT) DESC) LAG_1
      ,LEAT(T1.CUS_ID,1) OVER(ORDER BY SUM(T1.ORD_AMT) DESC) LEAD_1
FROM T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE ('20170401','YYYYMMDD')
AND T1.CUS_ID IN ('CUS_0020','CUS_0021','CUS_0022','CUS_0023')
GROUP BY T1.CUS_ID;

 

- LAG: ORDER BY SUM(T1.ORD_AMT) DESC, 자신보다 주문금액이 높은 이전 데이터를 조회, offset이 1이므로, 1건 이전의 CUS_ID 조회

- LEAD:   ORDER BY SUM(T1.ORD_AMT) DESC, 자신보다 주문금액이 낮은 다음 데이터를 조회, offset 1이므로, 1건 이후의 CUS_ID 조회 

 

SELECT TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
       ,SUM(T1.ORD_AMT) ORD_AMT
       ,LAG(SUM(T1.ORD_AMT),1) OVER(ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM') ASC) BF_YM_ORD_AMT
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM');

SQL BOOSTER

월별 주문금액과 1개월 전 주문금액을 같이 보여주는 SQL이다. OFFSET을 3으로주면 3개월 이전의 데이터를 같이 띄울 수 있다.

 

---

참고자료

https://www.yes24.com/Product/Goods/82818767

SQL BOOSTER - 예스24