윈도우 함수

윈도우 함수는 행을 줄이지 않고, 행마다 순위나 합계 같은 값을 붙입니다. 순위 함수 RANK·DENSE_RANK·ROW_NUMBER는 동점을 다르게 다룹니다.

"OS 과목에서 학생마다 몇 등인가?"를 묻는다고 해 봅시다. 그룹과 집계: GROUP BY·HAVING의 GROUP BY로 묶으면 과목 하나가 한 줄로 접혀서 학생 한 명 한 명의 줄이 사라집니다. 윈도우 함수는 학생 줄을 그대로 두고, 줄마다 순위를 열로 붙입니다. 순위를 매길 때 동점을 어떻게 다루는지가 함수마다 다르고, 시험은 이 차이를 묻습니다.

SELECT 학번,  점수FROM 수강WHERE 과목코드 = 'OS';

SELECT 학번,  점수FROM 수강WHERE 과목코드 = 'OS'ORDER BY 점수 DESC;

SELECT 학번,  점수,  RANK() OVER (    ORDER BY 점수 DESC  ) AS RNKFROM 수강WHERE 과목코드 = 'OS';

SELECT 학번,  점수,  RANK() OVER (…) AS RNK,  DENSE_RANK() OVER (    ORDER BY 점수 DESC  ) AS DRNKFROM 수강WHERE 과목코드 = 'OS';

SELECT 학번,  점수,  RANK() OVER (…) AS RNK,  DENSE_RANK() OVER (…) AS DRNK,  ROW_NUMBER() OVER (    ORDER BY 점수 DESC, 학번  ) AS RNFROM 수강WHERE 과목코드 = 'OS';

SELECT 과목코드,  학번,  점수,  RANK() OVER (    PARTITION BY 과목코드    ORDER BY 점수 DESC  ) AS RNKFROM 수강;

SELECT 학번,  점수,  AVG(점수) OVER (    PARTITION BY 과목코드  ) AS 과목평균FROM 수강WHERE 과목코드 = 'OS';

SELECT 학번,  점수FROM 수강WHERE 과목코드 = 'OS';

그림은 OS 과목을 들은 3행으로 시작합니다. 101과 104가 75점으로 같습니다.

1OVER — 순위를 매길 차례 정하기

OS 과목의 수강은 101 75점, 102 60점, 104 75점입니다. OVER (ORDER BY 점수 DESC)⁠가 순위를 매길 차례를 정합니다. 점수가 높은 차례로 101, 104, 102입니다.

2RANK·DENSE_RANK·ROW_NUMBER — 동점을 다루는 세 가지

RANK는 동점에 같은 순위를 주고, 다음 순위는 동점인 수만큼 건너뜁니다. 1, 1, 3입니다. DENSE_RANK는 동점에 같은 순위를 주고, 다음 순위를 건너뛰지 않습니다. 1, 1, 2입니다. ROW_NUMBER는 동점이어도 서로 다른 번호를 줍니다. 1, 2, 3입니다. 동점 둘 가운데 누가 먼저인지는 정해져 있지 않아서, ORDER BY에 학번을 더해 학번이 작은 101을 먼저 세웁니다.

3PARTITION BY — 칸마다 따로 매기기

PARTITION BY 과목코드는 행을 과목별 칸(파티션, partition)으로 나눕니다. 순위는 칸마다 1부터 다시 매깁니다.

4OVER를 붙인 집계 함수

집계 함수도 OVER와 함께 쓰면 윈도우 함수가 됩니다. AVG(점수)에 과목별 PARTITION BY를 붙이면 OS 3행 모두에 과목 평균 70이 붙고, 행은 3행 그대로입니다.

예제

윈도우 함수는 다음 꼴로 씁니다. 대괄호는 생략할 수 있는 부분입니다.

함수() OVER (
  [PARTITION BY 열]
  ORDER BY 열 [DESC]
)

세 순위 함수

SELECT 학번,
  점수,
  RANK() OVER (
    ORDER BY 점수 DESC
  ) AS RNK,
  DENSE_RANK() OVER (
    ORDER BY 점수 DESC
  ) AS DRNK,
  ROW_NUMBER() OVER (
    ORDER BY 점수 DESC, 학번
  ) AS RN
FROM 수강
WHERE 과목코드 = 'OS'
ORDER BY RN;

60점인 102의 순위가 함수마다 다릅니다. RANK는 앞에 2명이 있어 3, DENSE_RANK는 앞의 순위 1 다음이라 2입니다.

