Amazon Redshift 中的递归 CTE

我们正在尝试移植代码以在 Amazon Redshift 上运行，但 Refshift 不会运行递归 CTE 函数。有好心人知道如何移植这个吗？

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;

或者，使用不同的代码来重现下面的逻辑。

• 这是一个二进制结果：
• 如果它是 ID 的第一个已知值，则为 1
• 如果比该 ID 的前一个“1”晚 3 秒或晚，则为 1
• 如果比该 ID 的前一个“1”小于 3 秒，则为 0

注1：这不是与之前记录的秒数差异
注2：数据集中有很多ID
注3：原始数据集有ID和日期

期望的输出：https://i.stack.imgur.com/k4KUQ.png https://i.stack.imgur.com/k4KUQ.png

数据集poc：http://www.sqlfiddle.com/#!15/41d4b http://www.sqlfiddle.com/#!15/41d4b

截至撰写本文时，Redshift 确实支持递归 CTE：请参阅此处的文档 https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html#r_WITH_clause-recursive-cte-example

在 Redshift 中创建递归 CTE 时要注意：

• 开始查询：with recursive
• 列名must为所有递归 cte 声明

请考虑以下使用递归 CTE 创建日期列表的示例：

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