こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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で実現できる方法だとありがたいです。

投稿日時 - 2014-10-08 18:44:57

QNo.8783535

すぐに回答ほしいです

質問者が選んだベストアンサー

#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を持つ要素がないため
対象外

投稿日時 - 2014-10-09 11:45:05

お礼

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


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);

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


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

投稿日時 - 2014-10-09 13:56:56

ANo.3

このQ&Aは役に立ちましたか?

8人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

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

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

投稿日時 - 2014-10-09 14:36:44

お礼

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

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

投稿日時 - 2014-10-09 15:23:20

ANo.2

仕様が微妙

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

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

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

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

投稿日時 - 2014-10-09 09:30:08

補足

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

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

その通りになります。

投稿日時 - 2014-10-09 11:35:09

ANo.1

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

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

投稿日時 - 2014-10-08 21:35:09

お礼

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



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

投稿日時 - 2014-10-09 00:32:45

あなたにオススメの質問