[SQL] 분석 함수 - Avg, Row_Number, Rank, Dense_Rank, First/Last_Value, Lag, Lead

분석함수란?

• 분석 함수란 특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 합계 및 카운트 등을 계산할 수 있는 함수

AVG - 평균

• 특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 특정 컬럼의 평균을 구하는 함수

SELECT
      AVG (PRICE)                                  -- PRICE컬럼의 평균값
 FROM
      PRODUCT
;

---------- # AVG + Group By
SELECT
      B.GROUP_NAME
    , AVG (PRICE)
 FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
    ON (A.GROUP_ID = B.GROUP_ID)
GROUP BY B.GROUP_NAME
;

---------- # AVG
SELECT
       A.PRODUCT_NAME 
     , A.PRICE
     , B.GROUP_NAME
     , AVG (A.PRICE) OVER (PARTITION BY B.GROUP_NAME) -- GROUP_NAME 기준
 FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
    ON (A.GROUP_ID = B.GROUP_ID)
;

---------- # 누적평균
SELECT
       A.PRODUCT_NAME 
     , A.PRICE
     , B.GROUP_NAME
     , AVG (A.PRICE) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
 FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
    ON (A.GROUP_ID = B.GROUP_ID)
;

 

Row_Number, Rank, Dense_Rank - 순위

• 특정 집합 내에서 결과 건수의 변화와 상관없이, 해당 집합안에서 특정 컬럼의 순위를 구하는 함수

---------- # 무조건 순차 (ex. 1,2,3,4,5 ~)
SELECT
       A.PRODUCT_NAME
     , B.GROUP_NAME
     , A.PRICE
     , ROW_NUMBER () OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC)
 FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
    ON (A.GROUP_ID = B.GROUP_ID)
;

---------- # 같은 순위면 같은 순위 + 건너뜀 (ex. 1,1,3,4~)
SELECT
       A.PRODUCT_NAME
     , B.GROUP_NAME
     , A.PRICE
     , RANK () OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE ASC)
  FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
    ON (A.GROUP_ID = B.GROUP_ID)
;

---------- # 같은 순위면 같은 순위 + 건너뛰지 않음 (ex. 1,1,2,3~) 
SELECT
       A.PRODUCT_NAME
     , B.GROUP_NAME
     , A.PRICE
     , DENSE_RANK () OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
  FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
    ON (A.GROUP_ID = B.GROUP_ID)
;

  

First/Last_Value - 값 구하기

• 특정 집합 내에서 결과 건수의 변화 없이, 해당 집합안에서 특정 컬럼의 첫번째 또는 마지막 값을 구하는 함수

---------- # First_Value
SELECT
       A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
     , FIRST_VALUE (A.PRICE) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC) 
        AS LOWEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
        ON (A.GROUP_ID = B.GROUP_ID)
;

---------- # Last_Value + RANGE BETWEEN
SELECT
       A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
     , LAST_VALUE (A.PRICE) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC 
        RANGE BETWEEN UNBOUNDED PRECEDING               -- UNBOUNDED PRECEDING 그룹의 첫번째값
        AND UNBOUNDED FOLLOWING)                        -- UNBOUNDED FOLLOWING 그룹의 마지막값
        AS LOWEST_PRICE_PER_GROUP
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
        ON (A.GROUP_ID = B.GROUP_ID)
;

---------- # Last_Value
SELECT
       A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
     , LAST_VALUE (A.PRICE) OVER                        -- Range between 없으면 Lastvalue 못 뽑음
       (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)     -- Range between 안쓰면 Current row가 Default
        AS LOWEST_PRICE_PER_GROUP                       -- Current row까지만 지정한다는 뜻
FROM PRODUCT A
INNER JOIN PRODUCT_GROUP B
        ON (A.GROUP_ID = B.GROUP_ID)
;

 

Lag, Lead - 이전/다음행의 값

• 특정 집합 내에서 결과 건수의 변화 없이, 해당 집합안에서 특정 컬럼의 이전이나 다음 행의 값을 구함

---------- # LAG (카테고리, 간격): 이전 행 값 찾기
SELECT
	  A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
	, LAG (A.PRICE, 1) OVER                          -- PRICE의 이전 행의 값을 구함
      (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)   -- GROUP_NAME컬럼 기준 PRICE컬럼으로 정렬한 값 중에서
      AS PREV_PRICE
	, A.PRICE - LAG (A.PRICE, 1) OVER                -- 현재행 PRICE - 이전행 PRICE
      (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)   -- GROUP_NAME컬럼 기준으로 PRICE컬럼으로 정렬한 값 중에서
      AS CUR_PREV_DIFF
FROM
    PRODUCT A
INNER JOIN PRODUCT_GROUP B
    ON (A.GROUP_ID = B.GROUP_ID)
;

---------- # LEAD (카테고리, 간격): 다음 행 값 찾기
SELECT
	  A.PRODUCT_NAME
    , B.GROUP_NAME
    , A.PRICE
	, LEAD (A.PRICE , 1) OVER 
      (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
      AS NEXT_PRICE
	, A.PRICE - LEAD (A.PRICE , 1) OVER 
     (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
     AS CUR_NEXT_DIFF
FROM
  	PRODUCT A
INNER JOIN PRODUCT_GROUP B
   ON (A.GROUP_ID = B.GROUP_ID)
;

 

Reference

• Fast Campus, 모두를 위한 SQL/DB 올인원 패키지 온라인 강의