DuckDB

DuckDB 07 - Advanced Practice

Created: September 8, 2026 1:34 PM Class: DuckDB Jupyter Notebook: duckdb07.ipynb

심화 실습 — 9천만 행 직접 다뤄보기

실습 파일: duckdb07.ipynb
학습 로드맵 9단계(심화 실습). duckdb06이 1개월 296만 행이었다면, 여기서는 30배 규모를 여러 파일에 흩어진 채로, 그것도 중간에 스키마가 바뀌는 상태로 다룹니다.

데이터

data/parquet/yellow/ 아래 24개 파일(2024-01 ~ 2025-12), 총 1.42 GB, 약 8,989만 행
data/csv/taxi_zone_lookup.csv — 지역 코드 매핑 (265개 구역)

이번에 새로 다루는 것

주제	duckdb06까지	이번
파일 개수	1개	24개 (glob)
스키마	고정	중간에 컬럼 추가됨
시간 범위	1개월	24개월 (시계열·계절성)
분석 유형	기술통계	정책 효과 추정 (인과 추론의 맛)
저장 전략	단일 파일 정렬	파티셔닝

가장 중요한 건 3부입니다. 1·2·4부는 문법과 도구라 검색하면 나오지만, “전월 대비와 전년 동월 대비가 정반대를 가리킬 때 어느 쪽을 믿을지” 같은 판단은 직접 부딪혀봐야 늘어납니다.

준비 — 데이터 확인

import duckdb, os, glob

YELLOW = 'data/parquet/yellow/*.parquet'
ZONES  = 'data/csv/taxi_zone_lookup.csv'

files = sorted(glob.glob(YELLOW))
total = sum(os.path.getsize(f) for f in files)
print(f"파일{len(files)}개, 총{total/1024/1024/1024:.2f} GB")
print(f"처음:{os.path.basename(files[0])}")
print(f"마지막:{os.path.basename(files[-1])}")

파일 24개, 총 1.42 GB
처음: yellow_tripdata_2024-01.parquet
마지막: yellow_tripdata_2025-12.parquet

YELLOW 안의 *는 glob 패턴입니다. “이 자리에 아무 문자열이나 와도 좋다”는 뜻이고, 파일 24개를 한 번에 가리킵니다. 이후 모든 쿼리가 이 변수 하나를 씁니다.

1부. 여러 파일을 하나처럼 다루기

문제 1 — 전체 행 수 세기

파일이 24개로 흩어져 있어도, DuckDB에서는 한 번의 FROM으로 전부 잡습니다.

duckdb.sql(f"SELECT count(*) FROM '{YELLOW}'").show()

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│     89892322 │
└──────────────┘

8,989만 행입니다. 파일을 하나씩 열어 더한 게 아니라, DuckDB가 glob을 펼쳐 24개를 하나의 테이블처럼 취급한 결과입니다.

초보자를 위한 메모: 다른 도구였다면 파일 24개를 반복문으로 읽어 이어붙이는 코드를 짜야 합니다. DuckDB에서는 경로 문자열에 *를 넣는 것이 그 반복문을 대신합니다. 이게 이 노트북 전체를 관통하는 첫 번째 감각입니다.

문제 2 — 일부러 실패해보기

2025년에 추가된 뉴욕 혼잡통행료 컬럼 cbd_congestion_fee를 조회해 봅니다.

# 두 파일의 컬럼 개수를 먼저 비교해보자
duckdb.sql("SELECT count(*) FROM (DESC SELECT * FROM 'data/parquet/yellow/yellow_tripdata_2024-01.parquet')").show()  # 19
duckdb.sql("SELECT count(*) FROM (DESC SELECT * FROM 'data/parquet/yellow/yellow_tripdata_2025-01.parquet')").show()  # 20

duckdb.sql(f"SELECT cbd_congestion_fee FROM '{YELLOW}' LIMIT 3").show()

┌──────────────┐        ┌──────────────┐
│ count_star() │        │ count_star() │
├──────────────┤        ├──────────────┤
│           19 │        │           20 │
└──────────────┘        └──────────────┘

BinderException: Binder Error: Referenced column "cbd_congestion_fee" not found in FROM clause!

2024-01 파일은 19개 컬럼, 2025-01 파일은 20개 컬럼입니다. 그런데 조회는 실패합니다. 세 가지를 따져봅시다.

1. 에러 메시지가 말하는 것

핵심 단어는 Binder Error 입니다.

바인딩(binding)이란: SQL을 실행하기 전, DuckDB가 “이 쿼리에 나오는 이름들이 실제로 어디에 있는지” 를 연결하는 단계입니다. cbd_congestion_fee라는 이름을 테이블 스키마에서 찾지 못했으니, 데이터는 한 줄도 읽지 않은 채 계획 단계에서 멈춘 것입니다.

에러의 종류만 봐도 원인의 층위를 알 수 있습니다:

에러	언제 나나
`Parser Error`	문장 구조가 깨짐 (콤마·괄호 누락)
`Binder Error`	문장은 읽혔지만 이름을 못 찾음
`Conversion Error`	이름은 찾았지만 타입이 안 맞음

2. 문제 1의 `count(*)`는 왜 멀쩡했나

count(*)는 참조하는 컬럼이 하나도 없습니다. 별표는 “행을 세라”는 뜻이지 특정 컬럼을 가리키는 게 아니라서, 바인더가 이름을 맞춰볼 대상 자체가 없습니다.
게다가 Parquet은 자체 기술 스키마(self-describing schema) 와 함께 각 row group의 행 수가 footer 메타데이터에 들어 있습니다. DuckDB는 컬럼 데이터를 전혀 디코딩하지 않고 24개 파일의 footer만 읽어서 8,989만을 더합니다.
즉 문제 1이 성공한 것은 “스키마가 일치해서”가 아니라 “스키마를 볼 필요조차 없어서” 입니다. 스키마 불일치는 그때도 똑같이 있었고, 드러나지 않았을 뿐입니다.

3. DuckDB가 여러 파일의 스키마를 정하는 방식

glob으로 잡힌 파일 목록을 정렬합니다 → 첫 번째는 yellow_tripdata_2024-01.parquet
첫 번째 파일의 스키마를 전체 스키마로 채택합니다. 나머지 23개는 이 스키마에 위치(position) 기준으로 끼워 맞춥니다
2024-01에 cbd_congestion_fee가 없으니 전체 스키마에도 없음 → 2025년 파일 12개에는 실제로 있는데도 조회 불가

문제 3 — 스키마가 다른 파일들 합치기

기본 동작을 바꾸는 옵션이 union_by_name 입니다.

duckdb.sql(f"""
    SELECT count(cbd_congestion_fee) AS has_value
    FROM read_parquet('{YELLOW}', union_by_name=true)
""").show()

┌───────────┐
│ has_value │
│   int64   │
├───────────┤
│  48722602 │
└───────────┘

전체 8,989만 중 4,872만 건(약 54%) 에 값이 있습니다.

알아둘 점 두 가지

① union_by_name=true는 컬럼을 위치가 아니라 이름으로 맞춥니다. 어떤 파일에 없는 컬럼은 그 파일의 행에서 NULL로 채워집니다. 그래서 2024년 행은 NULL, 2025년 행은 실제 값이 됩니다.

② 이 옵션을 쓰려면 FROM '파일' 축약형 대신 함수 형태로 읽어야 합니다.

FROM 'data/.../*.parquet'                              -- 축약형: 파라미터를 넘길 자리가 없다
FROM read_parquet('data/.../*.parquet', union_by_name=true)   -- 함수형: 옵션을 줄 수 있다

③ count(컬럼)은 NULL을 세지 않습니다. count(*)와 헷갈리기 쉬운 지점입니다.

# 셋 다 같은 값이어야 정상 — 교차 검증
duckdb.sql(f"""
    SELECT count(cbd_congestion_fee)                                    AS by_count_col,
           count(*) FILTER (WHERE cbd_congestion_fee IS NOT NULL)       AS by_filter,
           sum(CASE WHEN cbd_congestion_fee IS NOT NULL THEN 1 ELSE 0 END) AS by_case
    FROM read_parquet('{YELLOW}', union_by_name=true)
""").show()

┌──────────────┬───────────┬──────────┐
│ by_count_col │ by_filter │ by_case  │
├──────────────┼───────────┼──────────┤
│     48722602 │  48722602 │ 48722602 │
└──────────────┴───────────┴──────────┘

⚠️ 미리 경고: 여기서 센 4,872만은 “값이 NULL이 아닌 행” 이지 “요금이 실제로 부과된 행” 이 아닙니다. 0.0도 엄연한 값이라 count(컬럼)에 포함됩니다. 문제 8에서 이 구분이 결정적으로 중요해집니다.

문제 4 — 파일별로 나눠 보기

각 행이 어느 파일에서 왔는지는 filename 가상 컬럼으로 알 수 있습니다.

duckdb.sql(f"""
    SELECT parse_filename(filename) AS file,
           count(*) AS n_rows
    FROM read_parquet('{YELLOW}', filename=true)
    GROUP BY file
    ORDER BY file
""").show(max_rows=30)

┌─────────────────────────────────┬─────────┐
│              file               │ n_rows  │
├─────────────────────────────────┼─────────┤
│ yellow_tripdata_2024-01.parquet │ 2964624 │
│ yellow_tripdata_2024-02.parquet │ 3007526 │
│ yellow_tripdata_2024-03.parquet │ 3582628 │
│ yellow_tripdata_2024-04.parquet │ 3514289 │
│ yellow_tripdata_2024-05.parquet │ 3723833 │
│ yellow_tripdata_2024-06.parquet │ 3539193 │
│ yellow_tripdata_2024-07.parquet │ 3076903 │
│ yellow_tripdata_2024-08.parquet │ 2979183 │
│ yellow_tripdata_2024-09.parquet │ 3633030 │
│ yellow_tripdata_2024-10.parquet │ 3833771 │
│ yellow_tripdata_2024-11.parquet │ 3646369 │
│ yellow_tripdata_2024-12.parquet │ 3668371 │
│ yellow_tripdata_2025-01.parquet │ 3475226 │
│ yellow_tripdata_2025-02.parquet │ 3577543 │
│ yellow_tripdata_2025-03.parquet │ 4145257 │
│ yellow_tripdata_2025-04.parquet │ 3970553 │
│ yellow_tripdata_2025-05.parquet │ 4591845 │
│ yellow_tripdata_2025-06.parquet │ 4322960 │
│ yellow_tripdata_2025-07.parquet │ 3898963 │
│ yellow_tripdata_2025-08.parquet │ 3574091 │
│ yellow_tripdata_2025-09.parquet │ 4251015 │
│ yellow_tripdata_2025-10.parquet │ 4428699 │
│ yellow_tripdata_2025-11.parquet │ 4181444 │
│ yellow_tripdata_2025-12.parquet │ 4305006 │
├─────────────────────────────────┴─────────┤
│ 24 rows                         2 columns │
└───────────────────────────────────────────┘

`filename` 가상 컬럼

