SQL 필수 구문 레퍼런스 (Level 4)

코딩 테스트 SQL Level 4를 위한 윈도우 함수·CTE 정리

Programmers SQL Level 4에서 핵심인 윈도우 함수(ROW_NUMBER, RANK, LAG/LEAD), 이동 평균, 누적합, CTE, FIRST_VALUE/LAST_VALUE, WINDOW 절, GROUP_CONCAT, 재귀 CTE 기초를 DS·AIE 트랙 우선순위로 정리한다.

Code Test
저자

Kwangmin Kim

공개

2026년 04월 15일

1 개요

SQL Level 4의 핵심은 윈도우 함수(Window Function)다. GROUP BY가 행을 합쳐 집계하는 것과 달리, 윈도우 함수는 행을 유지한 채 집계값을 추가한다. CTE와 결합하면 복잡한 순위·누적·이동 통계를 읽기 쉬운 쿼리로 표현할 수 있다.

2 DS vs AIE 트랙 우선순위

구문 DS AIE 설명
ROW_NUMBER ★★★ ★★★ 행 번호 부여
RANK / DENSE_RANK ★★★ ★★☆ 순위 (동점 처리 방식 다름)
LAG / LEAD ★★★ ★★☆ 이전/다음 행 참조
SUM OVER (누적합) ★★★ ★★☆ 누적 집계
AVG OVER (이동 평균) ★★★ ★★☆ 이동 집계
NTILE ★★★ ★☆☆ N등분
CTE (WITH) ★★★ ★★★ 복잡 쿼리 분해
PARTITION BY ★★★ ★★★ 그룹 내 윈도우
FIRST_VALUE / LAST_VALUE ★★★ ★★☆ 윈도우 첫·마지막 행 값
NTH_VALUE ★★☆ ★☆☆ 윈도우 N번째 행 값
WINDOW 절 (명명 윈도우) ★★☆ ★★☆ 같은 윈도우 여러 함수 재사용
재귀 CTE (WITH RECURSIVE) ★★★ ★★☆ 시퀀스·계층 탐색
프레임 (ROWS vs RANGE) ★★★ ★★☆ 윈도우 범위 지정
GROUP_CONCAT ★★☆ ★★☆ 그룹 내 값 결합
JSON_EXTRACT ★★☆ ★★☆ JSON 컬럼 값 추출

3 윈도우 함수 기본 구조

-- 윈도우 함수 기본 문법
-- 함수명() OVER (
--     PARTITION BY 그룹_기준열    -- 선택: 그룹 구분 (GROUP BY와 달리 행을 합치지 않음)
--     ORDER BY 정렬_기준열       -- 정렬 순서
--     ROWS BETWEEN ... AND ...   -- 선택: 윈도우 프레임 범위
-- )

-- GROUP BY는 행을 합쳐 집계, 윈도우 함수는 행을 유지하면서 집계
-- GROUP BY 결과: 부서별 1행
-- WINDOW 결과: 원본 행 수 유지, 각 행에 집계값 추가

4 ROW_NUMBER / RANK / DENSE_RANK

-- 입력: scores (name, subject, score)
-- Kim, Math, 90 / Lee, Math, 85 / Park, Math, 90 / Choi, Math, 80

SELECT
    name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores WHERE subject = 'Math';
-- 결과:
-- name | score | row_num | rank | dense_rank
-- -----|-------|---------|------|----------
-- Kim  | 90    | 1       | 1    | 1         ← 동점
-- Park | 90    | 2       | 1    | 1         ← 동점
-- Lee  | 85    | 3       | 3    | 2         ← RANK는 3, DENSE_RANK는 2
-- Choi | 80    | 4       | 4    | 3

-- 차이점:
-- ROW_NUMBER: 동점에도 고유 번호 (1,2,3,4)
-- RANK:       동점이면 같은 순위, 다음 순위는 건너뜀 (1,1,3,4)
-- DENSE_RANK: 동점이면 같은 순위, 다음 순위는 이어감 (1,1,2,3)

-- PARTITION BY: 그룹 내 순위
SELECT
    name, dept, salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;
-- 각 부서(dept)별로 독립적으로 순위를 매긴다

-- 부서 내 Top-1 직원 추출 (ROW_NUMBER 활용)
SELECT name, dept, salary
FROM (
    SELECT name, dept, salary,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn = 1;
-- 각 부서에서 급여가 가장 높은 직원 1명

5 LAG / LEAD

-- 입력: daily_sales (date, revenue)
-- 2024-01-01, 1000 / 2024-01-02, 1200 / 2024-01-03, 900 / 2024-01-04, 1500

SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date)  AS prev_revenue,
    LEAD(revenue) OVER (ORDER BY date) AS next_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS day_change
