Amazon Redshift中的递归CTE [英] Recursive CTE in Amazon Redshift

问题描述

我们正在尝试移植代码以在Amazon Redshift上运行，但是Refshift不会运行递归CTE功能。知道如何移植此内容的好灵魂吗？

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.

• 是二进制结果：


• 如果它是ID的第一个已知值，则为1


• 如果3秒或更晚于该ID的前一个 1，则为1


• 如果它小于该ID的前一个 1的时间少于3秒，则为0的ID

• 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

注1：这与上一个记录的秒数没有区别

注2：数据集中有很多ID

注3：原始数据集具有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

所需的输出：
https://i.stack.imgur.com/k4KUQ.png

数据集poc：
http：//www.sqlfiddle .com /＃！15 / 41d4b

推荐答案

以下代码可以为您提供帮助。

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

这篇关于Amazon Redshift中的递归CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！