[SQL] window function(집계, 순위, 데이터 위치)

1. monthly-transactions: window function

1 - 풀이 1) sum(case) case로 조건걸고 조건값 더해주기

SELECT SUBSTR(trans_date,1,7) month
    , country  
    , COUNT(id) trans_count
    , SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) approved_count
        # CASE를 사용하여 하나씩 COUNT하여 전체를 SUM으로 총 합을 구한다.
        # 위 식은 IF로도 변경가능하다. SUM(IF(state = 'approved',1,0)) AS approved_count 
    , SUM(amount)trans_total_amount
    , SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) approved_total_amount
FROM Transactions
GROUP BY 1,2

 

1 - 풀이 2) window function

• 윈도우함수는 GROUP BY와 비슷한 결과를 가져온다.
• 기존데이터를 가져오고 집계된 값을 따로 가져올 수 있다.
• 함수(column) OVER(PARTITION BY : 그룹기준  ORDER BY : 순서기준) 으로 작성한다.
• 집계함수(max, min, avg, count, sum)로 집계할 수 있다.
  • 최대/최소 값, 평균값, 갯수, 합계를 내어 반환한다.
• 순위도 집계할 수 있다.
  • ROW_NUMBER() : 중복 없는 순위. 행 번호 (1,2,3,4,5,6,7,8,9,,,)
  • DENSE_RANK() : 중복가능, 중복된 순위를 하나의 순위로 친다(1,1,2,2,2,3,4,5,5,,,)
  • RANK() : 중복 가능. 공동순위만큼 건너뛴다.(1,1,3,3,3,6,7,8,8,10,,,)
• 데이터 행을 바꿀 수 있다.
  • LAG(열, n, 결측값 채울 값) OVER() : n행을 미룬다
  • LEAD(열, n, 결측값 채울 값) OVER()  : n행 당긴다.

SELECT 
    SUBSTR(trans_date, 1, 7) AS month
    ,country
    ,COUNT(id) AS trans_count
    ,SUM(amount) AS trans_total_amount
    ,CASE WHEN state = 'approved' THEN COUNT(id) 
    	OVER (PARTITION BY SUBSTR(trans_date, 1, 7), country) 
        ELSE 0 
        END AS approved_count
    ,CASE WHEN state = 'approved' THEN SUM(amount) 
    	OVER (PARTITION BY SUBSTR(trans_date, 1, 7), country) 
        ELSE 0 
        END AS approved_total_amount
FROM 
    Transactions

 

1 - WINDOW LAG사용 예시 : 기온이 상승한 날 구하기

WITH rising AS (SELECT
                    id
                    , recordDate
                    , temperature
                    , lag(temperature) OVER() AS `templus1`
                FROM 
                    Weather
                )

SELECT id
FROM rising
WHERE temperature > templus1

 

with의 구문을 실행한 결과 하나씩 아래 행으로 밀린 것을 알 수 있다.

lag(temperature,2) OVER() AS `templus1`을 사용하면 2행이 뒤로 밀린다.

참고 : https://mizykk.tistory.com/121