가상 컬럼(virtual column)이란: Parquet 파일 안에 실제로 저장돼 있지 않은데도 DuckDB가 스캔하면서 만들어 붙여주는 컬럼입니다. filename은 그 행을 읽어온 원본 파일 경로를 담습니다.
v1.3.0부터는 옵션 없이도 자동 제공됩니다. filename=true는 구버전 호환용입니다.
덕분에 24개 파일을 여전히 하나의 테이블처럼 다루면서, 동시에 파일 단위로 쪼개볼 수 있습니다.
parse_filename()은 경로에서 마지막 요소(파일명)만 떼어냅니다. regexp_extract나 split_part보다 짧고, 구분자(/ vs \)를 알아서 처리해 Windows에서도 안전합니다.

결과에서 이미 보이는 것

2024-01은 296만인데 2025-01은 347만 — 전년 대비 늘었습니다. 반면 2024-12(366만) → 2025-01(347만)은 줄었습니다. 이 두 방향이 3부의 핵심 논점이 됩니다.

2부. 시계열 — 24개월 추세

여기서부터는 union_by_name 읽기 방식을 계속 씁니다. 매번 쓰기 번거로우니 뷰를 하나 만들어두고 시작합니다.

문제 5 — 기준 뷰 만들기

duckdb.sql("DROP VIEW IF EXISTS trips")   # 셀 재실행 대비
duckdb.sql(f"""
    CREATE VIEW trips AS
    SELECT
        *,
        date_diff('minute', tpep_pickup_datetime, tpep_dropoff_datetime) AS duration_min,
        date_trunc('month', tpep_pickup_datetime) AS ym
    FROM read_parquet('{YELLOW}', union_by_name=true)
    WHERE
        -- 규칙 1: 분석 대상 기간(2024.01 ~ 2025.12.31) 밖 데이터 제외
        tpep_pickup_datetime >= TIMESTAMP '2024-01-01'
        AND tpep_pickup_datetime < TIMESTAMP '2026-01-01'
        -- 규칙 2: 하차가 승차보다 빠른 건 명백한 오류
        AND tpep_dropoff_datetime > tpep_pickup_datetime
        -- 규칙 3: 거리는 0 초과 100마일 미만
        AND trip_distance > 0 AND trip_distance < 100
        -- 규칙 4: 요금은 양수, 500달러 미만
        AND fare_amount > 0 AND fare_amount < 500
""")

duckdb.sql(f"""
    SELECT (SELECT count(*) FROM '{YELLOW}') AS 정제_전,
           (SELECT count(*) FROM trips)      AS 정제_후,
           (SELECT count(*) FROM '{YELLOW}') - (SELECT count(*) FROM trips) AS 제거됨,
           round(100.0 * (SELECT count(*) FROM trips)
                       / (SELECT count(*) FROM '{YELLOW}'), 2) AS 유지율_퍼센트
""").show()

┌──────────┬──────────┬─────────┬───────────────┐
│ 정제_전  │ 정제_후  │ 제거됨  │ 유지율_퍼센트 │
├──────────┼──────────┼─────────┼───────────────┤
│ 89892322 │ 83871796 │ 6020526 │          93.3 │
└──────────┴──────────┴─────────┴───────────────┘

뷰를 쓰는 이유

뷰(VIEW)란: 쿼리에 이름을 붙여둔 것입니다. 데이터를 복사해 저장하지 않고, trips를 쓸 때마다 저장된 쿼리를 그 자리에 펼쳐 실행합니다. 그래서 디스크를 전혀 쓰지 않고, 원본 파일이 바뀌면 뷰 결과도 자동으로 바뀝니다.

read_parquet(..., union_by_name=true)와 정제 규칙 4개를 여기 한 번만 써두면, 이후 문제 6~15에서는 전부 FROM trips 한 단어로 끝납니다.

파생 컬럼 두 개

컬럼	식	하는 일
`duration_min`	`date_diff('minute', 승차, 하차)`	두 시각의 차이를 분 단위 정수로
`ym`	`date_trunc('month', 승차)`	월 단위 아래를 잘라내 그 달 1일 0시로 내림

date_trunc가 왜 필요한지가 초보자가 가장 자주 막히는 지점입니다.

원본 `tpep_pickup_datetime`	`date_trunc('month', ...)`
`2024-01-03 14:22:07`	`2024-01-01 00:00:00`
`2024-01-28 09:15:44`	`2024-01-01 00:00:00`
`2024-02-01 00:03:12`	`2024-02-01 00:00:00`

같은 달의 모든 행이 똑같은 값을 갖게 됩니다. 값이 같아야 GROUP BY의 키로 쓸 수 있습니다. 원본 타임스탬프는 초 단위까지 전부 달라서 그룹이 만들어지지 않습니다.

중요한 발상 전환: “월별로 나눈다”는 파일을 나누는 게 아니라 컬럼을 만드는 일입니다. 오히려 이쪽이 더 정확합니다 — 2월 파일 안에 3월 1일 새벽 승차 행이 섞여 있어도, 타임스탬프 기준이면 제대로 3월로 갑니다.

유지율이 96.8% → 93.3%로 떨어진 이유

duckdb06의 1개월치는 96.8%였습니다. 2025년치가 섞이면서 낮아졌는데, 월별 유지율을 뽑아보면 어느 시점부터 떨어지는지 확인할 수 있습니다. (2025년에 요금 0원·거리 0 기록이 늘어난 것이 주된 요인으로 보입니다.)

문제 6 — 월별 추세

duckdb.sql("""
    SELECT strftime(ym, '%Y-%m')       AS '연월',
           count(*)                     AS '운행 건수',
           round(avg(fare_amount), 2)   AS '평균 요금',
           round(avg(trip_distance), 1) AS '평균 거리'
    FROM trips
    GROUP BY ym
    ORDER BY ym
""").show(max_rows=30)

┌─────────┬───────────┬───────────┬───────────┐
│  연월   │ 운행 건수 │ 평균 요금 │ 평균 거리 │
├─────────┼───────────┼───────────┼───────────┤
│ 2024-01 │   2869518 │     18.49 │       3.3 │
│ 2024-02 │   2901352 │     18.41 │       3.2 │
│ 2024-03 │   3439728 │     19.15 │       3.4 │
│ 2024-04 │   3413924 │     19.45 │       3.4 │
│ 2024-05 │   3616139 │     20.14 │       3.4 │
│ 2024-06 │   3427868 │     19.94 │       3.4 │
│ 2024-07 │   2973595 │     20.14 │       3.6 │
│ 2024-08 │   2867068 │     20.36 │       3.7 │
│ 2024-09 │   3483512 │     20.65 │       3.5 │
│ 2024-10 │   3681586 │     20.37 │       3.5 │
│ 2024-11 │   3509698 │     19.74 │       3.3 │
│ 2024-12 │   3518502 │     20.39 │       3.3 │
│ 2025-01 │   3252361 │     17.95 │       3.2 │
│ 2025-02 │   3306159 │     18.03 │       3.1 │
│ 2025-03 │   3826945 │     19.16 │       3.3 │
│ 2025-04 │   3671924 │     19.31 │       3.4 │
│ 2025-05 │   4092206 │     20.38 │       3.5 │
│ 2025-06 │   3869341 │     20.76 │       3.6 │
│ 2025-07 │   3494627 │     20.41 │       3.6 │
│ 2025-08 │   3180650 │     20.23 │       3.8 │
│ 2025-09 │   3838214 │     20.92 │       3.6 │
│ 2025-10 │   3943793 │     20.28 │       3.6 │
│ 2025-11 │   3643519 │     19.56 │       3.5 │
│ 2025-12 │   4049567 │     22.51 │       3.5 │
└─────────┴───────────┴───────────┴───────────┘

표시용과 정렬용을 분리하는 요령

SELECT에는 strftime(ym, '%Y-%m')으로 보기 좋은 문자열을, GROUP BY/ORDER BY에는 원본 TIMESTAMP인 ym 을 씁니다.

문자열로 정렬해도 제로 패딩 덕에 우연히 맞지만, 타임스탬프로 정렬하면 우연에 기대지 않습니다.
GROUP BY/ORDER BY에서 SELECT의 별칭도 쓸 수 있지만, 원본 컬럼을 쓰는 편이 안전합니다.

눈에 띄는 것

최다 운행 월은 2025-05 (409만)
2025-01 평균 요금 17.95는 24개월 중 최저입니다. 혼잡통행료가 붙은 달인데 요금은 오히려 내려갔습니다 — cbd_congestion_fee는 fare_amount와 별도 컬럼이라 여기 안 잡히기 때문입니다
2025-12 평균 요금 22.51만 유일하게 22달러를 넘습니다

문제 7 — 전월 대비 증감률

duckdb.sql("""
    WITH monthly AS (
        -- 1단계: 월별 집계. 반올림하지 않고 원값 유지
        SELECT ym,
               count(*)           AS n,
               avg(fare_amount)   AS avg_fare,
               avg(trip_distance) AS avg_dist
        FROM trips
        GROUP BY ym
    ),
    withlag AS (
        -- 2단계: 이전 달 값을 옆에 붙인다
        SELECT *,
               lag(n)        OVER (ORDER BY ym) AS prev_n,
               lag(avg_fare) OVER (ORDER BY ym) AS prev_fare,
               lag(avg_dist) OVER (ORDER BY ym) AS prev_dist
        FROM monthly
    )
    -- 3단계: 지표마다 [현재값 · 전월값 · 증감률] 세 컬럼씩
    SELECT strftime(ym, '%Y-%m') AS '연월',
           n                                                    AS '운행 건수',
           prev_n                                               AS '전월 건수',
           round(100.0 * (n - prev_n) / prev_n, 1)              AS '건수 전월대비%',
           round(avg_fare, 2)                                   AS '평균 요금',
           round(prev_fare, 2)                                  AS '전월 요금',
           round(100.0 * (avg_fare - prev_fare) / prev_fare, 1) AS '요금 전월대비%',
           round(avg_dist, 2)                                   AS '평균 거리',
           round(prev_dist, 2)                                  AS '전월 거리',
           round(100.0 * (avg_dist - prev_dist) / prev_dist, 1) AS '거리 전월대비%'
    FROM withlag
    ORDER BY ym
""").show(max_rows=30)

