Created
December 9, 2021 19:45
-
-
Save hncuong/4b7aa23139b55bb557232b681964633b to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ### Recursive | |
| with recursive r (i) as ( | |
| select 1 -- non-recursive term | |
| union all | |
| select i * 2 from r where i < 50) -- recursive term | |
| select * from r; | |
| with recursive r (i) as ( | |
| select 3-- non-recursive term | |
| union all | |
| select i * 2 from r where i < 50) -- recursive term | |
| select * from r; | |
| with recursive r (i) as ( | |
| select * from (values(1), (1)) a -- non-recursive term | |
| union all | |
| select i + 1 from r where i < 5) -- recursive term | |
| select * from r; | |
| ### Tree | |
| with recursive | |
| animals (id, name, parent) as (values (1, 'animal', null), | |
| (2, 'mammal', 1), (3, 'giraffe', 2), (4, 'tiger', 2), | |
| (5, 'reptile', 1), (6, 'snake', 5), (7, 'turtle', 5), | |
| (8, 'green sea turtle', 7)), | |
| r as ( | |
| select * from animals where name = 'green sea turtle' | |
| union all | |
| select animals.* | |
| from r, animals | |
| where animals.id = r.parent) | |
| select * from r; | |
| with recursive | |
| animals (id, name, parent) as (values (1, 'animal', null), | |
| (2, 'mammal', 1), (3, 'giraffe', 2), (4, 'tiger', 2), | |
| (5, 'reptile', 1), (6, 'snake', 5), (7, 'turtle', 5), | |
| (8, 'green sea turtle', 7)), | |
| r as ( | |
| select * from animals where name = 'green sea turtle' | |
| union all | |
| select animals.* | |
| from r, animals | |
| where animals.parent = r.id) | |
| select * from r; | |
| ### Compute 10! using recursion | |
| with recursive r (n) as ( | |
| select 1, 1::bigint as f -- non-recursive term | |
| union all | |
| select n+1,f*(n+1) from r where n < 16) -- recursive term | |
| select * from r; | |
| with recursive r (n) as ( | |
| select 1, 1::bigint as f -- non-recursive term | |
| union all | |
| select n+1,f*(n+1) from r where n < 16) -- recursive term | |
| select f from r where n=15; | |
| with recursive r (n, f) as ( | |
| select 10, 1::bigint -- non-recursive term | |
| union all | |
| select n-1,f * n from r where n > 0) -- recursive term | |
| select * from r; | |
| ### Fibonaci | |
| with recursive fib (cur, prev, n) as ( | |
| select 1, 1, 2::bigint -- non-recursive term | |
| union all | |
| select cur + prev, cur, n+1 from fib where n < 20) -- recursive term | |
| select n, cur from fib; | |
| ### GRAPH | |
| # DO NOT use UNION ALL to avoid circle | |
| with recursive | |
| friends (a, b) as (values ('Alice', 'Bob'), ('Alice', 'Carol'), | |
| ('Carol', 'Grace'), ('Carol', 'Chuck'), ('Chuck', 'Grace'), | |
| ('Chuck','Anne'),('Bob','Dan'),('Dan','Anne'),('Eve','Adam')), | |
| friendship (name, friend) as -- friendship is symmetric | |
| (select a, b from friends union all select b, a from friends), | |
| r as (select 'Alice' as name | |
| union | |
| select friendship.name from r, friendship | |
| where r.name = friendship.friend) | |
| select * from r; | |
| with recursive | |
| friends (a, b) as (values ('Alice', 'Bob'), ('Alice', 'Carol'), | |
| ('Carol', 'Grace'), ('Carol', 'Chuck'), ('Chuck', 'Grace'), | |
| ('Chuck','Anne'),('Bob','Dan'),('Dan','Anne'),('Eve','Adam')), | |
| friendship (name, friend) as -- friendship is symmetric | |
| (select a, b from friends union all select b, a from friends), | |
| r as (select 'Alice' as name, 1 as step | |
| union | |
| select friendship.name, step + 1 | |
| from r, friendship | |
| where r.name = friendship.friend | |
| and step < 2) | |
| select * from r; | |
| with recursive | |
| friends (a, b) as (values ('Alice', 'Bob'), ('Alice', 'Carol'), | |
| ('Carol', 'Grace'), ('Carol', 'Chuck'), ('Chuck', 'Grace'), | |
| ('Chuck','Anne'),('Bob','Dan'),('Dan','Anne'),('Eve','Adam')), | |
| friendship (name, friend) as -- friendship is symmetric | |
| (select a, b from friends union all select b, a from friends), | |
| -- Recursive start here | |
| r as (select 'Alice' as name, 1 as step | |
| union | |
| select friendship.name, step + 1 | |
| from r, friendship | |
| where r.name = friendship.friend | |
| and step < (select count(*) from friends)) | |
| select * from r; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment