SQL 윈도 함수와 CTE: 실무 보고서 쿼리로 익히기

Q: 누적 합계가 같은 날짜 행에서 한꺼번에 튀는 이유는?

ORDER BY 만 있고 프레임 절이 없으면 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 이고, ORDER BY 값이 같은 행(피어)을 모두 포함합니다. 그래서 같은 날의 행이 전부 그날의 최종 합계를 보여 줍니다. 유일한 정렬 키를 더하고 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 를 지정합니다.

Q: 재귀 CTE 가 끝나지 않는 걸 어떻게 막나요?

데이터에 순환(자기 조상이 된 카테고리)이 있으면 UNION ALL 재귀는 끝나지 않습니다. 예제는 5초 뒤에도 돌고 있어서 kill 했습니다. 재귀 쪽 WHERE 에 깊이 상한을 두거나, 지나온 경로를 들고 다니며 이미 나온 id 를 건너뛰거나, 시험할 때는 LIMIT 을 붙입니다.

ROW_NUMBER·RANK·LAG·누적 합계, 그룹별 상위 N개, 재귀 CTE, JSON 함수를 SQLite 에서 실행. 프레임과 NULL 함정, 상관 서브쿼리와의 속도 비교까지 실측.

t.laboratory 작성일 2026년 11월 12일

시리즈: SQL 강좌 6/6 (고급). JOIN 과 GROUP BY 는 편하게 쓰지만 순위, 전월 대비, 누적 합계를 낼 때는 아직 데이터를 엑셀이나 구글 시트로 옮겨 계산하는 분을 위한 글입니다. 다 읽으면 그것들을 SQL 한 번으로 쓰고, 재귀 CTE 로 트리를 따라가고, JSON 열에서 값을 꺼내고, 보고서를 "조용히 틀리게" 만드는 세 가지 함정을 피할 수 있습니다.

보고서 쿼리는 SQL 이 가장 빛나는 곳이면서 틀린 답이 가장 안 보이는 곳입니다. 조금 어긋난 누적 합계나, "데이터 없음"이어야 할 자리에 0.0% 가 찍힌 전월 대비 열은 어드민 대시보드 위에서 그럴듯해 보입니다. 사내 어드민 페이지, 주간 KPI 리포트 메일, 데이터 추출 배치를 만드는 분이라면 이 글이 맞습니다. 아래 쿼리는 모두 실행했고 출력은 찍힌 그대로 붙였습니다. 그중 하나의 함정은 출력이 너무 깔끔해 보여서 알아챈 것입니다.

실행 환경: SQLite 3.51.0 CLI (-header -column 출력), 시간 측정에는 Python 3.9.6 의 sqlite3 모듈(포함된 SQLite 도 3.51.0), Apple Silicon Mac (M1 Pro), macOS.

데이터, 그리고 1분 만에 보는 윈도 함수

세 매장의 10월 일별 매출입니다. 매출이 없는 날은 행 자체가 없고, 이것이 뒤에서 중요해집니다. shop.sql 로 저장하세요. (매장 이름은 예제일 뿐이니 강남·홍대·해운대로 바꿔 읽어도 됩니다.)

CREATE TABLE sales (
  id      INTEGER PRIMARY KEY,
  shop    TEXT    NOT NULL,
  day     TEXT    NOT NULL,      -- 'YYYY-MM-DD'
  amount  INTEGER NOT NULL
);
INSERT INTO sales (shop, day, amount) VALUES
 ('Shibuya','2026-10-01',120),('Shibuya','2026-10-02', 80),('Shibuya','2026-10-04',150),
 ('Shibuya','2026-10-05',150),('Shibuya','2026-10-06', 90),
 ('Umeda',  '2026-10-01',200),('Umeda',  '2026-10-02',200),('Umeda',  '2026-10-03', 60),
 ('Umeda',  '2026-10-05',110),
 ('Tenjin', '2026-10-02', 70),('Tenjin', '2026-10-03', 95);

