SQL によるデータ分析

は、データエンジニアがデータを集計し、検証し、分析用の形に整えるための共通の言語です。とはどちらも SQL で問い合わせができ、集約、グループ化、ウィンドウ関数、ビューといった基本の書き方は共通です。

DEA では、データ集約、移動平均、グループ化、ピボットの意味を説明できること、Redshift と Athena の SQL でクエリやビューを作れること、そして SQL の書き方による性能の差を見分けられることが問われます。

Point

Aggregate = 集約する です。複数の行を1つの値 (合計、平均、件数など) にまとめることを指します。
Window function (ウィンドウ関数) は、行をまとめて1行にせず、各行の「周り (窓)」の行を見て計算する関数です。
Pivot = 回転軸 の意味で、行に並んだ値を列に回転させる操作です。
和訳
- Aggregate: 集計する
- Weblio英和辞書「aggregate」

概要

分析のは、次の4つの操作の組み合わせです。

操作	何をするか	例
集約	複数の行を1つの値にまとめます	売上の合計、注文件数、平均単価
グループ化	集約の単位を決めます	店舗ごと、日ごと、商品カテゴリごと
ウィンドウ関数	行を残したまま、前後の行や同じグループの行を使って計算します	7日間の移動平均、順位、前日比
ピボット	行の値を列に、または列を行に組み替えます	月を列に並べた売上表

主要技術

①集約と GROUP BY ⚠️ 頻出

集約関数 (SUM、COUNT、AVG、MIN、MAX など) は、GROUP BY で指定した単位ごとに1行の結果を返します。

SELECT store_id,
       order_date,
       SUM(amount)              AS total_sales,
       COUNT(*)                 AS orders,
       COUNT(DISTINCT customer) AS customers
FROM   sales
WHERE  order_date >= DATE '2026-10-01'
GROUP  BY store_id, order_date
HAVING SUM(amount) > 100000;

句	役割	混同しやすい点
WHERE	集約の前に行を絞ります	集約関数は使えません。
GROUP BY	集約の単位を決めます	SELECT の集約していない列は、GROUP BY に入れます。
HAVING	集約の後に、グループを絞ります	「合計が 10 万を超える店舗」のような条件に使います。

小計と総計を一度に出したい場合は、ROLLUP、CUBE、GROUPING SETS を使います。とのどちらでも使えます。

②ウィンドウ関数と移動平均 ⚠️ 頻出

ウィンドウ関数は、OVER 句で「どの行の範囲を見るか」を指定します。

SELECT order_date,
       daily_sales,
       AVG(daily_sales) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d,
       RANK() OVER (PARTITION BY region ORDER BY daily_sales DESC) AS rank_in_region,
       daily_sales - LAG(daily_sales) OVER (ORDER BY order_date) AS diff_from_prev
FROM   daily_summary;

書き方	意味
`PARTITION BY`	窓をグループに分けます (地域ごとの順位など)。GROUP BY と違い、行はまとめられません。
`ORDER BY`	窓の中の並び順です。
`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`	自分を含む直近 7 行を窓にします。7 日間の移動平均になります (1 日 1 行の場合)。
`RANK()`、`ROW_NUMBER()`、`DENSE_RANK()`	順位を付けます。同順位の扱いが違います。
`LAG()`、`LEAD()`	前の行、後の行の値を取ります。前日比に使います。
`SUM() OVER (ORDER BY ...)`	累計 (ランニングトータル) を計算します。

移動平均は、日々の変動をならして傾向を見るための指標です。たとえば「直近 7 日の平均売上」は、毎日の値の上下をならします。

ROW_NUMBER() は重複の除去にも使います。同じ注文 ID の行の中で最新の1行だけを残す、という処理を、PARTITION BY order_id ORDER BY updated_at DESC で番号を付けて 1 番だけを選ぶ形で書けます。

③ピボット

ピボットは、縦に並んだ値を横の列に並べ替える操作です (その逆がアンピボット)。

元のデータ (縦持ち)	ピボット後 (横持ち)
店舗、月、売上の3列で、月ごとに行がある	店舗、1月、2月、3月… の列で、店舗ごとに1行

書き方はエンジンによって違います。

