실행 가능한 SQLite 문서: 윈도우 함수

Q: SQLite의 윈도우 함수란 무엇인가요?

윈도우 함수는 현재 행과 관련된 여러 행을 대상으로 값을 계산하지만, GROUP BY처럼 행을 합쳐 버리지는 않습니다. ROW_NUMBER(), RANK(), SUM(), LAG() 같은 함수에 OVER (...) 절을 붙여서 어떤 범위(윈도우)를 기준으로 계산할지 지정하죠. 입력된 모든 행은 그대로 결과에 남고, 거기에 계산된 컬럼이 하나 더 붙는다고 보면 됩니다.

Q: SQLite에서 RANK와 DENSE_RANK는 뭐가 다른가요?

둘 다 ORDER BY 기준으로 순위를 매기지만, 동점 처리 방식이 다릅니다. RANK()는 동점이 생기면 그다음 순위를 건너뜁니다. 예를 들어 1위가 두 명이면 그다음은 2위가 아니라 3위가 되죠. 반면 DENSERANK()는 건너뛰지 않고 다음 행에 2를 줍니다. 순위를 연속된 번호로 매기고 싶다면 DENSERANK(), 동점으로 인한 간격을 그대로 보여주고 싶다면 RANK()를 쓰면 됩니다.

Q: SQLite에서 누적 합계는 어떻게 구하나요?

SUM(컬럼) OVER (ORDER BY ...) 형태로 윈도우 프레임과 함께 사용하면 됩니다. OVER 안에 ORDER BY만 적으면 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 잡혀서 자연스럽게 누적 합계가 됩니다. 그룹별로 누적 합계를 새로 시작하고 싶다면 PARTITION BY를 추가해 주세요.

윈도우 함수는 행을 줄이지 않고 컬럼을 더한다

GROUP BY는 여러 행을 하나로 묶어버린다. 반면 SQLite 윈도우 함수는 동작 방식이 다르다. 관련된 행들의 집합에 대해 값을 계산하면서도 입력된 모든 행을 그대로 결과에 남겨둔다. 즉, 행 단위 상세 데이터와 집계값을 한 화면에 나란히 볼 수 있다는 뜻이다.

형태는 늘 똑같다. 함수 뒤에 OVER (...)를 붙이면 된다.

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

total_all 컬럼을 보면 전체 합계가 모든 행에 똑같이 반복되어 표시됩니다. 원본 행은 그대로 살아 있죠. SELECT SUM(amount) FROM sales와 비교해 보세요. 결과 값은 같지만, 이 쿼리는 단 한 줄만 돌려줍니다. 윈도우 함수를 쓰면 두 가지 관점을 한 번에 볼 수 있는 셈이죠.

PARTITION BY: 그룹별로 집계하기

OVER ()를 비워 두면 테이블 전체를 대상으로 집계합니다. 여기에 PARTITION BY를 더하면 GROUP BY처럼 그룹 단위로 집계할 수 있는데, 역시나 행을 합치지 않고 그대로 유지한 채 동작합니다.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

각 행마다 해당 부서의 합계와 그 안에서 차지하는 비중이 함께 표시됩니다. 일반 GROUP BY로 처리하면 직원별 상세 정보가 사라져 버리죠. 바로 이 점이 윈도우 함수의 가장 큰 매력입니다. 한 번의 쿼리로 상세 데이터와 집계 결과를 동시에 얻을 수 있다는 것.

순위 매기기: ROW_NUMBER, RANK, DENSE_RANK

순위 함수들은 OVER 안에 지정한 ORDER BY를 기준으로 행에 번호를 매깁니다. 세 함수의 차이는 동점(tie) 처리 방식에 있습니다.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

출력 결과를 하나씩 살펴보겠습니다.

ROW_NUMBER()는 항상 유일한 값을 매깁니다. 동점이 있어도 임의로 순서를 정해 번호를 부여하죠. 행마다 안정적이면서 중복 없는 번호가 필요할 때 쓰면 됩니다.
RANK()는 동점인 행에 같은 순위를 주되, 다음 번호를 건너뜁니다. 두 명이 1위로 동점이면 그다음은 3위가 되는 식이죠.
DENSE_RANK()도 동점은 같은 순위로 묶지만, 번호를 건너뛰지 않습니다. 다음 순위는 2가 됩니다.