┌─────────┬───────────┬───────────┬────────────────┬───────────┬───────────┬────────────────┐
│  연월   │ 운행 건수 │ 전월 건수 │ 건수 전월대비% │ 평균 요금 │ 전월 요금 │ 요금 전월대비% │
├─────────┼───────────┼───────────┼────────────────┼───────────┼───────────┼────────────────┤
│ 2024-01 │   2869518 │      NULL │           NULL │     18.49 │      NULL │           NULL │
│ 2024-02 │   2901352 │   2869518 │            1.1 │     18.41 │     18.49 │           -0.4 │
│ 2024-03 │   3439728 │   2901352 │           18.6 │     19.15 │     18.41 │            4.0 │
│ 2024-04 │   3413924 │   3439728 │           -0.8 │     19.45 │     19.15 │            1.6 │
│ 2024-05 │   3616139 │   3413924 │            5.9 │     20.14 │     19.45 │            3.6 │
│ 2024-06 │   3427868 │   3616139 │           -5.2 │     19.94 │     20.14 │           -1.0 │
│ 2024-07 │   2973595 │   3427868 │          -13.3 │     20.14 │     19.94 │            1.0 │
│ 2024-08 │   2867068 │   2973595 │           -3.6 │     20.36 │     20.14 │            1.1 │
│ 2024-09 │   3483512 │   2867068 │           21.5 │     20.65 │     20.36 │            1.4 │
│ 2024-10 │   3681586 │   3483512 │            5.7 │     20.37 │     20.65 │           -1.4 │
│ 2024-11 │   3509698 │   3681586 │           -4.7 │     19.74 │     20.37 │           -3.1 │
│ 2024-12 │   3518502 │   3509698 │            0.3 │     20.39 │     19.74 │            3.3 │
│ 2025-01 │   3252361 │   3518502 │           -7.6 │     17.95 │     20.39 │          -12.0 │
│ 2025-02 │   3306159 │   3252361 │            1.7 │     18.03 │     17.95 │            0.4 │
│ 2025-03 │   3826945 │   3306159 │           15.8 │     19.16 │     18.03 │            6.3 │
│ 2025-04 │   3671924 │   3826945 │           -4.1 │     19.31 │     19.16 │            0.8 │
│ 2025-05 │   4092206 │   3671924 │           11.4 │     20.38 │     19.31 │            5.6 │
│ 2025-06 │   3869341 │   4092206 │           -5.4 │     20.76 │     20.38 │            1.8 │
│ 2025-07 │   3494627 │   3869341 │           -9.7 │     20.41 │     20.76 │           -1.7 │
│ 2025-08 │   3180650 │   3494627 │           -9.0 │     20.23 │     20.41 │           -0.9 │
│ 2025-09 │   3838214 │   3180650 │           20.7 │     20.92 │     20.23 │            3.4 │
│ 2025-10 │   3943793 │   3838214 │            2.8 │     20.28 │     20.92 │           -3.0 │
│ 2025-11 │   3643519 │   3943793 │           -7.6 │     19.56 │     20.28 │           -3.6 │
│ 2025-12 │   4049567 │   3643519 │           11.1 │     22.51 │     19.56 │           15.1 │
└─────────┴───────────┴───────────┴────────────────┴───────────┴───────────┴────────────────┘

(거리 3개 컬럼은 지면상 생략)

`lag()` — 윈도우 함수 입문

lag()가 하는 일: 같은 결과 집합의 “이전 행” 값을 현재 행으로 끌어옵니다.

ym	n	`lag(n) OVER (ORDER BY ym)`
2024-01	2,869,518	NULL ← 이전 행이 없음
2024-02	2,901,352	2,869,518
2024-03	3,439,728	2,901,352

한 행 안에 “이번 달”과 “지난달”이 나란히 놓이면, 증감률은 그냥 사칙연산입니다.

lag(  n  )  OVER  (  ORDER BY ym  )
    ─┬─       ─┬─      ─────┬────
     │         │            └─ 무엇을 "이전"으로 볼지 정하는 순서
     │         └─ "이건 윈도우 함수다" 선언. 없으면 에러
     └─ 끌어올 값

OVER가 없으면 lag는 성립하지 않습니다. “이전 행”이라는 개념 자체가 “행들이 어떤 순서로 늘어서 있는가”를 알아야 성립하고, 그 순서를 정해주는 게 OVER (ORDER BY ...)입니다.
OVER 안의 ORDER BY(계산 기준)와 맨 아래 ORDER BY(출력 순서)는 완전히 별개입니다. 둘 다 필요합니다.
첫 행은 NULL이 정상입니다. 0으로 채우지 마세요 — “이전 달이 없다”와 “변화가 없다”는 다른 사실입니다.

CTE로 층을 나누는 이유

CTE(WITH)란: 쿼리 안에서 임시로 이름 붙인 중간 결과입니다. WITH a AS (...), b AS (...) 처럼 콤마로 여러 개를 이어 쓸 수 있고, 뒤 단계가 앞 단계를 참조합니다.

SQL의 처리 순서는 이렇습니다:

FROM → WHERE → GROUP BY → 집계함수 → 윈도우함수 → SELECT → ORDER BY

윈도우 함수는 집계보다 나중에 돕니다. lag(count(*)) OVER (...)처럼 한 층에 겹쳐 써도 문법적으로는 유효하지만, 읽는 사람이 실행 순서를 머릿속으로 재구성해야 합니다. CTE로 나누면 “24행짜리 표를 먼저 만들고, 그 위에서 이전 행을 본다”가 코드 모양 그대로 드러납니다.

⚠️ 함정 — 반올림을 먼저 하고 차이를 계산하면 안 됩니다

monthly CTE에서 반올림하지 않고 원값을 유지한 것이 핵심입니다.

평균 거리를 소수 1자리로 먼저 뭉개면 3.3 → 3.2의 증감률은 -3.0%로 나오지만, 원값이 3.251 → 3.249였다면 참값은 -0.06%입니다.
작은 값에서 작은 변화를 볼 때 반올림 오차가 결과를 지배합니다.
원칙: 계산은 원값으로, 반올림은 출력 직전에 한 번만. 운행 건수는 정수라 이 함정이 없어서 잘 드러나지 않습니다.

이 표에서 3부의 재료가 나옵니다

2025-01의 전월 대비 = -7.6%. 그리고 문제 4에서 봤듯 전년 동월 대비는 늘었습니다. 방향이 반대입니다.

참고: DuckDB의 /는 정수끼리여도 실수 나눗셈입니다(1/2 → 0.5). Postgres였다면 100.0을 곱해 실수로 승격시켜야 했지만 여기선 안전합니다. 정수 나눗셈은 //입니다.

3부. 정책 효과 분석 — 뉴욕 혼잡통행료

2025년 1월, 뉴욕시는 맨해튼 중심업무지구(CBD)에 진입하는 차량에 혼잡통행료(congestion pricing) 를 부과하기 시작했습니다. cbd_congestion_fee 컬럼이 생긴 게 그 흔적입니다.

이제 “이 정책이 택시 통행에 영향을 줬는가” 를 데이터로 따져봅니다.

문제 8 — 시행 시점을 데이터로 특정하기

뉴스나 위키를 찾지 말고, 데이터만 보고 언제부터 실제로 부과됐는지 알아냅니다.

1단계 — 월별로 세 가지를 나란히

duckdb.sql("""
    SELECT strftime(ym, '%Y-%m') AS '연월',
           count(*)                                       AS '전체',
           count(cbd_congestion_fee)                      AS '값 있음',
           count(*) FILTER (WHERE cbd_congestion_fee > 0) AS '부과됨'
    FROM trips
    GROUP BY ym
    ORDER BY ym
""").show(max_rows=30)

┌─────────┬─────────┬─────────┬─────────┐
│  연월   │  전체   │ 값 있음 │ 부과됨  │
├─────────┼─────────┼─────────┼─────────┤
│ 2024-01 │ 2869518 │       0 │       0 │
│   ···   │   ···   │       0 │       0 │
│ 2024-11 │ 3509698 │       0 │       0 │
│ 2024-12 │ 3518502 │      21 │       0 │   ← 시행 전인데 값이 21건?
│ 2025-01 │ 3252361 │ 3252359 │ 2139541 │
│ 2025-02 │ 3306159 │ 3306158 │ 2448351 │
│ 2025-03 │ 3826945 │ 3826943 │ 2844407 │
│ 2025-04 │ 3671924 │ 3671924 │ 2718031 │
│ 2025-05 │ 4092206 │ 4092206 │ 2998333 │
│ 2025-06 │ 3869341 │ 3869341 │ 2852728 │
│ 2025-07 │ 3494627 │ 3494627 │ 2599443 │
│ 2025-08 │ 3180650 │ 3180650 │ 2343336 │
│ 2025-09 │ 3838214 │ 3838214 │ 2835841 │
│ 2025-10 │ 3943793 │ 3943793 │ 2900483 │
│ 2025-11 │ 3643519 │ 3643519 │ 2653861 │
│ 2025-12 │ 4049567 │ 4049567 │ 2918751 │
└─────────┴─────────┴─────────┴─────────┘

왜 컬럼을 세 개나 세나 — “0원도 값이다”

컬럼 상태	`count(*)`	`count(cbd_congestion_fee)`	`FILTER (WHERE > 0)`
`NULL` (2024년 파일엔 컬럼 자체가 없음)	✓	✗	✗
`0.0` (컬럼은 있지만 부과 안 됨)	✓	✓	✗
`0.75` (실제 부과)	✓	✓	✓

count(컬럼)은 0.0을 세어버립니다. 문제 3의 4,872만과 실제 부과 건수가 다른 이유가 이것입니다. 컬럼 이름을 '값 있음'과 '부과됨'으로 나눠 붙인 것도 스스로를 속이지 않기 위해서입니다.

새 문법 — `FILTER`

count(*) FILTER (WHERE 조건)

FILTER란: “이 집계 함수에만 적용되는 WHERE” 입니다. 바깥 WHERE는 행을 아예 버리지만, FILTER는 행은 남겨둔 채 그 집계에서만 제외합니다.
덕분에 한 번의 스캔으로 전체와 부분을 동시에 셀 수 있습니다.
SUM(CASE WHEN 조건 THEN 1 ELSE 0 END)과 같은 결과지만 훨씬 읽기 쉽습니다.

2단계 — 일 단위로 좁히기

duckdb.sql("""
    SELECT tpep_pickup_datetime::DATE                     AS '날짜',
           count(*)                                       AS '전체',
           count(*) FILTER (WHERE cbd_congestion_fee > 0) AS '부과됨',
           round(100.0 * count(*) FILTER (WHERE cbd_congestion_fee > 0)
                       / count(*), 1)                     AS '부과율%'
    FROM trips
    WHERE tpep_pickup_datetime >= TIMESTAMP '2024-12-25'
      AND tpep_pickup_datetime <  TIMESTAMP '2025-01-15'
    GROUP BY 1
    ORDER BY 1
""").show(max_rows=30)

┌────────────┬────────┬────────┬─────────┐
│    날짜    │  전체  │ 부과됨 │ 부과율% │
├────────────┼────────┼────────┼─────────┤
│ 2024-12-25 │  52261 │      0 │     0.0 │
│ 2024-12-26 │  71102 │      0 │     0.0 │
│ 2024-12-27 │  80865 │      0 │     0.0 │
│ 2024-12-28 │  84349 │      0 │     0.0 │
│ 2024-12-29 │  76062 │      0 │     0.0 │
│ 2024-12-30 │  77472 │      0 │     0.0 │
│ 2024-12-31 │  82373 │      0 │     0.0 │
│ 2025-01-01 │  81008 │      0 │     0.0 │
│ 2025-01-02 │  80649 │      0 │     0.0 │
│ 2025-01-03 │  87046 │      0 │     0.0 │
│ 2025-01-04 │  93773 │    389 │     0.4 │
│ 2025-01-05 │  76108 │  50716 │    66.6 │   ← 계단
│ 2025-01-06 │  76789 │  50821 │    66.2 │
│ 2025-01-07 │  95747 │  67256 │    70.2 │
│ 2025-01-08 │ 107153 │  77562 │    72.4 │
│ 2025-01-09 │ 111955 │  81330 │    72.6 │
│ 2025-01-10 │ 104844 │  75516 │    72.0 │
│ 2025-01-11 │ 115756 │  89686 │    77.5 │
│ 2025-01-12 │  96827 │  73385 │    75.8 │
│ 2025-01-13 │  94518 │  68250 │    72.2 │
│ 2025-01-14 │ 113894 │  84829 │    74.5 │
└────────────┴────────┴────────┴─────────┘

