SQL基礎問題9：特定商品購入者の平均購入金額

公開日：2025-11-04　最終更新日：2025-11-04

SQL基礎問題9：特定商品購入者の平均購入金額

特定商品を購入した人と、購入していない人の、それぞれの平均購入金額を求める問題です。

SQLの練習用になるべく単純なSQLで済む問題を出します。
SQLの練習として取り組んで見てください。

問題

※画像はExcelです。

receipt	seq	item	name	qty	price	amt
R001	1	P001	紙おむつ	1	2800	2800
R001	2	A001	ビール(6缶)	1	1200	1200
R001	3	J001	いちごジャム	1	450	450
R002	1	P001	紙おむつ	1	2800	2800
R002	2	A001	ビール(6缶)	1	1200	1200
R002	3	B001	食パン	2	350	700
R003	1	P001	紙おむつ	1	2800	2800
R003	2	A001	ビール(6缶)	3	1200	3600
R003	3	J001	いちごジャム	2	450	900
R004	1	C001	コーヒー豆	1	800	800
R004	2	B001	食パン	1	350	350
R005	1	C001	コーヒー豆	1	800	800
R005	2	B001	食パン	1	350	350
R005	3	M001	牛乳	4	250	1000
R005	4	R001	シリアル	2	550	1100
R006	1	C001	コーヒー豆	1	800	800
R006	2	B001	食パン	1	350	350
R006	3	D001	プロセスチーズ	4	300	1200
R007	1	M001	牛乳	1	250	250
R007	2	R001	シリアル	1	550	550
R008	1	D001	プロセスチーズ	3	300	900
R008	2	J001	いちごジャム	1	450	450
R009	1	P001	紙おむつ	1	2800	2800
R009	2	C001	コーヒー豆	1	800	800
R010	1	A001	ビール(6缶)	1	1200	1200
R010	2	S001	ポテトチップス	3	120	360

【SQL問題】
テーブル：t_receipt（画像参照）
・「紙おむつ(P001)」を購入した人と、購入していない人の2グループにおいて、それぞれのグループでのレシートごとの合計金額の平均を求めてください。
※合計金額の平均は四捨五入してください。
※画像の5013は(R001,R002,R003,R009)が該当。

解答SQL

SQLite3

単一サブクエリ + MAX/CASE

SELECT ROUND(AVG(CASE WHEN has_diaper = 1 THEN total_amount ELSE NULL END)) AS おむつ購入者,
       ROUND(AVG(CASE WHEN has_diaper = 0 THEN total_amount ELSE NULL END)) AS それ以外
FROM (SELECT receipt,
             SUM(amt) AS total_amount,
             MAX(CASE WHEN item = 'P001' THEN 1 ELSE 0 END) AS has_diaper
      FROM t_receipt
      GROUP BY receipt
     ) AS ReceiptSummary

二重サブクエリ + EXISTS

SELECT
    ROUND(AVG(CASE WHEN groupT.target = 'おむつ購入者' THEN groupT.amtT END)) AS おむつ購入者,
    ROUND(AVG(CASE WHEN groupT.target = 'それ以外' THEN groupT.amtT END)) AS それ以外
FROM (SELECT receiptT.amtT,
             CASE WHEN EXISTS
                       (SELECT 1
                        FROM t_receipt tr
                        WHERE tr.receipt = receiptT.receipt AND tr.item = 'P001'
                       ) THEN 'おむつ購入者'
                  ELSE 'それ以外'
              END AS target
      FROM (SELECT receipt,SUM(amt) AS amtT
            FROM t_receipt
            GROUP BY receipt
           ) AS receiptT
     ) AS groupT

CTE + EXISTS

WITH ReceiptT AS (
    SELECT receipt,SUM(amt) AS amtT
    FROM t_receipt
    GROUP BY receipt
 ),
 GroupedT AS (
    SELECT rt.amtT,
        CASE WHEN EXISTS
                  (SELECT 1
                   FROM t_receipt tr
                   WHERE tr.receipt = rt.receipt AND tr.item = 'P001'
                  ) THEN 'おむつ購入者'
             ELSE 'それ以外'
        END AS target
    FROM ReceiptT rt
)
SELECT
    ROUND(AVG(CASE WHEN gt.target = 'おむつ購入者' THEN gt.amtT END)) AS おむつ購入者,
    ROUND(AVG(CASE WHEN gt.target = 'それ以外' THEN gt.amtT END)) AS それ以外
FROM
    GroupedT gt

SQLの解説

3つのSQL手法の概要

No.	手法の名前	主な特徴とロジック	メリット
1	単一サブクエリ + MAX/CASE	単一のサブクエリ内で、集計 (SUM) とグループ判定 (MAX(CASE...)) を同時に行い、最後に条件付き集計で結果を横並びにする。	最もシンプルな構造で、相関サブクエリを使わず効率が良い。集計と判定を一度に処理する。
2	二重サブクエリ + EXISTS	レシート合計を算出した後、相関サブクエリ (EXISTS) を使ってグループ判定を行い、最後に条件付き集計 (AVG(CASE...)) で結果を横並びにする。	相関サブクエリによる厳密な判定ロジック。
3	CTE + EXISTS	WITH句で処理を2段階に分け、EXISTSでグループ判定を行った後、条件付き集計で結果を横並びにする。	WITH句により、複雑な処理が構造化され、可読性が高い。