윈도 함수는 현재 행과 관련된 행들의 묶음(윈도)에서 값을 계산하지만, GROUP BY 와 달리 행을 하나로 합치지 않고 모든 행을 남깁니다. PARTITION BY 로 그룹을 나누고 ORDER BY 로 그룹 안을 정렬합니다. 세 순위 함수는 동점 처리만 다릅니다.

SELECT shop, day, amount,
       ROW_NUMBER() OVER w AS row_number,
       RANK()       OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank
FROM sales
WINDOW w AS (PARTITION BY shop ORDER BY amount DESC)
ORDER BY shop, row_number;

shop     day         amount  row_number  rank  dense_rank
-------  ----------  ------  ----------  ----  ----------
Shibuya  2026-10-04  150     1           1     1
Shibuya  2026-10-05  150     2           1     1
Shibuya  2026-10-01  120     3           3     2
Shibuya  2026-10-06  90      4           4     3
Shibuya  2026-10-02  80      5           5     4
Tenjin   2026-10-03  95      1           1     1
Tenjin   2026-10-02  70      2           2     2
Umeda    2026-10-01  200     1           1     1
Umeda    2026-10-02  200     2           1     1
Umeda    2026-10-05  110     3           3     2
Umeda    2026-10-03  60      4           4     3

ROW_NUMBER 는 언제나 1, 2, 3. RANK 는 동점에 같은 번호를 주고 다음을 건너뛰며(1, 1, 3), DENSE_RANK 는 건너뛰지 않습니다(1, 1, 2). 그리고 150 두 개 중 어느 쪽이 1번이 될지는 이 쿼리로는 정해지지 않습니다. 결과를 고정하려면 , day 같은 정렬 키를 더합니다.

그룹별 상위 N개

"매장별 매출 상위 2일"은 단골 질문입니다. 곧이곧대로 쓰면 실패합니다.

