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.生成AIパスポート試験練習問題（四肢択一式）｜生成AI活用研究
2.最終行の取得（End,Rows.Count）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.繰り返し処理（For Next)｜VBA入門
6.RangeとCellsの使い方｜VBA入門
7.FILTER関数（範囲をフィルター処理）｜エクセル入門
8.日本の祝日一覧｜Excelリファレンス
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.セルのクリア（Clear,ClearContents）｜VBA入門

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

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

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.

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