SQL 윈도우 함수 요약, 예제

WINDOW FUNCTION 개요

• 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수가 윈도우 함수
• 윈도우 함수는 집계함수 뿐만 아니라, 새로운 윈도우 함수 전용 만들어진 기능도 있음
• 윈도우 함수는 다른 함수와 중첩 X, 서브쿼리에는 사용 가능

윈도우 함수 종류

1. 그룹 내 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER
2. 그룹 내 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT (sql server는 OVER 절의 OREDER BY 지원 X)
3. 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (오라클에서만 지원)
4. 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
5. 선형 분석을 포함한 통계 분석 함수

윈도우 함수 기본 구조

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ; 

• WINDOW_FUNCTION : 윈도우 함수
• ARGUMENTS(인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다.
• PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 order by 절을 기술한다.
• WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. (sql server 에서는 지원하지 않음)

예제

SELECT userid, channel
FROM (SELECT usc.userid, st.ts, usc.channel, ROW_NUMBER() OVER (PARTITION BY usc.userid ORDER BY st.ts ASC) as seq
      FROM raw_data.user_session_channel usc
      INNER JOIN raw_data.session_timestamp st
      ON usc.sessionid = st.sessionid
      ORDER BY usc.userid, st.ts)
WHERE seq = 1
LIMIT 10; 

• USER_SESSION_CHANNEL 테이블, SESSION_TIMESTAMP 테이블을 INNER JOIN한 테이블에서
• ROW_NUMBER()라는 윈도우 함수 사용: PARTITION된 부분에서, 순서에 맞게 NUMBER를 붙여주는 함수
• 순서는 SESSION_TIMESTAMP의 ts 컬럼 사용해서 순서 지정, 순서에 맞게 seq라는 컬럼으로 생성
• seq가 1 ASC : 오름차순일 때는 가장 작은 순서 값 / DESC : 내림차순일 때는 가장 큰 순서 값으로
• 뽑는다.