sql - 在包含日期范围行的表格中，从每一行中，每天生成一行包含使用小时数

Question

给定一个包含以下行的表格:

+----+-------------------------+------------------------+
| ID |        StartDate        |        EndDate         |
+----+-------------------------+------------------------+
|  1 | 2016-02-05 20:00:00.000 | 2016-02-07 5:00:00.000 |
+----+-------------------------+------------------------+

我想生成这样的表格:

+----+------------+----------+
| ID |    Date    | Duration |
+----+------------+----------+
|  1 | 2016-02-05 |        4 |
|  1 | 2016-02-06 |       24 |
|  1 | 2016-02-07 |        5 |
+----+------------+----------+

这是一道面试题。我想知道如何解决这个问题。是否可以只使用标准的 SQL 查询语法来做到这一点？或者是否需要像 pl/pgSQL 这样的过程语言来执行这样的查询？

Answer 1

基本思路是这样的:

SELECT date_trunc('day', dayhour) as dd,count(*)
FROM (VALUES (1, '2016-02-05 20:00:00.000'::timestamp, '2016-02-07 5:00:00.000'::timestamp)
     ) v(ID, StartDate, EndDate), lateral
    generate_series(StartDate, EndDate, interval '1 hour') g(dayhour) 
GROUP BY dd
ORDER BY dd;

这增加了一个小时，因此更准确:

SELECT date_trunc('day', dayhour) as dd,count(*)
FROM (VALUES (1, '2016-02-05 20:00:00.000'::timestamp, '2016-02-07 5:00:00.000'::timestamp)
     ) v(ID, StartDate, EndDate), lateral
    generate_series(StartDate, EndDate - interval '1 hour', interval '1 hour') g(dayhour) 
GROUP BY dd
ORDER BY dd;

从技术上讲，不需要lateral(在这种情况下，我会将逗号替换为cross join)。然而，这是一个横向连接的例子，所以明确是好的。

我还应该注意，以上是最简单的方法。但是，group by 确实会减慢查询速度。还有其他方法不需要每小时生成一个系列。