Presto Query Parent Child Hierarchy

Question

I want to write a recursive query using Common Table Expression (CTE) in Presto to find 'status evolution' Hierarchy using syntax and logic as below:

with cte as(select 1 n
       union all
       select cte.n+1 from cte where n<50)
select * from cte

Unfortunately, I found out that Presto does not support recursive WITH queries as discussed on the link below:

Does Presto SQL support recursive query using CTE just like SQL Server? e.g. employee hierarchy level

I have a query result as example below (not actual columns), we shall name it MY_TABLE:

PLEASE NOTE THE BELOW IS MY ATTEMPT TO TRY TO REPRODUCE THE DATA STRUCTURES AND THE CODE. I do not have the access the create a Table to make sure my code below is accurate. It is bound not to work for you if you tried it, but I hope it clarifies my quustion somewhat

Name    State       PredecessorState
A       A1  
B       B1  
C       C1  
D       D3  
E       E1  
A       A2          A1  
B       B2          B1  
C       C2          C1  
D       D4          D3  
A       A_Temp_1    A1  
B       B3          B4  
C       C3          C2  
D       D5          D4  
B       B4          B2  
B       B_Temp_1    B2  
D       D_Temp_3    D5  
D       D_Temp_6    D5  
D       D_Temp_5    D_Temp_3  
D       D7          D5  
C       C7          C3

How can we query using Presto to get the output as below?

Name     State1     State2      State3     State4      State5    State6     State7
A        A1         A2
A        A1         A_Temp_1
B        B1         B2          B4         B3
B        B1         B2          B_Temp_1
C        C1         C2          C3         C7
D        D3         D4          D5         D_Temp_3
D        D3         D4          D5         D_Temp_3   D_Temp_5
D        D3         D4          D5         D_Temp_6
D        D3         D4          D5         D7
E        E1

I have tried a query similar to the below on DBeaver, and it gives an error:

SQL Error [57]: Query failed (#20200924_****): line 25:12: Schema must be specified when session schema is not set

with PredecessorsAndSuccessors
        (Name,
        State,
        PredecessorState,
        State_Level)
as
(
select Name,
       State,
       PredecessorState,
       0
       
       from MY_TABLE
       where PredecessorState is null   -- Determine the first State (root Parent of Tree), note there are multiple roots

union all

select MY_TABLE1.Name,
       MY_TABLE1.State,
       MY_TABLE1.PredecessorState,
       PredecessorsAndSuccessors.State,
       PredecessorsAndSuccessors.State_Level + 1
       
       from MY_TABLE as MY_TABLE1
       
       join PredecessorsAndSuccessors on MY_TABLE1.PredecessorState = PredecessorsAndSuccessors.State
)

select *
from PredecessorsAndSuccessors
order by State_Level

I can only get one join to work, without the ability to make the query recursive for a number of times corresponding to the deepest child Level for all given parents in MY_TABLE.

You describe a hierarchical structure, but you show a query that generates a series of number. Which one of the two do you want? — GMB, Sep 22 '20 at 19:28
Hi @GMB, sorry about that, I need a hierarchical structure. I have tried to make the question more specific. Please pardon the code, I am unable to re-create a generic table to use to clarify the question due to lack of access to database. — TendaiM, Sep 24 '20 at 04:30

score 0 · Answer 1 · answered Sep 23 '20 at 08:34

0

You can achieve this by using sequence and unnest

with cte as 
(
    select sequence(1,50) n_sequence
)
select t.n
from cte 
cross join unnest(n_sequence) as t(n);

answered Sep 23 '20 at 08:34

Philipp Johannis

2,718
1
15
18

Presto Query Parent Child Hierarchy

1 Answers1