SQLite RANK DENSE_RANK 차이가 헷갈렸다면 이 세 가지만 기억해 두면 충분합니다.

"그룹별 상위 N개"를 뽑고 싶다면 순위 함수와 PARTITION BY를 함께 쓰고, 바깥쪽 쿼리에서 필터링해야 합니다. WHERE 절에서는 윈도우 함수를 직접 참조할 수 없기 때문이죠.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

지역별 상위 2명의 고소득자를 뽑는 예시입니다.

LAG와 LEAD: 이웃한 행 들여다보기

LAG(col)은 윈도우 안에서 바로 이전 행의 col 값을 가져오고, LEAD(col)은 반대로 다음 행의 값을 가져옵니다. 시간 흐름에 따른 변화량을 구할 때 이만한 함수가 없죠.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

첫 번째 행의 yesterday는 NULL입니다. 그 앞에 비교할 행이 없으니 당연하죠. 기본값을 지정하고 싶다면 LAG(celsius, 1, celsius) OVER (ORDER BY day)처럼 쓰면 되는데, 이전 행이 없을 때 오늘 값을 그대로 사용합니다.

LEAD는 LAG의 반대 방향입니다. 두 함수를 PARTITION BY와 함께 쓰면 그룹별 시퀀스를 만들 수 있어요. 예를 들면 지역별로 이번 달 매출을 지난달과 비교하는 식이죠.

윈도우 프레임으로 누적 합계 구하기

OVER 안에 ORDER BY를 넣어 보세요. 그 순간부터 SUM, AVG, COUNT 같은 집계 함수가 누적 방식으로 계산되기 시작합니다:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

여기서 두 가지를 짚고 넘어가야 합니다.

SUM(amount) OVER (ORDER BY day)는 누적 합계입니다. ORDER BY만 쓰고 프레임을 명시하지 않으면 기본 프레임은 "윈도우의 시작부터 현재 행까지"가 됩니다.
두 번째 컬럼은 프레임을 명시적으로 지정했습니다: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. 3행짜리 슬라이딩 윈도우, 즉 이동 평균이죠.

프레임을 이해하는 핵심은 이렇습니다. 모든 윈도우 함수는 현재 행을 기준으로 정의된 프레임 위에서 계산된다는 것. 자주 쓰이는 프레임은 다음과 같습니다.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — 누적 합계 (암묵적 기본값).
ROWS BETWEEN N PRECEDING AND CURRENT ROW — 직전 N개를 포함하는 트레일링 윈도우.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — 파티션 전체.

ROWS는 물리적 행 개수로 셉니다. 이와 달리 RANGE는 값 단위로 묶기 때문에, ORDER BY 컬럼에 동일한 값(타이)이 여럿 있을 때 이들을 한 묶음으로 처리하고 싶다면 유용합니다.

FIRST_VALUE, LAST_VALUE, NTILE

알아두면 좋은 윈도우 함수가 몇 가지 더 있습니다.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

FIRST_VALUE와 LAST_VALUE는 프레임 안에서 첫 번째 값 또는 마지막 값을 돌려줍니다. LAST_VALUE를 쓸 때는 프레임 범위를 꼭 확인하세요. 기본 프레임이 CURRENT ROW에서 끝나기 때문에, 파티션의 진짜 마지막 값을 얻으려면 보통 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING까지 명시해줘야 합니다.
NTILE(n)은 행들을 n개의 거의 균등한 버킷으로 나눠줍니다. 사분위수나 백분위수 계산, A/B 테스트식 그룹 분할에 유용하죠.

WINDOW 절로 윈도우에 이름 붙이기

여러 컬럼에서 똑같은 OVER (...) 구문을 반복해서 쓰다 보면 금세 지저분해집니다. 이럴 때 SQLite에서는 윈도우를 한 번만 정의해두고 이름으로 재사용할 수 있습니다:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

