Query by cypher

Query cypher with Neo4j 3.0.6
After loading livedoor grourmet dataset, issued various queries.
refer: https://speakerdeck.com/asakoraven/load-csv-by-cypher

Transcript

1. Query csv by cypher Neo4j 3.0.6 2016.11.xx Asako TOYAMA
2. ໨త Neo4j cypher ͷֶश
3. Livedoor άϧϝ dataset Λ࢖͏ datasetͷઆ໌ livedoorάϧϝͷDataSetΛެ։ σʔλμ΢ϯϩʔυઌ https://github.com/livedoor/datasets csv load ͷtips·ͱΊ͸Load by cypher ࢀর
5. Best restaurant? 1. ධՁͷtotal, service, food, atmosphere ͷείΞΛ଍͠ ߹ΘͤΔ 2. είΞͷଟ͍ͷΛbest restaurantͱ͢Δ
6. MATCH (c:Comment) - [rate:RATE] -> (restaurant:Restaurant) WITH restaurant, (rate.total + rate.service + rate.food + rate.atmosphere) as rate_sum WHERE rate_sum > 15 RETURN restaurant.name, rate_sum ORDER BY rate_sum DESC LIMIT 25 ;
8. ͜ΕͰ͸͋·Γ໘ന ͘ͳ͍ comment Λͨ͘͞ΜूΊ ͨrestaurant Λbest restaurant ͱͯ͠ΈΔ
9. Best restaurant? 1. comment Λͨ͘͞ΜूΊͨϨετϥϯΛbest restaurant ͱ͢Δ
10. MATCH p = (u:User) <- [vote:VOTED_BY] - (c:Comment) - [rate:RATE] -> (r:Restaurant) RETURN p, r.name AS restaurant, COUNT(c) AS comment_count, COLLECT(c) AS comments ORDER BY comment_count DESC LIMIT 25 ;
12. ίϝϯτʹରͯ͠ͷ ʮ͍͍ͶʂʯΛߟྀ ͍ͨ͠
13. Best restaurant? 1. comment ͕͍ͭͨ͜ͱΛείΞ10ͱ਺͑Δ 2. comment ʹ͍ͭͨʮ͍͍Ͷʂʯ(VOTEDʣΛείΞ1ͱ ਺͑Δ 3. 1.ͱ2. Λ଍͠߹ΘͤͨείΞͷ্ҐΛbest restaurant ͱ ͢Δ
14. MATCH (r:Restaurant) <- [:RATE] - (c:Comment) OPTIONAL MATCH (c) - [v:VOTED_BY] -> (u:User) WITH c, r, v RETURN r.name AS restaurant, COUNT(DISTINCT c) AS comment_count, SIZE(COLLECT(DISTINCT v)) AS like_count, (COUNT(DISTINCT c) * 10 + SIZE(COLLECT(DISTINCT v))) AS score ORDER BY score DESC LIMIT 25 ;
15. ॱҐ͕มΘΔɾɾɾ
16. ѱ͍ίϝϯτ΋εί ΞʹΧ΢ϯτ͞Εͯ ͠·͏
17. Best restaurant? 1. comment ͕͍ͭͨ͜ͱΛείΞ10ͱ਺͑Δ 2. comment ʹ͍ͭͨʮ͍͍Ͷʂʯ(VOTEDʣΛείΞ1ͱ ਺͑Δ 3. rate.total ͕ 3ҎԼͷcomment͸ධՁର৅ͱ͠ͳ͍ 3. 1.ͱ2. Λ଍͠߹ΘͤͨείΞͷ্ҐΛbest restaurant ͱ ͢Δ
18. MATCH (r:Restaurant) <- [rate:RATE] - (c:Comment) OPTIONAL MATCH (c) - [v:VOTED_BY] -> (u:User) WITH c, r, v, (rate.total + rate.service + rate.food + rate.atmosphere) as rate_sum WHERE rate.total > 3 RETURN r.name AS restaurant, COUNT(DISTINCT c) AS comment_count, SIZE(COLLECT(DISTINCT v)) AS like_count, (COUNT(DISTINCT c) * 10 + SIZE(COLLECT(DISTINCT v))) AS score ORDER BY score DESC LIMIT 25 ;
19. ॱҐ͕มΘΔɾɾɾ
20. User similarity? 1. ᅂ޷ͷ͍ۙϢʔβΛϐοΫΞοϓ͍ͨ͠ 2. rate.total, atmosphere, service, food ͷڑ཭Λܭࢉ͢Δ 3. ڞ௨ͰධՁ͍ͯ͠Δrestaurant ͕ 10ҎԼͷuser ಉ࢜͸ ධՁ͠ͳ͍ 4. ڑ཭͕͍ۙuser ͕ ᅂ޷͕͍ۙϢʔβͱ͢Δ
21. MATCH (u1:User) <- [:COMMENTED_BY] - (c1:Comment) - [r1:RATE] -> (r:Restaurant) <- [r2:RATE] - (c2:Comment) - [:COMMENTED_BY] -> (u2:User) WITH u1, u2, COUNT(r) AS restaurant_count, SQRT(SUM((r1.total - r2.total)^2 + (r1.atmosphere - r2.atmosphere)^2 + (r1.service - r2.service)^2 + (r1.food - r2.food)^2)) AS distance WHERE restaurant_count > 10 RETURN u1, u2, restaurant_count, distance, (1 / (1+distance)) AS similarity ORDER BY distance LIMIT 50 ;
23. User similarity? ᅂ޷ͷۙա͗ΔϢʔβ͕͍ΔɻͲͷrestaurant ΋·ͬͨ͘ ಉ͡ධՁ ͜Ε͸restaurant ධՁͷ΍ΒͤͰ͸ͳ͍͔ʁ 1. ͓ޓ͍ͷcomment ʹ ʮ͍͍Ͷʂʯ͍͋ͬͯ͠Δ user ͷڑ཭ΛݟͯΈΔ
24. MATCH (u1:User) <- [:COMMENTED_BY] - (c1:Comment) - [r1:RATE] -> (r:Restaurant) <- [r2:RATE] - (c2:Comment) - [:COMMENTED_BY] -> (u2:User), (u1) <- [v1:VOTED_BY] - (c2), (u2) <- [v2:VOTED_BY] - (c1) WITH u1, u2, COUNT(r) AS restaurant_count, SQRT(SUM((r1.total - r2.total)^2 + (r1.atmosphere - r2.atmosphere)^2 + (r1.service - r2.service)^2 + (r1.food - r2.food)^2)) AS distance WHERE distance < 10 AND restaurant_count > 10 RETURN u1, u2, distance ORDER BY distance LIMIT 50
26. User similarity? ͓ޓ͍ʹʮ͍͍Ͷʂʯ͍͋ͬͯ͠Δuserಉ࢜ͱ͍͏ͷ͸ɺ ΍ΒͤͰ͸ͳ͍ͷ͔ʁ ڑ཭0ͱ͍͏Θ͚Ͱ͸ͳ͍Β͍͠ ͲͷΑ͏ͳrestaurantΛධՁ͍ͯ͠Δ͔ɺ֬ೝͯ͠ΈΔ
27. MATCH (u1:User) <- [:COMMENTED_BY] - (c1:Comment) - [r1:RATE] -> (r:Restaurant) <- [r2:RATE] - (c2:Comment) - [:COMMENTED_BY] -> (u2:User), (u1) <- [v1:VOTED_BY] - (c2), (u2) <- [v2:VOTED_BY] - (c1) WITH u1, u2, COLLECT(r.name) AS restaurants, COUNT(r) AS restaurant_count, SQRT(SUM((r1.total - r2.total)^2 + (r1.atmosphere - r2.atmosphere)^2 + (r1.service - r2.service)^2 + (r1.food - r2.food)^2)) AS distance WHERE distance < 10 AND restaurant_count > 10 RETURN u1, u2, restaurants, distance ORDER BY distance LIMIT 50
28. ୯ʹߦಈൣғ͕ಉ͡ͷ ϥʔϝϯϚχΞͷϢʔ βಉ͔࢜΋͠Εͳ ͍ɾɾɾ