识别丢失的时间-找到时间间隔

一尘不染

识别丢失的时间-找到时间间隔

sql

我的桌子上有几个小时，但是有空白。我需要找到哪些是缺少的时间。

select datehour
from stored_hours
order by 1;

很容易找到此时间轴中的差距：

select lag(datehour) over(order by datehour) since, datehour until
  , timestampdiff(hour, lag(datehour) over(order by datehour), datehour) - 1 missing
from stored_hours
qualify missing > 0

我如何创建这些天中缺少时间的列表？

阅读 230

2021-05-16

共1个答案

一尘不染

要创建缺少时间的列表/表格，请执行以下操作：

生成现有表的最小值/最大值之间的所有小时数的列表。
要使用Snowflake生成该列表，您将需要使用会话变量（因为生成器仅采用常量作为长度。
然后使用左联接查找丢失的小时数，查找空值。

使用变量找出开始时间和总小时数：

set (min_hour, total_hours) = (
    select min(datehour) min_hour
        , timestampdiff('hour', min(datehour), max(datehour)) total_hours
    from stored_hours
);

然后对所有小时数的生成表进行左连接，以查找丢失的小时数：

select generated_hour missing_hour
from ( -- generated hours
    select timestampadd('hour', row_number() over(order by 0), $min_hour) generated_hour
    from table(generator(rowcount => $total_hours))
) a
left outer join stored_hours b
on generated_hour=b.datehour
where datehour is null;

结果是缺少时间的列表：

（如果输入的是日期，则可以对丢失的日期应用类似的技术）

2021-05-16