PARTITION BY

DS와 OS 과목만 골라 과목마다 순위를 매깁니다. WHERE가 먼저 행을 거르고, 순위는 남은 행으로 매깁니다.

SELECT 과목코드,
  학번,
  점수,
  RANK() OVER (
    PARTITION BY 과목코드
    ORDER BY 점수 DESC
  ) AS RNK
FROM 수강
WHERE 과목코드 IN ('DS', 'OS')
ORDER BY 과목코드, RNK, 학번;

PARTITION BY를 빼면 7행을 한 줄로 세워 순위를 매깁니다. 그러면 OS의 75점은 3등이 됩니다.

집계 함수와 OVER

SELECT 학번,
  점수,
  AVG(점수) OVER (
    PARTITION BY 과목코드
  ) AS 과목평균
FROM 수강
WHERE 과목코드 = 'OS'
ORDER BY 학번;

GROUP BY 과목코드로 평균을 내면 OS는 한 줄이 됩니다. 윈도우 함수는 3행을 그대로 두고 줄마다 평균을 붙여, 내 점수와 평균을 한 줄에서 견줄 수 있습니다.

시험 포인트

• 필기: 동점 75점 둘과 그다음 점수에 RANK는 1·1·3, DENSE_RANK는 1·1·2, ROW_NUMBER는 1·2·3을 줍니다. PARTITION BY는 칸마다 순위를 따로 매깁니다. 윈도우 함수는 GROUP BY와 달리 행을 줄이지 않습니다.
• 실기: 순위 함수를 쓴 쿼리의 결과를 동점까지 따져 행 차례대로 씁니다. 설명을 보고 RANK·DENSE_RANK·ROW_NUMBER를 가려 쓰고, 빈칸에 OVER·PARTITION BY를 씁니다.

확인 문제

1. 수강 12행을 점수가 높은 차례로 세우면 100·95·90·85·80·75·75·70·65·60·55·50입니다. 70점 행의 RANK()와 DENSE_RANK() 값으로 알맞은 것을 고르세요. 둘 다 OVER (ORDER BY 점수 DESC)⁠로 매깁니다.

• ① RANK 7 · DENSE_RANK 7
• ② RANK 7 · DENSE_RANK 8
• ③ RANK 8 · DENSE_RANK 7
• ④ RANK 8 · DENSE_RANK 8

2. 과목마다 점수로 등수를 매깁니다. (가)~(라)에 맞는 함수나 키워드의 이름을 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 점수가 같은 두 사람에게 같은 등수를 주고, 그다음 사람은 빈 등수를 건너뛴 등수를 받습니다.
• (나) 점수가 같은 두 사람에게 같은 등수를 주고, 그다음 사람의 등수는 빈 자리 없이 이어집니다.
• (다) 점수가 같아도 두 사람에게 서로 다른 값을 줍니다. 1부터 하나씩 늘어 겹치는 값이 없습니다.
• (라) (가)~(다)를 윈도우 함수로 만드는 낱말과, 과목마다 1부터 다시 세게 하는 낱말입니다.

3. 수강 표에 등수를 붙입니다. (1)~(3)을 차례로 풀어 보세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

(1) DS 과목에 106이 85점으로 들어왔습니다. 아래 쿼리의 결과 표에서 (가)~(라)를 채우세요.

INSERT INTO 수강
VALUES (106, 'DS', 85);
SELECT 학번,
  점수,
  RANK() OVER (
    ORDER BY 점수 DESC
  ) AS RNK,
  DENSE_RANK() OVER (
    ORDER BY 점수 DESC
  ) AS DRNK
FROM 수강
WHERE 과목코드 = 'DS'
ORDER BY 점수 DESC, 학번;

(2) WHERE를 빼고 수강 전체에 등수를 매기되, 과목마다 1부터 다시 매기려 합니다. OVER 괄호 안에 어떤 줄을 더할까요?

(3) NW 과목만 보고 학번·점수와 점수 등수를 등수 차례로 조회하는 SELECT 문을 쓰세요. 동점이 생겨도 다음 등수를 건너뛰지 않는 함수를 씁니다. 결과도 쓰세요.

SELECT 학번,
  점수,
  DENSE_RANK() OVER (
    ORDER BY 점수 DESC
  ) AS 등수
FROM 수강
WHERE 과목코드 = 'NW'
ORDER BY 등수;

더 자세히: SQLD의 윈도우 함수