确定缺少的时间 - 找到时间间隔

问题描述：

我有一张有小时数的桌子，但有空档.我需要找出缺少的小时数.

I have a table with hours, but there are gaps. I need to find which are the missing hours.

select datehour
from stored_hours
order by 1;

这个时间线中的差距很容易找到:

The gaps in this timeline are easy to find:

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

如何创建这些天中缺少的小时数的列表?

How can I create a list of the missing hours during these days?

(使用雪花和 SQL)

(with Snowflake and SQL)

答

要创建缺少时间的列表/表格:

To create a list/table of the missing hours:

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

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

Use variables to find out the start and total number of hours:

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

然后对生成的所有小时表进行左连接，以找到缺失的:

Then do the left join with a generated table of all hours, to find the missing ones:

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;

结果是缺少时间的列表:

The result is a list of the missing hours:

(如果输入是日期，您可以应用类似的技术来处理缺失的天数)

(you could apply a similar technique for missing days, if the input are dates)

确定缺少的时间 - 找到时间间隔

相关推荐