时间戳

首页 > 解决方案 > Redshift：将持续时间行按 24 小时时段拆分

sql - Redshift：将持续时间行按 24 小时时段拆分

问题描述

我还尝试将行拆分为 , 之间的 24 小时期间FromDate范围Todate。

例如，如果一个时间行给出如下，（之间的范围FromDate是Todate4 天，所以我想要 4 行）

ID---FromDate---Todate     
1---2014-04-01 08:00:00.000---2014-04-04 12:00:00.000

我想看到的结果是这样的：

ID---FromDate---Todate---DateDiff(HH)
1---2014-04-01 08:00:00.000---2014-04-01 23:59:59.000---15
1---2014-04-02 00:00:00.000---2014-04-02 23:59:59.000---23
1---2014-04-03 00:00:00.000---2014-04-03 23:59:59.000---23
1---2014-04-04 00:00:00.000---2014-04-04 12:00:00.000---12

我找到了一个解决方案，但是当我在自己的辅助表中迭代时，它无法使用 Redshift 工作（与使用 SQL Server的解决方案不同！）

我不确定这是否可以使用 Redshift 或者我是否应该寻找另一种方法来解决它（比如自定义函数或类似的东西）。

谢谢！

标签： sqlamazon-web-servicesamazon-redshift

解决方案

Redshift 有一个令人痛苦的“特征”。生成派生表很痛苦。让我假设您有一个足够大的表来生成一个计数表或数字表。

然后，剩下的就是日期算术。. . 但有点复杂。我手头没有 Redshift —— 它有来自 SQL Server 和 Postgres 的日期语法的奇怪组合

with numbers as (
      select row_number() over () - 1 as n
      from t
     )
select t.id,
       greatest(date_trunc('day', t.fromdate) + n.n * interval '1 day', t.fromdate)  as fromdate,
       least(date_trunc('day', t.fromdate) + (n.n + 1) * interval '1 day', t.todate) as todate
       datediff(hour,
                greatest(date_trunc('day', t.fromdate) + n.n * interval '1 day', t.fromdate),
                least(date_trunc('day', t.fromdate) + (n.n + 1) * interval '1 day', todate)
               ) as hours
from t join
     numbers n
     on todate > date_trunc('day', t.fromdate) + n.n * interval '1 day';

这是一个使用 Postgres 的 db<>fiddle，它说明了这背后的主要思想。

推荐阅读