2026-04-30·実装·⏱ 約 6 分

SQL 中級 ─ ML エンジニアが書く 7 つの定石パターン

Window 関数・CTE・PIVOT・自己結合など、データ分析で必須の中級 SQL を 7 パターン。BigQuery・Snowflake・PostgreSQL で動く形で。

SQL 中級 はデータエンジニア・データサイエンティストの基礎体力。Pandas より速く、メモリも食わず、データウェアハウス上で完結します。本記事で 7 つの定石を紹介。

1. CTE(WITH 句) ─ 可読性の救世主

ネストせずに段階的に

WITH active_users AS (
    SELECT user_id
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
),
user_purchases AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT a.user_id, COALESCE(p.total, 0) AS spent
FROM active_users a
LEFT JOIN user_purchases p USING (user_id);

2. ROW_NUMBER() ─ 各グループから上位 N

ユーザー毎の最新注文

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
    FROM orders
) t
WHERE rn = 1;

3. LAG / LEAD ─ 前後の行を参照

前日比

SELECT date, sales,
       LAG(sales, 1) OVER (ORDER BY date) AS prev_sales,
       sales - LAG(sales, 1) OVER (ORDER BY date) AS diff
FROM daily_sales;

4. 累積和・移動平均

ROWS BETWEEN を使う

SELECT date, sales,
       SUM(sales) OVER (ORDER BY date) AS cumulative_sum,
       AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM daily_sales;

5. PIVOT(縦持ち → 横持ち)

条件付き集計で代用

SELECT user_id,
       SUM(CASE WHEN category = 'A' THEN amount ELSE 0 END) AS cat_a,
       SUM(CASE WHEN category = 'B' THEN amount ELSE 0 END) AS cat_b,
       SUM(CASE WHEN category = 'C' THEN amount ELSE 0 END) AS cat_c
FROM orders
GROUP BY user_id;

6. 自己結合(コホート分析)

新規ユーザーのリテンション

WITH first_purchase AS (
    SELECT user_id, MIN(order_date) AS first_date
    FROM orders GROUP BY user_id
),
retention AS (
    SELECT f.user_id, f.first_date, o.order_date,
           EXTRACT(MONTH FROM AGE(o.order_date, f.first_date)) AS months_since
    FROM first_purchase f
    JOIN orders o USING (user_id)
)
SELECT first_date, months_since, COUNT(DISTINCT user_id) AS users
FROM retention
GROUP BY first_date, months_since
ORDER BY first_date, months_since;

7. ARRAY と UNNEST(NoSQL 風データ)

BigQuery の場合

-- ARRAY を行に展開
SELECT user_id, tag
FROM users, UNNEST(tags) AS tag
WHERE tag IN ('python', 'ml');

-- ARRAY を集約
SELECT user_id, ARRAY_AGG(DISTINCT product_category) AS categories
FROM orders
GROUP BY user_id;

パフォーマンス Tips

インデックスを意識: WHERE / JOIN 列に
EXPLAIN ANALYZE: 実行計画を確認
パーティション: 日付列でパーティショニング(BigQuery / Snowflake)
列指向 DB: BigQuery / Snowflake は列指向、必要列だけ SELECT
LIMIT を先に: サブクエリで早期に絞り込む

Pandas vs SQL

SQL が速い: 大量データの集計・ウィンドウ関数
Pandas が便利: 小規模データの探索的分析・ ML 前処理
併用: SQL で抽出 → Pandas で前処理 → モデル学習が王道

学習リソース

[Pandas Tips](/blog/pandas-tips-for-ml)
[DS 検定ロードマップ](/certs/ds-literacy/roadmap)
[DS 基礎ロードマップ](/certs/ds-basic/roadmap)
[scikit-learn 入門](/blog/sklearn-introduction)