エンジン	書き方
	`PIVOT` と `UNPIVOT` の構文が使えます。
( ベース)	条件付きの集計で書きます。例: `SUM(CASE WHEN month = 1 THEN amount END) AS jan`
どちらでも	条件付きの集計 (CASE 式と集約関数) は共通に使えます。

④ビューとマテリアライズドビュー

種類	結果の保存	向く場面
ビュー	保存しません。参照のたびに元のクエリが実行されます	複雑な結合や列の絞り込みを隠し、利用者に簡単な名前で見せる。アクセスさせる列を限る	対応	対応
	結果を保存し、更新 (REFRESH) で最新にします	重い集計を何度も参照するダッシュボード	対応。自動更新や増分の更新ができます	結果を保存したい場合はで集計テーブルを作ります
遅延バインドのビュー	参照先のテーブルを作成時に確かめません	の外部テーブルを参照するビュー。参照先のテーブルを DROP して作り直す	`WITH NO SCHEMA BINDING`	―

Redshift の通常のビューやマテリアライズドビューは参照先のテーブルと結び付くため、参照先を DROP TABLE しようとすると依存関係のエラーになり、CASCADE を付けるとビューも一緒に消えます。遅延バインドのビューは実行時に参照先を解決するので、参照先を作り直してもビューは残ります。

⚠️ 頻出: 「同じ重い集計を多数の利用者のダッシュボードが繰り返し参照し、応答を速くしたい」は Redshift のマテリアライズドビューです。通常のビューは毎回元のクエリが走るため速くなりません。

⑤SQL の書き方による性能の差

書き方	遅い例	速い例	理由
列の指定	`SELECT *`	必要な列だけ	列指向の形式では、読む列が減るほどスキャン量が減ります。
パーティションの絞り込み	パーティション列に関数をかける (`WHERE year(dt) = 2026`)	パーティション列をそのまま比べる (`WHERE dt BETWEEN ...`)	そのまま比べると、読まないパーティションを除外できます。
結合の順序	大きなテーブル同士を先に結合する	先に絞り込んでから結合する	結合する行が減ります。
重複の除去	大きな結果に対する `DISTINCT` を多用	必要な段階で一度だけ行う	並べ替えとが重い処理です。
件数の概算	`COUNT(DISTINCT x)` を大量のデータで	`APPROX_COUNT_DISTINCT` ( の `approx_distinct`)	厳密さが不要なら近似で高速にできます。
LIMIT	スキャン量を減らす目的で使う	結果の行数を減らす目的で使う	LIMIT は読む量を減らすとは限りません。

では、EXPLAIN で実行計画を確かめ、結合でデータの再配置 (DS_BCAST_INNER、DS_DIST_BOTH など) が起きていないかを見ます。との設計 (#36) がの速さに大きく影響します。

試験対策

①判断マトリクス

問題文のキーワード	選ぶ書き方	理由
日別・店舗別の合計	GROUP BY と集約関数	単位ごとに1行にまとめます。
集計後の条件で絞る (合計が一定以上)	HAVING	集約の後に絞ります。
直近 N 日の移動平均	`AVG() OVER (ORDER BY ... ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW)`	行を残したまま窓で平均します。
地域ごとの順位	`RANK() OVER (PARTITION BY region ORDER BY ...)`	グループごとに順位を付けます。
前日比	`LAG()`	前の行の値を取ります。
同じキーの最新の1行だけを残す	`ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) = 1`	重複を除けます。
月を列に並べた表	の PIVOT、または CASE 式の条件付き集計	行を列に組み替えます。
小計と総計を一度に	ROLLUP / CUBE / GROUPING SETS	複数の集約の単位をまとめて出します。
重い集計を繰り返し速く参照	Redshift の	結果を保存して更新します。
利用者に見せる列を限り、簡単な名前で提供	ビュー	元のテーブルを隠せます。

②よくある誤答

集約関数を WHERE 句で使うこと。集約後の条件は HAVING です。
ウィンドウ関数の PARTITION BY と GROUP BY を同じと考えること。PARTITION BY は行をまとめません。
通常のビューで性能が上がると考えること。結果を保存するのはやです。