Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Beer Pong: SQL vs PGQL

Gianni Ceresa
September 07, 2019

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…

Gianni Ceresa

September 07, 2019
Tweet

More Decks by Gianni Ceresa

Other Decks in Technology

Transcript

  1. 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
  2. edge edge label edge properties edge ID directed edge vertex

    (node) vertex properties vertex ID a vertex can have a label
  3. 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
  4. 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)
  5. 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
  6. 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
  7. 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
  8. 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
  9. └ └ ID MANAGER 100 100 120 120 185 Steven

    Diana Tom SELECT id, name, LEVEL FROM employees CONNECT BY PRIOR id = manager
  10. (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)
  11. SELECT id, name, LEVEL FROM employees CONNECT BY PRIOR id

    = manager SELECT emp.name, mgr.name AS manager MATCH (emp:Employee) -/:managedBy*/-> (mgr:Employee)
  12. 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 ?
  13. 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