如何在Presto中根据日期间隔取消测试表

Question

我有一个包含事件的表，其中start_dt是事件的开始，end_dt是事件的结束。该表由源自end_dt的dt列进行分区。这意味着在午夜之前开始和午夜之后结束的事件只出现在一个分区中。我需要做的是将每个事件拆分为与事件关联的日期数一样多的行。有什么聪明的方法可以使用Presto SQL语法实现它吗？

输入：

id  | start_dt                 | end_dt                  | dt
------+--------------------------+-------------------------+----------
1    | 2020-09-24 21:56:12.669  | 2020-09-25 00:26:16.440 | 2020-09-25
2    | 2020-09-25 17:12:02.699  | 2020-09-25 17:42:02.699 | 2020-09-25
3    | 2020-09-23 23:47:29.146  | 2020-09-25 00:17:29.146 | 2020-09-25

预期输出：

id  | start_dt                 | end_dt                  | dt
------+--------------------------+-------------------------+----------
1    | 2020-09-24 21:56:12.669  | 2020-09-24 23:59:59.999 | 2020-09-24
1    | 2020-09-25 00:00:00.001  | 2020-09-25 00:26:16.440 | 2020-09-25
2    | 2020-09-25 17:12:02.699  | 2020-09-25 17:42:02.699 | 2020-09-25
3    | 2020-09-23 23:47:29.146  | 2020-09-23 23:59:59.999 | 2020-09-23
3    | 2020-09-24 00:00:00.001  | 2020-09-24 23:59:59.999 | 2020-09-24
3    | 2020-09-25 00:00:00.001  | 2020-09-25 00:17:29.146 | 2020-09-25

Answer 1

在Presto中，可以使用sequence()生成日期数组。剩下的只是无意义的条件逻辑：

select t.id, 
case when date(t.start_dt) = s.dt then t.start_dt else cast(s.dt as timestamp)                    end as new_start_dt,
case when date(t.end_dt)   = s.dt then t.end_dt   else cast(s.dt as timestamp) + interval '1' day end as new_end_dt,
s.dt
from mytable t
cross join unnest(sequence(date(t.start_dt), date(t.end_dt))) as s(dt)

请注意，这会生成恰好在午夜开始和结束的日期间隔：半开放间隔逻辑对我来说比在这里或那里删除或添加毫秒更有意义。如果你愿意，你可以很容易地改变它。