[SQL] 윈도우 함수 총정리

윈도우함수는 행과 행간의 관계에서 다양한 연산 처리를 할 수 있는 함수이다. 분석함수로 알려져 있으며 일반적으로 윈도우함수는 일반함수와 달리 중첩하여 호출 될 수는 없다.

 

윈도우 함수

 

윈도우 함수의 종류

• 순위관련함수
• 집계관련함수
• 행순서관련함수
• 그룹내 비율관련함수

 

순위관련함수

RANK() : 그룹내 순위를 알려줌 (동일값 동일 순위) ex) 1위, 2위, 2위, 3위

DENSE_RANK() : 그룹내 순위를 알려줌 (동일값 동일 순위) ex) 1위, 2위, 2위, 3위

ROW_NUMBER : 동일값이라도 무조건 순위를 정함 말그대로 행번호를 지정해 줌

 

사용 예시

RANK() over (partition by 부서 ORDER BY 생년월일) : 부서별로 묶어서 생년월일 순위를 알 수 있음

 

 

예제 1

SELECT
    country,
    customerCount,
    RANK() OVER (ORDER BY customerCount DESC) AS customerRank
FROM (
    SELECT
        country,
        COUNT(*) AS customerCount
    FROM
        s_customers
    GROUP BY
        country
) AS counts
ORDER BY
    customerRank ASC,
    country ASC;

 

 

 

집계관련함수

집계관련함수는 SUM(), AVG() 등 이미 사용하고 있던 함수들이다. 하지만 윈도우 함수의 PARTITION BY 등의 기능을 통해 좀 더 세부적이고 자세하게 집계관련함수를 사용할 수 있다.

 

 

예제2

 

SELECT productLine, name, quantityInStock, 
    SUM(quantityInStock) OVER
    (
        PARTITION BY productLine
        ORDER BY quantityInStock DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS noOfQuantitys_sum
FROM s_products
ORDER BY productLine, quantityInStock desc

 

여기서 PARTITION BY productLine은 productLine 별로 총계를 보겠다는 것이다.

RANGE는 논리적인 값 기준으로 행을 구분한다 이에 값이 같다면 같은 값으로 처리한다. RANGE 외에 ROW를 사용하면 물리적으로 행을 구분할 수 있다.

BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING은 UNBOUNDED PRECEDING(가장 윗 경계)

와 UNBOUNDED FOLLOWING(가장 아랫 경계) 모두의 범위에서 값을 집계 한다는 것인데, 같은 productLine 전체의 집계를 하겠다는 뜻이다.

예제 2 결과

 

 

 

 

 

 

예제 3

예제 3

SELECT productLine, name, quantityInStock, 
    SUM(quantityInStock) OVER
    (
        PARTITION BY productLine
        ORDER BY quantityInStock DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS noOfQuantitys_sum
FROM s_products
ORDER BY productLine, quantityInStock desc

이번엔 productLine별로 전체적으로 집계하는 것이 아닌, 누적 집계를 보는 것이기에 UNBOUNDED PRECEDING부터 CURRENT ROW(현재행)까지 범위를 설정하면 된다.

 

 

 

WITH temp AS
(
	SELECT	CONCAT(firstName, ' ', lastName) AS 성명, 
		jobTitle AS 직책,
		COALESCE(SUM(amount),0) AS 매출액
	FROM 	s_employees 
		LEFT JOIN s_customers ON employeeId = salesRepId		/* 담당 고객이 없는 직원 포함 */
		LEFT JOIN s_payments USING (customerId)
	GROUP	BY employeeId
)
SELECT	성명, 직책, 매출액,
	CONCAT(매출액 - 100000, '~', 매출액 + 100000) AS '매출액 범위',
	COUNT(매출액) OVER 
        (
		ORDER BY 매출액 DESC
		RANGE BETWEEN 100000 PRECEDING AND 100000 FOLLOWING
	) AS '범위내 직원수'
FROM 	temp;

 

매출액 오차 100000 범위 내의 인원수를 구하기 위해 WINDOW 함수를 통해 SUM()함수를 적용했는데, 이때 논리적인 값의 범위를 적용해야해서 RANGE BETWEEN을 사용했다. 이때, ROW BETWEEN을 사용하면 오류가 난다.

 

 

 

3. 행순서관련함수

FIRST_VALUE() : 파티션의 프레임 내에서 첫번째 행의 FIRST_VALUE 기준 컬럼 값

LAST_VALUE() : 파티션의 프레임 내에서 마지막 행의 LAST_VALUE 기준 컬럼 값

 

LAG() : 파티션 내에서 현재 행에 뒤쳐진(lagging)행, 즉 앞 행의 LAG 기준 컬럼 값

LEAD() : 파티션 내에서 현재 행에 앞선(leading) 행, 즉 뒤 행의 LEAD 기준 컬럼 값

 

 

 

4. 그룹내 비율관련함수

PERCENT_RANK() : 전체 투플 개수에 대한, 순위의 누적 백분율 (0~1)

CUME_DIST() : 순위를 기준으로 한, 투플 개수의 누적 백분율 (0~1)

 

PERCENT_RANK() 와 CUME_DIST()의 차이 : 전자는 전체 개수를 기준으로 하기에 1위는 0으로 출력 되지만 후자는 순위를 기준으로 하기에 1위의 데이터가 (1/전체 투플 개수 +1) 값으로 0외의 수가 출력 된다.

 

NTILE() : 파티션을 N개 버킷으로 분할한다. ex. 5개의 데이터가 있다면 1/1/2/2/3으로 분리