前提・実現したいこと
データセット
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だけで似ているユーザーを探そうとしたのですが、さっぱり分かりませんでした。
よろしくお願い致します。
回答2件
あなたの回答
tips
プレビュー