[SQL] Window Function

Window Function

집계된 결과값을 기존 데이터에 추가하여 보여줌 (합계, 평균, 순위, 순서 조작등)
결과를 보여주되, 결과 건수가 줄어들지 않음

SELECT WINDOW_FUNCTION ( ) OVER ( PARTITION BY column ORDER BY column)
FROM table

PARTITION

GROUP BY 는 집계된 결과만 보여주기 때문에 최종 결과 건수가 줄어드는 반면, PARTITION 은 기존 데이터에 집계된 값을 추가하여 보여주기 때문에 결과 건수가 줄어들지 않음!

JobLevel , PerformanceRating 으로 그룹화

SELECT JobLevel, PerformanceRating
	, count(hn.EmployeeNumber) AS hc
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn ON hc.EmployeeNumber  = hn.EmployeeNumber 
WHERE Attrition = 'No'
GROUP BY 1, 2
ORDER BY 1, 2
;

위 결과에 각 JobLevel 별, PerformanceRating 별 인원수 합을 나타내는 컬럼을 추가하고 싶다면?

1. JobLevel로 집계한 결과

-- JobLevel로 집계
SELECT JobLevel,  count(DISTINCT hn.EmployeeNumber) hc_job_level
FROM hr.hr_number hn  
LEFT JOIN hr.hr_cate hc ON hn.EmployeeNumber = hc.EmployeeNumber 
WHERE Attrition = 'No'
GROUP BY 1
ORDER BY 1
;

2. PerformanceRating로 집계한 결과

-- PerformanceRating로 집계
SELECT PerformanceRating,  count(DISTINCT hn.EmployeeNumber)
FROM hr.hr_number hn  
LEFT JOIN hr.hr_cate hc ON hn.EmployeeNumber = hc.EmployeeNumber 
WHERE Attrition = 'No'
GROUP BY 1
ORDER BY 1
;

3. 1번과 2번에서 구한 집계값을 한꺼번에 나타내기

-- window function
SELECT JobLevel, PerformanceRating , count(DISTINCT hn.EmployeeNumber) hc
	, sum(count(DISTINCT hn.employeeNumber)) OVER (PARTITION BY JobLevel) AS hc_job_level
	, sum(count(DISTINCT hn.employeeNumber)) OVER (PARTITION BY PerformanceRating) AS hc_perform_level
FROM hr.hr_number hn  
LEFT JOIN hr.hr_cate hc ON hn.EmployeeNumber = hc.EmployeeNumber 
WHERE Attrition = 'No'
GROUP BY 1,2
ORDER BY 1,2
;

집계된 결과값을 기존 데이터에 추가하는 것을 볼 수 있음!!

LEAD & LAG

순서를 조작하는 window function이므로 order by가 필수!
lead : n칸 뒤에 있는 데이터를 당겨옴
lag : n칸 앞의 데이터를 뒤로 미룸

SELECT LEAD / LAG (col, n) OVER ( PARTITION BY column ORDER BY column)
FROM table

-- lead/lag + partition
SELECT Department, JobLevel
	, count(hn.EmployeeNumber) hc
	, lead(count(hn.EmployeeNumber)) OVER (PARTITION BY Department ORDER BY JobLevel) AS lead_hc
	, lag(count(hn.EmployeeNumber),1) OVER (PARTITION BY Department ORDER BY JobLevel) AS lag_hc
FROM hr.hr_number hn  
LEFT JOIN hr.hr_cate hc ON hn.EmployeeNumber = hc.EmployeeNumber 
GROUP BY Department, JobLevel
ORDER BY Department
;