시행일은 2025년 1월 5일입니다. 0.0% → 66.6%로 하루 만에 계단처럼 튑니다.

문법 메모

::DATE — 타임스탬프에서 시·분·초를 잘라 날짜만 남기는 캐스팅입니다. date_trunc('day', ...)와 같은 역할이고 더 짧습니다.
GROUP BY 1 — “SELECT의 첫 번째 항목으로 묶어라”. 긴 식을 두 번 쓰지 않아도 됩니다.
부과율% 컬럼이 핵심입니다. 절대 건수는 요일마다 출렁이지만, 비율은 정책 경계에서 계단처럼 튑니다.

1월 4일의 389건(0.4%)은 자정 근처 경계에서 생긴 잡음으로 보입니다.

문제 9 — 이상한 21건

문제 8에서 2024년 12월에 cbd_congestion_fee 값이 있는 행이 21건 발견됐습니다. 시행 전인데요.

먼저 흔한 실수 하나

WHERE tpep_pickup_datetime = TIMESTAMP '2024-12-31'   -- ❌ 하루가 아니라 '한 순간'

TIMESTAMP '2024-12-31'은 시·분·초가 생략되면 2024-12-31 00:00:00 으로 해석됩니다. 즉 이 조건은 “12월 31일에 탄 손님” 이 아니라 “12월 31일 0시 0분 0초 정각에 탄 손님” 입니다.

-- ① 날짜로 잘라서 비교
WHERE tpep_pickup_datetime::DATE = DATE '2024-12-31'
-- ② 범위로 지정 (프루닝에 유리해 실무 표준)
WHERE tpep_pickup_datetime >= TIMESTAMP '2024-12-31'
  AND tpep_pickup_datetime <  TIMESTAMP '2025-01-01'
-- ③ 찾는 대상 자체로 거른다 ← 이 문제엔 이게 정답
WHERE cbd_congestion_fee IS NOT NULL

날짜는 추론한 것이지 조사 조건이 아닙니다. 찾으려는 대상으로 직접 걸면 날짜를 몰라도 21건이 잡힙니다.

실제 행을 눈으로 보기

duckdb.sql(f"""
    SELECT tpep_pickup_datetime     AS 승차,
           tpep_dropoff_datetime    AS 하차,
           trip_distance            AS 거리,
           fare_amount              AS 요금,
           total_amount             AS 총액,
           cbd_congestion_fee       AS 혼잡료,
           PULocationID             AS 승차지역,
           parse_filename(filename) AS 원본파일
    FROM read_parquet('{YELLOW}', union_by_name=true, filename=true)
    WHERE tpep_pickup_datetime >= TIMESTAMP '2024-12-01'
      AND tpep_pickup_datetime <  TIMESTAMP '2025-01-01'
      AND cbd_congestion_fee IS NOT NULL
    ORDER BY tpep_pickup_datetime
""").show(max_rows=30)

┌─────────────────────┬─────────────────────┬────────┬────────┬────────┬────────┬──────────┬─────────────────────────────────┐
│        승차         │        하차         │  거리  │  요금  │  총액  │ 혼잡료 │ 승차지역 │            원본파일             │
├─────────────────────┼─────────────────────┼────────┼────────┼────────┼────────┼──────────┼─────────────────────────────────┤
│ 2024-12-31 20:47:55 │ 2024-12-31 20:54:00 │   1.72 │    9.3 │  17.16 │    0.0 │       48 │ yellow_tripdata_2025-01.parquet │
│ 2024-12-31 20:52:50 │ 2024-12-31 21:09:34 │   5.05 │   23.3 │   32.3 │    0.0 │      249 │ yellow_tripdata_2025-01.parquet │
│         ···         │         ···         │    ··· │    ··· │    ··· │    ··· │      ··· │ yellow_tripdata_2025-01.parquet │
│ 2024-12-31 23:56:19 │ 2025-01-01 00:11:19 │   2.28 │   14.9 │  23.88 │    0.0 │       68 │ yellow_tripdata_2025-01.parquet │
├─────────────────────┴─────────────────────┴────────┴────────┴────────┴────────┴──────────┴─────────────────────────────────┤
│ 21 rows                                                                                                          8 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

# 금액 분포 — 정말 전부 0원인가
duckdb.sql(f"""
    SELECT cbd_congestion_fee, count(*)
    FROM read_parquet('{YELLOW}', union_by_name=true)
    WHERE tpep_pickup_datetime >= TIMESTAMP '2024-12-01'
      AND tpep_pickup_datetime <  TIMESTAMP '2025-01-01'
      AND cbd_congestion_fee IS NOT NULL
    GROUP BY 1 ORDER BY 1
""").show()

# 2025년의 정상 부과액과 비교
duckdb.sql("""
    SELECT cbd_congestion_fee, count(*)
    FROM trips
    WHERE ym = TIMESTAMP '2025-01-01' AND cbd_congestion_fee > 0
    GROUP BY 1 ORDER BY count(*) DESC LIMIT 5
""").show()

┌────────────────────┬──────────────┐     ┌────────────────────┬──────────────┐
│ cbd_congestion_fee │ count_star() │     │ cbd_congestion_fee │ count_star() │
├────────────────────┼──────────────┤     ├────────────────────┼──────────────┤
│                0.0 │           21 │     │               0.75 │      2139541 │
└────────────────────┴──────────────┘     └────────────────────┴──────────────┘

결론

21건 전부 0.00입니다. 2025년 정상 부과액은 0.75 단일 값인데, 이 21건은 한 푼도 부과되지 않았습니다. “시행 전에 잘못 부과된 사례”가 아닙니다.
전부 yellow_tripdata_2025-01.parquet에서 왔습니다. 이게 결정적 증거입니다 — 2024-12 파일에는 cbd_congestion_fee 컬럼 자체가 없으므로, 그 파일에서 읽은 행은 union_by_name 때문에 무조건 NULL입니다. 값이 NULL이 아니라는 사실만으로 “이 행은 2025년 파일에서 왔다”가 논리적으로 확정됩니다.
승차 시각이 전부 12월 31일 밤이고, 여러 건은 하차가 1월 1일로 넘어갑니다. 연말연시를 넘긴 운행이 1월 파일에 담긴 월 경계 artifact입니다.
처리 방침: 제외할 필요 없습니다. 요금·거리가 정상인 정당한 운행이고, 부과액이 0이라 어떤 집계에도 영향을 주지 않습니다.
다만 “파일명 ≠ 승차 월”이라는 사실은 기록해둘 가치가 있습니다. 문제 4(파일 기준)와 문제 6(ym 기준)의 숫자가 미세하게 다른 이유가 바로 이것입니다.

문제 10 — ⚠️ 함정 문제: 정책이 통행량을 줄였는가?

10-1. 먼저 순진하게 답해보기

duckdb.sql("""
    SELECT ym, count(*)
    FROM trips
    WHERE ym >= DATE '2024-12-01'
      AND ym <= DATE '2025-01-31'
    GROUP BY ym
""").show()

┌────────────┬──────────────┐
│     ym     │ count_star() │
├────────────┼──────────────┤
│ 2024-12-01 │      3518502 │
│ 2025-01-01 │      3252361 │
└────────────┴──────────────┘

7.6% 감소. 여기서 멈추면 “정책이 통행량을 줄였다”고 말하고 싶어집니다.

팁 — 두 행을 한 행으로 접어 계산하기: lag()는 “비교 대상이 행마다 바뀔 때” 쓰는 도구입니다. 비교 대상이 두 개로 고정돼 있으면 FILTER로 행을 열로 돌리는 편이 짧습니다.
WITH t AS (
    SELECT count(*) FILTER (WHERE ym = DATE '2024-12-01') AS dec24,
           count(*) FILTER (WHERE ym = DATE '2025-01-01') AS jan25
    FROM trips WHERE ym IN (DATE '2024-12-01', DATE '2025-01-01')
)
SELECT dec24, jan25, round(100.0 * (jan25 - dec24) / dec24, 1) FROM t
두 숫자가 같은 행에 놓이는 순간 증감률은 그냥 뺄셈이 됩니다.

10-2. 그런데 정말 그럴까?

두 지표가 각각 무엇을 담고 있는지 분해해 봅시다.

전월 대비 (-7.6%)      =  계절성  +  연간 추세  +  정책 효과
전년 동월 대비(+13.3%) =            연간 추세  +  정책 효과

전월 대비는 12월(연말 성수기) → 1월(한파·연휴 후 소비 위축)이라는 뉴욕의 구조적 비수기 전환을 통째로 뒤집어씁니다. 정책이 없었어도 떨어질 구간입니다.
전년 동월 대비는 1월끼리 비교하므로 계절성이 상쇄됩니다. 하지만 연간 성장 추세는 여전히 섞여 있습니다.
두 지표 모두 정책 효과만 따로 떼어내지 못합니다. 그러니 “둘 중 어느 쪽이 정답이냐”를 묻는 순간 답이 안 나옵니다. 각각이 무엇을 통제했고 무엇을 못 통제했는지를 따져야 합니다.

우회로 A — 12개월 전부 전년 동월 대비를 구한다

duckdb.sql("""
    WITH m AS (
        SELECT year(ym) AS y, month(ym) AS mo, count(*) AS n
        FROM trips
        GROUP BY 1, 2
    )
    SELECT mo AS '월',
           max(n) FILTER (WHERE y = 2024) AS '2024',
           max(n) FILTER (WHERE y = 2025) AS '2025',
           round(100.0 * (max(n) FILTER (WHERE y = 2025) - max(n) FILTER (WHERE y = 2024))
                       / max(n) FILTER (WHERE y = 2024), 1) AS '전년동월대비%'
    FROM m
    GROUP BY mo
    ORDER BY mo
""").show(max_rows=20)

┌───────┬─────────┬─────────┬───────────────┐
│  월   │  2024   │  2025   │ 전년동월대비% │
├───────┼─────────┼─────────┼───────────────┤
│     1 │ 2869518 │ 3252361 │          13.3 │   ← 정책 시행 첫 달
│     2 │ 2901352 │ 3306159 │          14.0 │
│     3 │ 3439728 │ 3826945 │          11.3 │
│     4 │ 3413924 │ 3671924 │           7.6 │
│     5 │ 3616139 │ 4092206 │          13.2 │
│     6 │ 3427868 │ 3869341 │          12.9 │
│     7 │ 2973595 │ 3494627 │          17.5 │
│     8 │ 2867068 │ 3180650 │          10.9 │
│     9 │ 3483512 │ 3838214 │          10.2 │
│    10 │ 3681586 │ 3943793 │           7.1 │
│    11 │ 3509698 │ 3643519 │           3.8 │
│    12 │ 3518502 │ 4049567 │          15.1 │
└───────┴─────────┴─────────┴───────────────┘

12개월 평균은 +11.4%. 정책 시행 첫 달인 1월의 +13.3%는 평균보다 오히려 높습니다. 4·10·11월이 훨씬 낮습니다. 1월은 특별히 나쁜 달이 아니었습니다.

우회로 B — 연내 상대 위치로 계절 패턴을 겹쳐본다

duckdb.sql("""
    WITH m AS (
        SELECT year(ym) AS y, month(ym) AS mo, count(*) AS n
        FROM trips GROUP BY 1, 2
    )
    SELECT y AS '연', mo AS '월', n AS '건수',
           round(100.0 * n / avg(n) OVER (PARTITION BY y), 1) AS '연평균대비%'
    FROM m
    ORDER BY y, mo
""").show(max_rows=30)

