SQLで条件に一致するユーザIDを取得したい

Question

質問

「りんご2個以上」かつ「バナナ2個以上」を持っているユーザIDを取得したいです。

以下データの場合、結果としてuser_id=2が得られることを期待していますが思ったような結果が得られません。
（期待する結果を得られるSQLは完成しましたが効率が悪いように思います。改善点や最適な方法が知りたいです。）

テーブル定義

itemsはユーザーの所持品を保存するテーブル

CREATE TABLE `items` (
	`user_id` INT(11) UNSIGNED NOT NULL,
	`name` VARCHAR(16) NOT NULL COLLATE 'utf8_general_ci',
	`num` INT(11) UNSIGNED NOT NULL,
	INDEX `name` (`name`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

テーブルデータ

user_id	name	num
1	りんご	1
1	バナナ	2
2	りんご	2
2	バナナ	2

試したこと

以下SQL001は結果が0件です。
りんごが2個以上のwhere条件で既に結果にバナナが含まれていない為、2つめのバナナに関するwhere条件を満たせず0件になったと思われます。

/* SQL001 */
select
  i.user_id
from items i
where
	(i.name = 'りんご' and i.num >= 2) and
	(i.name = 'バナナ' and i.num >= 2)

そこで、以下SQL002のようにgroup byを利用してnameごとに条件指定ができるようにしたつもりですが結果が0件でした。

/* SQL002 */
select
  i.user_id
from items i
where
	(i.name = 'りんご' and i.num >= 2) and
	(i.name = 'バナナ' and i.num >= 2)
group by
	i.name

以下SQL003のようにサブクエリで指定すると期待する結果が得られました。
しかし、selectが2回あるのであまり効率が良いSQLとは思えないです。
りんご、バナナの他に検索対象の数が増えた場合、増えた数に比例してサブクエリを増やす必要があるはずです。

/* SQL003 */
select
  i.user_id
from items i
where
	(i.name = 'りんご' and i.num >= 2) and
	i.user_id in (select user_id from items where (name = 'バナナ' and num >= 2))

今回のようなケースではどのようなSQLが最適でしょうか？

※追記
以下SQL004でも期待する結果が取れて少しSQLが綺麗になったかと思いましたがりんごが1個、バナナが3個（またはそれ以外の合計が4個以上のパターン）でも結果が0件にならないのでダメでした。

/* SQL004 */
select
  i.user_id
from items i
where
    (i.name = 'りんご') or (i.name = 'バナナ')
group by
    i.user_id
having
	sum(i.num) >= 4

環境

• MariaDB 10.5.9

Comments

[m.ts10806]

>りんご2個、バナナ2個

コードは >= なので「２個以上」を指定していますが、これは？

[daiki002]

すみません、色々試していたのでごちゃごちゃになっていました。
りんご2個以上、バナナ2個以上が正しいです

Answer 1

ユーザーを検索するならユーザーテーブルに対してexsits複数実行したほうが個人的に好きかな
ユーザーテーブル「users」でidフィールドがitemsのuser_idだとすると

select u.id
from users u
where exists(select 0 from items i where i.user_id=u.id
    and i.name = 'りんご' and i.num >= 2)
  and exists(select 0 from items i where i.user_id=u.id
    and i.name = 'バナナ' and i.num >= 2)

itemsリンゴを2個以上持っていて
itemsバナナを2個以上持っているユーザーが明示的でわかりやすいと思う

Comments

[daiki002]

ご回答ありがとうございます調べると今回のような問い合わせはexistsと相関副問合せを利用した形のSQLが一番良さそうでした。

Answer 2

select user_id from items
group by user_id
having sum((name='りんご')*num)>=2 and sum((name='バナナ')*num)>=2

Answer 3

たぶん、りんご２個以上**かつ（AND）**バナナ２個以上、という条件でuser_idを抽出したいんだろうと思うけど…。

考え方として
・りんご２個以上を持つuser_id
・バナナ２個以上を持つuser_id

をまず個別に特定するクエリを書いてみて、この個別のクエリの結果の双方に存在しているuser_idを抽出できれば目的達成なわけなので、そこまでわかれば、JOINを使ったりEXISTSを使ったりINを使ったり色んな方法で実現できると思う。

慣れてるなら一度にこれだけの事を実現するクエリは書けるだろうけど、最初のうちはこうやって単純なモノに分解して、最後に結果を組み合わせる、というアプローチを取って学んだ方が良い。

あと「最適か？」というのがどういう観点の話なのかによるけど、それは上記のようなアプローチで同じ結果になる色んなクエリを書いてみて、実行プランを比較するなりして、その意味を学ぶべき。

どんな場合にどんな観点で「最適」なのかによるけど。（読みやすさの点で最適なクエリがパフォーマンスが高いとは限らない、とか）

Answer 4

selectが2回あるのであまり効率が良いSQLとは思えないです。

必ずしもそうだとは限りません。実行計画で確認すべきものです。

条件を表にして不等結合するパターン。

select user_id
from  items i 
      full join (
        select 'りんご' as name, 2 as num
        union all select 'バナナ', 2
      ) c     
      on i.name = c.name and i.num >= c.num
group by user_id
having count(i.name)=count(c.name)

条件が多いようなら、条件用の一時テーブルを使用する方が簡潔になると思います。
条件を一時テーブルconditionとした場合

select user_id
from  items i 
      full join condition c
      on i.name = c.name and i.num >= c.num
group by user_id
having count(i.name)=count(c.name)

Comments

[sazi]

MySQL系はFull Joinが使えないみたいなので、スルーして下さい。

Answer 5

以下SQL003のようにサブクエリで指定すると期待する結果が得られました。
しかし、selectが2回あるのであまり効率が良いSQLとは思えないです。
りんご、バナナの他に検索対象の数が増えた場合、増えた数に比例してサブクエリを増やす必要があるはずです。

抽出条件が変化することも想定する場合は、SQL 文で記述する場合と、
「別途、Where 条件が記録されたワークテーブルを用意」しておき、テーブル結合を利用する。
という場合もあります。

例えば、いろんな組み合わせが想定されるかと思います。

・バナナだけ条件にしたい（りんご　に関するデータは不要）
・バナナは 2個以下、リンゴは　5個以上

また、データベース管理アプリケーションによっては、内部的に変数を持たせて処理できるものもあります。効率という観点からは、そのようなアプリケーションに特化した形を採用する場合もあります。

ただ、今回の場合、SQL001 の判断が間違っているので、その間違いに気づかず、あれこれ試行錯誤して袋小路に入り込んでしまっているようすがうかがえます。

まずは、基本的な SQL を理解してから、次の疑問を解決するほうがよさそうですよ。

以下SQL001は結果が0件です。
りんごが2個以上のwhere条件で既に結果にバナナが含まれていない為、2つめのバナナに関するwhere条件を満たせず0件になったと思われます。

where
    (i.name = 'りんご' and i.num >= 2) and
    (i.name = 'バナナ' and i.num >= 2)

この条件で抽出結果が 0件になってしまうのは、”絶対に存在しない、不可能な条件” を記述したためです。

日本語で、それっぽく書くと

集合体 (item テーブル）から、（無作為に）1件のレコードを取り出したとき、
そのレコードの　name (という箱） の中に入っている値を見て
値が　リンゴ　であり、同時に、バナナ　であるもの。　を抽出せよ。

    (i.name = 'りんご' ) and (i.name = 'バナナ' )

という条件の成立を期待していることになります。

つまり、 name の中に同時に２つの異なる値が存在することはあり得ませんので、
本来の意図と異なり、結果が どんな場合でも 0件 になってしまいます

SQL は （C やスクリプト系言語のような）手続き型言語ではなく、集合指向の言語であるため、手順で考えるのではなく、集合体の操作へと、発想を変える必要がありますので、頑張ってください。