経営情報システム
重要SQLと集計
結合後の行と集計結果
この章で覚えておきたいこと
青木さんはパン店に2回、井上さんは1回注文しました。上田さんは会員ですが、まだ注文していません。「会員ごとの注文回数」を出すとき、どのようにすれば上田さんの行に0と表示できるでしょうか。
基本知識
主キーと外部キーで表を結ぶ
SQL(Structured Query Language/構造化問合せ言語)で2表を読むときは、まず一方の主キーと、それを参照する外部キーを探します。
この記事の例では顧客表に3行があります。顧客番号1が青木さん、2が井上さん、3が上田さんです。受注表は次の3行です。
- 注文101は顧客番号1、金額500円です。
- 注文102は顧客番号1、金額700円です。
- 注文103は顧客番号2、金額900円です。
受注表では注文番号が各行を識別します。同じ青木さんからの2件は顧客番号が同じでも、注文番号が異なる2行です。顧客名が偶然同じ人もいるので、結合条件には顧客番号を使います。
内部結合と左外部結合
内部結合は両表の条件が一致した組を返します。左外部結合はそれに加え、右側に一致行がない顧客も残します。
次の問い合わせでは顧客を左に置き、受注を右に置きます。結果は「青木・101」「青木・102」「井上・103」「上田・NULL」の4行です。NULLは対応する値がないことを表します。
SELECT c.氏名, o.注文番号
FROM 顧客 AS c
LEFT JOIN 受注 AS o
ON c.顧客番号 = o.顧客番号
ORDER BY c.顧客番号, o.注文番号;
同じ文の LEFT JOIN を INNER JOIN に替えると上田さんの行が消え、3行になります。顧客が3人でも、注文の多い人は結合結果に複数回登場します。受注表から顧客番号だけを取り出すと1・1・2の3行です。SELECT DISTINCT 顧客番号 FROM 受注 ORDER BY 顧客番号 は重複を除くため、結果は1・2の2行です。
また、左外部結合の後で右側の金額について WHERE o.金額 >= 600 とすると、金額がNULLの上田さんも絞り落とされます。
行を数えるCOUNTと値を数えるCOUNT
左外部結合で残った上田さんには結果行が一つあります。COUNT(*) はその行を数え、注文番号を指定したCOUNTはNULLの値を数えません。
SELECT c.氏名, COUNT(*) AS 行数,
COUNT(o.注文番号) AS 注文件数
FROM 顧客 AS c
LEFT JOIN 受注 AS o
ON c.顧客番号 = o.顧客番号
GROUP BY c.顧客番号, c.氏名
ORDER BY c.顧客番号;
青木さんは「2行・2件」、井上さんは「1行・1件」、上田さんは「1行・0件」です。金額の合計や平均には SUM、AVG を使います。例えば青木さんの平均注文額は(500+700)÷2=600円です。
WHEREとHAVING
WHEREは個々の行に条件を適用し、HAVINGはグループごとの集計結果に条件を適用します。
SELECT 顧客番号, SUM(金額) AS 合計
FROM 受注
WHERE 金額 >= 600
GROUP BY 顧客番号
HAVING SUM(金額) >= 800
ORDER BY 合計 DESC;
まず500円の注文101が外れます。次に顧客別にまとめると青木さん700円、井上さん900円です。最後に合計800円以上のグループを選ぶので、結果は顧客番号2・900円の1行です。ORDER BY は最後に表示順を指定します。
区分ごとに別の合計列を作る場合は、例えば SUM(CASE WHEN 金額 >= 600 THEN 金額 ELSE 0 END) と書きます。これはグループ内で条件に合う行の金額だけを足します。集計前の行そのものを落とすWHEREとは働きが異なります。
SELECTはデータの読出しに使います。表を定義するCREATE TABLEはDDL(Data Definition Language/データ定義言語)、INSERT・UPDATE・DELETEはDML(Data Manipulation Language/データ操作言語)の例です。権限の付与・取消しにはGRANT・REVOKEを使います。
この章のまとめ
結合条件と残す側の表を決め、一度、結合後の行を書き出します。その後に数える対象が行全体か、NULLを除く列の値かを確認します。絞り込みは、グループを作る前か後かで適用先が変わります。
確認クイズ
問1 顧客Aに注文2件、Bに注文1件、Cに注文0件があります。顧客を左側にした左外部結合は合計何行ですか。
答えと理由を見る
4行です。Aの2行、Bの1行に、受注側をNULLとしたCの1行が加わります。
問2 注文のない会員が左外部結合の結果に1行残りました。この人のCOUNT(*)とCOUNT(受注側の注文番号)はそれぞれいくつですか。
答えと理由を見る
1と0です。COUNT(*)は結果行を数えます。注文番号はNULLなので、COUNT(注文番号)の対象にはなりません。
問3 500円、700円の注文をしたAと、900円の注文をしたBがいます。600円以上の注文に絞り、顧客別合計が800円以上の人だけを残すと誰が該当しますか。
答えと理由を見る
Bです。WHEREで500円を外し、残りを顧客別に合計するとAが700円、Bが900円です。HAVINGの条件を満たすのはBだけです。
参考資料 PostgreSQL公式チュートリアルの表の結合と集約関数
関連記事 表とキーの設計、トランザクションと回復
一次試験過去問での出方
2025年第12問では、利用者をすべて残す集計SQLが出題され、
LEFT OUTER JOINとCOUNT(列名)の組み合わせを正しく読めるかが問われました。
2024年第8問では、
CASEとSUMとGROUP BYを組み合わせた条件付き集計が出題され、分類に使う列と合計対象の列を切り分ける力が求められました。
2023年度第2回第4問では、表の結合後に店舗単位で集計し、並び順まで含めてSQLの実行結果を読む問題が出ました。