質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.20%

PostgreSQL Yelpで"似ている"ユーザーを探したい

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 236
退会済みユーザー

退会済みユーザー

前提・実現したいこと

データセット

Yelpとは、アメリカ版の食べログ+ホットペッパービューティーみたいなアプリです。いろんな分野の口コミレビューアプリになります。
今回はそのYelpのデータセットを多少加工したデータセットを使っております。(以下のURLは加工前のデータセットです。)
Yelp Dataset

テーブルは全部で4つです。

yelp=> \d
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | business | table | postgres
 public | review   | table | postgres
 public | tip      | table | postgres
 public | users    | table | postgres
(4 rows)

それぞれのテーブルのCREATE文は以下の通りです。

-- Table Definition ----------------------------------------------

CREATE TABLE business (
    business_id character varying(30) PRIMARY KEY,
    name character varying(150) NOT NULL,
    neighborhood character varying(100),
    address character varying(200),
    city character varying(100),
    state character(3),
    postal_code character(10),
    latitude real,
    longitude real,
    is_open boolean
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX business_pkey ON business(business_id text_ops);



-- Table Definition ----------------------------------------------

CREATE TABLE review (
    review_id character varying(30) PRIMARY KEY,
    business_id character varying(30),
    user_id character varying(30),
    stars smallint,
    review_date date,
    review_text text,
    useful_votes integer,
    funny_votes integer,
    cool_votes integer
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX review_pkey ON review(review_id text_ops);
CREATE INDEX review_bus_id ON review(business_id text_ops);
CREATE INDEX review_user_id ON review(user_id text_ops);



-- Table Definition ----------------------------------------------

CREATE TABLE tip (
    tip_text text,
    tip_date date,
    likes integer,
    business_id character varying(30),
    user_id character varying(30)
);

-- Indices -------------------------------------------------------

CREATE INDEX tip_bus_id ON tip(business_id text_ops);
CREATE INDEX tip_user_id ON tip(user_id text_ops);



-- Table Definition ----------------------------------------------

CREATE TABLE users (
    user_id character varying(30) PRIMARY KEY,
    name character varying(150),
    yelping_since date,
    useful_votes integer,
    funny_votes integer,
    cool_votes integer
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX users_pkey ON users(user_id text_ops);

INSERT文の内、10行を抜粋したものは以下になります。

insert into business
  values
('-000aQFeK6tqVLndf7xORg', 'CoolCatAutoRepair', '', '6348N27thAve', 'Phoenix', 'AZ', '85017', '33.531105', '-112.11761', 'TRUE'),
('0010xnFOCyJZeMAuTtiv5w', 'HennaShoppe', '', '7235E1stAve', 'Scottsdale', 'AZ', '85251', '33.49402', '-111.92512', 'TRUE'),
('001jVKJHdhU2z_m9xTgOrg', 'AgaveChiropractic', '', '3040N44thSt,Ste3', 'Phoenix', 'AZ', '85018', '33.483635', '-111.98701', 'TRUE'),
('002Gv4JE2bAfXkGJVM1IQw', 'AirgunsofArizona', '', '1970WElliotRd,Ste109', 'Gilbert', 'AZ', '85233', '33.35031', '-111.83239', 'TRUE'),
('002ISUJGzG_5ejVrIqsPFA', 'SalonAtmosphere', 'Dormont', '3000WLibertyAve', 'Pittsburgh', 'PA', '15216', '40.393173', '-80.03695', 'TRUE'),
('003b6f4PdppnakGqwo0fUQ', 'SolaSalon', 'Southwest', '7355SBuffalo,SteD', 'LasVegas', 'NV', '89113', '36.05418', '-115.262375', 'TRUE'),
('003QzJjq0FYALjyFxKT2tA', 'HarbourBar', '', '1-3Sandside', 'Scarborough', 'NYK', 'YO111PE', '54.283997', '-0.39249977', 'TRUE'),
('003wb6vvUFyNgDfD-2dPcw', 'AlterReality', '', '15601DetroitAve', 'Lakewood', 'OH', '44107', '41.48493', '-81.806206', 'TRUE'),
('0044Q4cVBcHBgXPmhGgtcQ', 'TheKingsLanding', '', '5946MainStreet', 'Whitchurch-Stouffville', 'ON', 'L4A3A1', '43.969337', '-79.256386', 'TRUE'),
('005XmZKuJZuNbl5tGXc5SA', 'TonyRomas', 'Downtown', '620ESaharaAve', 'LasVegas', 'NV', '89104', '36.14431', '-115.14569', 'FALSE');

insert into review
  values
('0005v4K5iZM3QAfImf_Ugw', 'hH74Tvfd-nzqT6jKDerMTw', 'S63nehkM27Zr77YxN2bA0Q', '1', '2016-08-11', 'ImsoupsetIwontevenreturnwithmyteenageboys.', '0', '0', '0'),
('0006hhVjawuXu097INsiIw', '9CyNha8NE-7HEgjvUZFmnQ', 'TNLPYG78zJjjZPYTyeOZyA', '4', '2010-12-11', 'Youwillhaveagreattime!', '4', '0', '2'),
('0008P7Cb7U-dpPchjamFWw', '-od707p4FHGul0gte29AoQ', 'XtJbDZODXi3mXHLoScGtwg', '5', '2015-09-11', 'ThepancakeistheyummiestIveeverhadandImdefinitelygoingback.', '2', '1', '1'),
('0008uFuu1Sr5yqXeDN8RFw', 'Jb_QTIjDm5zbfrdk11R4fg', 'NaZVUOzqk5b-l0mlki-9Og', '5', '2016-09-10', 'Hadleftoverforthenextdayanditwasevengoodcold.', '2', '0', '1'),
('0008zUEB-cuVAR4JZhXwlg', '5MPAZiYotUqgxkbXYY0c_Q', '1_qcKXfm8H8zecygxHjeWw', '2', '2014-02-20', 'Theyhadtherightidea!', '7', '2', '1'),
('000AYfflyggMLAtTR6zdaA', 'taTF-fXetRbziIl80xPFjw', 'J-yULQA56PXaphJNA1RKGQ', '1', '2010-03-09', 'Einmalundniemehr...', '0', '0', '0'),
('000BqmhDAkCYVa_MfJntpg', '-PfcLYFMW9ZXc1DEWSqw-g', 'gTlDDzDEHyDQ6iwjNhpI6A', '5', '2013-01-12', 'Andtheyofferedanaccommodationforourdisabledsonwithoutouraskingforit,whichwasverykind.Wellbebackagainsoon.', '1', '0', '0'),
('000dJwYh7sr5xnsTFNlP5g', '7fzppFRWw-GyPVKqNlC5Eg', 'O7ZvoH_ONGNKbXvHDSZlwg', '1', '2017-10-09', 'Yapaquelesigonoregreso.,', '1', '0', '0'),
('000DSSWY_UiSSdm2V4yJUQ', 'o9XzWtzTuV2X9fyYevXmkw', 'CESgogUugOYph4p12qvzFg', '4', '2016-07-01', 'Thewashroomwastinybutsqueakyclean.', '3', '1', '3'),
('0-00GgzKPd9ik-X_6r4lBg', 'Oy_5I50HRZ1vXA5bnTPjHg', 'RxFxenmxZ_NUvSdkwPcY4Q', '5', '2013-11-21', 'Awesomeplace!', '3', '1', '1');

insert into tip
  values
('Greatbreakfastlargeportionsandfriendlywaitress.Ihighlyrecommendit', '2015-08-12', '0', 'jH19V2I9fIslnNhDzPmdkA', 'ZcLKXikTHYOnYt5VYRO5sg'),
('Niceplace.Greatstaff.Afixtureinthetownshipforever', '2014-06-20', '0', 'dAa0hB2yrnHzVmsCkN4YvQ', 'oaYhjqBbh18ZhU0bpyzSuw'),
('Happyhour5-7Monday-Friday', '2016-10-12', '0', 'dAa0hB2yrnHzVmsCkN4YvQ', 'ulQ8Nyj7jCUR8M83SUMoRQ'),
('Parkingisapremium,keepcircling,youwilleventuallyfindagreatspot', '2017-01-28', '0', 'ESzO3Av0b1_TzKOiqzbQYQ', 'ulQ8Nyj7jCUR8M83SUMoRQ'),
('Homemadepastaisthebestinthearea', '2017-02-25', '0', 'k7WRPbDd7rztjHcGGkEjlw', 'ulQ8Nyj7jCUR8M83SUMoRQ'),
('Excellentservice,staffisdressedprofessionally,polite,mannered,andwelltrained', '2017-04-08', '0', 'k7WRPbDd7rztjHcGGkEjlw', 'ulQ8Nyj7jCUR8M83SUMoRQ'),
('ComeearlyonSundaystoavoidtherush', '2016-07-03', '0', 'SqW3igh1_Png336VIb5DUA', 'ulQ8Nyj7jCUR8M83SUMoRQ'),
('Lovetheirsoup!', '2016-01-07', '0', 'KNpcPGqDORDdvtekXd348w', 'ulQ8Nyj7jCUR8M83SUMoRQ'),
('Soupsarefantastic!', '2016-05-22', '0', 'KNpcPGqDORDdvtekXd348w', 'ulQ8Nyj7jCUR8M83SUMoRQ'),
('Thursdaynightis$5burgernight', '2016-06-09', '0', 'KNpcPGqDORDdvtekXd348w', 'ulQ8Nyj7jCUR8M83SUMoRQ');

insert into users
  values
('000AJVuwnwpGL8hHQUVLYg', 'Randy', '2013-04-21', '1', '0', '0'),
('000JFML-9-W2-TGBTS0oaA', 'Deshone', '2014-07-01', '0', '0', '0'),
('000JgS1HGrjvIxCn-BrpSw', 'Teresa', '2014-09-28', '0', '0', '0'),
('000M3Xl73OIPztL97c3FSg', 'Todd', '2012-02-10', '0', '1', '0'),
('000tGHVWdNNkjLk_jWJX5w', 'Megan', '2014-09-01', '0', '0', '0'),
('000TZpy1bjaDzwMIaZxmlw', 'Jen', '2015-02-01', '0', '0', '0'),
('-0012EkX22eeb2AiW_RkcQ', 'Jake', '2013-11-17', '0', '0', '0'),
('0012OgDoCUa0o2icjVgwmg', 'Andrew', '2010-04-25', '30', '15', '27'),
('0017YLRTmdhG5m8qggAlPw', 'Edward', '2008-03-14', '1', '0', '0'),
('0019WdoIywMw3imBqtSZgg', 'Caitlin', '2015-07-27', '0', '0', '0');

条件

①review数が200以上のユーザーのみ扱います。
②最も"似ている"2人のユーザーを探したいです。ここでいう"似ている"とは、それぞれのユーザーのreviewとtipの中で、被っているbusiness_idが一番多い2人のユーザーが最も似ているということになります。

例えば、Aさん(user_id=a)とBさん(user_id=b)がいて、reviewテーブルに、
(user_id=a, business_id=1234)
(user_id=b, business_id=1234)
というタプルがあれば、一つ"似ている"ということになります。さらに、tipテーブルに、
(user_id=a, business_id=XXX)
(user_id=b, business_id=XXX)
もあるとすると、2つ"似ている"ということになります。
この"似ている"数が、最も多いユーザーのペアを一つ探したいです。要するに、Yelpで同じ場所を訪れる傾向のあるユーザーを見つけたいです。

試したこと

とりあえず、tipは置いといて、reviewだけで似ているユーザーを探そうとしたのですが、さっぱり分かりませんでした。

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • 退会済みユーザー

    退会済みユーザー

    2019/11/16 09:26

    ご返信が遅くなってしまいましたが、修正させて頂きました。
    CREATE文の方は載せたのですが、自分で作ったデータセットでない、かつ、データ量が多かったため、INSERT文の代わりにスクリーンショットを載せました。見づらいかもしれませんが、そちらでお願い致します。

    キャンセル

  • Orlofsky

    2019/11/16 09:37

    CREATE TABLEやINSERTの提示を依頼するのは実際にSQLの動作確認するためです。各テーブルのINSERTは精査したデータを数件ずつに変更してください。

    キャンセル

  • 退会済みユーザー

    退会済みユーザー

    2019/11/16 11:14

    INSERT文を10行だけ抜粋したものを追加させて頂きました。10行ずつしか無いので、今回の結果を求めようとすると、空の結果が返ってきてしまうかもしれません、、、

    キャンセル

回答 2

0

一意キーとして、business(business_id), review(review_id), tip(user_id, business_id)で、その一意キー同士の関係がbusiness : review : tip=1 : 1 : 1という前提で。

分析関数を用います。

select business_id, user_id from (
  select *
       , rank() over(partition by business_id, user_id order by tip_count desc) tip_rank   
  from (
    select *, count(*) over(partition by business_id, user_id) tip_count 
          , count(*) over(partition by business_id) user_count
          , count(*) over(partition by user_id) review_count
    from tip
  ) step1
  where user_count >= 2 and review_count >= 200
) step2
where tip_rank <= 2
group by business_id, user_id


※文法エラーになっていたので修正。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

check解決した方法

-1

質問が分かりずらかったようですので、修正版を新たに投稿させて頂きました。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.20%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる