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

Question

前提・実現したいこと

ユーザーの中から、「類似している」ユーザーのペアを一組探したいです。
ここでいう「類似している」とは、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

データセット

-- business table
CREATE TABLE business (
    business_id character varying(2) PRIMARY KEY,
    name character varying(30) NOT NULL
);

-- review table
CREATE TABLE review (
    review_id character varying(3) PRIMARY KEY,
    business_id character varying(2),
    user_id character varying(10),
    stars smallint
);

-- tip table
CREATE TABLE tip (
    tip_text text,
    business_id character varying(2),
    user_id character varying(10)
);

-- users
CREATE TABLE users (
    user_id character varying(10) PRIMARY KEY
);

insert into business
  values
  ('01', 'Super Store'),
  ('02', 'Family Store'),
  ('03', 'Great Store'),
  ('04', 'Everyday Store');

insert into review
  values
  ('000', '01', 'Bob', '1'),
  ('100', '04', 'Bob', '5'),
  ('200', '04', 'Alex', '4'),
  ('300', '02', 'Chapman', '3'),
  ('400', '03', 'Dong', '2'),
  ('500', '01', 'Liz', '5'),
  ('600', '03', 'Chapman', '2'),
  ('700', '02', 'Alex', '1'),
  ('800', '01', 'Dong', '5'),
  ('900', '04', 'Dong', '3');

insert into tip
  values
  ('nice store', '01', 'Chapman'),
  ('I dont like this store.', '01', 'Liz'),
  ('AWSOME!', '04', 'Alex'),
  ('great', '01', 'Alex'),
  ('I often use this store.', '02', 'Alex'),
  ('not good...', '03', 'Chapman'),
  ('wonderful', '01', 'Bob'),
  ('Very clean', '03', 'Dong'),
  ('My mother likes it.', '02', 'Liz'),
  ('Tasted food.', '04', 'Bob');

insert into users
  values
  ('Bob'),
  ('Alex'),
  ('Dong'),
  ('Liz'),
  ('Chapman');

試したこと

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

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

Comments

[退会済みユーザー]

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

[hentaiman]

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

[退会済みユーザー]

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

[hentaiman]

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

[退会済みユーザー]

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

[dameo]

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

Answer 1

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でグループ化して、それぞれの個数をカウントしてソートすれば、一番多い類似の嗜好パターンが取得できるはずです。

Comments

[hentaiman]

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

[退会済みユーザー]

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

[退会済みユーザー]

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

すいません、よくわからないのですが、今回私がやりたいことは、全てのユーザーのペアの中から表通しているbusiness_idの数が最も多いペアを出したいのですが、同時にグループ化すれば良いですか？

[FKM]

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

Answer 2

レビューしたbusiness_idを基に、
どのユーザーがどのbusiness_idをレビューしたかをカウントして
足し上げればいいんじゃないかと思ったけど。

それを泥臭く細かく区切って取得するか、
一発で出せるくらいにうまく組み合わせるかは、
力量次第で。

Comments

[退会済みユーザー]

すいません、よくわからないのですが、今回私がやりたいことは、全てのユーザーのペアの中から表通しているbusiness_idの数が最も多いペアを出したいのですが、各ペアがどのbusiness_idをレビューしたかをカウントして足し上げるにはどういった方法があるでしょうか？

Answer 3

同じbusiness_idで、usersテーブルでレビューした人、かつ、tipテーブルでコメントした人
ていうだけだと思いますが。

「類似」などという考えがややこしくしているだけに見えます。

但し、「最も類似している」というのはどのような条件なのか？という事になりますけど。
（レビュー数が多い人とかコメント数が多い人というような気がしますが）