sqlite> SELECT shop, day, amount FROM sales WHERE ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC) <= 2;
Parse error near line 13: misuse of window function ROW_NUMBER()
  SELECT shop, day, amount FROM sales WHERE ROW_NUMBER() OVER (PARTITION BY shop
                              error here ---^

윈도 함수는 WHERE 다음에 계산되므로 WHERE 안에서 쓸 수 없습니다. CTE(공통 테이블 식, WITH 로 이름 붙인 서브쿼리) 안에서 계산하고 바깥에서 거릅니다.

WITH ranked AS (
  SELECT shop, day, amount,
         ROW_NUMBER() OVER (PARTITION BY shop ORDER BY amount DESC, day) AS rn,
         RANK()       OVER (PARTITION BY shop ORDER BY amount DESC)      AS rnk
  FROM sales
)
SELECT shop, day, amount, rn, rnk FROM ranked WHERE rn <= 2 ORDER BY shop, rn;

shop     day         amount  rn  rnk
-------  ----------  ------  --  ---
Shibuya  2026-10-04  150     1   1
Shibuya  2026-10-05  150     2   1
Tenjin   2026-10-03  95      1   1
Tenjin   2026-10-02  70      2   2
Umeda    2026-10-01  200     1   1
Umeda    2026-10-02  200     2   1

"정확히 N개"는 rn 으로, "N개와 마지막과 동점인 행 전부"는 rnk 로 거릅니다.

흔한 실수: 같은 날에서 튀는 누적 합계

실수. 전사 일별 누적 합계를 자연스럽게 SUM(amount) OVER (ORDER BY day) 로 썼다.

실제로 일어나는 일. 같은 날의 행이 모두 같은 숫자를 보여 주고, 아래 행의 몫까지 이미 들어가 있습니다.

SELECT day, shop, amount,
       SUM(amount) OVER (ORDER BY day)                     AS default_frame,
       SUM(amount) OVER (ORDER BY day, id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM sales ORDER BY day, id;

day         shop     amount  default_frame  rows_frame
----------  -------  ------  -------------  ----------
2026-10-01  Shibuya  120     320            120
2026-10-01  Umeda    200     320            320
2026-10-02  Shibuya  80      670            400
2026-10-02  Umeda    200     670            600
2026-10-02  Tenjin   70      670            670
2026-10-03  Umeda    60      825            730
2026-10-03  Tenjin   95      825            825
2026-10-04  Shibuya  150     975            975
2026-10-05  Shibuya  150     1235           1125
2026-10-05  Umeda    110     1235           1235
2026-10-06  Shibuya  90      1325           1325

프레임은 집계 함수가 윈도 중 어느 범위를 보는지입니다. ORDER BY 가 있고 프레임 절이 없으면 기본값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 이고, RANGE 는 피어(ORDER BY 값이 같은 행)를 모두 포함합니다. 해결: 유일한 정렬 키와 ROWS 를 붙입니다(rows_frame 열). 두 열 모두 마지막은 1,325 라서 최종 합계 검증으로는 이 오류를 잡을 수 없습니다. 틀린 것은 중간 행뿐입니다.

LAG 와 빠진 날, 재귀 CTE 로 달력 만들기

LAG(x) 는 윈도 안 바로 앞 행의 x 를 돌려줍니다(LEAD 는 바로 뒤). Shibuya 매장의 전일 대비입니다.

shop     day         amount  prev_amount  prev_day    diff
-------  ----------  ------  -----------  ----------  ----
Shibuya  2026-10-01  120
Shibuya  2026-10-02  80      120          2026-10-01  -40
Shibuya  2026-10-04  150     80           2026-10-02  70
Shibuya  2026-10-05  150     150          2026-10-04  0
Shibuya  2026-10-06  90      150          2026-10-05  -60

10-04 행이 10-02 와 비교하고 있습니다. "바로 앞 행"은 "전날"이 아닙니다. 고치려면 재귀 CTE(자기 자신을 참조하는 CTE)로 모든 날짜를 만듭니다. 시작 행, UNION ALL, 그리고 WHERE 가 멈출 때까지 직전 결과에 대해 반복되는 단계로 이루어집니다.

WITH RECURSIVE cal(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM cal WHERE day < '2026-10-06'
),
daily AS (
  SELECT cal.day, COALESCE(s.amount, 0) AS amount
  FROM cal LEFT JOIN sales s ON s.day = cal.day AND s.shop = 'Shibuya'
)
SELECT day, amount,
       LAG(amount) OVER (ORDER BY day) AS prev_day_amount,
       ROUND(AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3d
FROM daily;

day         amount  prev_day_amount  avg_3d
----------  ------  ---------------  ------
2026-10-01  120                      120.0
2026-10-02  80      120              100.0
2026-10-03  0       80               66.7
2026-10-04  150     0                76.7
2026-10-05  150     150              100.0
2026-10-06  90      150              130.0

이제 10-03 이 0 으로 나타나고, 3일 이동 평균이 진짜 3일 평균이 됩니다(처음 두 행은 더 적은 날수의 평균입니다. 보고서에 보일지 미리 정해 두세요).

트리용 재귀 CTE, 그리고 끝나지 않는 쿼리

parent_id 로 저장한 카테고리 트리도 일상적인 쓰임새입니다.

CREATE TABLE categories (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT NOT NULL);
INSERT INTO categories VALUES
 (1, NULL, 'Electronics'), (2, 1, 'Computers'), (3, 2, 'Laptops'), (4, 2, 'Desktops'),
 (5, 1, 'Audio'), (6, 5, 'Headphones'), (7, NULL, 'Books'), (8, 7, 'Programming');
WITH RECURSIVE tree(id, name, depth, path) AS (
  SELECT id, name, 0, name FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.depth + 1, t.path || ' > ' || c.name
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT substr('        ', 1, depth * 2) || name AS indented, depth, path FROM tree ORDER BY path;

indented        depth  path
--------------  -----  ----------------------------------
Books           0      Books
  Programming   1      Books > Programming
Electronics     0      Electronics
  Audio         1      Electronics > Audio
    Headphones  2      Electronics > Audio > Headphones
  Computers     1      Electronics > Computers
    Desktops    2      Electronics > Computers > Desktops
    Laptops     2      Electronics > Computers > Laptops

그런데 누군가 어드민에서 실수로 "Electronics" 의 부모를 "Laptops" 로 바꿨다고 합시다(UPDATE categories SET parent_id = 3 WHERE id = 1). 1 → 2 → 3 → 1 순환이 생기고, Computers 에서 시작하는 같은 재귀는 끝나지 않습니다. 5초 돌린 뒤 kill 했습니다.

  PID STAT      TIME    RSS COMMAND
37366 RN     0:04.95   2896 sqlite3 :memory:
exit=143

CPU 코어 하나를 꽉 쓰면서 메모리는 거의 안 쓰므로, "응답이 안 온다" 말고는 아무 경고가 없습니다. 종료 코드 143 은 128 + 15(SIGTERM)입니다. 세 가지 방어책을 실행했습니다.

-- 1. LIMIT outside (for testing): returned 100000 rows, max depth 50000, in 0.040 s
-- 2. depth limit in the recursive step: UNION + WHERE t.depth < 20  -> 40 rows, max depth 20
-- 3. remember the path, skip ids already on it:
WITH RECURSIVE tree(id, path) AS (
  SELECT 2, '/2/'
  UNION ALL
  SELECT c.id, t.path || c.id || '/' FROM categories c JOIN tree t ON c.parent_id = t.id
  WHERE instr(t.path, '/' || c.id || '/') = 0
)
SELECT id, path FROM tree;
id  path
--  -----------
2   /2/
3   /2/3/
4   /2/4/
1   /2/3/1/
5   /2/3/1/5/
6   /2/3/1/5/6/

운영에 남길 것은 3번입니다. 각 노드를 한 번씩만 돌려주고 순환이 경로에 드러납니다.

보고서에서 쓰는 JSON 열

외부 API 에서 받은 주문을 JSON 그대로 저장하는 일은 흔합니다. SQLite 는 바로 읽을 수 있습니다. -> 는 JSON 을, ->> 는 SQL 값을 돌려주고, json_each 는 배열을 행으로 펼칩니다.

CREATE TABLE orders (id INTEGER PRIMARY KEY, shop TEXT, payload TEXT);
INSERT INTO orders VALUES
 (1, 'Shibuya', '{"customer":"c-01","items":[{"sku":"A1","qty":2,"price":300},{"sku":"B7","qty":1,"price":1200}],"coupon":null}'),
 (2, 'Umeda',   '{"customer":"c-02","items":[{"sku":"A1","qty":1,"price":300}],"coupon":"OCT10"}'),
 (3, 'Shibuya', '{"customer":"c-01","items":[{"sku":"C3","qty":3,"price":450}]}');
SELECT id, payload ->> '$.customer' AS customer, payload -> '$.coupon' AS coupon_json,
       payload ->> '$.coupon' AS coupon_sql, json_array_length(payload, '$.items') AS n_items
FROM orders;
SELECT i.value ->> 'sku' AS sku, SUM((i.value ->> 'qty') * (i.value ->> 'price')) AS revenue,
       json_group_array(DISTINCT o.shop) AS shops
FROM orders o, json_each(o.payload, '$.items') AS i GROUP BY sku ORDER BY revenue DESC;

id  customer  coupon_json  coupon_sql  n_items
--  --------  -----------  ----------  -------
1   c-01      null                     2
2   c-02      "OCT10"      OCT10       1
3   c-01                               1
sku  revenue  shops
---  -------  -------------------
C3   1350     ["Shibuya"]
B7   1200     ["Shibuya"]
A1   900      ["Shibuya","Umeda"]

1행과 3행의 차이는 미묘하지만 중요합니다. JSON null 은 -> 로는 문자열 null, ->> 로는 SQL NULL 이 되고, 키가 아예 없으면 둘 다 SQL NULL 입니다.

마주칠 오류 메시지:

sqlite> SELECT json_extract('{"a": 1,', '$.a');
Runtime error near line 2: malformed JSON
sqlite> SELECT json_extract('{"a": 1}', 'a');
Runtime error near line 3: bad JSON path: 'a'

그리고 오류가 안 나는 것: {"customer": "c-03", items: []}(키에 따옴표 없음)는 올바른 JSON 이 아니지만 json_extract(payload, '$.customer') 는 c-03 을 돌려줬습니다. SQLite 가 JSON5 도 읽기 때문입니다. 그 행의 json_valid(payload) 는 0 이었습니다. API 계약이 "올바른 JSON"을 요구한다면 추출이 되는지가 아니라 json_valid 로 확인하세요.

100만 행 월간 보고서, 그리고 0.0% 로 둔갑한 NULL

4강의 100만 행 orders 테이블로 월별 결제 매출, 전월 대비, 연초부터의 누적, 연간 비중을 쿼리 하나로 냅니다. 윈도 함수는 GROUP BY 다음에 계산되므로 집계 결과에 쓸 수 있습니다.

WITH monthly AS (
  SELECT substr(created_at, 1, 7) AS month, count(*) AS orders, sum(amount) AS revenue
  FROM orders WHERE status = 'paid' GROUP BY month
)
SELECT month, orders, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month)                          AS diff,
       printf('%.1f%%', 100.0 * revenue / LAG(revenue) OVER (ORDER BY month) - 100) AS mom,
       sum(revenue) OVER (ORDER BY month)                                    AS ytd,
       printf('%.1f%%', 100.0 * revenue / sum(revenue) OVER ())              AS share
FROM monthly ORDER BY month;

month    orders  revenue    diff       mom     ytd         share
-------  ------  ---------  ---------  ------  ----------  -----
2025-01  73160   737992278             0.0%    737992278   8.6%
2025-02  65317   659562716  -78429562  -10.6%  1397554994  7.7%
2025-03  72047   728849528  69286812   10.5%   2126404522  8.5%
…
2025-12  72379   730238892  30203404   4.3%    8584287069  8.5%
Run Time: real 0.422 user 0.346108 sys 0.058657

1월의 mom 을 보세요. 0.0% 입니다. 전월이 없으니 diff 는 제대로 비어 있는데 printf 가 NULL 을 0 으로 바꿨습니다. 보는 사람은 "1월은 보합"으로 읽을 겁니다. printf('%.1f%%', NULL) 은 0.0% 를 돌려주고, round(NULL, 1) || '%' 는 NULL 로 남습니다. 숫자 서식은 애플리케이션에서 하거나, CASE WHEN … IS NULL 로 NULL 이 보이게 남기세요.

실측: 윈도 함수 대 상관 서브쿼리

윈도 함수(SQLite 3.25) 이전에는 그룹별 상위 N개를 상관 서브쿼리로 썼습니다. 행마다 "같은 그룹에서 나보다 위인 행이 몇 개인지"를 세는 방식입니다. 4강 테이블의 첫 고객 5,000명분(100,212행)으로 두 방식을 비교했고, 결과 집합이 같다는 것도 확인했습니다. 5회 중앙값이며, 인덱스 없는 상관 서브쿼리만 1회 실행입니다.

쿼리	인덱스 없음	인덱스 있음
고객별 상위 3, `ROW_NUMBER`	79.9 ms	48.5 ms
고객별 상위 3, 상관 `count(*)`	350,554 ms (5.8분)	125.6 ms
고객별 누적 합계, `SUM() OVER`	127.5 ms	87.6 ms
고객별 누적 합계, 상관 `SUM`	실행 안 함	253.8 ms

인덱스는 상위 N 용 (customer_id, amount DESC, id), 누적 합계용 (customer_id, created_at, id, amount) 입니다.

인덱스가 있을 때 쿼리 시간 막대그래프. ROW_NUMBER 로 고객별 상위 3개: 48.5ms. 상관 count 서브쿼리로 상위 3개: 125.6ms. SUM OVER 로 고객별 누적 합계: 87.6ms. 상관 SUM 서브쿼리로 누적 합계: 253.8ms. — 같은 결과, 다른 비용: 100,212행(고객 5,000명)에서 윈도 함수 대 상관 서브쿼리. 둘 다 인덱스 있음, 5회 중앙값. 인덱스 없는 상관 서브쿼리 상위 3개 쿼리는 350,554ms. SQLite 3.51.0, Apple M1 Pro.

윈도 함수는 데이터를 한 번 읽고 정렬하지만, 상관 서브쿼리는 행마다 안쪽 쿼리를 한 번씩 실행합니다. 인덱스가 있으면 안쪽이 짧은 SEARCH 라서 차이가 2.6배와 2.9배입니다. 인덱스가 없으면 안쪽이 매번 100,212행 전체 스캔이 되어 약 100억 행을 읽고, 차이는 4,000배를 넘었습니다. 윈도 함수 버전은 인덱스조차 필요 없었습니다.

어떤 질문에 어떤 도구를

판단 흐름. 그룹마다 한 행이면 되면 GROUP BY. 모든 행을 남겨야 하면 행이 계층 구조인지 확인하고, 그렇다면 순환 방지가 있는 재귀 CTE. 아니라면 동점을 같은 순위로 할지 확인해, 그렇다면 RANK 나 DENSE_RANK, 그룹마다 정확히 N 행이면 CTE 안의 ROW_NUMBER 를 바깥에서 거른다. 누적 합계는 ROWS 프레임과 유일한 정렬 키를 붙인 SUM OVER 를 쓴다. — 보고서 질문에 맞는 도구 고르기. 스프레드시트로 가져가게 만드는 질문 대부분은 윈도 함수나 재귀 CTE 로 끝난다.

질문	도구
그룹마다 한 행(매장별 합계)	`GROUP BY`
모든 행과 그룹 값(매장 합계 대비 비중)	`SUM(x) OVER (PARTITION BY g)`
동점은 같은 순위	`RANK` / `DENSE_RANK`
그룹마다 정확히 N개	CTE 안 `ROW_NUMBER`, 바깥에서 거르기
앞 행과의 차이	`LAG` / `LEAD`
빠진 날이 있을 때 전날 대비	재귀 CTE 달력 + `LEFT JOIN`, 그다음 `LAG`
누적 합계	`SUM() OVER (ORDER BY … 유일한 키 ROWS …)`
조건부 건수를 한 번에	`count(*) FILTER (WHERE …)`
계층(카테고리, 조직도, 댓글)	경로나 깊이로 순환을 막은 재귀 CTE
JSON 안의 값	`->>`, `json_each`, `json_group_array`

FILTER 도 실행해 봤습니다. count(*) FILTER (WHERE amount >= 100) 은 Shibuya·Tenjin·Umeda 에서 각각 3·0·3일을 돌려줬습니다.

연습 문제

매장마다, 기록이 있는 바로 앞날보다 매출이 가장 많이 늘어난 날을 구하세요.
재귀 CTE 로 2026년 매달 1일을 만드세요. "월말"을 만들려고 '2026-01-31' 에서 '+1 month' 를 더해 가면 안 되는 이유는?

정답

LAG 로 차이를 구하고 ROW_NUMBER 로 매장별 1위를 고릅니다.

WITH d AS (
  SELECT shop, day, amount - LAG(amount) OVER (PARTITION BY shop ORDER BY day) AS diff
  FROM sales
), r AS (
  SELECT shop, day, diff, ROW_NUMBER() OVER (PARTITION BY shop ORDER BY diff DESC) AS rn
  FROM d WHERE diff IS NOT NULL
)
SELECT shop, day, diff FROM r WHERE rn = 1 ORDER BY shop;

shop     day         diff
-------  ----------  ----
Shibuya  2026-10-04  70
Tenjin   2026-10-03  25
Umeda    2026-10-05  50

diff IS NOT NULL 이 중요합니다. SQLite 에서 NULL 은 오름차순에서 맨 앞, 내림차순에서 맨 뒤에 오므로 여기서는 1위가 되지 않지만, 오름차순으로 "가장 많이 떨어진 날"을 찾는 쿼리라면 NULL 이 1위가 됩니다.

WITH RECURSIVE m(d) AS (SELECT '2026-01-01' UNION ALL SELECT date(d, '+1 month') FROM m WHERE d < '2026-12-01') SELECT count(*), min(d), max(d) FROM m; 은 12|2026-01-01|2026-12-01 을 돌려줬습니다. 31일에서 시작하면 깨집니다. date('2026-01-31', '+1 month') 는 2026-03-03 을 돌려줬는데, 2월 31일이 넘쳐흐르기 때문입니다. 월말은 date(d, 'start of month', '+1 month', '-1 day') 로 만듭니다.

솔직한 한계와 버전 메모

실행한 것은 SQLite 3.51.0 뿐입니다. 윈도 함수는 SQLite 3.25.0, GROUPS·EXCLUDE 같은 프레임 지정은 3.28.0, 일반 집계 함수의 FILTER 는 3.30.0, 재귀 CTE 는 3.8.3, ->·->> 연산자는 3.38.0, JSON5 읽기는 3.42.0 부터입니다. 오래된 안드로이드나 오래된 서버의 SQLite 에서는 이 글의 일부가 동작하지 않습니다.
인덱스 없는 상관 서브쿼리는 1회(5.8분)만 실행했습니다. 5회 중앙값을 위해 30분을 더 쓸 가치는 없다고 봤습니다. 표의 "실행 안 함"은 한 번도 실행하지 않았다는 뜻입니다.
PostgreSQL 과 MySQL 8 도 같은 윈도 함수와 재귀 CTE 를 지원하지만, MySQL 은 cte_max_recursion_depth(기본 1000)로 재귀 깊이를 제한하고, PostgreSQL 은 의미가 다른 자체 JSON 연산자를 씁니다(여기서는 실행하지 않음).
매출 테이블은 일부러 작게 만들었습니다. 측정 표는 금액이 반복되는 합성 데이터라서 데이터 모양이 다르면 비율도 달라집니다.

SQL 강좌는 여기까지입니다. 다음으로 프로그래밍 학습 로드맵에서 SQL 과 함께 배울 것을 확인하거나, PDO + SQLite 와 SQL 인젝션에서 이런 쿼리를 웹 페이지 뒤에서 안전하게 쓰는 법을 보세요.

이전: 5강: 트랜잭션·격리 수준·잠금 · 처음으로: 1강: SELECT·WHERE·ORDER BY

자주 묻는 질문

SQL 에서 그룹별 상위 N개를 어떻게 뽑나요?

CTE 안에서 ROW_NUMBER() OVER (PARTITION BY 그룹 ORDER BY 값 DESC) 로 번호를 매기고, 바깥 쿼리에서 rn <= N 으로 거릅니다. WHERE 에 윈도 함수를 바로 쓰면 SQLite 는 'misuse of window function ROW_NUMBER()' 를 냅니다. 100,212행에서 윈도 함수 버전은 48.5ms, 상관 서브쿼리는 인덱스가 있으면 125.6ms, 없으면 350.6초였습니다.

누적 합계가 같은 날짜 행에서 한꺼번에 튀는 이유는?