Tweet
Tweet

Slide 1

Slide 1 text

The 10 mightiest SQL queries in the world Pablo Gonzalez Doval

Slide 2

Slide 2 text

PGCONF.EU 2017

Slide 3

Slide 3 text

PGCONF.EU 2017

Slide 4

Slide 4 text

DEV Working @ OnGres (www.ongres.com) Software Developer PostgreSQL support @dovaleac pgdoval PABLO GONZALEZ

Slide 5

Slide 5 text

PGCONF.EU 2017 1. generate_series() 2. WITH RECURSIVE 3. LATERAL 4. Window functions 5. Grouping sets 6. Moving data atomically 7. Run WHATEVER you want Post-92

Slide 6

Slide 6 text

PGCONF.EU 2017 PGCONF.EU 2017 [EX1] Premium users

Slide 7

Slide 7 text

user position 3 1 4 2 2 3 1 4 scoring premium user position 4 1 3 3 user position 4 1 2 2 3 3 1 4 PGCONF.EU 2017 [EX1] Premium users

Slide 8

Slide 8 text

PGCONF.EU 2017 PGCONF.EU 2017 1. generate_series()

Slide 9

Slide 9 text

generate_series 1 2 3 4 5 6 7 8 PGCONF.EU 2017 1. generate_series()

Slide 10

Slide 10 text

generate_series 1 3 5 PGCONF.EU 2017 1. generate_series()

Slide 11

Slide 11 text

Generate count of events per hour id t 1 12:23 2 12:55 3 15:06 4 22:47 PGCONF.EU 2017 1. generate_series()

Slide 12

Slide 12 text

PGCONF.EU 2017 1. generate_series()

Slide 13

Slide 13 text

PGCONF.EU 2017 1. generate_series() Does it work?

Slide 14

Slide 14 text

Generate event count per hour id t 1 12:23 2 12:55 3 15:06 4 22:47 PGCONF.EU 2017 1. generate_series()

Slide 15

Slide 15 text

PGCONF.EU 2017 1. generate_series()

Slide 16

Slide 16 text

PGCONF.EU 2017 PGCONF.EU 2017 2. WITH RECURSIVE

Slide 17

Slide 17 text

PGCONF.EU 2017 sum 5050 2. WITH RECURSIVE

Slide 18

Slide 18 text

id name parent_id 1 good1 - 2 wrong1 - 3 good2 1 4 good3 2 5 wrong2 1 6 good4 3 7 good5 4 id name parent_id 1 good1 - 3 good2 1 6 good4 3 name NOT LIKE ‘wrong%’ PGCONF.EU 2017 2. WITH RECURSIVE

Slide 19

Slide 19 text

PGCONF.EU 2017 2. WITH RECURSIVE

Slide 20

Slide 20 text

PGCONF.EU 2017 PGCONF.EU 2017 [EX1] Premium users

Slide 21

Slide 21 text

user position 3 1 4 2 2 3 1 4 scoring premium user position 4 1 3 3 user position 4 1 2 2 3 3 1 4 PGCONF.EU 2017 [EX1] Premium users

Slide 22

Slide 22 text

PGCONF.EU 2017 [EX1] Premium users

Slide 23

Slide 23 text

PGCONF.EU 2017 [EX1] Premium users

Slide 24

Slide 24 text

PGCONF.EU 2017 [EX1] Premium users

Slide 25

Slide 25 text

PGCONF.EU 2017 [EX1] Premium users

Slide 26

Slide 26 text

PGCONF.EU 2017 PGCONF.EU 2017 [EX2] User sessions

Slide 27

Slide 27 text

id user_id time 1 1 12:40 2 1 13:05 3 2 13:40 4 1 13:45 5 2 14:02 6 1 18:23 events user_id events 1 2 1 1 1 1 2 2 *Two consecutive events by the same user belong to the same session iff t2-t1<= 30 minutes sessions PGCONF.EU 2017 [EX2] User sessions

Slide 28

Slide 28 text

PGCONF.EU 2017 PGCONF.EU 2017 3. LATERAL

Slide 29

Slide 29 text

PGCONF.EU 2017 3. LATERAL

Slide 30

Slide 30 text

PGCONF.EU 2017 3. LATERAL

Slide 31

Slide 31 text

id name 1 Deportivo 2 Barcelona 3 Real Madrid 4 Atlético Madrid id team_id name value country 1 2 Messi 150 ARG 2 2 Iniesta 100 ESP 3 2 Umtiti 70 FRA 4 3 Modric 100 CRO 5 3 Cristiano Ronaldo 95 POR 6 4 Griezmann 120 FRA teams players PGCONF.EU 2017 3. LATERAL

Slide 32

Slide 32 text

id name value 1 Deportivo 80 2 Barcelona 1000 3 Real Madrid 1000 4 Atlético Madrid 700 PGCONF.EU 2017 3. LATERAL

Slide 33

Slide 33 text

team best_player value country Deportivo Emre Çolak 15 TUR Barcelona Messi 150 ARG Real Madrid Modric 100 CRO Atlético Griezmann 120 FRA PGCONF.EU 2017 3. LATERAL

Slide 34

Slide 34 text

PGCONF.EU 2017 3. LATERAL

Slide 35

Slide 35 text

PGCONF.EU 2017

Slide 36

Slide 36 text

PGCONF.EU 2017

Slide 37

Slide 37 text

PGCONF.EU 2017 3. LATERAL

Slide 38

Slide 38 text

PGCONF.EU 2017

Slide 39

Slide 39 text

PGCONF.EU 2017 PGCONF.EU 2017 4. Window functions

Slide 40

Slide 40 text

team_id name value 1 Emre Çolak 15 2 Messi 150 3 Modric 100 4 Griezmann 120 PGCONF.EU 2017 4. Window functions