FROM daily_sales;
-- 결과:
-- date       | revenue | prev_revenue | next_revenue | day_change
-- -----------|---------|--------------|--------------|----------
-- 2024-01-01 | 1000    | NULL         | 1200         | NULL
-- 2024-01-02 | 1200    | 1000         | 900          | +200
-- 2024-01-03 | 900     | 1200         | 1500         | -300
-- 2024-01-04 | 1500    | 900          | NULL         | +600

-- LAG/LEAD 기본값 지정 (NULL 대신 특정 값)
-- LAG(revenue, 1, 0) OVER (ORDER BY date)    -- 이전 값, 없으면 0
-- LEAD(revenue, 2) OVER (ORDER BY date)      -- 2행 뒤의 값

-- 전일 대비 성장률
SELECT
    date, revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY date)) /
          LAG(revenue) OVER (ORDER BY date) * 100, 1) AS growth_pct
FROM daily_sales;
-- 결과: 2024-01-02의 성장률 = (1200-1000)/1000 * 100 = 20.0%

6 누적합 / 이동 평균

-- 누적합 (Running Total)
SELECT
    date, revenue,
    SUM(revenue) OVER (ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumsum
FROM daily_sales;
-- 결과:
-- 2024-01-01: cumsum = 1000
-- 2024-01-02: cumsum = 2200  (1000+1200)
-- 2024-01-03: cumsum = 3100  (1000+1200+900)
-- 2024-01-04: cumsum = 4600  (1000+1200+900+1500)

-- 이동 평균 (Moving Average) — 최근 3일
SELECT
    date, revenue,
    AVG(revenue) OVER (ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM daily_sales;
-- 결과:
-- 2024-01-01: ma3 = 1000.0   (1일만 있음)
-- 2024-01-02: ma3 = 1100.0   ((1000+1200)/2)
-- 2024-01-03: ma3 = 1033.3   ((1000+1200+900)/3)
-- 2024-01-04: ma3 = 1200.0   ((1200+900+1500)/3) ← 슬라이딩 윈도우

-- ROWS vs RANGE 차이
-- ROWS: 물리적 행 수 기준
-- RANGE: 값 범위 기준 (동점 행 모두 포함)

7 FIRST_VALUE / LAST_VALUE / NTH_VALUE

-- FIRST_VALUE: 윈도우 내 첫 번째 행의 값
-- LAST_VALUE: 윈도우 내 마지막 행의 값 (프레임 주의)
-- NTH_VALUE: N번째 행의 값

SELECT
    dept, name, salary,
    FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner,
    LAST_VALUE(name) OVER (
        PARTITION BY dept ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_earner,
    NTH_VALUE(name, 2) OVER (
        PARTITION BY dept ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_earner
FROM employees;

LAST_VALUE 함정: 기본 프레임은 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (현재 행까지). 그래서 LAST_VALUE는 “현재 행까지 중 마지막” = 현재 행 자신을 반환한다. 진짜 마지막을 원하면 프레임을 UNBOUNDED FOLLOWING까지 명시해야 한다.

8 WINDOW 절 — 명명 윈도우 (같은 윈도우 재사용)

-- 같은 PARTITION/ORDER를 여러 함수에서 쓸 때 중복 제거
SELECT
    dept, name, salary,
    RANK()       OVER w AS r,
    DENSE_RANK() OVER w AS dr,
    ROW_NUMBER() OVER w AS rn,
    LAG(salary)  OVER w AS prev_sal
FROM employees
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);
-- 윈도우 정의를 한 번만 쓰고 재사용 — 가독성·유지보수성 향상

9 프레임: ROWS vs RANGE

-- ROWS: 물리적 행 수 기준 (동점 무관)
-- RANGE: 값 범위 기준 (ORDER BY 컬럼 값이 같으면 한 그룹으로 처리)

-- 예시: ORDER BY date, 동일 날짜 2건 있을 때
-- ROWS BETWEEN 1 PRECEDING AND CURRENT ROW:
--   → 바로 직전 1행만 포함 (동일 날짜 구분)
-- RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW:
--   → 날짜 값이 -1일 이상인 모든 행 포함 (동일 날짜 다 포함)

-- 프레임 경계 지시자
-- UNBOUNDED PRECEDING: 파티션 시작부터
-- N PRECEDING:         현재 행에서 N행 앞
-- CURRENT ROW:         현재 행
-- N FOLLOWING:         현재 행에서 N행 뒤
-- UNBOUNDED FOLLOWING: 파티션 끝까지

-- 관용 조합
-- 누적합:     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- 이동평균 3: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-- 중앙 3일:   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
-- 전체 평균:  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

10 재귀 CTE 기초 (Level 4 도입 수준)

-- 1~N 시퀀스 생성 (달력 테이블, 번호표 등)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

-- 날짜 시퀀스
WITH RECURSIVE dates AS (
    SELECT DATE('2024-01-01') AS d
    UNION ALL
    SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM dates WHERE d < '2024-01-31'
)
SELECT d FROM dates;
  • 종료 조건이 없으면 무한 루프. MySQL은 cte_max_recursion_depth (기본 1000)로 방어
  • 심화 패턴 (조직도 탐색 등)은 Level 5에서 다룸

11 GROUP_CONCAT 심화 (윈도우와 결합)

-- 그룹별로 이름 나열 + 순서 지정
SELECT dept,
       GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ' > ') AS ranking
FROM employees
GROUP BY dept;
-- 결과: Sales | Kim > Lee > Park (급여 높은 순)

12 JSON 함수 (MySQL 5.7+)

-- JSON_EXTRACT 또는 -> 연산자
SELECT id, JSON_EXTRACT(payload, '$.user.name') AS user_name FROM events;
SELECT id, payload->'$.user.name' AS user_name FROM events;

-- JSON_UNQUOTE 또는 ->> 연산자 (문자열 따옴표 제거)
SELECT id, payload->>'$.user.name' AS user_name FROM events;

-- JSON_LENGTH, JSON_CONTAINS
SELECT * FROM events WHERE JSON_LENGTH(payload, '$.tags') > 0;
SELECT * FROM events WHERE JSON_CONTAINS(payload, '"premium"', '$.tags');
  • Programmers 코딩 테스트에는 드물지만, 현업에서 이벤트 로그 처리에 자주 등장

13 NTILE — N등분

-- 데이터를 N개 그룹으로 균등 분할
SELECT
    name, score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM exam_scores;
-- 결과: 4분위로 분류 (1=상위 25%, 4=하위 25%)
-- 데이터 분석에서 분위수 기반 레이블링에 자주 활용

14 CTE (Common Table Expression)

-- WITH 절: 쿼리를 이름 붙인 임시 결과로 분해
-- 장점: 가독성 향상, 동일 서브쿼리 반복 방지, 재귀 쿼리 가능

-- 단일 CTE
WITH dept_avg AS (
    SELECT dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept
)
SELECT e.name, e.salary, d.avg_sal,
       e.salary - d.avg_sal AS diff
FROM employees e
JOIN dept_avg d ON e.dept = d.dept
ORDER BY diff DESC;
-- 결과: 각 직원의 급여와 부서 평균의 차이

-- 다중 CTE (순차 참조 가능)
WITH
monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
           SUM(amount) AS total
    FROM orders
    GROUP BY month
),
ranked AS (
    SELECT month, total,
           RANK() OVER (ORDER BY total DESC) AS rnk
    FROM monthly
)
SELECT month, total
FROM ranked
WHERE rnk <= 3;
-- 매출 상위 3개월 조회

-- CTE로 ROW_NUMBER 후 필터링 (서브쿼리보다 가독성 좋음)
WITH ranked_emp AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, dept, salary
FROM ranked_emp
WHERE rn <= 2;    -- 부서별 급여 Top-2 직원

15 유형별 패턴 매핑 (Level 4)

문제 유형 핵심 구문 패턴
그룹 내 순위 RANK + PARTITION BY RANK() OVER (PARTITION BY dept ORDER BY sal)
고유 순번 부여 ROW_NUMBER ROW_NUMBER() OVER (ORDER BY col)
Top-N per group ROW_NUMBER + CTE CTE에서 rn 계산 후 WHERE rn <= N
전일 대비 변화 LAG revenue - LAG(revenue) OVER (ORDER BY date)
누적합 SUM OVER SUM(col) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)
이동 평균 AVG OVER AVG(col) OVER (ORDER BY date ROWS 2 PRECEDING)
복잡 쿼리 분해 CTE WITH 이름 AS (쿼리) SELECT ...
분위수 분류 NTILE NTILE(4) OVER (ORDER BY col)
그룹 최고·최저 FIRST_VALUE / LAST_VALUE FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY y DESC)
같은 윈도우 여러 함수 WINDOW 절 ... OVER w ... WINDOW w AS (PARTITION BY ...)
시퀀스 생성 재귀 CTE WITH RECURSIVE seq AS (...)
이벤트 로그 파싱 JSON_EXTRACT payload->'$.user.id'
고정 기간 슬라이딩 ROWS N PRECEDING ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

16 관련 문서

Subscribe

Enjoy this blog? Get notified of new posts by email: