CODEWITHSUNDEEP
sqldateportamazon-redshiftcalculated-columns
mfulvio
mfulvio

Reputation: 33

Recursive CTE in Amazon Redshift

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.

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.sstatic.net/k4KUQ.png

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

Upvotes: 3

Views: 13134

Answers (2)

TastySlowCooker
TastySlowCooker

Reputation: 145

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

Upvotes: 3

Devanarayanan G
Devanarayanan G

Reputation: 1

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

Upvotes: 0

Related Questions