Slide 41

Slide 41 text

id team_id name value country 1 2 Messi 150 ARG 2 2 Iniesta 100 ESP 3 2 Umtiti 70 FRA 4 3 Modric 100 CRO 5 3 Cristiano Ronaldo 95 POR 6 4 Griezmann 120 FRA PGCONF.EU 2017 4. Window functions

Slide 42

Slide 42 text

position id team_id name value country 1 1 2 Messi 150 ARG 2 2 2 Iniesta 100 ESP 3 3 2 Umtiti 70 FRA 1 4 3 Modric 100 CRO 2 5 3 Cristiano Ronaldo 95 POR 1 6 4 Griezmann 120 FRA PGCONF.EU 2017 4. Window functions

Slide 43

Slide 43 text

PGCONF.EU 2017 4. Window functions

Slide 44

Slide 44 text

PGCONF.EU 2017 4. Window functions

Slide 45

Slide 45 text

row_number() rank() dense_rank() percent_rank() cume_dist() ntile(num_buckets integer) lag(value anyelement [, offset integer [,default anyelement ]]) lead(value anyelement [, offset integer [,default anyelement ]]) first_value(value any) last_value(value any) nth_value(value any, nth integer) PGCONF.EU 2017 4. Window functions

Slide 46

Slide 46 text

PGCONF.EU 2017 PGCONF.EU 2017 [EX2] User sessions

Slide 47

Slide 47 text

id user_id time 1 1 12:40 2 1 13:05 3 2 13:40 4 1 13:45 5 2 14:02 6 1 18:23 events user_id events 1 2 1 1 1 1 2 2 *Two consecutive events by the same user belong to the same session iff t2-t1<= 30 minutes sessions PGCONF.EU 2017 [EX2] User sessions

Slide 48

Slide 48 text

PGCONF.EU 2017 [EX2] User sessions

Slide 49

Slide 49 text

PGCONF.EU 2017 [EX2] User sessions

Slide 50

Slide 50 text

PGCONF.EU 2017 [EX2] User sessions

Slide 51

Slide 51 text

PGCONF.EU 2017 PGCONF.EU 2017 [EX3] Palindromes

Slide 52

Slide 52 text

words user_id events racard r ACA rd racard RACAR d ifisi IFI si ifisi If ISI palindromes pneumonoultramicroscopicsilicovolc anoconiosis pseudopseudohypoparathyroidism floccinaucinihilipilification antidisestablishmentarianism supercalifragilisticexpialidocious incomprehensibilities honorificabilitudinitatibus tattarrattat PGCONF.EU 2017 [EX3] Palindromes

Slide 53

Slide 53 text

PGCONF.EU 2017 PGCONF.EU 2017 5. Grouping sets

Slide 54

Slide 54 text

country team_id avg ARG 150 CRO 100 ESP 100 FRA 95 POR 95 1 15 2 106.6667 3 97.5 4 120 105.8333 PGCONF.EU 2017 5. Grouping sets

Slide 55

Slide 55 text

PGCONF.EU 2017 5. Grouping sets

Slide 56

Slide 56 text

PGCONF.EU 2017 5. Grouping sets

Slide 57

Slide 57 text

groupin g country team_i d avg 1 ARG 150 1 CRO 100 1 ESP 100 1 FRA 95 1 POR 95 2 1 15 2 2 106.6667 2 3 97.5 2 4 120 3 105.8333 PGCONF.EU 2017 5. Grouping sets

Slide 58

Slide 58 text

PGCONF.EU 2017 PGCONF.EU 2017 6. Moving data atomically

Slide 59

Slide 59 text

stay id t 1 t 2 f 3 f 4 t1 id 5 t2 stay id t 1 t 2 t1 id 3 4 5 t2 PGCONF.EU 2017 6. Moving data atomically

Slide 60

Slide 60 text

PGCONF.EU 2017 6. Moving data atomically

Slide 61

Slide 61 text

PGCONF.EU 2017 PGCONF.EU 2017 7. Run WHATEVER you want

Slide 62

Slide 62 text

DO allows you to run whatever you want: - No parameters - No result - You can run any PostgreSQL compatible procedure - pl/pgsql - perl - python - The procedure is not stored anywhere PGCONF.EU 2017 7. Run WHATEVER you want

Slide 63

Slide 63 text

PGCONF.EU 2017 PGCONF.EU 2017 [EX3] Palindromes

Slide 64

Slide 64 text

words user_id events racard r ACA rd racard RACAR d ifisi IFI si ifisi If ISI https://blog.jooq.org/2017/08/22/finding-all-palindromes-contained-in-st rings-with-sql/ palindromes pneumonoultramicroscopicsilicovolc anoconiosis pseudopseudohypoparathyroidism floccinaucinihilipilification antidisestablishmentarianism supercalifragilisticexpialidocious incomprehensibilities honorificabilitudinitatibus tattarrattat PGCONF.EU 2017 [EX3] Palindromes By @lukaseder

Slide 65

Slide 65 text

PGCONF.EU 2017 [EX3] Palindromes

Slide 66

Slide 66 text

PGCONF.EU 2017 [EX3] Palindromes

Slide 67

Slide 67 text

PGCONF.EU 2017 [EX3] Palindromes

Slide 68

Slide 68 text

PGCONF.EU 2017 [EX3] Palindromes

Slide 69

Slide 69 text

PGCONF.EU 2017 PGCONF.EU 2017 Extra!

Slide 70

Slide 70 text

PGCONF.EU 2017

Slide 71

Slide 71 text

PGCONF.EU 2017 Fractals!

Slide 72

Slide 72 text

PGCONF.EU 2017 Questions? Thank you