Recursive CTE in Amazon Redshift

Question

We are trying to port a code to run on Amazon Redshift, but Refshift won't run the recursive CTE function. Any good soul that knows how to port this?

with tt as (
      select t.*, row_number() over (partition by id order by time) as seqnum
      from t
     ),     
     recursive cte as (
      select t.*, time as grp_start
      from tt
      where seqnum = 1
      union all
      select tt.*,
             (case when tt.time < cte.grp_start + interval '3 second'
                   then tt.time
                   else tt.grp_start
               end)
      from cte join
           tt
           on tt.seqnum = cte.seqnum + 1
     )
select cte.*,
       (case when grp_start = lag(grp_start) over (partition by id order by time)
             then 0 else 1
        end) as isValid
from cte;

Or, a different code to reproduce the logic below.

It is a binary result that:
it is 1 if it is the first known value of an ID
it is 1 if it is 3 seconds or later than the previous "1" of that ID
It is 0 if it is less than 3 seconds than the previous "1" of that ID

Note 1: this is not the difference in seconds from the previous record
Note 2: there are many IDs in the data set
Note 3: original dataset has ID and Date

Desired output: https://i.stack.imgur.com/k4KUQ.png

Dataset poc: http://www.sqlfiddle.com/#!15/41d4b

I don't think Redshift supports recursive CTEs, as it is based on a very old fork of Postgres. So, you should instead edit your question and focus strongly on explaining the logic behind your query, what it does, and then maybe give some sample data. — Tim Biegeleisen, Aug 15 '18 at 17:27
I think RedShift is based on PostgreSQL 8.x. At that point CTEs were not supported. — The Impaler, Aug 15 '18 at 20:10
I think Redshift is not a good fit for your needs (if you really need CTEs). However Amazon Web Services offers PostgreSQL 9.x, 10.x (and even beta 11.x). Maybe you should consider one of these versions. — The Impaler, Aug 15 '18 at 20:35
Thanks. Any ideas on how to handle this with Redshift? I dont think using another platform at this time is viable — mfulvio, Aug 17 '18 at 14:57
Did you get anywhere? would be great to hear what you ended up porting as — da Bich, Apr 01 '20 at 19:12

TastySlowCooker · Answer 1 · 2021-11-18T22:15:51.523

As of this writing, Redshift does support recursive CTE's: see documentation here

To note when creating a recursive CTE in Redshift:

start the query: with recursive
column names must be declared for all recursive cte's

Consider the following example for creating a list of dates using recursive CTE's:

with recursive
      start_dt as (select current_date s_dt)
      , end_dt as (select dateadd(day, 1000, current_date) e_dt)
      -- the recusive cte, note declaration of the column `dt`
      , dates (dt) as (
            -- start at the start date
            select s_dt dt from start_dt
            union all
            -- recursive lines
            select dateadd(day, 1, dt)::date dt  -- converted to date to avoid type mismatch
            from dates
            where dt <= (select e_dt from end_dt)  -- stop at the end date
      )
select *
from dates

score 0 · Answer 2 · edited Sep 17 '20 at 14:03

The below code could help you.

SELECT id, time, CASE WHEN sec_diff is null or prev_sec_diff - sec_diff > 3
    then 1
    else 0
  end FROM (
    select id, time, sec_diff, lag(sec_diff) over(
      partition by id order by time asc
    )
    as prev_sec_diff
    from (
      select id, time, date_part('s', time - lag(time) over(
        partition by id order by time asc
      )
    )
    as sec_diff from hon
  ) x
) y

Recursive CTE in Amazon Redshift

2 Answers2

Linked