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

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

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

Q&A

解決済

2回答

1160閲覧

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

退会済みユーザー

退会済みユーザー

総合スコア0

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

0グッド

1クリップ

投稿2019/11/14 21:05

編集2019/11/16 02:12

前提・実現したいこと

データセット

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文は以下の通りです。

PostgreSQL

1-- Table Definition ---------------------------------------------- 2 3CREATE TABLE business ( 4 business_id character varying(30) PRIMARY KEY, 5 name character varying(150) NOT NULL, 6 neighborhood character varying(100), 7 address character varying(200), 8 city character varying(100), 9 state character(3), 10 postal_code character(10), 11 latitude real, 12 longitude real, 13 is_open boolean 14); 15 16-- Indices ------------------------------------------------------- 17 18CREATE UNIQUE INDEX business_pkey ON business(business_id text_ops); 19 20 21 22-- Table Definition ---------------------------------------------- 23 24CREATE TABLE review ( 25 review_id character varying(30) PRIMARY KEY, 26 business_id character varying(30), 27 user_id character varying(30), 28 stars smallint, 29 review_date date, 30 review_text text, 31 useful_votes integer, 32 funny_votes integer, 33 cool_votes integer 34); 35 36-- Indices ------------------------------------------------------- 37 38CREATE UNIQUE INDEX review_pkey ON review(review_id text_ops); 39CREATE INDEX review_bus_id ON review(business_id text_ops); 40CREATE INDEX review_user_id ON review(user_id text_ops); 41 42 43 44-- Table Definition ---------------------------------------------- 45 46CREATE TABLE tip ( 47 tip_text text, 48 tip_date date, 49 likes integer, 50 business_id character varying(30), 51 user_id character varying(30) 52); 53 54-- Indices ------------------------------------------------------- 55 56CREATE INDEX tip_bus_id ON tip(business_id text_ops); 57CREATE INDEX tip_user_id ON tip(user_id text_ops); 58 59 60 61-- Table Definition ---------------------------------------------- 62 63CREATE TABLE users ( 64 user_id character varying(30) PRIMARY KEY, 65 name character varying(150), 66 yelping_since date, 67 useful_votes integer, 68 funny_votes integer, 69 cool_votes integer 70); 71 72-- Indices ------------------------------------------------------- 73 74CREATE UNIQUE INDEX users_pkey ON users(user_id text_ops);

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

PostgreSQL

