SQL文作成についてお知恵をお貸しください

質問

ユーザーと商品のテーブル（省略）、

商品ジャンルテーブル、
genre
id　　 name
1　　　家電
2　　　3万円台
3　　　テレビ
4　　　黒
5　　　SONY
6　　　三菱

商品とジャンルのリレーションテーブル、
item_genre
item_id genre_id
1　　　　1
1　　　　2
1　　　　3
2　　　　1
2　　　　3
2　　　　4
2　　　　6

ユーザーが希望する商品ジャンルのテーブル（ユーザーとジャンルのリレーションテーブル）、
user_genre
user_id genre_id
1　　　　1
1　　　　3
2　　　　1
2　　　　3
2　　　　4
3　　　　1
3　　　　3
3　　　　4
3　　　　5

以上があるとします。

この時に商品側から（商品IDを元に）、その商品を希望しているユーザーIDを取得する場合の
SQL文の作成方法のご教示をお願いいたします。

上記のテーブルの例ですと、アイテム1から取得したいユーザーIDは1、アイテム2からは
ユーザーID 1と2を取得できればOKです。

商品が持つジャンルIDが1,2,3に対してユーザーが希望するジャンルIDが1,3のように少ない
場合は対象にふくまれますが、商品が持つジャンルIDが1,3に対してユーザーが希望する
ジャンルIDが1,3,4のように多くなる場合は対象に含めません。

色々と考えてみたのですが、頭がこんがらがってしまい迷宮に迷い込んでしまいました。
すみませんがお知恵をお貸しください。よろしくお願いします。

※DBサーバーがMySQLのため、MySQLで実現できる方法だとありがたいです。

回答No.3

yambejp · ベストアンサー

#2です

ちなみにこんな感じ？

//元データ
create table item_genre(item_id int, genre_id int);
insert into item_genre values(1,1),(1,2),(1,3),(2,1),(2,3),(2,4),(2,6);
create table user_genre(user_id int,genre_id int);
insert into user_genre values(1,1),(1,3),(2,1),(2,3),(2,4),(3,1),(3,3),(3,4),(3,5),(4,1),(4,4),(5,5);

//結果
select user_id,item_id from user_genre as ug
inner join item_genre as ig on ug.genre_id=ig.genre_id
group by user_id,item_id
having (user_id,count(*)) in (select user_id,count(*) from user_genre group by user_id);

user4(user_id=4)は希望ジャンルが2個、item1(item_id=1)はジャンルが3個なので
ジャンル数だけで考えればuser4はitem1に含まれますが、item1はgenre_id=4を
含まないので対象外、item2はジャンル1も4も持つため対象

user5(user_id=4)はgenre_id=5しか持たず、itemリストにgenre_id=5を持つ要素がないため
対象外

お礼

バッチリです。目から鱗でした。
難しくて理解するのに時間が掛かりましたが、以下の認識で合ってますでしょうか？

select user_id,item_id
from user_genre as ug
inner join item_genre as ig
on ug.genre_id=ig.genre_id

で余計なジャンルを持つものを省き、

group by user_id,item_id
having (user_id,count(*))

でユーザーと商品に共通するジャンルの数をカウント、

in (
select user_id,count(*)
from user_genre
group by user_id);

でユーザーごとの全ジャンル数をカウントして
ユーザーが持つジャンル全てが含まれているもののみ抽出

ありがとうございました！後ほどベストアンサー付けさせていただきます。

回答No.4

yambejp

#3の解釈については問題ないと思いますよ

SQL文作成の流れとしては
(1)ユーザーテーブル、アイテムテーブルもに存在するジャンルの数を数える
select user_id,item_id,count(*)
from user_genre as ug
inner join item_genre as ig
on ug.genre_id=ig.genre_id
group by user_id,item_id;

(2)ユーザーテーブルのユーザーごとのジャンル数を数える
select user_id,count(*) from user_genre group by user_id;

(3)この２つを突合し、user_idごとのカウント数が同じものを選ぶ
仮にitem_idが持ってないジャンルをuser_idが持っていた場合は(2)>(1)になる
逆にuser_idに持ってないジャンルをitem_idが持っている場合でも
(1)で選別されているので(1)が(2)を超えることはない

なお(1)と(2)の付け合わせの際には(1)の集計結果と(2)を比較するので
集計結果を利用して検索をする場合はルール上havingを利用するのが妥当。
もちろん(1)をサブクエリにして(2)とjoinしても同じ結果。

お礼

なるほど。非常にわかりやすい解説ありがとうございます。

ホントに助かりました、ありがとうございました！

回答No.2

yambejp

仕様が微妙

＞商品が持つジャンルIDが1,3に対してユーザーが希望する
＞ジャンルIDが1,3,4のように多くなる場合は対象に含めません。

多い少ないが判断材料なのでしょうか？例えば・・・

商品が持つジャンルIDが1,3,5,6をもっていて
ユーザーが希望するジャンルIDが1,3,4だった場合
少ないですがヒットするのはおかしいですよね？
このままだと、たくさんジャンルを持つ商品は、ほとんどのユーザーがヒットします

「ユーザーの希望するすべてのジャンルIDを含む商品」ってこと？

補足

ご回答ありがとうございます。
説明がわかりづらくてすみません。

> 「ユーザーの希望するすべてのジャンルIDを含む商品」ってこと？

その通りになります。

回答No.1

bin-chan

１）キーワード「MySQL クロス集計」でググって、ノウハウを得る。
２）商品に比較用カテゴリ文字列を作る。（101000みたいに）
３）ユーザーに比較用カテゴリ文字列を作る。（101100みたいに）
４）２）と３）を比較用カテゴリ文字列で結合する。

例示のデータ、商品テーブルを省略しないほうが良いかも

お礼

回答ありがとうございます。
１）のほう、検索して色々調べてみたいと思います。正直どうしていいか検討つかない状態だったので、ヒントだけでもありがたいです。
４）は
> 商品が持つジャンルIDが1,2,3に対してユーザーが希望するジャンルIDが1,3のように少ない
> 場合は対象にふくまれますが、商品が持つジャンルIDが1,3に対してユーザーが希望する
> ジャンルIDが1,3,4のように多くなる場合は対象に含めません。
の条件があり商品側の文字列と対象のユーザー側文字列が同じにならないため、恐らく結合は出来ないかと思いますので、
３）をおこなってLikeや正規表現（？）で工夫して検索とかになるんでしょうか？

他にもなにか良い方法があればと思いますので、引き続き回答を募集いたします。
※1Stepで求められなくても構いません。またPHPと連携するような方法でも構いません。
あまりコストが掛かる方法は採用できませんが、なにかあればお願い致します。