윈도 함수

묶지 않고 셈합니다

2.2 의 GROUP BY 는 여러 행을 한 행으로 줄입니다. 그래서 묶지 않은 열은 낼 수 없었습니다(오류 8120).

윈도 함수는 행을 그대로 두고 집계값을 옆에 덧붙입니다. 각 행이 "내가 속한 무리에서 몇 등인지", "그 무리의 평균이 얼마인지" 를 함께 가질 수 있습니다.

문법은 집계 함수 뒤에 OVER 를 붙이는 것입니다. 괄호가 비어 있으면 결과 전체가 한 무리입니다.

SELECT TOP 5 num, board_num, hit_count,
       AVG(hit_count) OVER ()                        AS 전체평균,
       AVG(hit_count) OVER (PARTITION BY board_num) AS 게시판평균
FROM Board.POSTS
ORDER BY num;

행이 줄지 않았습니다. 각 글이 자기 값을 그대로 가지면서 전체 평균과 제 게시판의 평균을 함께 얻었습니다. PARTITION BY 가 GROUP BY 자리에 해당하지만, 나누기만 하고 줄이지는 않습니다.

순위 매기기 — 셋의 차이

순위를 매기는 함수가 셋인데 동점을 어떻게 다루는지가 다릅니다. 조회수가 겹치는 구간으로 한 번에 봅니다.

SELECT num, hit_count,
       ROW_NUMBER() OVER (ORDER BY hit_count DESC) AS row_num,
       RANK()       OVER (ORDER BY hit_count DESC) AS rank_,
       DENSE_RANK() OVER (ORDER BY hit_count DESC) AS dense_
FROM Board.POSTS
WHERE hit_count BETWEEN 194 AND 198
ORDER BY hit_count DESC, num;

공동 1위가 둘이면 다음은 3위인가 2위인가. 그 답이 다른 것입니다. "공동 1위, 공동 1위, 3위" 가 흔한 순위표라면 RANK, 등급을 매기는 것처럼 번호가 끊기면 안 되면 DENSE_RANK 입니다.

ROW_NUMBER 는 동점이어도 번호를 겹치지 않게 주므로 페이징이나 중복 자료 정리에 사용합니다. 다만 동점일 때 누가 먼저인지는 정해져 있지 않으므로(1.7), 겹치지 않는 열을 ORDER BY 에 함께 적어야 매번 같은 결과가 나옵니다.

WHERE 에서는 사용할 수 없습니다

"조회수 1·2위만 보고 싶다" 를 그대로 적으면 오류입니다.

SELECT num, hit_count FROM Board.POSTS
WHERE ROW_NUMBER() OVER (ORDER BY hit_count DESC) <= 3;

2.3 의 실행 차례를 떠올리십시오. WHERE 는 SELECT 보다 먼저 실행되는데, 윈도 함수는 SELECT 단계에서 계산됩니다. 그 시점에 아직 순위가 없습니다.

CTE 나 파생 테이블로 한 겹 감싸면 됩니다. 안쪽에서 순위를 매기고 바깥에서 거릅니다.

그룹마다 상위 N — 대표 용법

PARTITION BY 와 ROW_NUMBER 를 CTE 로 감싸면 게시판마다 1·2위를 한 번에 뽑을 수 있습니다. TOP 으로는 할 수 없는 일입니다. 그쪽은 결과 전체에서 앞의 몇 개를 자를 뿐입니다(1.7).

WITH 순위매김 AS (
    SELECT board_num, num, title, hit_count,
           ROW_NUMBER() OVER (
               PARTITION BY board_num
               ORDER BY hit_count DESC, num
           ) AS 순위
    FROM Board.POSTS
)
SELECT board_num, 순위, num, title, hit_count
FROM 순위매김
WHERE 순위 <= 2
ORDER BY board_num, 순위;

ORDER BY 에 num 을 덧붙인 것은 동점일 때 차례를 못 박기 위해서입니다. 이것이 없으면 같은 문장이 실행할 때마다 다른 글을 1위로 낼 수 있습니다.

2.4 의 상관 서브쿼리를 대신하기

2.4 실습에서 "자기 게시판 평균보다 조회수가 높은 글" 을 상관 서브쿼리로 적었습니다. 그때는 행마다 안쪽 조회가 한 번씩 돌았습니다. 윈도 함수로는 한 번만 탐색합니다.

WITH T AS (
    SELECT num, board_num, hit_count,
           AVG(hit_count) OVER (PARTITION BY board_num) AS 게시판평균
    FROM Board.POSTS
)
SELECT COUNT(*) AS 평균초과 FROM T WHERE hit_count > 게시판평균;

2.4 의 상관 서브쿼리와 같은 215 입니다. 결과가 같으면서 읽기도 쉽습니다. "게시판 평균을 옆에 붙이고, 그보다 큰 것을 센다" 로 읽힙니다.

누적 합과 앞뒤 행

OVER 안에 ORDER BY 를 두고 범위를 적으면 여기까지의 합을 낼 수 있습니다.

SELECT TOP 5 num, hit_count,
       SUM(hit_count) OVER (
           ORDER BY num
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS 누적
FROM Board.POSTS
ORDER BY num;

UNBOUNDED PRECEDING 은 맨 앞부터, CURRENT ROW 는 지금 행까지입니다. 매출 누계나 잔액 계산에 사용합니다.

LAG 와 LEAD 는 앞뒤 행의 값을 가져옵니다. 값이 얼마나 늘고 줄었는지 볼 때 사용합니다.

SELECT TOP 5 num, hit_count,
       LAG(hit_count)  OVER (ORDER BY num) AS 이전글,
       LEAD(hit_count) OVER (ORDER BY num) AS 다음글
FROM Board.POSTS
ORDER BY num;

첫 행에는 앞 행이 없어 NULL 입니다. 뺄셈에 그대로 넣으면 결과도 NULL 이 되므로(1.9), COALESCE 로 채우거나 그 행을 걸러 내야 합니다.

직접 해보기