┌───────┬───────┬─────────┬─────────────┐
│  연   │  월   │  건수   │ 연평균대비% │
├───────┼───────┼─────────┼─────────────┤
│  2024 │     1 │ 2869518 │        86.7 │   ← 2024년 최저
│  2024 │     2 │ 2901352 │        87.7 │
│  2024 │     3 │ 3439728 │       104.0 │
│  2024 │     8 │ 2867068 │        86.7 │
│  2024 │    10 │ 3681586 │       111.3 │
│  2024 │    12 │ 3518502 │       106.3 │
│  2025 │     1 │ 3252361 │        88.4 │   ← 2025년도 1월이 최저권, 그런데 더 높다
│  2025 │     2 │ 3306159 │        89.8 │
│  2025 │     8 │ 3180650 │        86.4 │
│  2025 │    12 │ 4049567 │       110.0 │
└───────┴───────┴─────────┴─────────────┘

(24행 중 일부만 표시)

PARTITION BY가 처음 등장하는 자리입니다. avg(n) OVER (PARTITION BY y)는 “같은 연도끼리만 묶어서 평균을 내라”는 뜻입니다. 2024년 행은 2024년 평균과, 2025년 행은 2025년 평균과 비교됩니다. PARTITION BY가 없으면 24개월 전체 평균 하나로 비교하게 되어 의미가 달라집니다.
양쪽 해 모두 1월이 연중 최저권입니다 → 1월이 비수기라는 건 정책과 무관한 구조입니다.
그런데 2025년 1월의 상대 위치(88.4%)가 2024년 1월(86.7%)보다 높습니다. 정책이 통행량을 눌렀다면 반대로 나왔어야 합니다.

모순의 해소

모순이 아닙니다. 두 지표가 서로 다른 질문에 답한 것뿐입니다.

지표	답한 질문	답
-7.6%	“지난달보다 줄었나?”	줄었다 (사실)
+13.3%	“작년 같은 달보다 줄었나?”	늘었다 (이것도 사실)

둘 다 참인데 방향이 반대인 이유는 12월이 성수기이고 1월이 비수기이기 때문입니다.

정책 효과를 보려면 계절성을 통제한 쪽을 써야 합니다. 10-1에서 “정책 때문에 7.6% 줄었다”고 말하면, 사실은 매년 1월마다 일어나는 일을 정책 탓으로 돌린 것입니다.
그렇다고 +13.3%가 “정책 효과 없음”의 증명은 아닙니다. 이 지표도 연간 성장 추세를 못 걷어냅니다. 그래서 다른 11개 달과 나란히 놓고 봐야 했고, 1월이 특별히 나쁘지 않다는 것까지가 이 데이터로 말할 수 있는 최대치입니다.

10-3. 결론 쓰기

2025년 1월 5일부터 혼잡통행료가 실제로 부과되기 시작했으며, 시행 직전 달 대비 택시 운행은 7.6% 감소했습니다. 다만 이 감소를 정책 효과로 단정할 수 없습니다. 12월→1월은 뉴욕에서 매년 반복되는 비수기 전환 구간이고, 실제로 전년 동월 대비로는 13.3% 증가해 방향이 정반대입니다. 2025년 12개월의 전년 동월 대비 평균이 +11.4%인 점을 감안하면 1월의 증가폭은 오히려 평균을 웃돌아, 총 통행량이 정책으로 억제됐다는 근거는 확인되지 않았습니다.
한계: 2025년 전 기간이 정책 시행 상태라 순수 대조군이 없어, 이 데이터만으로 인과관계는 증명할 수 없습니다. 또한 이 정책의 타깃은 택시가 아니라 CBD에 진입하는 자가용이므로, 자가용 이용자가 택시로 전환했다면 택시 통행량 증가는 오히려 정책이 의도대로 작동한 결과일 수 있습니다.
추가로 필요한 데이터: CBD 진입 차량 통행량(자가용 포함), 지하철·버스 승객 수, 그리고 부과 지역과 비부과 지역을 나눈 지역별 데이터가 있으면 이중차분으로 효과를 분리할 수 있습니다.

실무에서 데이터 분석가의 가치는 “숫자를 뽑는 것”이 아니라 “이 숫자로 어디까지 말할 수 있는지 아는 것” 에서 나옵니다.

문제 11 — 부과 대상 지역 확인

1. 승차 지역별 부과율

duckdb.sql(f"""
    SELECT z.Borough                                         AS 자치구,
           z.Zone                                            AS 승차지역,
           count(*)                                          AS 운행건수,
           count(*) FILTER (WHERE t.cbd_congestion_fee > 0)  AS 부과건수,
           round(100.0 * count(*) FILTER (WHERE t.cbd_congestion_fee > 0)
                       / count(*), 1)                        AS "부과율%"
    FROM trips t
    JOIN '{ZONES}' z ON t.PULocationID = z.LocationID
    WHERE t.tpep_pickup_datetime >= TIMESTAMP '2025-01-05'
    GROUP BY z.Borough, z.Zone
    HAVING count(*) >= 1000
    ORDER BY "부과율%" DESC
""").show(max_rows=50)

┌───────────┬───────────────────────────────────┬──────────┬──────────┬─────────┐
│  자치구   │             승차지역              │ 운행건수 │ 부과건수 │ 부과율% │
├───────────┼───────────────────────────────────┼──────────┼──────────┼─────────┤
│ Manhattan │ SoHo                              │   373391 │   372494 │    99.8 │
│ Manhattan │ Greenwich Village North           │   615418 │   613335 │    99.7 │
│ Manhattan │ West Village                      │   968238 │   965425 │    99.7 │
│ Manhattan │ East Village                      │  1087311 │  1083260 │    99.6 │
│ Manhattan │ Midtown Center                    │  1930002 │  1923088 │    99.6 │
│ Manhattan │ Times Sq/Theatre District         │  1378352 │  1371303 │    99.5 │
│    ·      │          ·                        │      ·   │       ·  │      ·  │
│ Brooklyn  │ Canarsie                          │    34768 │      541 │     1.6 │
│ Queens    │ Saint Albans                      │    14340 │      182 │     1.3 │
│ Queens    │ Far Rockaway                      │     8810 │       62 │     0.7 │
│ Brooklyn  │ Starrett City                     │     9853 │       64 │     0.6 │
├───────────┴───────────────────────────────────┴──────────┴──────────┴─────────┤
│ 227 rows (50 shown)                                                 5 columns │
└───────────────────────────────────────────────────────────────────────────────┘

부과율 상위권은 전부 Manhattan입니다. SoHo, Greenwich Village, East Village, Midtown, Times Square — 전형적인 맨해튼 중심부입니다. 하위권은 Brooklyn·Queens 외곽입니다.

세 가지 설계 판단

① 왜 2025-01-05 이후로 잘랐나ym >= '2025-01-01'로 하면 부과 시작 전인 1월 1~4일이 분모에 섞입니다. 그 4일은 부과율 0%라 모든 지역의 부과율을 일률적으로 끌어내립니다. 문제 8에서 시점을 특정해둔 것이 여기서 값을 합니다.

정책 분석의 일반 원칙: 처치 시작 전 기간을 처치군에 넣으면 효과가 희석됩니다.

② HAVING count(*) >= 1000이 필요한 이유 265개 구역 중에는 운행이 수십 건뿐인 곳이 있습니다. 3건 중 3건 부과 = 100% 가 되어 정렬 상단을 점령합니다. 비율을 정렬할 때는 분모의 최소 크기를 반드시 걸어야 합니다.

WHERE vs HAVING: WHERE는 그룹을 만들기 전 행을 거르고, HAVING은 만들어진 그룹을 거릅니다. count(*) 같은 집계 결과로 걸러야 하니 WHERE에는 쓸 수 없습니다.

③ JOIN으로 코드를 이름으로 바꾸기PULocationID는 숫자 코드(1~265)입니다. taxi_zone_lookup.csv와 조인해야 사람이 읽을 수 있는 이름이 됩니다. DuckDB에서는 CSV 파일을 테이블처럼 그대로 조인할 수 있습니다 — 미리 로드할 필요가 없습니다.

2. 자치구 단위로 압축

duckdb.sql(f"""
    SELECT z.Borough  AS 자치구,
           count(*)   AS 운행건수,
           round(100.0 * count(*) FILTER (WHERE t.cbd_congestion_fee > 0)
                       / count(*), 1) AS "부과율%"
    FROM trips t
    JOIN '{ZONES}' z ON t.PULocationID = z.LocationID
    WHERE t.tpep_pickup_datetime >= TIMESTAMP '2025-01-05'
    GROUP BY z.Borough
    ORDER BY "부과율%" DESC
""").show()

┌───────────────┬──────────┬─────────┐
│    자치구     │ 운행건수 │ 부과율% │
├───────────────┼──────────┼─────────┤
│ Manhattan     │ 37797310 │    80.0 │
│ Unknown       │    74044 │    64.2 │
│ Queens        │  4136086 │    39.0 │
│ Brooklyn      │  1466949 │    21.3 │
│ Staten Island │     3656 │    13.0 │
│ EWR           │     1316 │    11.6 │
│ N/A           │     8956 │    10.3 │
│ Bronx         │   338513 │     7.0 │
└───────────────┴──────────┴─────────┘

Manhattan만 80%로 압도적입니다. 하지만 맨해튼 전체가 100%는 아닙니다 — CBD는 맨해튼 전체가 아니라 60번가 남쪽이므로 Upper East/West Side 같은 북부 구역은 부과율이 낮습니다. Borough만 보면 이 층위를 놓칩니다.

3. 부과 기준은 “승차 지역”이 아니라 “CBD 진입 여부”

승차지가 CBD 밖이어도 하차지가 CBD면 부과됩니다. 승하차 조합으로 확인합니다.

duckdb.sql("""
    WITH cbd AS (   -- 정책 문서를 보지 않고 데이터만으로 CBD 구역을 역추정
        SELECT PULocationID AS loc
        FROM trips
        WHERE tpep_pickup_datetime >= TIMESTAMP '2025-01-05'
        GROUP BY 1
        HAVING count(*) >= 1000
           AND 100.0 * count(*) FILTER (WHERE cbd_congestion_fee > 0) / count(*) >= 90
    )
    SELECT t.PULocationID IN (SELECT loc FROM cbd) AS 승차가_CBD,
           t.DOLocationID IN (SELECT loc FROM cbd) AS 하차가_CBD,
           count(*)                                AS 건수,
           round(100.0 * count(*) FILTER (WHERE t.cbd_congestion_fee > 0)
                       / count(*), 1)              AS "부과율%"
    FROM trips t
    WHERE t.tpep_pickup_datetime >= TIMESTAMP '2025-01-05'
    GROUP BY 1, 2
    ORDER BY 1 DESC, 2 DESC
""").show()

┌────────────┬────────────┬──────────┬─────────┐
│ 승차가_CBD │ 하차가_CBD │   건수   │ 부과율% │
├────────────┼────────────┼──────────┼─────────┤
│ true       │ true       │ 17763668 │    99.7 │
│ true       │ false      │  7245441 │    98.0 │
│ false      │ true       │  6437200 │    96.5 │
│ false      │ false      │ 12380521 │    10.0 │
└────────────┴────────────┴──────────┴─────────┘