単一サブクエリ + MAX/CASE

この手法は、効率性とシンプルさに優れています。
特徴: 一つの集計処理内でレシートの合計金額を計算し、同時に MAX(CASE WHEN item = 'P001' ...) を使って「紙おむつ購入有無」をフラグとして判定します。
ロジック: 外部のテーブルに再アクセスすることなく（非相関）、すべての判定を一度の集計で完了させます。メインクエリでこのフラグを使って条件付き集計（AVG(CASE WHEN ...)）を行い、結果を横に並べます。
評価: 最も推奨される手法です。

二重サブクエリ + EXISTS

この手法は、相関サブクエリを利用して厳密にグループ判定を行います。
特徴: 最も内側のサブクエリでレシート合計を計算し、その外側でEXISTS句（相関サブクエリ）を用いてグループ分けのフラグを立てます。
ロジック: レシートIDごとに、そのIDが元のテーブルに「P001」を持っているか都度チェックしながら（相関クエリ）グループを判定します。処理が2段階のネスト構造になっています。
評価: ロジックは明確ですが、相関サブクエリの処理が入るため、大規模データでは効率が劣る可能性があります。

CTE + EXISTS

この手法は、可読性と構造化を重視した書き方です。
特徴: WITH句（CTE）を使用して、処理を段階的に構造化しています。グループ判定には、2番目の手法と同様にEXISTS句を使用します。
ロジック: レシート合計を計算するステップと、グループ判定を行うステップをWITH句で名前を付けて分離しているため、クエリの意図が追いやすいです。
評価: 構造化されており読みやすいですが、グループ判定ロジック自体は2番目の手法と同じで、効率面では1番目の手法に劣ります。

Excel

=LET(sql,"
SELECT Int(AVG(IIf(ReceiptS.has_diaper = 1, ReceiptS.amountT, Null)) + 0.5) AS おむつ購入者,
       Int(AVG(IIf(ReceiptS.has_diaper = 0, ReceiptS.amountT, Null)) + 0.5) AS それ以外
FROM (SELECT receipt,
             SUM(amt) AS amountT,
             MAX(IIf(item = 'P001', 1, 0)) AS has_diaper
      FROM ?TBL
      GROUP BY receipt
     ) AS ReceiptS
",
QUERY(SUBSTITUTE(sql,"?TBL","[t_receipt$A1:G27]")))

Excelでは、ウィンドウ関数とCTE、さらに、CASE WHENも使えません。
したがって、今回のSQLiteのSQLは、そのままでは移植できません。
また、ROUND関数は銀行丸めになりますので、四捨五入は自前で実装する必要があります。

RDBMSごとの ROUND 関数挙動比較

環境 / RDBMS	主な丸め方式	端数 0.5 の扱い	ROUND(2.5)	ROUND(3.5)
SQLite	四捨五入（算術丸め）	切り上げ（away from zero）	3	4
MySQL 8.x	四捨五入（算術丸め）	切り上げ（away from zero）	3	4
PostgreSQL 16+	四捨五入（算術丸め）	切り上げ（away from zero）	3	4
SQL Server 2022	四捨五入（算術丸め）	切り上げ（away from zero）	3	4
Oracle DB 23c	四捨五入（算術丸め）	切り上げ（away from zero）	3	4
Microsoft Access	銀行丸め（偶数丸め）	最も近い偶数に丸める	2	4
Excel ADO	銀行丸め（偶数丸め）	最も近い偶数に丸める	2	4

※複数の生成AIで情報をまとめたものです。
これらは、バージョンによって違う場合もあります。
筆者が直接確認出来ていないものも含まれています。

※SQLの作成及び解説には生成AIの助けを借りています。特に解説文においてはAI出力を多く使用しています。

同じテーマ「SQL入門」の記事

SQL基礎問題1：最大在庫数を持つ製品の在庫金額
 SQL基礎問題2：文字列「-nn-」のnnが偶数のみ抽出
 SQL基礎問題3：文字列の一部をキーにして集計
 SQL基礎問題4：2つのテーブルの不一致を抽出
 SQL基礎問題5：複数のマスタテーブルの結合
 SQL基礎問題6：成績表から教科ごとの点数ベスト3を抽出
 SQL基礎問題7：成績表から各教科の最高点と最低点を抽出
 SQL基礎問題8：バスケット分析・ペア商品の出現回数
SQL基礎問題9：特定商品購入者の平均購入金額
SQL基礎問題10：非正規化（カンマ区切り）の結合と集計
 SQL基礎問題11：連続期間の開始月と終了月を抽出

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
その他
SQL入門
SQL基礎問題9：特定商品購入者の平均購入金額

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

SQL入門
SQL基礎問題9：特定商品購入者の平均購入金額