分析のための SQL

ウィンドウ関数は、GROUP BY のように行をまとめずに、「同じグループの行たち（ウィンドウ）」を見て計算した値を各行に付け足す関数です。関数() OVER (PARTITION BY グループ ORDER BY 並び) の形で書きます。CTE（WITH 名前 AS (…)）は、途中の結果に名前をつけて段階的に書く方法で、長い分析 SQL が読みやすくなります。

WITH daily AS (
  SELECT order_date, SUM(amount) AS sales
  FROM orders
  GROUP BY order_date
)
SELECT
  order_date,
  sales,
  sales - LAG(sales) OVER (ORDER BY order_date)           AS diff_prev_day,
  SUM(sales) OVER (ORDER BY order_date)                   AS running_total,
  AVG(sales) OVER (ORDER BY order_date
                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily
ORDER BY order_date;

重複の除去・最新の1件

ウィンドウ関数の落とし穴

CTE（共通テーブル式）は、WITH 名前 AS (SELECT …) で途中の結果に名前をつける書き方です。サブクエリを何重にも入れ子にする代わりに、上から順に「何をしているか」を読めるようになります。

WITH daily AS (
  SELECT order_date, SUM(amount) AS sales
  FROM orders
  GROUP BY order_date
),
with_prev AS (
  SELECT order_date, sales,
         LAG(sales) OVER (ORDER BY order_date) AS prev_sales
  FROM daily
)
SELECT order_date, sales,
       ROUND(100.0 * (sales - prev_sales) / NULLIF(prev_sales, 0), 1) AS growth_pct
FROM with_prev;

注意：CTE は読みやすさのための道具で、速くなるとは限りません。BigQuery では、再帰でない CTE を何か所から参照すると、参照の数だけ実行されます。重い CTE を何度も使うなら、一時テーブルや中間のモデルに出します。

重複の除去は、データエンジニアが毎日のように書く形です。CDC や再送で同じ注文が何行も届いたとき、キーごとに一番新しい行だけを残します。

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC, _ingested_at DESC
         ) AS rn
  FROM raw_orders
)
SELECT * FROM ranked WHERE rn = 1;

BigQuery・Snowflake・DuckDB などでは QUALIFY ROW_NUMBER() OVER (…) = 1 と書けば CTE なしで絞り込めます。

同じ形で「ユーザーごとの最新の注文」「商品ごとの最新の価格」も取れます。PARTITION BY に何ごとに1件にしたいか、ORDER BY にどれを残すかを書く、と覚えます。DISTINCT は行のすべての列が同じときしか消せないので、更新された行の重複には使えません。

ウィンドウ関数は便利な一方、エラーにならずに違う数字を返す落とし穴がいくつかあります。レビューで見るところでもあります。

• 同点：ORDER BY updated_at だけだと、同じ時刻の行がどちらになるかは実行ごとに変わり得ます。2つ目の並び（取り込み時刻や ID）を足して結果を決まったものにします。
• 累計の既定の範囲：ORDER BY だけを書くと範囲は RANGE … CURRENT ROW になり、同じ日付の行はまとめて足されます。1行ずつにしたいときは ROWS を明示します。
• WHERE ではウィンドウ関数を使えない：SQL は WHERE の後にウィンドウ関数を計算するので、CTE やサブクエリで包んでから絞ります。
• LAG は「前の行」であって「前の日」ではありません。売上のない日があると、2日前と比べてしまいます。日付の表とつないで穴を埋めてから使います。

「連続ログイン日数の最大」「在庫切れが続いた期間」のように、続いているかたまりを見つける問題を gaps and islands と呼びます。SQL の面接の定番です。決まった解き方は「日付から、並び順の連番を引く」です。連続した日なら、日付も連番も1ずつ増えるので、引いた値が同じになります。

