sql - Impala:根据日期和时间将单行拆分为多行

Question

我想根据时间将单行拆分为多行。

SrNo    Employee       StartDate                  EndDate
---------------------------------------------------------------------------
1         emp1      30/03/2020 09:00:00        31/03/2020 07:15:00  
2         emp2      01/04/2020 09:00:00        02/04/2020 08:00:00

预期输出如下:

SrNo    Employee       StartDate                  EndDate
---------------------------------------------------------------------------
1         emp1      30/03/2020 09:00:00        30/03/2020 11:59:00   
1         emp1      31/03/2020 00:00:00        31/03/2020 07:15:00  
2         emp2      01/04/2020 09:00:00        01/04/2020 11:59:00  
2         emp2      02/04/2020 00:00:00        02/04/2020 08:00:00

每天从00:00 AM开始到第二天00:00 AM。如果 EndDate时间大于00:00 AM(午夜)，则将此日期分为两行。第一行结束日期是30/03/2020 11:59:00，下一行开始日期31/03/2020 00:00:00。

请帮我搞定。

Answer 1

对于递归CTE来说，这将是一个不错的选择，但是不幸的是，Hive不支持这些。这是另一个方法，它使用派生的数字表分割句点:

select
    t.SrNo,
    t.Employee,
    greatest(t.startDate, date_add(to_date(t.startDate), x.n)) startDate,
    least(t.endDate, date_add(to_date(t.startDate), x.n + 1)) endDate
from mytable t
inner join (select 0 n union all select 1 union all select 2) x
    on date_add(to_date(t.startDate), x.n) <= t.endDate

您可以扩展子查询以处理每行更多的可能期间。

还要注意，这会生成半开间隔，其中前一个间隔的末尾等于下一个间隔的开始(而在您的结果集中有一分钟的滞后)。逻辑是，间隔在其较小范围内是包含性的，而在外部范围上是排他性的(这样，您确保不留任何间隙)。