1insert into business 2 values 3('-000aQFeK6tqVLndf7xORg', 'CoolCatAutoRepair', '', '6348N27thAve', 'Phoenix', 'AZ', '85017', '33.531105', '-112.11761', 'TRUE'), 4('0010xnFOCyJZeMAuTtiv5w', 'HennaShoppe', '', '7235E1stAve', 'Scottsdale', 'AZ', '85251', '33.49402', '-111.92512', 'TRUE'), 5('001jVKJHdhU2z_m9xTgOrg', 'AgaveChiropractic', '', '3040N44thSt,Ste3', 'Phoenix', 'AZ', '85018', '33.483635', '-111.98701', 'TRUE'), 6('002Gv4JE2bAfXkGJVM1IQw', 'AirgunsofArizona', '', '1970WElliotRd,Ste109', 'Gilbert', 'AZ', '85233', '33.35031', '-111.83239', 'TRUE'), 7('002ISUJGzG_5ejVrIqsPFA', 'SalonAtmosphere', 'Dormont', '3000WLibertyAve', 'Pittsburgh', 'PA', '15216', '40.393173', '-80.03695', 'TRUE'), 8('003b6f4PdppnakGqwo0fUQ', 'SolaSalon', 'Southwest', '7355SBuffalo,SteD', 'LasVegas', 'NV', '89113', '36.05418', '-115.262375', 'TRUE'), 9('003QzJjq0FYALjyFxKT2tA', 'HarbourBar', '', '1-3Sandside', 'Scarborough', 'NYK', 'YO111PE', '54.283997', '-0.39249977', 'TRUE'), 10('003wb6vvUFyNgDfD-2dPcw', 'AlterReality', '', '15601DetroitAve', 'Lakewood', 'OH', '44107', '41.48493', '-81.806206', 'TRUE'), 11('0044Q4cVBcHBgXPmhGgtcQ', 'TheKingsLanding', '', '5946MainStreet', 'Whitchurch-Stouffville', 'ON', 'L4A3A1', '43.969337', '-79.256386', 'TRUE'), 12('005XmZKuJZuNbl5tGXc5SA', 'TonyRomas', 'Downtown', '620ESaharaAve', 'LasVegas', 'NV', '89104', '36.14431', '-115.14569', 'FALSE'); 13 14insert into review 15 values 16('0005v4K5iZM3QAfImf_Ugw', 'hH74Tvfd-nzqT6jKDerMTw', 'S63nehkM27Zr77YxN2bA0Q', '1', '2016-08-11', 'ImsoupsetIwontevenreturnwithmyteenageboys.', '0', '0', '0'), 17('0006hhVjawuXu097INsiIw', '9CyNha8NE-7HEgjvUZFmnQ', 'TNLPYG78zJjjZPYTyeOZyA', '4', '2010-12-11', 'Youwillhaveagreattime!', '4', '0', '2'), 18('0008P7Cb7U-dpPchjamFWw', '-od707p4FHGul0gte29AoQ', 'XtJbDZODXi3mXHLoScGtwg', '5', '2015-09-11', 'ThepancakeistheyummiestIveeverhadandImdefinitelygoingback.', '2', '1', '1'), 19('0008uFuu1Sr5yqXeDN8RFw', 'Jb_QTIjDm5zbfrdk11R4fg', 'NaZVUOzqk5b-l0mlki-9Og', '5', '2016-09-10', 'Hadleftoverforthenextdayanditwasevengoodcold.', '2', '0', '1'), 20('0008zUEB-cuVAR4JZhXwlg', '5MPAZiYotUqgxkbXYY0c_Q', '1_qcKXfm8H8zecygxHjeWw', '2', '2014-02-20', 'Theyhadtherightidea!', '7', '2', '1'), 21('000AYfflyggMLAtTR6zdaA', 'taTF-fXetRbziIl80xPFjw', 'J-yULQA56PXaphJNA1RKGQ', '1', '2010-03-09', 'Einmalundniemehr...', '0', '0', '0'), 22('000BqmhDAkCYVa_MfJntpg', '-PfcLYFMW9ZXc1DEWSqw-g', 'gTlDDzDEHyDQ6iwjNhpI6A', '5', '2013-01-12', 'Andtheyofferedanaccommodationforourdisabledsonwithoutouraskingforit,whichwasverykind.Wellbebackagainsoon.', '1', '0', '0'), 23('000dJwYh7sr5xnsTFNlP5g', '7fzppFRWw-GyPVKqNlC5Eg', 'O7ZvoH_ONGNKbXvHDSZlwg', '1', '2017-10-09', 'Yapaquelesigonoregreso.,', '1', '0', '0'), 24('000DSSWY_UiSSdm2V4yJUQ', 'o9XzWtzTuV2X9fyYevXmkw', 'CESgogUugOYph4p12qvzFg', '4', '2016-07-01', 'Thewashroomwastinybutsqueakyclean.', '3', '1', '3'), 25('0-00GgzKPd9ik-X_6r4lBg', 'Oy_5I50HRZ1vXA5bnTPjHg', 'RxFxenmxZ_NUvSdkwPcY4Q', '5', '2013-11-21', 'Awesomeplace!', '3', '1', '1'); 26 27insert into tip 28 values 29('Greatbreakfastlargeportionsandfriendlywaitress.Ihighlyrecommendit', '2015-08-12', '0', 'jH19V2I9fIslnNhDzPmdkA', 'ZcLKXikTHYOnYt5VYRO5sg'), 30('Niceplace.Greatstaff.Afixtureinthetownshipforever', '2014-06-20', '0', 'dAa0hB2yrnHzVmsCkN4YvQ', 'oaYhjqBbh18ZhU0bpyzSuw'), 31('Happyhour5-7Monday-Friday', '2016-10-12', '0', 'dAa0hB2yrnHzVmsCkN4YvQ', 'ulQ8Nyj7jCUR8M83SUMoRQ'), 32('Parkingisapremium,keepcircling,youwilleventuallyfindagreatspot', '2017-01-28', '0', 'ESzO3Av0b1_TzKOiqzbQYQ', 'ulQ8Nyj7jCUR8M83SUMoRQ'), 33('Homemadepastaisthebestinthearea', '2017-02-25', '0', 'k7WRPbDd7rztjHcGGkEjlw', 'ulQ8Nyj7jCUR8M83SUMoRQ'), 34('Excellentservice,staffisdressedprofessionally,polite,mannered,andwelltrained', '2017-04-08', '0', 'k7WRPbDd7rztjHcGGkEjlw', 'ulQ8Nyj7jCUR8M83SUMoRQ'), 35('ComeearlyonSundaystoavoidtherush', '2016-07-03', '0', 'SqW3igh1_Png336VIb5DUA', 'ulQ8Nyj7jCUR8M83SUMoRQ'), 36('Lovetheirsoup!', '2016-01-07', '0', 'KNpcPGqDORDdvtekXd348w', 'ulQ8Nyj7jCUR8M83SUMoRQ'), 37('Soupsarefantastic!', '2016-05-22', '0', 'KNpcPGqDORDdvtekXd348w', 'ulQ8Nyj7jCUR8M83SUMoRQ'), 38('Thursdaynightis$5burgernight', '2016-06-09', '0', 'KNpcPGqDORDdvtekXd348w', 'ulQ8Nyj7jCUR8M83SUMoRQ'); 39 40insert into users 41 values 42('000AJVuwnwpGL8hHQUVLYg', 'Randy', '2013-04-21', '1', '0', '0'), 43('000JFML-9-W2-TGBTS0oaA', 'Deshone', '2014-07-01', '0', '0', '0'), 44('000JgS1HGrjvIxCn-BrpSw', 'Teresa', '2014-09-28', '0', '0', '0'), 45('000M3Xl73OIPztL97c3FSg', 'Todd', '2012-02-10', '0', '1', '0'), 46('000tGHVWdNNkjLk_jWJX5w', 'Megan', '2014-09-01', '0', '0', '0'), 47('000TZpy1bjaDzwMIaZxmlw', 'Jen', '2015-02-01', '0', '0', '0'), 48('-0012EkX22eeb2AiW_RkcQ', 'Jake', '2013-11-17', '0', '0', '0'), 49('0012OgDoCUa0o2icjVgwmg', 'Andrew', '2010-04-25', '30', '15', '27'), 50('0017YLRTmdhG5m8qggAlPw', 'Edward', '2008-03-14', '1', '0', '0'), 51('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だけで似ているユーザーを探そうとしたのですが、さっぱり分かりませんでした。

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2019/11/14 21:18

ご指摘ありがとうございます。 修正しておきました。
m.ts10806

2019/11/14 22:46

サンプルデータもご提示ください(INSERT文で)。 できれば定義もCREATE文のほうがありがたいです。
退会済みユーザー

退会済みユーザー

2019/11/16 00:26

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

2019/11/16 00:37

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

退会済みユーザー

2019/11/16 02:14

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

回答2

0

ベストアンサー

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

投稿2019/11/25 05:16

退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

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

分析関数を用います。

SQL

1select business_id, user_id from ( 2 select * 3 , rank() over(partition by business_id, user_id order by tip_count desc) tip_rank 4 from ( 5 select *, count(*) over(partition by business_id, user_id) tip_count 6 , count(*) over(partition by business_id) user_count 7 , count(*) over(partition by user_id) review_count 8 from tip 9 ) step1 10 where user_count >= 2 and review_count >= 200 11) step2 12where tip_rank <= 2 13group by business_id, user_id

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

投稿2019/11/15 01:54

編集2019/11/25 13:49
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問