WITH d AS (            -- 1日に何回ログインしても1行に
  SELECT DISTINCT user_id, login_date FROM logins
),
g AS (
  SELECT user_id, login_date,
         login_date - CAST(ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
  FROM d
),
streak AS (
  SELECT user_id, grp, COUNT(*) AS days
  FROM g GROUP BY user_id, grp
)
SELECT user_id, MAX(days) AS max_streak
FROM streak GROUP BY user_id;

上は PostgreSQL の書き方（日付から整数を引くと日付になる）です。BigQuery では DATE_SUB(login_date, INTERVAL rn DAY) のように書きます。最初に DISTINCT で1日1行にするのを忘れると、同じ日の2回目のログインで連番がずれます。

NULL は「値が分からない」という特別な状態で、= で比べると真でも偽でもない「不明」になります。WHERE は真の行だけを残すので、NULL が混ざると行が黙って消えます。数字が合わないときの原因の上位です。

レポートでは「地域×商品の売上」と一緒に「地域ごとの小計」「全体の合計」もほしくなります。UNION ALL で何本もつなぐ代わりに、GROUP BY の拡張で1回で出せます。

SELECT
  CASE WHEN GROUPING(region)  = 1 THEN '（全地域）' ELSE region  END AS region,
  CASE WHEN GROUPING(product) = 1 THEN '（小計）'   ELSE product END AS product,
  SUM(amount) AS sales
FROM sales
GROUP BY ROLLUP(region, product)
ORDER BY GROUPING(region), region, GROUPING(product), product;

小計の行では、まとめた列が NULL になります。もとのデータにも NULL があると見分けられないので、GROUPING(列)（まとめた行なら 1）で判定してラベルをつけます。

使えない組み合わせは、集計を何本か書いて UNION ALL でつなぎます。

行を列に回す（ピボット）

ピボット（行を列に回す。例：月を横に並べる）は、どの DB でも動く条件つき集計で書くのが確実です。

SELECT
  product,
  SUM(CASE WHEN month = '2026-01' THEN amount ELSE 0 END) AS jan,
  SUM(CASE WHEN month = '2026-02' THEN amount ELSE 0 END) AS feb,
  SUM(CASE WHEN month = '2026-03' THEN amount ELSE 0 END) AS mar
FROM monthly_sales
GROUP BY product;

PostgreSQL では SUM(amount) FILTER (WHERE month = '2026-01') とも書けます。BigQuery・Snowflake・SQL Server には PIVOT 句がありますが、構文が少しずつ違います。どの書き方でも列の名前は SQL に書いておく必要があり、月が増えるたびに列を足すことになります。列が動的に増えるものは、縦持ち（月・金額の2列）のまま BI ツールで横に並べるほうが運用が楽です。

クラウド DWH は列指向（列ごとに保存）なので、読む列を減らすほど読むデータ量が減ります。BigQuery のオンデマンド料金のように「読んだ量」で課金される場合、そのまま料金と時間に効きます。

• SELECT * を避ける：100列のテーブルで3列しか使わないなら、3列だけ書けば読む量はおよそ数十分の1です。LIMIT 10 をつけても、多くの場合読む量は減りません（全部読んでから10行返す）。中身を見たいだけならプレビュー機能を使います。
• パーティション：日付などでテーブルを区切って保存し、WHERE event_date = '2026-11-01' のように条件をつけると、その区切りだけを読みます（パーティションの刈り込み）。列に関数をかける（WHERE DATE(created_at) = … を時刻のパーティションに対して書く等）と刈り込みが効かない場合があるので、実行前の見積もりで確かめます。BigQuery ではパーティションの条件を必須にする設定もあります。
• クラスタリング：パーティションの中を、よく絞り込む列（user_id・country など）で並べて保存します。その列で絞ると読むブロックが減ります。パーティションは「日付など1つの粗い区切り」、クラスタリングは「その中でよく絞る数列」と使い分けます。

実行前に「何 GB 読むか」を見る習慣をつけるのが、一番の節約です。

クラウド DWH の料金の仕組み

COUNT(DISTINCT user_id) は、全部の ID を覚えて重複を除くので、何億行にもなると重くなります。しかも「日別のユニーク数」を足しても「月のユニーク数」にはなりません（2日来た人を2回数えるため）。

近似を使うときは、利用者に「近似値である」と伝えて合意することが先です。経営の公式の数字や請求に使う数は正確に数えます。

時刻は UTC で保存し、集計するときに利用者のタイムゾーンに直すのが基本です。日本の「日別の売上」を UTC の日付で区切ると、日本時間の 0時〜9時の注文が前の日に入ってしまいます。

• パーティションの日付も UTC か日本時間かを決めて、テーブルの説明に書く。食い違うと、日付で絞ったときに数時間分が漏れる。
• 期間の条件は「以上・未満」で書く（ts >= '2026-11-01' AND ts < '2026-11-02'）。BETWEEN や「23:59:59 まで」は端の値で漏れる。
• イベントの時刻はスマホの時計で付くことがあり、ずれていることがある。届いた時刻（サーバーの時刻）も一緒に持つ。