QUESTION 6
結合してから集計 — JOIN した結果を GROUP BY して親ごとに子を数える
前提知識
▼
実務のデータは複数テーブルに分かれています。JOIN で結合してから GROUP BY すると、「親（顧客）ごとに子（注文）を集計する」が表現できます。子が1件も無い親も残したいときは LEFT JOIN を使います。
-- customers を軸に orders を左外部結合してから集計 SELECT c.name, COUNT(o.order_id), SUM(o.amount) FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name;
外部結合と COUNT の関係：LEFT JOIN で注文が無い顧客は、結合相手が NULL の1行になります。ここで
COUNT(*) はその NULL 行も1件と数えてしまうのに対し、COUNT(o.order_id) はNULL を無視して0件と正しく数えます。合計は COALESCE(SUM(...), 0) で NULL を0に整えます。
問題
▼
customers と orders を結合し、顧客ごとの注文件数と合計金額を求めてください。注文が1件も無い顧客も 0件・0円で残すこと。出力列は name, order_count, total_amount。total_amount の降順、同額なら name の昇順で返してください。
使用テーブル
► customers（3行）
| customer_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
► orders（5行）
| order_id | customer_id | amount |
|---|---|---|
| 101 | 1 | 1200 |
| 102 | 1 | 800 |
| 103 | 2 | 3000 |
| 104 | 2 | 1500 |
| 105 | 2 | 500 |
期待出力
▼
| name | order_count | total_amount |
|---|---|---|
| Bob | 3 | 5000 |
| Alice | 2 | 2000 |
| Carol | 0 | 0 |
模範解答コード
▼
SELECT c.name, COUNT(o.order_id) AS order_count, -- NULLを無視して注文行だけ数える（注文0件の顧客は0になる） COALESCE(SUM(o.amount), 0) AS total_amount -- 合計がNULL（注文なし）の場合は0に置き換える FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id -- 注文が無い顧客も残す左外部結合 GROUP BY c.customer_id, c.name -- 顧客単位でグループ化（主キーと表示名で束ねる） ORDER BY total_amount DESC, c.name; -- 合計金額の降順、同額は名前の昇順 /* 実行順序（SQLの論理的な評価順）: 1. FROM customers LEFT JOIN orders → 全顧客を保持 2. GROUP BY c.customer_id, c.name → 顧客でグループ化 3. 集計 → COUNT・SUM（NULLは0補正） 4. ORDER BY total_amount DESC, name → 合計の多い順で並べ替え */
解説（テーブル変化・ポイント）
▼
LEGEND
データ取得・読込対象
除外・非表示データ
① FROM customers LEFT JOIN orders
FROM customers LEFT JOIN orderscustomers を軸に orders を左外部結合します。Carol は対応する注文が無いため、order_id・amount が NULL の1行として残ります（この NULL の扱いが後で効いてきます）。
1 / 4
| customer_id | name | order_id | amount |
|---|---|---|---|
| 1 | Alice | 101 | 1200 |
| 1 | Alice | 102 | 800 |
| 2 | Bob | 103 | 3000 |
| 2 | Bob | 104 | 1500 |
| 2 | Bob | 105 | 500 |
| 3 | Carol | NULL | NULL |
6行（Carol は結合相手なしで NULL）
学習ポイント
「結合してから集計」の順序：実行順は FROM/JOIN → GROUP BY → 集計。まず結合で行を作り、その結合後の行に対してグループ化と集計が走ります。1対多（顧客1：注文多）の関係を「親ごとに子をまとめる」のが結合集計の基本形です。
LEFT JOIN + COUNT(列) で「0件」を正しく数える：注文の無い顧客は結合で NULL 行になります。
COUNT(*) はこの行も1と数えてしまいますが、COUNT(o.order_id) は NULL を無視して0と数えます。「子の件数」は必ず子テーブルの列で COUNT するのが鉄則です。COALESCE で NULL を 0 に整える：集計対象が無いグループの
SUM は 0 ではなく NULL です。COALESCE(SUM(o.amount), 0) で NULL を 0 に置き換え、「注文なし＝0円」という直感どおりの表示にできます。アンチパターン
件数を COUNT(*) で数える：LEFT JOIN 後に
COUNT(*) を使うと、注文0件の Carol が「1件」と誤カウントされます。結合先の列 COUNT(o.order_id) を使えば NULL 行は除かれ、正しく0になります。INNER JOIN にして0件の親が消える：「注文0件の顧客も出したい」のに
INNER JOIN を使うと、Carol は結果から丸ごと脱落します。親を全件残したいなら LEFT JOIN。要件（0件を残すか）でJOIN種別を選びましょう。実務コラム：1対多の集計は「親軸 LEFT JOIN」が定石
顧客×注文、記事×コメント、商品×レビュー——実務の集計の大半は1対多です。型は決まっていて、残したい親テーブルを FROM に置き、子を LEFT JOIN、子の列で COUNT/SUM、欠損は COALESCE。これだけで「全顧客の注文実績（0件含む）」のような表が安全に作れます。子側で先に WHERE 条件を効かせたい場合は、LEFT JOIN の
ON 句に条件を書く（WHERE に書くと外部結合が内部結合化して0件が消える）のがコツです。QUESTION 7
期間でグループ化 — DATE_TRUNC で日付を月単位に丸めて集計する
前提知識
▼
日付ごとの明細を「月別」「週別」にまとめたいとき、DATE_TRUNC で日付を指定単位に丸めて(truncate)から GROUP BY します。列そのものではなく式（計算結果）でグループ化できるのがポイントです。
-- 日付を「月初」に丸めて、同じ月の行を1グループにする DATE_TRUNC('month', sale_date) -- 2024-01-05 → 2024-01-01 DATE_TRUNC('day', ts) -- 時刻を切り捨てて日単位に
「式でグループ化」の考え方：同じ月の日付（1/5, 1/20…）は
DATE_TRUNC('month', …) ですべて 2024-01-01 という同じ値になります。GROUP BY はこの丸めた値でまとめるため、月別集計が成立します。GROUP BY には SELECT と同じ式を書く（または列番号 GROUP BY 1 で代用）点に注意。
問題
▼
sales テーブルから、月別の売上件数と合計金額を求めてください。出力列は month, sales_count, total_amount。month の昇順（時系列順）で返してください。
使用テーブル
► sales（6行）
| sale_id | sale_date | amount |
|---|---|---|
| 1 | 2024-01-05 | 1000 |
| 2 | 2024-01-20 | 1500 |
| 3 | 2024-02-03 | 2000 |
| 4 | 2024-02-15 | 500 |
| 5 | 2024-02-28 | 1000 |
| 6 | 2024-03-10 | 3000 |
期待出力
▼
| month | sales_count | total_amount |
|---|---|---|
| 2024-01-01 | 2 | 2500 |
| 2024-02-01 | 3 | 3500 |
| 2024-03-01 | 1 | 3000 |
模範解答コード
▼
SELECT DATE_TRUNC('month', sale_date)::date AS month, -- 月初の日付として返す COUNT(*) AS sales_count, -- 月内の件数 SUM(amount) AS total_amount -- 月内の合計金額 FROM sales GROUP BY DATE_TRUNC('month', sale_date) -- SELECTと同じ式でグループ化（GROUP BY 1 でも可） ORDER BY month; -- 月初の昇順（時系列順） /* 実行順序（SQLの論理的な評価順）: 1. FROM sales → 行を読込 2. DATE_TRUNC('month', sale_date) → 各行を月初に丸める 3. GROUP BY (丸めた値) → 月でグループ化 4. COUNT(*) / SUM(amount) → 月別に集計 5. ORDER BY month → 時系列に整列 */
解説（テーブル変化・ポイント）
▼
LEGEND
データ取得・読込対象
① FROM sales（6行）
FROM salessales テーブルの6行を読み込みます。sale_date は日単位でバラバラ。これを月単位に丸めて集計するのが目的です。
1 / 4
| sale_id | sale_date | amount |
|---|---|---|
| 1 | 2024-01-05 | 1000 |
| 2 | 2024-01-20 | 1500 |
| 3 | 2024-02-03 | 2000 |
| 4 | 2024-02-15 | 500 |
| 5 | 2024-02-28 | 1000 |
| 6 | 2024-03-10 | 3000 |
6行読込（日単位）
学習ポイント
列だけでなく「式」でグループ化できる：GROUP BY のキーは生の列に限りません。
DATE_TRUNC('month', sale_date) のような式の計算結果でまとめられます。これが「月別」「週別」など、元データに無い粒度の集計を可能にします。「丸める」から束ねられる：DATE_TRUNC は同じ月の日付をすべて同一の月初値に変換します。値が同じになるからこそ1グループにまとまるわけです。粒度を変えたいときは
'month' を 'week' や 'day'、'quarter' に差し替えるだけです。GROUP BY には式を再掲（または GROUP BY 1）：SELECT のエイリアス
month は GROUP BY の評価段階ではまだ確定していない場合があるため、同じ式を書くか 列番号 GROUP BY 1 を使うのが確実です（PostgreSQL はエイリアスも許容しますが、移植性を考えると式の再掲が安全）。アンチパターン
生の日付で GROUP BY する：
GROUP BY sale_date では日付が1日でも違えば別グループになり、月別にならず日別にバラけます。月でまとめたいなら必ず丸めた式（DATE_TRUNC）でグループ化します。月名の文字列だけでまとめる：
GROUP BY TO_CHAR(sale_date,'MM') のように「月番号の文字列」だけでまとめると、別の年の同月（2023年1月と2024年1月）が混ざり、さらに文字列ソートで時系列が崩れます。年も含む DATE_TRUNC（日付型）でまとめるのが安全です。実務コラム：時系列ダッシュボードは粒度の差し替えだけ
売上推移、登録数の伸び、エラー発生件数——時系列レポートは
DATE_TRUNC による期間グループ化が土台です。第1引数の単位を差し替えるだけで日次・週次・月次・四半期と粒度を切り替えられ、同じ集計ロジックを使い回せます。表示用にラベルを整えたいときは TO_CHAR(month, 'YYYY-MM') で文字列に整形しますが、並べ替えや結合のキーには日付型のまま使うのが、ソート崩れを防ぐコツです。QUESTION 8
区分でグループ化 — CASE 式で値をビン分割してカテゴリ別に集計する
前提知識
▼
「金額帯」「年齢層」のように連続値を区分（ビン）に分けて集計したいときは、CASE 式でカテゴリを作り、その式で GROUP BY します。生データに無い「区分」という軸を、その場で生成できます。
-- price を3つの価格帯に振り分けてグループ化キーにする CASE WHEN price < 500 THEN 'Low' WHEN price < 5000 THEN 'Mid' -- 500〜4999（上のWHEN通過＝500以上が確定済み） ELSE 'High' END
CASE は「上から順に最初の一致」：WHEN は上から評価され、最初に真になった枝で確定します。2つ目の
price < 5000 が「500以上5000未満」を意味するのは、1つ目の < 500 を通過した行だけが到達するからです。境界条件は順序が命です。
問題
▼
products テーブルから、価格帯ごとの商品数と平均価格を求めてください。価格帯は Low(500未満)・Mid(500以上5000未満)・High(5000以上)。出力列は price_band, product_count, avg_price（平均は整数に丸め）。avg_price の昇順で返してください。
使用テーブル
► products（6行）
| product_id | product | price |
|---|---|---|
| 1 | Pen | 100 |
| 2 | Notebook | 300 |
| 3 | Bag | 1500 |
| 4 | Lamp | 2500 |
| 5 | Chair | 8000 |
| 6 | Desk | 12000 |
期待出力
▼
| price_band | product_count | avg_price |
|---|---|---|
| Low | 2 | 200 |
| Mid | 2 | 2000 |
| High | 2 | 10000 |
模範解答コード
▼
SELECT CASE WHEN price < 500 THEN 'Low' -- 500未満 WHEN price < 5000 THEN 'Mid' -- 500以上5000未満（上のWHEN通過済みのため） ELSE 'High' -- 5000以上 END AS price_band, COUNT(*) AS product_count, -- 価格帯ごとの商品数 ROUND(AVG(price), 0) AS avg_price -- 価格帯ごとの平均価格（整数に丸め） FROM products GROUP BY CASE WHEN price < 500 THEN 'Low' WHEN price < 5000 THEN 'Mid' ELSE 'High' END -- SELECTと同じCASE式でグループ化（GROUP BY price_band でも可） ORDER BY avg_price; -- 平均価格の昇順 /* 実行順序（SQLの論理的な評価順）: 1. FROM products → 行を読込 2. CASE で price_band を算出 → 価格帯を分類 3. GROUP BY (CASE式) → 価格帯でグループ化 4. COUNT(*) / ROUND(AVG(price),0) → 件数と平均を集計 5. ORDER BY avg_price → 平均価格順に整列 */
解説（テーブル変化・ポイント）
▼
LEGEND
データ取得・読込対象
① FROM products（6行）
FROM productsproducts テーブルの6行を読み込みます。price は連続値。これを価格帯という区分に振り分けて集計します。
1 / 4
| product_id | product | price |
|---|---|---|
| 1 | Pen | 100 |
| 2 | Notebook | 300 |
| 3 | Bag | 1500 |
| 4 | Lamp | 2500 |
| 5 | Chair | 8000 |
| 6 | Desk | 12000 |
6行読込（連続値の price）
学習ポイント
CASE で「区分」という新しい軸を生成：元データに無いカテゴリ（価格帯・年齢層・評価ランクなど）を CASE 式でその場で作り、それをグループ化キーにできます。連続値を意味のあるビンに畳み込む、レポートの定番テクニックです。
CASE は上から最初に一致した枝で確定：WHEN は上から順に評価され、最初に真になった枝の値で確定します。
WHEN price < 5000 が「500以上5000未満」を表せるのは、直前の < 500 を通った行だけがそこへ到達するからです。境界の順序設計が結果を決めます。式でグループ化（エイリアス/番号でも可）：SELECT に書いた CASE と同じ式を GROUP BY にも書きます。PostgreSQL では
GROUP BY price_band（エイリアス）や GROUP BY 1（列番号）でも同じ意味になり、長い CASE の重複を避けられます。アンチパターン
WHEN の順序を誤る：広い条件を先に書くと細かい区分が吸われます。
WHEN price < 5000 THEN 'Mid' を先頭に置くと、本来 Low の100円・300円まで Mid に入り、Low が消滅します。狭い（小さい）条件から順に並べましょう。範囲に穴や重複を作る：境界で
>= と > を混在させたり ELSE を書き忘れると、どの区分にも入らない行（未分類）や二重計上が生じます。区分は「漏れなく・重複なく（MECE）」を意識して設計します。実務コラム：CASE ビン分割はヒストグラムの第一歩
「価格帯別の商品数」「客単価レンジ別の購入者数」「スコア帯別のユーザー数」——分布を把握するヒストグラムは、CASE で区分を作り COUNT するだけで描けます。区分の境界（しきい値）を変えれば粒度を調整でき、ビジネス上意味のある切り口（無料/有料、初心者/上級者など）にも自由に対応できます。等幅のビンなら
width_bucket() 関数や、よく使う区分なら生成列（GENERATED 列）に切り出して再利用する手もあります。QUESTION 9
重複の検出 — GROUP BY と HAVING COUNT(*) > 1 で重複データを見つける
前提知識
▼
「同じメールアドレスが複数登録されていないか？」——重複の検出はグループ化の代表的な応用です。重複を調べたい列で GROUP BY し、HAVING COUNT(*) > 1 で「2件以上あるグループ」だけを残します。
-- email ごとに件数を数え、2件以上（=重複）だけを残す SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1;
「一意であるべき列」を GROUP BY する：重複検出の型は「ユニークになってほしい列でグループ化 → HAVING COUNT(*) > 1」です。UNIQUE 制約を張る前の事前チェックや、データ取り込み後の品質確認で頻出します。複数列の組で重複を見るなら
GROUP BY col_a, col_b のように並べます。
問題
▼
users テーブルから、重複して登録されている email とその件数を求めてください（2件以上のものだけ）。出力列は email, cnt。cnt の降順、同数なら email の昇順で返してください。
使用テーブル
► users（6行）
| user_id | |
|---|---|
| 1 | a@x.com |
| 2 | b@x.com |
| 3 | a@x.com |
| 4 | c@x.com |
| 5 | b@x.com |
| 6 | a@x.com |
期待出力
▼
| cnt | |
|---|---|
| a@x.com | 3 |
| b@x.com | 2 |
模範解答コード
▼
SELECT email, COUNT(*) AS cnt -- email ごとの登録件数 FROM users GROUP BY email -- 重複を調べたい列でグループ化 HAVING COUNT(*) > 1 -- 【集計後フィルタ】2件以上のグループ（＝重複）だけを残す ORDER BY cnt DESC, email; -- 件数の多い順、同数は email の昇順 /* 実行順序（SQLの論理的な評価順）: 1. FROM users → 行を読込 2. GROUP BY email → email でグループ化 3. COUNT(*) → 各グループの件数を集計 4. HAVING COUNT(*) > 1 → 重複グループだけ残す 5. ORDER BY cnt DESC, email → 件数の多い順で並べ替え */
解説（テーブル変化・ポイント）
▼
LEGEND
データ取得・読込対象
① FROM users（6行）
FROM usersusers テーブルの6行を読み込みます。email 列を見ると a@x.com と b@x.com が複数回現れています。これを検出するのが目的です。
1 / 5
| user_id | |
|---|---|
| 1 | a@x.com |
| 2 | b@x.com |
| 3 | a@x.com |
| 4 | c@x.com |
| 5 | b@x.com |
| 6 | a@x.com |
6行読込
学習ポイント
重複検出の「型」を覚える：一意であるべき列で GROUP BY → HAVING COUNT(*) > 1。これが重複を見つける定番イディオムです。出力の
cnt がそのまま「何件ダブっているか」を表し、データ品質チェックに直結します。件数の条件は WHERE では書けない：
COUNT(*) はグループ化・集計の後でしか確定しないため、件数による絞り込みは HAVING の役割です。「生の行の条件＝WHERE、集計値の条件＝HAVING」という 使い分けが、ここでも効いてきます。複数列の重複も同じ型で検出：「氏名＋生年月日が同じ」のような複合キーの重複は
GROUP BY name, birth_date HAVING COUNT(*) > 1 で見つけられます。組み合わせ単位のグループ化（Q3）と HAVING を組み合わせるだけです。アンチパターン
WHERE COUNT(*) > 1 と書く：
WHERE COUNT(*) > 1 は構文エラーです。WHERE はグループ化の前に効くため、まだ COUNT が計算されていません。件数での絞り込みは必ず HAVING に書きます。DISTINCT で「消す」だけで満足する：
SELECT DISTINCT email は重複を畳んで見えなくするだけで、どの値が何件ダブっているかは分かりません。重複の有無と件数を把握したいなら、GROUP BY + HAVING で「検出」する必要があります。実務コラム：UNIQUE 制約を張る前の必須チェック
「email を UNIQUE にしたい」「会員番号は一意のはず」——制約を追加する前には、既存データに重複が無いかをこのクエリで必ず確認します。重複があるまま UNIQUE 制約を張ろうとすると失敗するからです。さらにどのレコードが重複しているかまで一覧したいときは、次の Q10 で学ぶ
STRING_AGG(user_id::text, ', ') を足せば「重複している実 id のリスト」まで一発で取れます。検出 → 特定 → 修正、というデータクレンジングの第一歩です。QUESTION 10
値の連結 — STRING_AGG でグループ内の値を1つの文字列にまとめる
前提知識
▼
集約の総仕上げは、数値ではなく文字列を集約する STRING_AGG です。グループ内の複数の値を、区切り文字でつないで1つの文字列にまとめます。「グループに属するメンバー一覧」を1行で表現できます。
-- グループ内の student を ', ' で連結（名前順に並べる） STRING_AGG(student, ', ' ORDER BY student) STRING_AGG(DISTINCT tag, ',') -- 重複を除いて連結 ARRAY_AGG(student) -- 文字列でなく配列で集約する姉妹関数
集約内 ORDER BY が連結順を決める：STRING_AGG の括弧の中に書く
ORDER BY は、連結する順序を制御します（指定しないと順序は不定）。第2引数が区切り文字です。COUNT/SUM が「多くの値→1つの数値」なのに対し、STRING_AGG は「多くの値→1つの文字列」へ畳み込む集計関数です。
問題
▼
enrollments テーブルから、コース別の受講者数と、受講者名を名前順にカンマ区切りで連結した一覧を求めてください。出力列は course, student_count, students。student_count の降順、同数なら course の昇順で返してください。
使用テーブル
► enrollments（6行）
| id | course | student |
|---|---|---|
| 1 | Math | Bob |
| 2 | Math | Alice |
| 3 | Math | Carol |
| 4 | Science | Dave |
| 5 | Science | Alice |
| 6 | Art | Bob |
期待出力
▼
| course | student_count | students |
|---|---|---|
| Math | 3 | Alice, Bob, Carol |
| Science | 2 | Alice, Dave |
| Art | 1 | Bob |
模範解答コード
▼
SELECT course, COUNT(*) AS student_count, -- コースごとの受講者数 STRING_AGG(student, ', ' ORDER BY student) AS students -- 受講者名を名前順にカンマ区切りで連結 FROM enrollments GROUP BY course -- コース単位でグループ化 ORDER BY student_count DESC, course; -- 受講者数の多い順、同数はコース名の昇順 /* 実行順序（SQLの論理的な評価順）: 1. FROM enrollments → 行を読込 2. GROUP BY course → コースでグループ化 3. 集計 → COUNT・STRING_AGG 4. ORDER BY student_count DESC, course → 人数の多い順で並べ替え */
解説（テーブル変化・ポイント）
▼
LEGEND
データ取得・読込対象
① FROM enrollments（6行）
FROM enrollmentsenrollments テーブルの6行を読み込みます。1行＝1人の受講登録。これをコースごとにまとめ、人数と名前一覧にします。
1 / 4
| id | course | student |
|---|---|---|
| 1 | Math | Bob |
| 2 | Math | Alice |
| 3 | Math | Carol |
| 4 | Science | Dave |
| 5 | Science | Alice |
| 6 | Art | Bob |
6行読込（1行＝1登録）
学習ポイント
STRING_AGG は「文字列を集約する」集計関数：COUNT や SUM が値を1つの数値に畳み込むのに対し、STRING_AGG はグループ内の文字列を1つの連結文字列に畳み込みます。「グループのメンバー一覧」を1セルで表現でき、レポートが一気に読みやすくなります。
集約内 ORDER BY で連結順を制御：
STRING_AGG(student, ', ' ORDER BY student) の括弧内 ORDER BY が連結の順序を決めます。これを省くと順序は不定（実行ごとに変わりうる）。第2引数 ', ' が区切り文字です。DISTINCT と姉妹関数 ARRAY_AGG：重複を除いて連結したいなら
STRING_AGG(DISTINCT student, ', ')。文字列ではなく配列で受け取りたいときは ARRAY_AGG(student) を使います。集約の出力形をニーズに合わせて選べます。アンチパターン
ORDER BY を省いて順序に依存する：連結順が重要なのに
STRING_AGG(student, ', ') と書くと、出力順は保証されず実行ごとに並びが変わる恐れがあります。順序が意味を持つなら、必ず集約内に ORDER BYを書きましょう。方言を取り違える：同じ機能でも MySQL は
GROUP_CONCAT、Oracle は LISTAGG と関数名が異なります。STRING_AGG は PostgreSQL（および SQL Server）の構文です。別DBへ移植するときは関数名と区切り指定の書き方に注意してください。実務コラム：一覧化と「検出→特定」への橋渡し
タグ一覧、担当者リスト、関連商品ID の CSV 出力——「グループに属する値をまとめて見たい」場面で STRING_AGG は大活躍します。とくに 重複検出と組み合わせると強力で、
GROUP BY email HAVING COUNT(*) > 1 に STRING_AGG(user_id::text, ', ') を足せば、「重複している email と、その実レコードの id 一覧」が1クエリで取れます。グループ化で学んだ COUNT・SUM・条件付き集計・結合・期間/区分・そして文字列集約——これらを組み合わせれば、明細データから実務で必要なサマリのほとんどを表現できます。