sql - 如何在水平行中显示特定日期范围内的所有日期？

Question

我在 PostgreSQL 中有一个名为 t1 的数据库表，例如:

|  Name  |  | StartDate |  | EndDate   |
----------------------------------------
| Oct-18 |  | 2018-10-01|  | 2018-10-05|

我想要日期范围内的结果，例如:

| Oct-18 | |2018-10-01| |2018-10-02| |2018-10-03| |2018-10-04| |2018-10-05|

在 generate_series() 的帮助下，我可以“垂直”进行，但如何在一行中获得结果？

Answer 1

使用generate_series()。但是 SQL 不允许动态数量的结果列。因此，您必须将结果包装在字符串、数组或文档类型中才能使其生效。LATERAL 子查询中的 ARRAY 构造函数示例 - 在 Postgres 10 或更高版本中:

SELECT t1.name, d.date_arr::date[]
FROM   t1
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT generate_series(t1.startdate::timestamp
                                     , t1.enddate::timestamp
                                     , interval '1 day'))
   ) d(date_arr) ON true;

为什么(最好)使用 Postgres 10 或更高版本？

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

为什么要强制转换为 timestamp？

• Generating time series between two dates in PostgreSQL

为什么 LEFT JOIN .. ON true？

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

虽然 LEFT JOIN 在这种特殊情况下不是必需的(可以是 CROSS JOIN)，因为 ARRAY 构造函数总是返回一行。

Postgres 9.1

LATERAL 需要 Postgres 9.3 或更高版本。您可以替换为相关的子查询:

SELECT name
     , ARRAY(SELECT generate_series(startdate::timestamp
                                  , enddate::timestamp
                                  , interval '1 day')::date)
FROM   t1;

甚至适用于 pg 8.4:

db<> fiddle here

但请考虑升级到当前版本。

交叉表()?

crosstab() 也无法克服 SQL 的静态特性。准备好的行类型的解决方法有限......

• Dynamic alternative to pivot with CASE and GROUP BY
• Dynamically generate columns for crosstab in PostgreSQL
• PostgreSQL Crosstab Query