규칙이 데이터로 완벽하게 재현됩니다. 승차든 하차든 CBD가 하나라도 걸리면 96~99%, 둘 다 밖이면 10%로 급락합니다. (남은 10%는 CBD를 통과한 운행으로 보입니다.)

여기서 cbd CTE는 정책 문서를 보지 않고 데이터만으로 CBD 구역 목록을 역추정한 것입니다. 문제 8이 시간축으로 한 일을 공간축으로 확장한 셈입니다.

4. ⭐ 10-2의 모순을 푸는 진짜 도구 — 이중차분(DiD)

계절성과 연간 추세는 CBD 지역과 비CBD 지역에 똑같이 작용합니다. 두 지역의 변화를 빼면 시간 요인이 상쇄됩니다. \[(\text{CBD}_{2025} - \text{CBD}_{2024}) - (\text{비CBD}_{2025} - \text{비CBD}_{2024})\]

duckdb.sql("""
    WITH cbd AS (
        SELECT PULocationID AS loc
        FROM trips
        WHERE tpep_pickup_datetime >= TIMESTAMP '2025-01-05'
        GROUP BY 1
        HAVING count(*) >= 1000
           AND 100.0 * count(*) FILTER (WHERE cbd_congestion_fee > 0) / count(*) >= 90
    ),
    m AS (
        SELECT month(ym)                             AS mo,
               year(ym)                              AS y,
               PULocationID IN (SELECT loc FROM cbd) AS is_cbd,
               count(*)                              AS n
        FROM trips
        GROUP BY 1, 2, 3
    )
    SELECT mo AS 월,
           round(100.0 * (sum(n) FILTER (WHERE y=2025 AND is_cbd)
                        - sum(n) FILTER (WHERE y=2024 AND is_cbd))
                       / sum(n) FILTER (WHERE y=2024 AND is_cbd), 1)     AS "CBD 증감%",
           round(100.0 * (sum(n) FILTER (WHERE y=2025 AND NOT is_cbd)
                        - sum(n) FILTER (WHERE y=2024 AND NOT is_cbd))
                       / sum(n) FILTER (WHERE y=2024 AND NOT is_cbd), 1) AS "비CBD 증감%"
    FROM m
    GROUP BY mo
    ORDER BY mo
""").show(max_rows=20)

┌───────┬───────────┬─────────────┐
│  월   │ CBD 증감% │ 비CBD 증감% │   차이(직접 계산)
├───────┼───────────┼─────────────┤
│     1 │      14.9 │        11.3 │   +3.6
│     2 │      13.1 │        15.2 │   -2.1
│     3 │       9.1 │        14.4 │   -5.3
│     4 │       6.0 │         9.8 │   -3.8
│     5 │      10.2 │        17.2 │   -7.0
│     6 │       8.0 │        20.0 │  -12.0
│     7 │      13.1 │        24.2 │  -11.1
│     8 │       6.3 │        17.9 │  -11.6
│     9 │       7.7 │        13.7 │   -6.0
│    10 │       4.6 │        10.5 │   -5.9
│    11 │       0.9 │         7.8 │   -6.9
│    12 │      10.0 │        22.1 │  -12.1
└───────┴───────────┴─────────────┘

2월부터 12월까지 11개월 연속으로 CBD의 성장률이 비CBD보다 낮습니다. 격차는 2~12%p이고 후반으로 갈수록 벌어집니다. 총 통행량만 보면 안 보이던 신호가, 지역을 나누자 일관된 방향으로 드러났습니다.

이 결과를 어디까지 말할 수 있나

말할 수 있는 것: 부과 지역의 택시 통행 증가율이 비부과 지역보다 체계적으로 낮았다.
말할 수 없는 것: 이것이 전부 정책 때문이라는 것. 평행 추세 가정(parallel trends) — 정책이 없었어도 두 지역이 나란히 움직였을 것이라는 가정 — 이 필요하고, 이건 증명이 아니라 가정입니다.
1월만 부호가 반대(+3.6) 인 것도 설명이 필요합니다. 1월에는 시행 전 4일이 섞여 있고, 초기 적응 기간이라 아직 효과가 나타나지 않았을 수 있습니다.
검증 방법: 2024년 12개월 동안 두 지역의 추세가 나란히 움직였는지 확인하면 평행 추세 가정을 간접 검증할 수 있습니다.

4부. 성능 — 9천만 행을 다루는 법

문제 12 — 파티셔닝해서 저장하기

import os, glob

os.makedirs('output/parquet', exist_ok=True)

duckdb.sql("""
    COPY (
        SELECT *,
               year(ym)  AS year,
               month(ym) AS month
        FROM trips
    ) TO 'output/parquet/yellow_partitioned' (
        FORMAT parquet,
        PARTITION_BY (year, month),
        OVERWRITE_OR_IGNORE true,   -- 없으면 재실행 시 "Directory is not empty" 에러
        COMPRESSION zstd
    )
""")

dirs = sorted(glob.glob('output/parquet/yellow_partitioned/year=*/month=*'))
print(f"파티션 디렉터리{len(dirs)}개")
size = sum(os.path.getsize(f)
           for f in glob.glob('output/parquet/yellow_partitioned/**/*.parquet', recursive=True))
print(f"총{size/1024/1024/1024:.2f} GB")

파티션 디렉터리 24개
처음: ['...\year=2024\month=1', '...\year=2024\month=10', '...\year=2024\month=11']
마지막: ['...\year=2025\month=7', '...\year=2025\month=8', '...\year=2025\month=9']
총 1.26 GB

duckdb.sql("""
    SELECT count(*) AS 전체행수
    FROM 'output/parquet/yellow_partitioned/**/*.parquet'
""").show()

┌──────────┐
│ 전체행수 │
├──────────┤
│ 83871796 │
└──────────┘

정제 후 행 수 83,871,796이 그대로 보존됐습니다.

`PARTITION_BY`가 하는 일

값에 따라 디렉터리를 자동으로 갈라서 저장합니다:

output/parquet/yellow_partitioned/
├── year=2024/
│   ├── month=1/data_0.parquet
│   ├── month=2/data_0.parquet
│   └── ...
└── year=2025/
    └── ...

Hive 파티셔닝이란: 이 키=값 디렉터리 규칙을 말합니다. Spark·Hive·Athena 등이 전부 같은 관례를 쓰기 때문에, DuckDB로 쓴 결과를 다른 엔진이 그대로 읽습니다.

핵심: year와 month 값은 parquet 파일 안에 저장되지 않습니다. 경로 문자열이 곧 값입니다. 그래서 ① 저장 공간이 절약되고 ② 파일을 열지 않고도 값을 알 수 있습니다. 이 ②가 문제 13의 프루닝으로 이어집니다.

읽을 때는 hive_partitioning이 기본 자동 감지라 year/month가 컬럼으로 자동 복원됩니다.

⚠️ 함정 — `month=10`이 `month=2`보다 앞에 옵니다

출력의 “처음” 목록을 보세요: month=1, month=10, month=11 순입니다. 디렉터리 이름은 문자열이라 사전순으로 정렬됩니다.

DuckDB가 값을 정수로 인식하므로 SQL의 ORDER BY month는 정상입니다.
하지만 Python 쪽 glob 정렬 순서에 의존하면 조용히 틀립니다.
제로 패딩(lpad(month(ym)::VARCHAR, 2, '0'))을 쓰면 정렬은 맞지만, 읽을 때 month가 문자열이 되어 WHERE month = '05'가 됩니다. 트레이드오프입니다.

파티션 키를 고르는 기준

자주 필터링하는 컬럼을 고릅니다. 시계열은 거의 항상 날짜입니다.
카디널리티가 너무 높으면 안 됩니다. (year, month, day)로 731개를 만들면 파일이 잘게 쪼개져 작은 파일 문제(small files problem) 가 생깁니다. duckdb04의 “파일 100MB~10GB” 권장 범위에서 벗어납니다.
24개 파티션 × 약 350만 행은 적정합니다.

문제 13 — 파티션 프루닝 확인

EXPLAIN 읽는 법: 결과는 (key, value) 한 행으로 나오고 트리 문자열은 두 번째 컬럼에 있습니다. .fetchone()[1]로 꺼내 print 해야 트리 모양이 보입니다. .show()로 보면 표 한 칸에 뭉개집니다.

(1) 파티션 + 파티션 키 필터

plan = duckdb.sql("""
    EXPLAIN
    SELECT count(*)
    FROM 'output/parquet/yellow_partitioned/**/*.parquet'
    WHERE year = 2025 AND month = 5
""").fetchone()[1]
print(plan)

┌───────────────────────────┐
│       PARQUET_SCAN        │
│    ────────────────────   │
│       File Filters:       │
│  (year = 2025)(month = 5) │
│                           │
│    Scanning Files: 1/24   │   ← 24개 중 1개만!
│                           │
│      ~3,681,586 rows      │
└───────────────────────────┘

(2) 원본 + 함수로 감싼 필터

plan = duckdb.sql(f"""
    EXPLAIN
    SELECT count(*)
    FROM read_parquet('{YELLOW}', union_by_name=true)
    WHERE year(tpep_pickup_datetime) = 2025
      AND month(tpep_pickup_datetime) = 5
""").fetchone()[1]
print(plan)

