SQL基礎問題8：バスケット分析・ペア商品の出現回数

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

SQL基礎問題8：バスケット分析・ペア商品の出現回数

バスケット分析です。同時に購入された商品のペアの出現回数を求める問題です。

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（画像参照、カラム名は推測して）
・同一レシート（receipt）内で同時に購入された商品のペアを分析。
・出力はペア商品の名前2つと同時出現回数。
・商品ペアは、順序違いを重複カウントしないこと。
・同時出現回数が2回以上のペアのみ出力。

解答SQL

SQLite3

HAVING句直接フィルタリング

SELECT t1.name AS item1,t2.name AS item2,COUNT(t1.receipt) AS 発生回数
FROM t_receipt t1
INNER JOIN t_receipt t2
        ON t1.receipt = t2.receipt AND t1.item <> t2.item AND t1.item < t2.item
GROUP BY t1.name,t2.name
HAVING COUNT(t1.receipt) >= 2
ORDER BY 発生回数 DESC,t1.name

サブクエリ（インラインビュー）方式

SELECT item1,item2,発生回数
FROM (SELECT t1.name AS item1,t2.name AS item2,COUNT(t1.receipt) AS 発生回数
      FROM t_receipt t1
      INNER JOIN t_receipt t2
              ON t1.receipt = t2.receipt AND t1.item <> t2.item AND t1.item < t2.item
      GROUP BY t1.name,t2.name
    ) AS basket
WHERE 発生回数 >= 2
ORDER BY 発生回数 DESC,item1

CTE（共通テーブル式）方式

WITH pair AS (
     SELECT t1.name AS item1,t2.name AS item2,COUNT(DISTINCT t1.receipt) AS 発生回数
     FROM t_receipt t1
     INNER JOIN t_receipt t2
             ON t1.receipt = t2.receipt AND t1.item < t2.item
     GROUP BY t1.name,t2.name
)
SELECT item1,item2,発生回数
FROM pair
WHERE 発生回数 >= 2
ORDER BY 発生回数 DESC,item1,item2

SQLの解説

SQLクエリの手法概要

3つのクエリは、いずれも「バスケット分析（同時購入ペアの抽出）」を行うものですが、結果を絞り込むための集計結果のフィルタリング方法が異なります。

クエリ	手法に付けた名前	特徴的な構文	解説
1つ目	HAVING句直接フィルタリング	GROUP BY と HAVING	集計（GROUP BY）と集計結果のフィルタリング（HAVING）を一連の流れで行う最も基本的な方法です。クエリが短く、シンプルに書けます。
2つ目	サブクエリ（インラインビュー）方式	FROM (SELECT ...) と WHERE	サブクエリで集計結果のテーブルを作成し、メインクエリでその結果（発生回数という別名が付いたカラム）をWHERE句でフィルタリングします。処理を分割することで、クエリ構造が理解しやすくなります。
3つ目	CTE（共通テーブル式）方式	WITH ... AS (...) と COUNT(DISTINCT...)	WITH句で集計結果の仮想テーブル（pair）を定義し、メインクエリでそれを使用します。サブクエリ方式よりさらに読みやすく、複雑な処理を段階的に記述するのに適しています。また、COUNT(DISTINCT)でレシート数を正確に集計しています。

HAVING句直接フィルタリング

集計とフィルタリングを一つのブロックで完結させる、最もシンプルな手法。
特徴: GROUP BYの直後にHAVING COUNT(...)を用いて、集計結果を直接絞り込む。
メリット: コードが短く、シンプルで分かりやすい。
デメリット: 集計結果にしか使えず、柔軟性が低い。

サブクエリ（インラインビュー）方式

集計処理とフィルタリング処理を分離する手法。
特徴: 内側のクエリで集計（GROUP BY）を行い、その結果に外側のクエリでWHERE句を適用して絞り込む。
メリット: 集計結果を通常のカラム名（例: 発生回数）として扱えるため、ロジックが分かりやすい。
デメリット: クエリがネスト構造になり、コードがやや冗長になる。

CTE（共通テーブル式）方式

集計処理を名前付きの仮想テーブルとして定義する、可読性に優れた手法。
特徴: WITH ... AS (...) で一時的な結果セット（CTE）を定義し、メインクエリでそれを呼び出して処理する。
メリット:クエリの構造が明確で可読性が高い（複雑なクエリで特に有効）。COUNT(DISTINCT t1.receipt) を使っており、集計の正確性が高い。
デメリット: 単純なクエリではコード量が増える。

Excel

ウィンドウ関数とCTEはExcelでは使えません。
それ以外のSQLは、ほぼそのままで移植できますので、試してみてください。

※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基礎問題8：バスケット分析・ペア商品の出現回数

このサイトがお役に立ちましたら「シェア」「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基礎問題8：バスケット分析・ペア商品の出現回数