我有一个包含两个日期列next_start_date
和cancel_date
的表。
group_id | personal_id | cancel_date | |
---|---|---|---|
1 | 001 | 2017-11-06 | >2014-03-26 |
1 | 001 | 2017-11-06 | <17-01-09>|
1 | 001 | 2017-11-06 | 018年4月16日|
2 | 001 | 2018-06-04 | |
2 | 001 | 2018-06-04 | |
2 | 001 | 2018-06-04 | |
2 | 001 | 2018-08-28 | 2014-03-26 |
2 | 001 | 2018-08-28 | 2017-01-09 |
2 | 001 | 2018-08-28 | 2018-04-16 |
使用FILTER
排除大于next_start_date_after_cancel
的cancel_date
,在子查询或CTE中获取其min()
值。最后减去两个日期,得到不同的天数:
WITH j AS (
SELECT
group_id,individual_id,cancel_date,
min(next_start_date_after_cancel) FILTER (WHERE next_start_date_after_cancel > cancel_date) AS next_start_date_after_cancel
FROM t GROUP BY 1,2,3)
SELECT
group_id,individual_id,cancel_date,next_start_date_after_cancel,
next_start_date_after_cancel-cancel_date
FROM j;
演示:db<>fiddle