为每个id生成日期序列



我有一个四列的表。我想为每个iddate1date3创建一个间隔1天的日期列。如果没有date3,则到date2,如果没有date2,则只到date1

如何在Postgres中实现这一点?谢谢!

样本数据:

+-------+---------------------+---------------------+---------------------+
|  id   |        date1        |        date2        |        date3        |
+-------+---------------------+---------------------+---------------------+
| 76efg | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 |
| b67cs | 2021-01-09 03:45:24 | 2021-01-14 06:55:13 |                     |
| fsf56 | 2021-01-25 11:18:03 | 2021-01-25 11:18:03 |                     |
| ghl56 | 2021-01-29 14:25:57 | 2021-02-02 17:37:10 | 2021-02-18 01:13:37 |
| 90tum | 2021-02-18 06:13:30 |                     |                     |
+-------+---------------------+---------------------+---------------------+

id '76efg'的期望输出:

+-------+--------------+---------------------+---------------------+---------------------+--+
|  id   | date_created |        date1        |        date2        |        date3        |  |
+-------+--------------+---------------------+---------------------+---------------------+--+
| 76efg | 2021-01-03   | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 |  |
| 76efg | 2021-01-04   | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 |  |
| 76efg | 2021-01-05   | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 |  |
| 76efg | 2021-01-06   | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 |  |
| 76efg | 2021-01-07   | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 |  |
| 76efg | 2021-01-08   | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03 |  |
+-------+--------------+---------------------+---------------------+---------------------+--+

你试过使用generated_series()吗?下面的查询生成一系列基于date1date2date3的时间戳,以及使用COALESCE找到的序列的限制——基本上检查一列是否为null

WITH j (id,date1,date2,date3) AS (
VALUES ('76efg',
'2021-01-03 06:33:54'::TIMESTAMP,
'2021-01-07 05:19:03'::TIMESTAMP,
'2021-01-08 05:19:03'::TIMESTAMP ),
('b67cs',
'2021-01-09 03:45:24'::TIMESTAMP,
'2021-01-14 06:55:13'::TIMESTAMP,
NULL),
('90tum','2021-02-18 06:13:30'::TIMESTAMP,
NULL,NULL)
)
SELECT 
id,
generate_series(
date1,
COALESCE(date3,date2,date1),'1 day'::INTERVAL) AS date_created,
date2,
date3
FROM j;
id   |    date_created     |        date2        |        date3        
-------+---------------------+---------------------+---------------------
76efg | 2021-01-03 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
76efg | 2021-01-04 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
76efg | 2021-01-05 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
76efg | 2021-01-06 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
76efg | 2021-01-07 06:33:54 | 2021-01-07 05:19:03 | 2021-01-08 05:19:03
b67cs | 2021-01-09 03:45:24 | 2021-01-14 06:55:13 | 
b67cs | 2021-01-10 03:45:24 | 2021-01-14 06:55:13 | 
b67cs | 2021-01-11 03:45:24 | 2021-01-14 06:55:13 | 
b67cs | 2021-01-12 03:45:24 | 2021-01-14 06:55:13 | 
b67cs | 2021-01-13 03:45:24 | 2021-01-14 06:55:13 | 
b67cs | 2021-01-14 03:45:24 | 2021-01-14 06:55:13 | 
90tum | 2021-02-18 06:13:30 |                     | 

检查这个db<>fiddle

感谢@clamp关于COALESCE的提示:

COALESCE(COALESCE(date3,date2),date1)等价于COALESCE(date3,date2,date1)

相关内容

  • 没有找到相关文章

最新更新