┌───────────────────────────┐
│           FILTER          │        ← 스캔 '뒤'에 별도 연산자로 분리됨
│  (("year"(tpep_pickup...  │
│      ~14,230,195 rows     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       READ_PARQUET        │
│        Projections:       │
│    tpep_pickup_datetime   │
│      ~71,150,976 rows     │        ← 7,100만 행을 다 읽는다
└───────────────────────────┘

(3) 원본 + 범위 조건

plan = duckdb.sql(f"""
    EXPLAIN
    SELECT count(*)
    FROM read_parquet('{YELLOW}', union_by_name=true)
    WHERE tpep_pickup_datetime >= TIMESTAMP '2025-05-01'
      AND tpep_pickup_datetime <  TIMESTAMP '2025-06-01'
""").fetchone()[1]
print(plan)

┌───────────────────────────┐
│       READ_PARQUET        │
│          Filters:         │        ← 필터가 스캔 '안'으로 들어갔다
│   tpep_pickup_datetime>=  │
│   '2025-05-01 00:00:00'   │
│    AND ... < '2025-06-01' │
│      ~14,230,195 rows     │
└───────────────────────────┘

세 계획의 차이

쿼리	무슨 일이 일어나나
(1) 파티션	`Scanning Files: 1/24` — 23개 파일을 아예 열지 않음
(2) 함수 필터	`FILTER`가 별도 연산자로 분리됨. 7,100만 행을 전부 읽어서 그 다음에 거름
(3) 범위 필터	`Filters:`가 `READ_PARQUET` 안으로 들어감. 읽으면서 거르고, zonemap으로 row group도 건너뜀

⚠️ 핵심 교훈 — 컬럼을 함수로 감싸지 마세요

WHERE year(tpep_pickup_datetime) = 2025    -- ❌ 컬럼이 함수 안에 갇힘
WHERE tpep_pickup_datetime >= TIMESTAMP '2025-05-01'   -- ✅ 컬럼이 홀로

컬럼에 함수를 씌우면 옵티마이저는 “어떤 원본 값이 이 조건을 통과하는지” 를 역산할 수 없습니다. min/max 통계와 대조할 방법이 없어 프루닝이 통째로 무력화됩니다.

SARGable 조건: 컬럼을 홀로 두고 상수 쪽을 변환한 조건을 말합니다. DuckDB뿐 아니라 모든 RDBMS·인덱스에 공통으로 적용되는 원칙이고, SQLD 범위에서도 다룹니다.

실행 시간

import time
for name, q in [
    ("파티션", "SELECT count(*) FROM 'output/parquet/yellow_partitioned/**/*.parquet' "
               "WHERE year = 2025 AND month = 5"),
    ("원본  ", f"SELECT count(*) FROM read_parquet('{YELLOW}', union_by_name=true) "
               "WHERE year(tpep_pickup_datetime) = 2025 AND month(tpep_pickup_datetime) = 5"),
]:
    t0 = time.time()
    n = duckdb.sql(q).fetchone()[0]
    print(f"{name}:{n:,}행,{time.time()-t0:.2f}초")

파티션: 4,092,206행, 0.01초
원본  : 4,591,844행, 0.20초

20배 차이입니다.

행 수가 다른 점에 주의하세요. 파티션 데이터는 정제된 trips에서 만들었고(409만), 원본은 정제 전(459만)이라 같은 것을 세고 있지 않습니다. 따라서 이 시간 비교는 엄밀한 A/B 테스트가 아닙니다. 다만 Scanning Files: 1/24와 ~71,150,976 rows의 차이는 계획에 명확히 드러나므로, 읽는 양의 차이 자체는 사실입니다.

추가 질문 — 파티션 프루닝 vs zonemap 프루닝

둘 다 “안 읽고 건너뛴다”지만 작동 층위가 다릅니다.

가장 큰 차이는 “파일을 여느냐” 입니다. 파일이 수천 개인 데이터 레이크에서 효과가 압도적입니다.
zonemap은 정렬 의존적입니다. duckdb04 실습에서 id(정렬됨)는 프루닝됐지만 value(무작위)는 50개 row group을 전부 읽어야 했던 이유가 그것입니다.
둘은 함께 작동합니다. 파티션으로 파일을 좁힌 뒤, 그 안에서 zonemap이 row group을 더 좁힙니다. 그래서 실무에서는 “자주 거르는 컬럼으로 파티셔닝 + 그 안에서 다른 컬럼으로 정렬” 조합을 씁니다.

문제 14 — 메모리 압박 상황 만들기

import time

def 측정(label, sql):
    t0 = time.time()
    rows = duckdb.sql(sql).fetchall()
    print(f"{label:10s}{len(rows):>10,}그룹{time.time()-t0:6.2f}초")

# 중간 카디널리티: 승하차 지역 조합 (최대 265x265) — 메모리를 몇 MB밖에 안 쓴다
Q_중 = """
    SELECT PULocationID, DOLocationID,
           count(*) AS n, avg(fare_amount) AS f
    FROM trips
    GROUP BY 1, 2
"""

# 고 카디널리티: 승하차 조합 x 시각 — 해시 테이블이 커져 실제로 압박된다
Q_고 = """
    SELECT PULocationID, DOLocationID,
           date_trunc('hour', tpep_pickup_datetime) AS h,
           count(*) AS n, avg(fare_amount) AS f
    FROM trips
    GROUP BY 1, 2, 3
"""

print("=== 기본 설정 ===")
print("memory_limit  :", duckdb.sql("SELECT current_setting('memory_limit')").fetchone()[0])
print("temp_directory:", duckdb.sql("SELECT current_setting('temp_directory')").fetchone()[0])
측정("중-기본", Q_중)
측정("고-기본", Q_고)

print("\n=== memory_limit = 500MB ===")
duckdb.sql("SET memory_limit = '500MB'")
try:
    측정("중-500MB", Q_중)
    측정("고-500MB", Q_고)
except Exception as e:
    print(f"실패:{type(e).__name__}\n{e}")
finally:
    duckdb.sql("RESET memory_limit")   # 연결 단위 설정이라 반드시 복구

=== 기본 설정 ===
memory_limit  : 50.0 GiB
temp_directory: .tmp
중-기본           56,569그룹    0.88초
고-기본       32,265,093그룹   15.47초

=== memory_limit = 500MB ===
실패: OutOfMemoryException
Out of Memory Error: failed to pin block of size 256.0 KiB (476.3 MiB/476.8 MiB used)

Possible solutions:
* Reducing the number of threads (SET threads=X)
* Disabling insertion-order preservation (SET preserve_insertion_order=false)
* Increasing the memory limit (SET memory_limit='...GB')

관찰 1 — 카디널리티가 곧 메모리다

쿼리	그룹 수	시간
중 (지역 조합)	56,569	0.88초
고 (지역 × 시각)	32,265,093	15.47초

카디널리티(cardinality)란: GROUP BY가 만들어내는 서로 다른 그룹의 개수입니다. 집계는 그룹마다 한 칸씩 해시 테이블에 자리를 잡으므로, 그룹 수가 곧 메모리 사용량입니다.

3,200만 그룹은 8,387만 행에서 나왔습니다. 즉 행 3개당 그룹 1개 — 집계가 거의 압축을 못 하는 상황이고, 이럴 때 메모리가 터집니다.

관찰 2 — 500MB에서는 에러가 났습니다

주목할 점: temp_directory가 .tmp로 이미 설정돼 있었는데도 실패했습니다. 게다가 중간 카디널리티 쿼리조차 통과하지 못했습니다(실패 메시지 전에 중-500MB 줄이 안 찍혔습니다).

이유는 에러 메시지가 알려줍니다: 476.3 MiB/476.8 MiB used. 집계 해시 테이블이 아니라 병렬 스캔 자체의 버퍼가 500MB를 거의 다 쓴 것입니다.

DuckDB는 out-of-core(외부 메모리) 처리를 지원합니다. 메모리가 부족하면 중간 결과를 디스크로 흘려보내고(spill) 계속 진행합니다. 하지만 스필이 만능은 아닙니다 — 스레드마다 잡는 버퍼는 스필할 수 없는 하한선이라, 그 하한 아래로 한도를 내리면 스필할 기회조차 없이 실패합니다.

에러가 제시한 해법 세 가지가 정확히 그 하한을 낮추는 방법입니다:

duckdb.sql("SET threads = 2")                    # 스레드당 버퍼 × 스레드 수를 줄인다
duckdb.sql("SET preserve_insertion_order = false")  # 순서 보존용 버퍼를 포기한다
duckdb.sql("SET memory_limit = '2GB'")           # 한도를 하한 위로 올린다

“에러냐 느려짐이냐”가 의미하는 것

| 상황 | 의미 | | — | — | | 느려지지만 완주 | 알고리즘이 메모리 크기와 무관하게 동작. 데이터가 RAM보다 커도 처리 가능 | | 에러로 중단 | 그 연산은 전부 메모리에 올라가야만 성립. 데이터 크기가 RAM에 종속 |

기본 설정(50 GiB)에서 3,200만 그룹 집계가 15초에 완주했다는 사실이 중요합니다. pandas였다면 이 지점에서 MemoryError로 죽고 대안이 없습니다.
9천만 행이 노트북에서 돌아가는 이유가 이것입니다.
duckdb04 체크리스트의 “메모리 한도를 오히려 낮춰보라” 는 역설적 조언도 같은 맥락입니다. 기본값이 다른 프로세스와 충돌해 OS 스왑을 유발하면 DuckDB 자체 스필보다 훨씬 느려지므로, DuckDB에게 스스로 관리하게 하는 편이 낫습니다. 다만 이번 결과가 보여주듯 하한 아래로 내리면 그냥 실패합니다.

반드시 RESET 하세요. memory_limit은 연결 단위 설정이라 커널이 살아 있는 동안 유지됩니다. try/finally로 감싸면 중간에 에러가 나도 확실히 복구됩니다.

5부. 자유 분석

문제 15 — 직접 질문 세우고 답하기

나의 질문

JFK·LaGuardia 공항 승차 수요는 요일과 시간대에 따라 어떻게 다른가?

답을 내기 전에 적는 예상 함정

① 공항을 무엇으로 정의할 것인가Zone 이름에 'Airport'가 들어가는 걸로 잡으면 EWR(뉴어크, 뉴저지) 까지 딸려옵니다. EWR은 뉴욕 택시의 정상 영업권 밖이라 성격이 완전히 다릅니다.

1,"EWR","Newark Airport","EWR"
132,"Queens","JFK Airport","Airports"
138,"Queens","LaGuardia Airport","Airports"

service_zone = 'Airports' 를 쓰면 JFK(132)·LaGuardia(138) 정확히 2개만 잡힙니다.

② 24개월치를 요일별로 단순 합산하면 안 된다 2024-01-01 ~ 2025-12-31은 731일 = 104주 + 3일입니다. 즉 어떤 요일은 105번, 어떤 요일은 104번 등장합니다. 절대 건수로 비교하면 약 1%의 가짜 차이가 섞이므로 일평균으로 정규화해야 합니다.

duckdb06 함정 ②(avg(a/b) vs sum(a)/sum(b))와 같은 계열입니다 — 분모를 의심하라.

③ 이상 타임스탬프trips 뷰가 이미 기간·순서 조건을 걸었으므로 안전합니다.

달력 테이블 만들기

duckdb.sql("""
    CREATE OR REPLACE VIEW 요일일수 AS
    SELECT dayofweek(d) AS 요일번호, count(*) AS 일수
    FROM generate_series(DATE '2024-01-01', DATE '2025-12-31', INTERVAL 1 DAY) AS t(d)
    GROUP BY 1
""")
duckdb.sql("SELECT * FROM 요일일수 ORDER BY 요일번호").show()

┌──────────┬───────┐
│ 요일번호 │ 일수  │
├──────────┼───────┤
│        0 │   104 │   일요일
│        1 │   105 │   월요일
│        2 │   105 │   화요일
│        3 │   105 │   수요일
│        4 │   104 │   목요일
│        5 │   104 │   금요일
│        6 │   104 │   토요일
└──────────┴───────┘

예상한 대로 월·화·수만 105일입니다.

generate_series(시작, 끝, INTERVAL 1 DAY) 는 날짜를 하루씩 채운 테이블을 즉석에서 만듭니다. “데이터에 없는 것을 세야 할 때” 반드시 필요한 관용구입니다. 택시 데이터에는 “2024년에 월요일이 며칠이었나”라는 정보가 없으므로, 달력을 직접 만들어야 합니다.
dayofweek() 는 0=일요일입니다. 요일 이름만 쓰면 정렬이 알파벳순이 되므로 번호를 ORDER BY에 함께 넣습니다.

요일별 — 정규화 효과를 눈으로 보기

duckdb.sql(f"""
    WITH 공항 AS (
        SELECT LocationID FROM '{ZONES}' WHERE service_zone = 'Airports'
    )
    SELECT dayname(t.tpep_pickup_datetime)   AS 요일,
           dayofweek(t.tpep_pickup_datetime) AS 요일번호,
           max(d.일수)                       AS 등장일수,
           count(*)                          AS 총건수,
           round(count(*) / max(d.일수), 1)  AS 일평균
    FROM trips t
    JOIN 공항 a     ON t.PULocationID = a.LocationID
    JOIN 요일일수 d ON dayofweek(t.tpep_pickup_datetime) = d.요일번호
    GROUP BY 1, 2
    ORDER BY 요일번호
""").show()

┌───────────┬──────────┬──────────┬─────────┬────────┐
│   요일    │ 요일번호 │ 등장일수 │ 총건수  │ 일평균 │
├───────────┼──────────┼──────────┼─────────┼────────┤
│ Sunday    │        0 │      104 │  987280 │ 9493.1 │
│ Monday    │        1 │      105 │ 1037620 │ 9882.1 │   ← 최다
│ Tuesday   │        2 │      105 │  885956 │ 8437.7 │
│ Wednesday │        3 │      105 │  848736 │ 8083.2 │
│ Thursday  │        4 │      104 │  891882 │ 8575.8 │
│ Friday    │        5 │      104 │  880034 │ 8461.9 │
│ Saturday  │        6 │      104 │  705630 │ 6784.9 │   ← 최소
└───────────┴──────────┴──────────┴─────────┴────────┘

월요일 최다(9,882), 토요일 최소(6,785). 출장 수요의 전형적 패턴입니다.
⚠️ 정규화가 실제로 순위를 뒤집었습니다. 화요일과 금요일을 보세요:
총건수 등장일수 일평균
화요일 885,956 105 8,437.7
금요일 880,034 104 8,461.9
총건수로는 화요일이 많고, 일평균으로는 금요일이 많습니다. 화요일이 하루 더 등장했다는 사실 하나로 순위가 바뀐 것이고, 정규화하지 않았다면 “화요일 수요가 금요일보다 높다”는 틀린 결론을 냈을 겁니다. 예상 함정 ②가 실제로 발현된 사례입니다.

	총건수	등장일수	일평균
화요일	885,956	105	8,437.7
금요일	880,034	104	8,461.9

시간대별

duckdb.sql(f"""
    WITH 공항 AS (
        SELECT LocationID FROM '{ZONES}' WHERE service_zone = 'Airports'
    )
    SELECT hour(t.tpep_pickup_datetime) AS 시,
           count(*)                     AS 총건수,
           round(count(*) / 731.0, 1)   AS 일평균
    FROM trips t
    JOIN 공항 a ON t.PULocationID = a.LocationID
    GROUP BY 1
    ORDER BY 1
""").show(max_rows=30)

┌───────┬────────┬────────┐
│  시   │ 총건수 │ 일평균 │
├───────┼────────┼────────┤
│     0 │ 246623 │  337.4 │
│     3 │  15165 │   20.7 │   ← 최저
│     4 │  13839 │   18.9 │
│     8 │ 154016 │  210.7 │
│    12 │ 276077 │  377.7 │
│    16 │ 456927 │  625.1 │   ← 최고
│    20 │ 409913 │  560.8 │
│    23 │ 380714 │  520.8 │
└───────┴────────┴────────┘

(24행 중 일부만 표시)

오후 4시가 정점(625), 새벽 4시가 바닥(19) 으로 33배 차이입니다. 자정(337)이 아침 8시(211)보다 높은 것도 공항 특유의 패턴입니다.

주중 vs 주말

duckdb.sql(f"""
    WITH 공항 AS (
        SELECT LocationID FROM '{ZONES}' WHERE service_zone = 'Airports'
    ),
    분모 AS (
        SELECT sum(일수) FILTER (WHERE 요일번호 IN (0, 6))     AS 주말일수,
               sum(일수) FILTER (WHERE 요일번호 NOT IN (0, 6)) AS 주중일수
        FROM 요일일수
    ),
    운행 AS (
        SELECT hour(t.tpep_pickup_datetime)                AS 시,
               dayofweek(t.tpep_pickup_datetime) IN (0, 6) AS 주말,
               count(*)                                    AS n
        FROM trips t
        JOIN 공항 a ON t.PULocationID = a.LocationID
        GROUP BY 1, 2
    )
    SELECT 운행.시,
           round(sum(n) FILTER (WHERE NOT 주말) / max(분모.주중일수), 1) AS 주중_일평균,
           round(sum(n) FILTER (WHERE 주말)     / max(분모.주말일수), 1) AS 주말_일평균
    FROM 운행, 분모
    GROUP BY 운행.시
    ORDER BY 운행.시
""").show(max_rows=30)

┌───────┬─────────────┬─────────────┐
│  시   │ 주중_일평균 │ 주말_일평균 │
├───────┼─────────────┼─────────────┤
│     7 │       214.2 │       147.2 │   주중이 46% 많다
│     8 │       231.3 │       159.0 │   주중이 45% 많다
│     9 │       292.5 │       211.9 │
│    16 │       632.8 │       605.6 │   비슷해짐
│    18 │       533.1 │       545.1 │   ← 역전
│    21 │       537.9 │       573.1 │   주말이 많다
│    23 │       504.8 │       561.1 │   주말이 11% 많다
└───────┴─────────────┴─────────────┘

(24행 중 일부만 표시)

오전은 주중이 압도적, 오후 6시를 기점으로 역전되어 밤에는 주말이 더 많습니다. 아침 출장 수요와 주말 저녁 귀가 수요가 갈리는 지점입니다.

원래 질문에 답이 됐는가 (자문)

됐다: 시간대별 곡선의 모양과 요일 간 차이를 일평균 기준으로 비교할 수 있습니다.
덜 됐다: “수요”라고 물었는데 센 것은 성사된 승차 건수입니다. 대기 줄이 길어 승차를 포기한 승객은 데이터에 없습니다. 관측된 것은 수요가 아니라 공급 제약 하의 거래량입니다.
다음에 확인할 것: 공항 승차의 평균 대기 간격이나 Airport_fee 부과 여부로 정규 대기열 승차를 구분하면 해석이 정교해집니다.

정리

부	주제	핵심
1부	다중 파일	glob, 스키마 불일치, `union_by_name`, `filename` 가상 컬럼
2부	시계열	`date_trunc`로 월 만들기, `lag()` 윈도우 함수, CTE 층 나누기
3부	정책 분석	`FILTER`, 시점 특정, 이상 데이터 조사, 계절성 함정, 이중차분
4부	성능	`PARTITION_BY`, 파티션 프루닝 vs zonemap, SARGable 조건, 메모리 한계
5부	자유 분석	달력 테이블, 정규화, 질문 설계

이 노트북이 남긴 다섯 가지 감각

에러의 종류가 원인의 층위를 알려준다 — Parser / Binder / Conversion은 각각 다른 곳을 보라는 신호입니다.
0과 NULL은 다르다 — count(*), count(컬럼), FILTER (WHERE > 0)이 세는 것이 각각 다릅니다. 컬럼 이름을 정확히 붙이면 스스로를 속이지 않습니다.
반올림은 마지막에 — 계산 전에 뭉개면 잡음이 신호를 덮습니다.
컬럼을 함수로 감싸지 마라 — 프루닝이 무력화되고, 이건 모든 DB에 공통입니다.
숫자 하나로는 아무것도 못 말한다 — -7.6%와 +13.3%가 동시에 참일 수 있습니다. 무엇을 통제했고 무엇을 못 통제했는지를 따져야 비로소 해석이 됩니다.

가장 어려웠던 건 3부입니다. 문법은 검색하면 나오지만, “전월 대비와 전년 동월 대비가 정반대를 가리킬 때 어느 쪽을 믿을지” 같은 판단은 직접 부딪혀야 늘어납니다. 그리고 실무에서 분석가가 값을 인정받는 지점도 정확히 거기입니다.

DuckDB 07 - Advanced Practice

심화 실습 — 9천만 행 직접 다뤄보기

데이터

이번에 새로 다루는 것

준비 — 데이터 확인

1부. 여러 파일을 하나처럼 다루기

문제 1 — 전체 행 수 세기

문제 2 — 일부러 실패해보기

1. 에러 메시지가 말하는 것

2. 문제 1의 count(*)는 왜 멀쩡했나

3. DuckDB가 여러 파일의 스키마를 정하는 방식

문제 3 — 스키마가 다른 파일들 합치기

알아둘 점 두 가지

문제 4 — 파일별로 나눠 보기

filename 가상 컬럼

결과에서 이미 보이는 것

2부. 시계열 — 24개월 추세

문제 5 — 기준 뷰 만들기

뷰를 쓰는 이유

파생 컬럼 두 개

유지율이 96.8% → 93.3%로 떨어진 이유

문제 6 — 월별 추세

표시용과 정렬용을 분리하는 요령

눈에 띄는 것

문제 7 — 전월 대비 증감률

lag() — 윈도우 함수 입문

CTE로 층을 나누는 이유

⚠️ 함정 — 반올림을 먼저 하고 차이를 계산하면 안 됩니다

이 표에서 3부의 재료가 나옵니다

3부. 정책 효과 분석 — 뉴욕 혼잡통행료

문제 8 — 시행 시점을 데이터로 특정하기

1단계 — 월별로 세 가지를 나란히

왜 컬럼을 세 개나 세나 — “0원도 값이다”

새 문법 — FILTER

2단계 — 일 단위로 좁히기

문법 메모

문제 9 — 이상한 21건

먼저 흔한 실수 하나

실제 행을 눈으로 보기

결론

문제 10 — ⚠️ 함정 문제: 정책이 통행량을 줄였는가?

10-1. 먼저 순진하게 답해보기

10-2. 그런데 정말 그럴까?

우회로 A — 12개월 전부 전년 동월 대비를 구한다

우회로 B — 연내 상대 위치로 계절 패턴을 겹쳐본다

모순의 해소

10-3. 결론 쓰기

문제 11 — 부과 대상 지역 확인

1. 승차 지역별 부과율

세 가지 설계 판단

2. 자치구 단위로 압축

3. 부과 기준은 “승차 지역”이 아니라 “CBD 진입 여부”

4. ⭐ 10-2의 모순을 푸는 진짜 도구 — 이중차분(DiD)

이 결과를 어디까지 말할 수 있나

4부. 성능 — 9천만 행을 다루는 법

문제 12 — 파티셔닝해서 저장하기

PARTITION_BY가 하는 일

⚠️ 함정 — month=10이 month=2보다 앞에 옵니다

파티션 키를 고르는 기준

문제 13 — 파티션 프루닝 확인

(1) 파티션 + 파티션 키 필터

(2) 원본 + 함수로 감싼 필터

(3) 원본 + 범위 조건

세 계획의 차이

⚠️ 핵심 교훈 — 컬럼을 함수로 감싸지 마세요

실행 시간

추가 질문 — 파티션 프루닝 vs zonemap 프루닝

문제 14 — 메모리 압박 상황 만들기

관찰 1 — 카디널리티가 곧 메모리다

관찰 2 — 500MB에서는 에러가 났습니다

“에러냐 느려짐이냐”가 의미하는 것

5부. 자유 분석

문제 15 — 직접 질문 세우고 답하기

나의 질문

답을 내기 전에 적는 예상 함정

달력 테이블 만들기

요일별 — 정규화 효과를 눈으로 보기

시간대별

주중 vs 주말

원래 질문에 답이 됐는가 (자문)

2. 문제 1의 `count(*)`는 왜 멀쩡했나

`filename` 가상 컬럼

`lag()` — 윈도우 함수 입문

새 문법 — `FILTER`

`PARTITION_BY`가 하는 일

⚠️ 함정 — `month=10`이 `month=2`보다 앞에 옵니다