[PostgreSQL] Analytic Functions (Window Functions)

인프런에 있는 권철민님의 데이터 분석 SQL Fundamentals 강의를 듣고 정리한 내용입니다.
Dev.log 블로그 글을 참고했습니다.

🐘 Analytic SQL vs Group by + 집계

• Group by는 데이터를 합치고 난 후 집계 (원본 데이터 집합 변경)
• Analytic SQL은 원본 데이터 집합을 유지하면서 집계
• How? Winodw box가 행 단위로 움직이면서 집계
• Group by와 함께 사용 불가

SELECT
    window_function(column) as name
OVER (
    PARTITION BY ...       -- Partition column
    ORDER BY ...           -- Sorting column
    RANGE(ROWS) ...        -- Window range
)
FROM Table1, 

Window Function 종류

• 순위 함수
  • Rank, Dense_Rank, Row_Number
• 비율 함수
  • Percent_Rank, Cume_Dist, ntile, Ratio_to_Report
• 역 분위 함수
  • Percentile_Cont, Percentile_Disc
• 집계 함수
  • Sum, Max, Min, Avg, Count
• 행 순서 함수 (window 범위)
  • First_Value, Last_Value, Lag, Lead

순위 함수

순위 함수	Description
Rank	공동순위 있을 경우 밀림 ex) 1, 2, 2, 4
Dense_Rank	공동순위 있더라도 밀리지 않음 ex) 1, 2, 2, 3
Row_Number	공동순위 있더라도 무조건 고유 번호 ex) 1, 2, 3, 4

SELECT
    JOB, ENAME, SAL,
    RANK() OVER (ORDER BY SAL DESC) ALL_RANK,
    RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

비율 함수

비율 함수	Description
Percent_Rank	순위를 0~1 값으로 표현 ex) 데이터 3개 → 0, 0.5, 1
Ratio_to_Report	파티션 내 sum에 대한 현재 컬럼 값 백분율
Cume_Dist	Accumulation Distribution : 누적 백분율
ntile(N)	N분위 값

SELECT
    DEPTNO, ENAME, SAL,
    CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST
FROM EMP;

SELECT
    DEPTNO, ENAME, SAL,
    NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE
FROM EMP;

역 분위 함수

비율 함수	Description
Percentile_Cont(N)	데이터가 연속분포라 가정. [0~1] 사이의 특정 분위수에 해당하는 값 반환
Percentile_Disc(N)	데이터가 이산분포라 가정. [0~1] 사이의 특정 분위수에 해당하는 값 반환

• 결측치 채울 때 유용
• Percentile_Disc의 경우 역분위 값이 범위로 나올 때 가장 작은 값 반환
• postgreSQL에서는 집계 함수로도 사용 가능

집계 함수

SELECT 
    MGR, ENAME, HIREDATE, SAL,
    ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PERCEDING AND 1 FOLLOWING)) AS MGR_AVG
FROM EMP;

• PARTITION 별로 현재 ROW 기준 이전 ROW 1칸, 이후 ROW 1칸 (총 3칸) 사이의 평균
• 이전/이후 ROW가 없으면 집계에서 제외

SELECT
    ENAME, SAL,
    COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PERCEDING AND 150 FOLLOWING) AS SIM_CNT
FROM EMP;

• 현재 ROW 기준 급여가 -50 ~ +150인 데이터 COUNT

행 순서 함수 (Window 범위)

순위 함수	Description
First_Value	파티션 내 가장 먼저 나온 값
Last_Value	파티션 내 가장 마지막에 나온 값
Lag(expr, [offset] [default])	현재 row 기준 이전 (offset) row의 값 offset : 이전 몇 칸? default : NULL일 경우 대체할 값.(기본값:NULL)
Lead(expr, [offset] [default])	현재 row 기준 다음 (offset) row의 값 offset : 다음 몇 칸? default : NULL일 경우 대체할 값.(기본값:NULL)

• Order by 반드시 필요함
• First/Last Value : window절 생략시 range between unbounded preceding and current row 가 default 값
• Lag/Lead : default(NULL 대체) 값 설정 시 offset값 1이라도 명시해야 함

SELECT
    DEPTNO, ENAME, SAL,
    FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;

--- 바로 다음에 입사한 직원의 입사 일
SELECT
    ENAME, HIREDATE
    LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) AS "NEXT_HIRED"
FROM EMP;

Window절 ROWS vs RANGE

• ROWS : 데이터의 물리적인 위치. 모든 행이 1개의 행으로 인식
• RANGE : 데이터의 논리적인 위치. 기준 값이 같으면 같은 행으로 취급.
• 집계 기준이 다르므로 주의
• Window절 생략시 default 값 :
  • range between unbounded preceding and current row.

NULL 다루기

PostgreSQL	ORACLE	MSSQL
Coalesce(col, default)	NVL(col, default)	ISNULL(col, default)

• Coalesce(col, default) : 해당 column 값이 NULL인 경우 default 값으로 대체
• ODER BY col Nulls first : NULL값을 가장 우선 순위로
• ODER BY col Nulls last : NULL값을 가장 마지막 순위로

맨 위로 이동하기

SQL 카테고리 내 다른 글 보러가기