【MySQL】アイテムの組合せに一致する取引IDの個別件数カウント

Question

MySQL(8.0.23)で取引テーブル(sample)から、指定商品の組合せを含む取引ID(t_id)のユニーク数カウントしようとしていますが、この場合の効率的なクエリ処理についてご教示いただきたいです。

---

・取引テーブル（sample）

t_id	dtl_id	item_id	item_name
1	1	111	apple
1	2	222	orange
2	1	111	apple
2	2	333	peach
3	1	222	orange
3	2	333	peach
3	3	444	grape

orangeとpeachの組合せを含むt_idを抽出条件とし、これに該当するt_id個別件数を取得する方法として、

/*sampleテーブルを横持ち変換*/
WITH a AS (
SELECT 
t_id, 
MAX(t_id) mx,
MAX(CASE WHEN dtl_id=1 THEN item_id ELSE NULL END) AS item1,
MAX(CASE WHEN dtl_id=2 THEN item_id ELSE NULL END) AS item2,
MAX(CASE WHEN dtl_id=3 THEN item_id ELSE NULL END) AS item3
FROM sample
GROUP BY t_id)

/*カウント*/
SELECT 
COUNT(*)
FROM (
SELECT 
a.t_id,
/*t_id毎の全itemを1カラム内で文字列結合*/
CASE WHEN a.mx = 1 THEN a.item1
WHEN a.mx = 2 THEN CONCAT(a.item1, " ", a.item2)
WHEN a.mx = 3 THEN CONCAT(a.item1, " ", a.item2, " ", a.item3)
ELSE NULL END AS all_items
FROM a) b
WHERE b.all_items LIKE '%222%' 
AND b.all_items LIKE '%333%' 
GROUP BY b.t_id; 

上記のsampleテーブルでは1取引当たり最大3アイテムですが、中には数十アイテム含まれる取引があり、その場合多行に渡るCASE文でクエリが長くなり処理量も増えるかと思います。

このようなケースでは、nアイテム分の行数を動的に処理する方法を検討するのが良いのか、または該当組み合わせの取引IDの個別カウントが目的であれば、上記のクエリとは何か別の考え方でカウント処理すべきなのか、アドバイスいただけましたら助かります。

Answer 1

たとえば以下のようなSQLはどうでしょうか。

SELECT COUNT(*)
FROM (
  SELECT t_id
  FROM sample
  WHERE item_id IN (333, 444)
  GROUP BY t_id
  HAVING COUNT(*) = 2
) X

Comments

[whkt]

ありがとうございます、ご提示のクエリで処理できることを確認しました。
とてもシンプルで分かりやすいクエリで、参考にさせていただきます。