英文:
How to generate all hours between 2 timestamps in Snowflake?
问题
我有一张表格,其中包含多个时间戳,并且我想在Snowflake中生成最大时间戳和最小时间戳之间的所有时间戳 - 每隔1小时一次。我应该如何做?
英文:
I have a table with multiple timestamps, and I want to generate all the timestamps between the max and the min - in intervals of 1 hour with Snowflake.
How can I do this?
答案1
得分: 1
如果您有一个名为 for_stacko_timeline
的表,其中包含一个名为 ts
的列,您可以查找最小和最大时间戳。然后使用新函数 array_generate_range()
生成一个数字列表,用于在创建完整时间序列时进行迭代:
select timestampadd(hour, value, start_hour) generated_hour
from (
select start_hour, array_generate_range(0, 1+timestampdiff(hour, start_hour, end_hour)) int_array
from (
select date_trunc(hour, min(ts)) start_hour, max(ts) end_hour
from for_stacko_timeline
)
), table(flatten(int_array))
然后,您可以使用小时列表进行 left join
,以在需要生成值为 0 而不是不存在行的情况下使用。
英文:
If you have a table for_stacko_timeline
with a column ts
, you can find the minimum and maximum timestamp. Then use the new function array_generate_range()
to generate a list of numbers to iterate over while creating the full time series:
select timestampadd(hour, value, start_hour) generated_hour
from (
select start_hour, array_generate_range(0, 1+timestampdiff(hour, start_hour, end_hour)) int_array
from (
select date_trunc(hour, min(ts)) start_hour, max(ts) end_hour
from for_stacko_timeline
)), table(flatten(int_array))
Then you can use that list of hours to do a left join
in cases where you need to generate rows with 0 instead of non-existing rows.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论