윈도우 함수

윈도우 함수

• 행고 행간의 관계를 쉽게 정의해주는 함수
• 집계함수(AVG, SUM, COUNT), 윈도우 함수 전용 함수(ROW NUMBER, LEAD, LAG)
• 순위, 합계, 평균, 행 위치 등을 조작

 

집계 함수

• 뒤에 OVER()와 함께 사용하면 윈도우 함수, 안사용하면 집계함수
• AVG()
• COUNT()
• MAX()
• MIN()
• SUM()

 

윈도우 함수 전용 함수

• DENSE_RANK()
• RANK()
• ROW_NUMBER()
• LAG()
• LEAD()

 

기본 형식

• 함수(컬럼) OVER (PARTITION BY 컬럼 ORDER BY 컬럼)
• PARTITION 
  • 윈도우 함수에서 group by처럼 쓰이는데, 그룹화의 기준이 되는 컬럼을 파악할 수 있다. 

ex )  SUM(profit) OVER (PARTITION BY country) 

나라 별 profit 합 구하기  

 

ex) SUM(kg) OVER (ORDER BY Line)

Line 컬럼 순으로 kg 누적합 구하기 

 

ex) MAX(Salary) OVER (PARTITION BY DepartmentId) AS MaxSalary

 부서별 최고 봉급 구하기

 

집계 함수 SUM

• SUM(컬럼) OVER()
  • 모든 ROW 집합을 하나의 PARTITION으로 간주
  • 윈도우 함수에서는 압축없이 모든 행을 보여주면서 파티션 별로 조회된 결과를 컬럼에 저장

SELECT
year, country, product, profit,
SUM(profit) OVER() AS total_profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;

 

ROW_NUMBER(), RANK(), DENSE_RANK()

• 윈도우 전용 함수
• 순위를 구하기 위해 사용
• ROW_NUMBER()
  • 중복값을 인식하지 못하고 순위를 부여
• RANK()
  • 중복값이 오는 경우 같은 순위를 부여
• DENSE_RANK()
  • 중복 값에 대해 같은 순위를 부여하지만 중복값에 대한 처리 이후

SELECT value
       ,ROW_NUMBER() OVER (ORDER BY value) AS 'row_number'
       ,RANK() OVER (ORDER BY value) AS 'rank'
       ,DENSE_RANK() OVER (ORDER BY value) AS 'dense_rank'
FROM tmp

 

LAG(), LEAD()

• 2개의 행 간의 차이를 구할 때 사용
• LAG()은 앞 행에서 값을 반환, LEAD()는 뒷 행에서 값을 반환

대륙 별 가장 높은 값과 본인의 인구수 차이를 계산

SELECT
	C.CODE,
    C.NAME,
    C.CONTINENT, # 대륙
    C.POPULATION, # 인구
    # 인구수 내림차순으로 정렬한 대륙별 이전 대륙의 인구수를 VALUE 컬럼으로 생성
    LAG(POPULATION) OVER(PARTITION BY CONTINENT ORDER BY POPULATION DESC) AS VALUE
    # 이전 대륙과 현재 대륙간 인구수 차이
    LAG(POPULATION) OVER(PARTITION BY CONTINENT ORDER BY POPULATION DESC) - C.POPULATION AS DIFFERNCE
FROM
	COUNTRY AS C