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

Question

MySQL(8.0.23)で取引テーブル(sample)から、指定商品の組合せを含む取引ID(t_id)のユニーク数カウントを試みています。

以前、こちらの質問をさせていただき、一つのアイテムの組み合わせ条件については解決していますが、「同アイテムを含む複数の組み合わせ条件」を個別でカウントする方法を探しています。

・取引テーブル（sample）

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

・カウント条件の組み合わせ
セットA: orangeとpeach
セットB: orangeとpeachとgrape

とした場合、
セットAのt_idカウント: 3 (t_id=4の3件)
セットBのt_idカウント: 2 (t_id=3と4の2件)
で集計するイメージです。

仮にセットAのみのカウントの場合は前回いただいた質問回答より、

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

のように出せるかと思いますが、上記のようにセットが複数の条件且つ一つのt_id内で併売される場合に、上記クエリに何か追記で算出すべきか、もしくは別の方法を検討すべきかをご教示いただきたいです。

※セットAとBの例で、AはBからgrapeを抜いたもののため、WHERE句にgrape除外を加えれば・・とも思いましたが、併売（一つのt_id内でAとBの両方を含む取引）があることを考慮すると成立しないと分かり模索中です。

Answer 1

やりかたはいろいろありそうですが、たとえばUNIONを利用するのはどうでしょうか?

  SELECT "セットA", COUNT(*)
  FROM (
    SELECT t_id
    FROM sample
    WHERE item_id IN (222, 333)
    GROUP BY t_id
    HAVING COUNT(*) = 2
  ) X
UNION ALL
  SELECT "セットB", COUNT(*)
  FROM (
    SELECT t_id
    FROM sample
    WHERE item_id IN (222, 333, 444)
    GROUP BY t_id
    HAVING COUNT(*) = 3
  ) X

Comments

[whkt]

前回に引き続き、ご回答をいただきありがとうございます。
今回と前回で異なる点が"quantity"カラムの存在で、ここへアイテム数量が入っている部分でして、

> セットAのt_idカウント: 3 (t_id=4の3件)
> セットBのt_idカウント: 2 (t_id=3と4の2件)

とカウントしたいのですが、上記クエリのようにUNIONで実行する場合、セットA:2件、セットB:2件でのカウントになってしまうところで行き詰っております・・。

[neko_the_shadow]

・t_id=4の取引は、セットAを3個、セットBを1個取引したという認識で正しいですか?
・t_id=4の取引は、セットAを4個、grapeを1個取引したとも取れますが、それは考慮しなくてよいですか?

[whkt]

ご確認をいただきまして、ありがとうございます。

>・t_id=4の取引は、セットAを3個、セットBを1個取引したという認識で正しいですか?
>・t_id=4の取引は、セットAを4個、grapeを1個取引したとも取れますが、それは考慮しなくてよいですか?

t_id=4について、セットAを3個とセットBを1個、の取引としてカウントする形としたいと考えています。
ご指摘の通りで、パターンとしては セットAを4個とgrapeを1個 とも取る余地がありますが、1セット内アイテム数が多い組み合わせの一致判定が優先されるような形でのカウントを想定しています。