postgresql - Generating dates for each id
问题描述
I have a table with 3 columns. I want to create a date column for each id from its start_date
till end_date
, if no end_date
then till today.
How can I active this in Postgres? Thanks!
Sample table:
+------+------------+------------+--+
| id | start_date | end_date | |
+------+------------+------------+--+
| 47ef | 2020-09-25 | 2020-09-30 | |
| b67c | 2020-09-21 | 2020-10-02 | |
| 9f9e | 2020-08-28 | 2020-10-02 | |
| 854a | 2020-07-29 | 2020-10-05 | |
| a316 | 2020-05-01 | NULL | |
+------+------------+------------+--+
Desired output for id '47ef':
+------+------------+------------+------------+
| id | start_date | end_date | date |
+------+------------+------------+------------+
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-25 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-26 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-27 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-28 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-29 |
| 47ef | 2020-09-25 | 2020-09-30 | 2020-09-30 |
+------+------------+------------+------------+
解决方案
You can indeed use generate_series()
:
select t.id, t.start_date, t.end_date, g.dt::date
from the_table t
cross join lateral generate_series(t.start_date, coalesce(t.end_date, current_date), interval '1 day') as g(dt)
order by t.id, g.dt
推荐阅读
- average - 对 tableau 2019 中的所有度量值进行平均
- r - 如何在向量中找到值开始平稳的点
- flutter - 您如何在视频播放器类之外播放视频?
- javascript - 分红
- python - 不存在参数查询过滤器会引发异常吗?
- image - vuejs 无法显示资产文件夹中的图像
- laravel-5 - 如何将角色和权限从 Laravel 传递给 vue.js
- azure - WebApp for Containers 上的“Webjobs”选项卡已禁用
- c - 有没有办法通过输入学生的 ID 从文本文件中删除记录?
- selenium-webdriver - 如何在 PageObjectModel 模式中创建用于检查网页上的 webElement 存在的实用方法