Beer Pong: SQL vs PGQL

SQL is in Relational Databases what PGQL is in the Graph Databases world (Oracle-wise). Did you ever think at challenging both solution? Doing the same job, compare how nice, easy, fast it is. Let’s see how it goes…

Transcript

4. Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | bit.ly/OracleACEProgram 450+ Technical Experts Helping Peers Globally Nominate yourself or someone you know: acenomination.oracle.com
6. • • (ask Martin Widlake for this one) • •
7. • • • • •
8. There is a lot more than that, but let’s keep it simple
9. Vertex edge A Property Graph (also called node)
10. edge edge label edge properties edge ID directed edge vertex (node) vertex properties vertex ID a vertex can have a label
12. From 45,700 nodes with 105,406 edges, to 85 nodes with 218 edges in seconds Catalog RPD
13. Spain Italy John Doe Company A Company B Company C Company D Located in Located in Located in Located in Buys from Buys from Buys from Buys from Money laundering and VAT frauds Owns
15. Every row of a table has a fixed, identical structure Connections at a table level (not row) Nodes and edges can have any number of properties Connections at a node level (can be seen as row level)
18. • • • •
19. SEGMENT CUSTOMER ACCOUNT TRANSACTION
20. SELECT othercustomer.name, SUM(transaction.amount) as total_transaction FROM customer INNER JOIN account ON customer.id = account.owner INNER JOIN transaction ON account.id = transaction.src_account OR account.id = transaction.dst_account INNER JOIN account otheraccount ON (transaction.src_account = otheraccount.id OR transaction.dst_account = otheraccount.id) AND account.id <> otheraccount.id INNER JOIN customer othercustomer ON otheraccount.owner = othercustomer.id INNER JOIN segment othersegment ON othercustomer.segment = othersegment.id WHERE customer.name = 'Nikita’ AND othersegment.name IN ('Person', 'Company') GROUP BY othercustomer.name
22. SELECT owner.name, SUM(t.amount) AS total_transaction FROM financial_transactions MATCH (p:Person) -[:ownerOf]-> (:Account) -[t:transaction]- (:Account) <- [:ownerOf]- (owner:Person|Company) WHERE p.name = 'Nikita' GROUP BY owner
23. SELECT owner.name, SUM(t.amount) AS total_transaction FROM financial_transactions MATCH (p:Person) -[:ownerOf]-> (:Account) -[t:transaction]- (:Account) <-[:ownerOf]- (owner:Person|Company) WHERE p.name = 'Nikita' GROUP BY owner
24. SELECT othercustomer.name, SUM(transaction.amount) as total_transaction FROM customer INNER JOIN account ON customer.id = account.owner INNER JOIN transaction ON account.id = transaction.src_account OR account.id = transaction.dst_account INNER JOIN account otheraccount ON (transaction.src_account = otheraccount.id OR transaction.dst_account = otheraccount.id) AND account.id <> otheraccount.id INNER JOIN customer othercustomer ON otheraccount.owner = othercustomer.id INNER JOIN segment othersegment ON othercustomer.segment = othersegment.id WHERE customer.name = 'Nikita’ AND othersegment.name IN ('Person', 'Company') GROUP BY othercustomer.name SELECT owner.name, SUM(t.amount) AS total_transaction FROM financial_transactions MATCH (p:Person) -[:ownerOf]-> (:Account) -[t:transaction]- (:Account) <-[:ownerOf]- (owner:Person|Company) WHERE p.name = 'Nikita' GROUP BY owner
27. • • • • • • •
31. └ └ ID MANAGER 100 100 120 120 185 Steven Diana Tom SELECT id, name, LEVEL FROM employees CONNECT BY PRIOR id = manager
32. (a vertex) –[connected by an edge to]-> (another vertex) Steven Diana Tom managedBy managedBy MATCH (v1)->(v2) MATCH (v1)–[e]->(v2) MATCH (v1)-(v2) MATCH (v1)–[e]-(v2) MATCH (v1)<-(v2) MATCH (v1)<-[e]-(v2) MATCH (v1)-/:label?/->(v2) MATCH (v1)-/:label*/->(v2) MATCH (v1)-/:label+/->(v2) MATCH (v1)-/:label{1,2}/->(v2)
33. SELECT id, name, LEVEL FROM employees CONNECT BY PRIOR id = manager SELECT emp.name, mgr.name AS manager MATCH (emp:Employee) -/:managedBy*/-> (mgr:Employee)
35. • •
36. B A C D E 8 min 5 min 4 min 2 min 7 min 5 min 4 min Shortest way from A to E ?
37. SELECT start.name, SUM(e.time) AS distance, dst.name MATCH SHORTEST( (start) -[e]->* (dst) ) WHERE start.name = 'A' AND dst.name = 'E'
38. SELECT start.name, SUM(e.time) AS distance, dst.name MATCH SHORTEST( (start) -[e]->* (dst) ) WHERE start.name = 'A' AND dst.name = 'E' B A C D E 8 min 5 min 4 min 2 min 7 min 5 min 4 min
39. SELECT start.name, SUM(e.time) AS distance, dst.name MATCH SHORTEST( (start) -[e]->* (dst) ) WHERE start.name = 'A' AND dst.name = 'E'
46. • • • • •