第03回: DuckDB によるデータ集計

資料のダウンロード

ファイル	内容
exercise.py	ハンズオン演習スクリプト

ヒントの使い方

python exercise.py --hint 1            # 方向性ヒント
python exercise.py --hint 1 --level 2  # より具体的なヒント
python exercise.py --hint 1 --level 3  # コード例
python exercise.py --check             # 提出前の自己チェック

演習問題への取り組み方

1. 下の講義ノートを読む（事前学習・2時間目安）
2. exercise.py を実行してグラフを確認する
3. 演習問題（5問）に取り組む
4. python exercise.py --check で確認する
5. exercise.py を提出する（締切は授業後1週間）

詰まったとき

# ヒントを見る
python exercise.py --hint N --level 1

# AIに質問する
python ../../ask.py "質問内容"

解決しない場合は Q&A ページの投稿フォームを使ってください。

---

講義ノート

1. DuckDB とは何か

DuckDB は「インプロセス（in-process）OLAP データベース」です。SQLite のようにファイル1つで動き、外部サーバは不要です。しかし SQLite が行ごとに読み書きする OLTP 向けなのに対し、DuckDB は列単位で圧縮・処理する OLAP 設計をとっています。これにより、数百万行のデータでもメモリ効率よく高速に集計できます。

DuckDB の主な特徴

特徴	説明
インプロセス動作	Python プロセス内に組み込まれる。サーバ起動不要
Parquet ネイティブ対応	read_parquet() でファイルを直接クエリできる
pandas / Arrow 連携	.df() で DataFrame に、.arrow() で Arrow Table に変換
標準 SQL + 拡張	WINDOW 関数・CTE・ASOF JOIN など分析 SQL を豊富にサポート
無料・オープンソース	MIT ライセンス、商用利用可

import duckdb
con = duckdb.connect()  # インメモリ接続

# Parquet ファイルを直接クエリ（ファイル全体をメモリに展開しない）
df = con.execute("SELECT * FROM read_parquet('data.parquet') LIMIT 5").df()

---

2. GROUP BY 集計

GROUP BY は「カテゴリ別に集約する」最もよく使う操作です。

SELECT
    place_type,
    AVG(happiness_score) AS avg_happiness,
    COUNT(*)             AS n
FROM read_parquet('city_emotion.parquet')
GROUP BY place_type
ORDER BY avg_happiness DESC

集計関数の種類

関数	説明
COUNT(*)	行数
SUM(col)	合計
AVG(col)	平均
MIN(col) / MAX(col)	最小・最大
STDDEV(col)	標準偏差
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col)	中央値

可視化との接続: GROUP BY 結果は「カテゴリ × 数値」の表なので、棒グラフ（ax.bar()）と相性が良い。複数の集計列を並べる場合は grouped bar chart（横に並べた棒グラフ）を使う。

---

3. WINDOW 関数

WINDOW 関数は「行を削除せずに集計値を隣に追加する」操作です。移動平均・累積和・順位付けに使います。

SELECT
    location_id,
    week,
    happiness_score,
    AVG(happiness_score) OVER (
        PARTITION BY location_id   -- グループ（地点ごと）
        ORDER BY week              -- 並び順
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW  -- 直近4行
    ) AS happiness_ma4
FROM read_parquet('city_emotion.parquet')

主要な WINDOW 関数

関数	用途
AVG(...) OVER (...)	移動平均
SUM(...) OVER (...)	累積和
MAX(...) OVER (...)	累積最大値
ROW_NUMBER() OVER (...)	行番号（1始まり）
RANK() OVER (...)	順位（同値は同順位）
LAG(col, n) OVER (...)	n 行前の値
LEAD(col, n) OVER (...)	n 行後の値

フレーム指定の読み方

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW  → 現在行を含む直近4行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW → 先頭から現在行まで（累積）
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING  → 前後1行ずつ（3行）

可視化との接続: 移動平均はオリジナルの時系列と重ねて折れ線グラフで描くと、トレンドと変動の分離が直感的に伝わる。

---

4. CASE WHEN による条件分岐

CASE WHEN は「値を条件で区分する」列変換です。数値を「低・中・高」のようなカテゴリに変換するときに使います。

SELECT
    noise_level,
    CASE
        WHEN noise_level < 50 THEN '静か'
        WHEN noise_level < 65 THEN '普通'
        ELSE                       'うるさい'
    END AS noise_category
FROM read_parquet('city_emotion.parquet')

よくある使い方

-- 年齢区分
CASE WHEN age < 30 THEN '若年' WHEN age < 60 THEN '中年' ELSE '高齢' END

-- NULL の置換（COALESCE でも可）
CASE WHEN col IS NULL THEN 0 ELSE col END

-- フラグ化
CASE WHEN score >= 7 THEN 1 ELSE 0 END AS is_high_score

可視化との接続: 区分後に GROUP BY と組み合わせて積み上げ棒グラフ（kind="bar", stacked=True）で分布を見せると、カテゴリ間の比較がしやすい。

---

5. CTE（WITH 句）と複数集計の組み合わせ

複数の集計結果を結合したい場合、CTE（Common Table Expression）で中間テーブルを作ると可読性が上がります。

-- 全体平均と地点別平均を比較する例
WITH global_avg AS (
    SELECT AVG(happiness_score) AS avg_all
    FROM read_parquet('city_emotion.parquet')
),
location_avg AS (
    SELECT location_id, place_type, AVG(happiness_score) AS avg_loc
    FROM read_parquet('city_emotion.parquet')
    GROUP BY location_id, place_type
)
SELECT
    l.location_id,
    l.place_type,
    ROUND(l.avg_loc, 2)           AS avg_happiness,
    ROUND(l.avg_loc - g.avg_all, 2) AS diff_from_global
FROM location_avg l
CROSS JOIN global_avg g
ORDER BY diff_from_global DESC

SQL 実行順序の確認

SQL は記述順と実行順が異なります。

実行順: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

この順序を意識すると「WHERE の後に GROUP BY するから WHERE 句で集計関数は使えない」といったエラーの原因が分かります。

---

6. 集計結果の可視化戦略

集計の種類	推奨グラフ
カテゴリ別の平均・合計	棒グラフ（bar chart）
カテゴリ別の分布	箱ひげ図（boxplot）、バイオリンプロット
時系列 + 移動平均	折れ線グラフ（line chart）の重ね描き
カテゴリ × カテゴリの件数	ヒートマップ（heatmap）、積み上げ棒グラフ
2変数の関係（散布）	散布図（scatter plot）

pandas との連携パターン

# SQL で集計 → pandas DataFrame → matplotlib
df = con.execute("SELECT place_type, AVG(happiness_score) FROM ...").df()
df.plot(kind="bar", x="place_type", y="avg_happiness")

DuckDB は SQL で集計・絞り込みを済ませてから DataFrame にするため、メモリ効率が良い。巨大データは「SQL で 1/100 に絞ってから pandas」が基本戦略です。

---

まとめ

• DuckDB は Python 内で動くインプロセス OLAP エンジン。Parquet を直接クエリできる。
• GROUP BY でカテゴリ別集計。棒グラフに接続しやすい。
• WINDOW 関数 で行を消さずに集計値を付加。移動平均・累積値・順位に使う。
• CASE WHEN で値を区分し、新しいカテゴリ列を作成する。
• CTE（WITH 句） で複数集計を可読性高く組み合わせる。
• 集計後は「集計の種類」に合ったグラフ種別を選ぶ。

---

次回（第4回）は Polars の遅延評価（LazyFrame）を使い、より大規模データにも対応できるメモリ効率の良い前処理を学びます。