如何在postgres上使用开始和结束日期以及季度生成系列

Question

我有一个如下所示的表，我想在其中使用开始和结束日期，将每行的值平均分配到每个季度的3个月，以及开始日期和结束日期之间的所有季度(最后两列(。

我熟悉Postgres中的生成序列和间隔，但我很难得到我想要的。

我的表有一个将行分组在一起的ID列，一个指示行引用ID的哪个季度的quarter列，一列是整个季度(以及日期范围中的每个季度(的值的value列，以及指示日期范围的start_date和end_date列。这是一个示例：

ID  quarter     value   start_date  end_date 
1   2            152    2019-11-07  2050-12-30
1   1            785    2019-11-07  2050-12-30
2   2            152    2019-03-05  2050-12-30
2   1            785    2019-03-05  2050-12-30
3   4            41     2018-06-12  2050-12-30
3   3            50     2018-06-12  2050-12-30
3   2            88     2018-06-12  2050-12-30
3   1            29     2018-06-12  2050-12-30
4   2           1607    2018-12-17  2050-12-30
4   1           4803    2018-12-17  2050-12-30

这是我想要的输出(对于ID1(：

ID  quarter     value   start_date  end_date 
1   2            152/3  2020-04-01  2020-07-01
1   1            785/3  2020-01-01  2020-04-01
1   2            152/3  2021-04-01  2021-07-01
1   1            785/3  2021-01-01  2021-04-01

start_date在输出中将是下一季度的第一张表。我需要从第一个表的start_date到end_date生成序列。

Answer 1

您可以使用GENERATE_SERIES函数，为每个唯一的(按ID(行传递开始和结束日期，并将间隔设置为3个月。然后将结果与ID和quarter上的原始表连接起来。

这里有一个例子(注意original_data就是我所说的第一个表(：

WITH
quarters_table AS (
SELECT
t.ID,
(EXTRACT('month' FROM t.quarter_date) - 1)::INT / 3 + 1 AS quarter,
t.quarter_date::DATE AS start_date,
COALESCE(
LEAD(t.quarter_date) OVER (),
DATE_TRUNC('quarter', t.original_end_date) + INTERVAL '3 months'
)::DATE AS end_date
FROM (
SELECT
original_record.ID,
original_record.end_date AS original_end_date,
GENERATE_SERIES(
DATE_TRUNC('quarter', original_record.start_date),
DATE_TRUNC('quarter', original_record.end_date),
INTERVAL '3 months'
) AS quarter_date
FROM (
SELECT DISTINCT ON (original_data.ID)
original_data.ID,
original_data.start_date,
original_data.end_date
FROM
original_data
ORDER BY
original_data.ID
) AS original_record
) AS t
)
SELECT
quarters_table.ID,
quarters_table.quarter,
original_data.value::DOUBLE PRECISION / 3 AS value,
quarters_table.start_date,
quarters_table.end_date
FROM
quarters_table
INNER JOIN
original_data
ON
quarters_table.ID = original_data.ID
AND quarters_table.quarter = original_data.quarter;

样本输出：

id | quarter |      value       | start_date |  end_date  
----+---------+------------------+------------+------------
1 |       1 | 261.666666666667 | 2020-01-01 | 2020-04-01
1 |       2 | 50.6666666666667 | 2020-04-01 | 2020-07-01
1 |       1 | 261.666666666667 | 2021-01-01 | 2021-04-01
1 |       2 | 50.6666666666667 | 2021-04-01 | 2021-07-01

为了完整起见，下面是我在测试中使用的original_data表：

WITH
original_data AS (
SELECT
1 AS ID,
2 AS quarter,
152 AS value,
'2019-11-07'::DATE AS start_date,
'2050-12-30'::DATE AS end_date

UNION ALL
SELECT
1 AS ID,
1 AS quarter,
785 AS value,
'2019-11-07'::DATE AS start_date,
'2050-12-30'::DATE AS end_date

UNION ALL
SELECT
2 AS ID,
2 AS quarter,
152 AS value,
'2019-03-05'::DATE AS start_date,
'2050-12-30'::DATE AS end_date

-- ...
)

Answer 2

这是实现它的一种方法。根据您概述的输出显示一个示例。然后，您可以向CASE/WHEN添加更多条件以获得更多季度。

SELECT
ID,
Quarter,
Value/3 AS "Value",
CASE
WHEN Quarter = 1 THEN '2020-01-01'
WHEN Quarter = 2 THEN '2020-04-01'
END AS "Start_Date",
CASE
WHEN Quarter = 1 THEN '2020-04-01'
WHEN Quarter = 2 THEN '2020-07-01'
END AS "End_Date"
FROM
Table