[PostgreSQL]　最も「類似している」ユーザーを探したい

前提・実現したいこと

ユーザーの中から、「類似している」ユーザーのペアを一組探したいです。
ここでいう「類似している」とは、reviewテーブルとtipテーブルで被っているbusiness_idの数のことを指します。(説明が難しいので、下の例で確認をお願いします。)

例えば、usersテーブルにBobとAlexというユーザーがいます。Bobはreviewテーブルの中で、business_idが01と04のお店のレビューをしています。一方で、Alexはreviewテーブルの中で、business_idが02と04のお店をレビューしており、BobもAlexも同じ04のお店をレビューしているので、1「類似している」となります。
同じようにtipテーブルを見てみると、BobとAlexは二人とも01と04のお店のコメントを残しているので、2「類似している」となり、合計で3「類似している」となりました。

他のユーザーを調べてみても、3「類似している」を超えるペアはいないので、最も「類似している」ユーザーのペアは、BobとAlexであることが分かります。この一連の流れを行うコードを書きたいです。

最終的には、最も「類似している」数が多いペアを一組求めたいので、以下のような形になります。

user_id1 | user_id2 | similarity
--------------------------------
Bob      | Alex     | 3

データセット

PostgreSQL
1-- business table
2CREATE TABLE business (
3    business_id character varying(2) PRIMARY KEY,
4    name character varying(30) NOT NULL
5);
6
7-- review table
8CREATE TABLE review (
9    review_id character varying(3) PRIMARY KEY,
10    business_id character varying(2),
11    user_id character varying(10),
12    stars smallint
13);
14
15-- tip table
16CREATE TABLE tip (
17    tip_text text,
18    business_id character varying(2),
19    user_id character varying(10)
20);
21
22-- users
23CREATE TABLE users (
24    user_id character varying(10) PRIMARY KEY
25);
26

PostgreSQL
1insert into business
2  values
3  ('01', 'Super Store'),
4  ('02', 'Family Store'),
5  ('03', 'Great Store'),
6  ('04', 'Everyday Store');
7
8insert into review
9  values
10  ('000', '01', 'Bob', '1'),
11  ('100', '04', 'Bob', '5'),
12  ('200', '04', 'Alex', '4'),
13  ('300', '02', 'Chapman', '3'),
14  ('400', '03', 'Dong', '2'),
15  ('500', '01', 'Liz', '5'),
16  ('600', '03', 'Chapman', '2'),
17  ('700', '02', 'Alex', '1'),
18  ('800', '01', 'Dong', '5'),
19  ('900', '04', 'Dong', '3');
20
21insert into tip
22  values
23  ('nice store', '01', 'Chapman'),
24  ('I dont like this store.', '01', 'Liz'),
25  ('AWSOME!', '04', 'Alex'),
26  ('great', '01', 'Alex'),
27  ('I often use this store.', '02', 'Alex'),
28  ('not good...', '03', 'Chapman'),
29  ('wonderful', '01', 'Bob'),
30  ('Very clean', '03', 'Dong'),
31  ('My mother likes it.', '02', 'Liz'),
32  ('Tasted food.', '04', 'Bob');
33
34insert into users
35  values
36  ('Bob'),
37  ('Alex'),
38  ('Dong'),
39  ('Liz'),
40  ('Chapman');

試したこと

とりあえず、reviewテーブルの方だけでもと思って取り組んでみたのですが、さっぱり分かりませんでした。

よろしくお願い致します。

退会済みユーザー

2019/11/25 05:13

https://teratail.com/questions/223266 の方は解決しましたか？前の質問を終わらせず見直しもせず次の質問を起こすと回答がつきにくいです。

hentaiman

2019/11/25 05:16

前回の質問とほぼ同じ質問のようですが、その時の回答では対応も流用も出来なかったのでしょうか？

退会済みユーザー

2019/11/25 05:19

すいません、前回の質問が分かりづらかったようで、解決しませんでしたので、新たに分かりやすくしたものを投稿させて頂きました。前回の分は、解決済みに変更しておきました。

hentaiman

2019/11/25 05:31

質問の冒頭にどうやって類似判断するか書いているので何がさっぱり分からないのかが分からないのですが、どういう風に結果を出したいのでしょうか？

退会済みユーザー

2019/11/25 08:46

最も「類似している」数が多いペアを一組求めたいと思っております。提示しているデータセットだと、BobとAlexが3「類似している」で、最も多いので、答えはBobとAlexになります。

退会済みユーザー

2019/11/25 17:08

何をしたいのか知りませんが、多分類似度の定義が曖昧なのではないかと思います。ユーザーiの店mの星評価をs(i,m)として、ユーザーが店別星評価のベクトルに見立てて、ユーザー類似度r(i,j)を距離っぽく Σm (s(i,m)-s(j,m))^2 か、内積っぽく Σm (s(i,m)*s(j,m)) とかにしとけば、何となくそれっぽくなるのでは？計算する前に正規化のようなことをした方がいいかもですけど… ただSQLでやるの？という気もします。

行動規範の内容に同意します

回答3件

DBテーブル設計の基本を学習してから設計しなおした方がいいような気が。紐付いてない番号もあるみたいですし、他にも色々と問題ありです。プログラムを書くのもテーブル設計をしっかり作ってからでしょうかね。

で、口コミサイトのような今回のテーブルの設計です（●は主キー）。カラム名はある程度自分で補完しています。
要点を整理しましょう。

userは、ある時間に任意の店舗へ投稿する。
userは口コミを投稿し、スター数で評価できる

なので、

users(利用者マスタ)：●user_id,user_name
business(店舗マスタ)   : ●business_id,shop_name
review(投稿トランザクション): ●user_id,●review_date,business_id,tip_id
tip(口コミトランザクション):●tip_id,●seq_no,tip_text,stars

このように整理できそうですね。こうすれば、ボブとアレックスが同じ店に対してコメントしていることが一目瞭然です。もし、一回あたり一コメント限定なら、訪問トランザクションと口コミトランザクションは集約できます。

こうやって、あとはreviewに対してuser_idとbusiness_idでグループ化して、それぞれの個数をカウントしてソートすれば、一番多い類似の嗜好パターンが取得できるはずです。

投稿2019/11/25 05:55

編集2019/11/25 06:07

FKM

総合スコア3647

hentaiman

2019/11/25 06:11

相関係数っていう単語教えた方が早そう

退会済みユーザー

2019/11/25 08:52 編集

ご回答ありがとうございます！言葉足らずで申し訳なかったのですが、元々は、Yelpのデータセット( https://www.yelp.com/dataset/documentation/main )を修正したものを使っており、今回の質問用に、不必要な部分を取り除いて、データ量も大幅に減らし、中身も分かりやすく書き換えたものをこちらに載せました。そのため、変なデータ構造になってしまいました、、

退会済みユーザー

2019/11/25 08:58

>>> あとはreviewに対してuser_idとbusiness_idでグループ化して、それぞれの個数をカウントしてソートすれば、一番多い類似の嗜好パターンが取得できるはずです。すいません、よくわからないのですが、今回私がやりたいことは、全てのユーザーのペアの中から表通しているbusiness_idの数が最も多いペアを出したいのですが、同時にグループ化すれば良いですか？

FKM

2019/11/25 09:22

よく考えたらbusiness_idだけでグループ化した方が適切ですね。 select count(business_id) as business_id user_id from review group by business_id order by 1; とすれば、ちょうど一番投稿数が多い店舗を取得でき、そのユーザー名も取得できるはずです。

行動規範の内容に同意します