• ベストアンサー
  • すぐに回答を!

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

  • 質問No.8783535
  • 閲覧数55
  • ありがとう数11
  • 回答数4

お礼率 75% (3/4)

ユーザーと商品のテーブル(省略)、

商品ジャンルテーブル、
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
  • ベストアンサー

ベストアンサー率 51% (3827/7415)

#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を持つ要素がないため
対象外
お礼コメント
hogehuga09

お礼率 75% (3/4)

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


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

その他の回答 (全3件)

  • 回答No.4

ベストアンサー率 51% (3827/7415)

#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しても同じ結果。
お礼コメント
hogehuga09

お礼率 75% (3/4)

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

ホントに助かりました、ありがとうございました!
投稿日時:2014/10/09 15:23
  • 回答No.2

ベストアンサー率 51% (3827/7415)

仕様が微妙

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

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

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

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

お礼率 75% (3/4)

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

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

その通りになります。
投稿日時:2014/10/09 11:35
  • 回答No.1

ベストアンサー率 33% (1403/4213)

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

例示のデータ、商品テーブルを省略しないほうが良いかも
お礼コメント
hogehuga09

お礼率 75% (3/4)

回答ありがとうございます。
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
関連するQ&A

ピックアップ

ページ先頭へ