같은 결과를 얻으면서도 쿼리는 훨씬 깔끔해집니다. WINDOW 절은 WHERE/GROUP BY/HAVING 다음, ORDER BY 앞에 위치합니다.

SQLite 윈도우 함수 vs GROUP BY 차이

둘 다 집계를 다루긴 하지만, 풀어내는 문제가 다릅니다.

GROUP BY는 줄입니다. 그룹마다 한 행만 남죠. 요약 결과만 필요할 때 사용합니다.
윈도우 함수는 보존합니다. 입력 행이 모두 그대로 남고, 계산된 컬럼이 옆에 추가됩니다.

GROUP BY로 집계한 다음 그 결과를 다시 원본 테이블에 조인하고 있다면, 십중팔구 윈도우 함수 한 방으로 끝낼 수 있다는 신호입니다.

자주 만나는 함정 몇 가지

WHERE에서는 윈도우 함수를 참조할 수 없습니다. 필터링이 윈도우 계산보다 먼저 일어나기 때문이죠. 서브쿼리나 CTE로 한 번 감싼 뒤 바깥쪽에서 필터링해야 합니다.
암묵적 프레임이 발목을 잡습니다. SUM(x) OVER (ORDER BY y)는 누적 합계가 됩니다. 기본 프레임이 RANGE UNBOUNDED PRECEDING이기 때문이죠. 파티션 전체 합계가 필요하다면 ORDER BY 없이 OVER (PARTITION BY ...)만 쓰거나, 프레임을 명시적으로 지정해야 합니다.
LAST_VALUE는 처음 쓰면 누구나 당황합니다. 기본 프레임이 현재 행에서 끝나기 때문에, 파티션의 마지막 값이 아니라 현재 행의 값을 돌려줍니다. 프레임을 직접 덮어써 줘야 합니다.
윈도우 함수는 SQLite 3.25 이상이 필요합니다 (2018년 릴리스). 요즘 설치된 SQLite라면 거의 다 지원하지만, 일부 임베디드 환경은 버전이 뒤처져 있을 수 있으니 주의하세요.

다음: 생성 컬럼(Generated Columns)

윈도우 함수가 _쿼리 시점_에 값을 계산한다면, 다음 페이지에서 다룰 생성 컬럼은 _저장 시점_에 값을 계산합니다. 컬럼 값을 표현식으로 정의해 두면, 원본 데이터가 바뀔 때마다 자동으로 갱신되는 방식이죠.

자주 묻는 질문

SQLite의 윈도우 함수란 무엇인가요?

윈도우 함수는 현재 행과 관련된 여러 행을 대상으로 값을 계산하지만, GROUP BY처럼 행을 합쳐 버리지는 않습니다. ROW_NUMBER(), RANK(), SUM(), LAG() 같은 함수에 OVER (...) 절을 붙여서 어떤 범위(윈도우)를 기준으로 계산할지 지정하죠. 입력된 모든 행은 그대로 결과에 남고, 거기에 계산된 컬럼이 하나 더 붙는다고 보면 됩니다.

SQLite에서 RANK와 DENSE_RANK는 뭐가 다른가요?

둘 다 ORDER BY 기준으로 순위를 매기지만, 동점 처리 방식이 다릅니다. RANK()는 동점이 생기면 그다음 순위를 건너뜁니다. 예를 들어 1위가 두 명이면 그다음은 2위가 아니라 3위가 되죠. 반면 DENSE_RANK()는 건너뛰지 않고 다음 행에 2를 줍니다. 순위를 연속된 번호로 매기고 싶다면 DENSE_RANK(), 동점으로 인한 간격을 그대로 보여주고 싶다면 RANK()를 쓰면 됩니다.

SQLite에서 누적 합계는 어떻게 구하나요?

SUM(컬럼) OVER (ORDER BY ...) 형태로 윈도우 프레임과 함께 사용하면 됩니다. OVER 안에 ORDER BY만 적으면 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 잡혀서 자연스럽게 누적 합계가 됩니다. 그룹별로 누적 합계를 새로 시작하고 싶다면 PARTITION BY를 추가해 주세요.

SQLite 윈도우 함수: OVER, PARTITION BY, 프레임 정리