create table loan_data(loan_no varchar(10),cust_no varchar(10),start_date date,end_date date
)INSERT INTO loan_data VALUES ('LN001', 'CUST001', '2025-01-04', '2025-01-08');
INSERT INTO loan_data VALUES ('LN002', 'CUST001', '2025-01-06', '2025-01-09');
INSERT INTO loan_data VALUES ('LN003', 'CUST001', '2025-01-09', '2025-01-10');
INSERT INTO loan_data VALUES ('LN004', 'CUST001', '2025-01-11', '2025-01-12');
INSERT INTO loan_data VALUES ('LN005', 'CUST001', '2025-01-14', '2025-01-16');
INSERT INTO loan_data VALUES ('LN005', 'CUST001', '2025-01-19', '2025-01-20');select * from loan_data;with date_with_max_end AS (SELECT cust_no,start_date,end_date,MAX(end_date) OVER (PARTITION BY cust_no ORDER BY start_date) AS max_end_dateFROM loan_data
),
date_with_gaps AS (SELECT cust_no,start_date,end_date,LAG(max_end_date) OVER (PARTITION BY cust_no ORDER BY start_date) AS prev_date,CASE WHEN start_date <= LAG(max_end_date) OVER (PARTITION BY cust_no ORDER BY start_date)THEN 0 --连续天数ELSE 1 --不连续开始新的区间END AS is_new_periodFROM date_with_max_end
),
-- 计算连续区间分组
date_groups AS (SELECT cust_no,start_date,end_date,SUM(is_new_period) OVER (PARTITION BY cust_no ORDER BY start_date) AS period_groupFROM date_with_gaps
),
-- 计算每个连续区间的统计信息
period_stats AS (SELECT cust_no,period_group,MIN(start_date) AS period_start,MAX(end_date) AS period_endFROM date_groupsGROUP BY cust_no, period_grouporder by period_group
)
-- 最终结果
SELECT cust_no,SUM(period_end-period_start+1) AS total_unique_days,MIN(period_start) AS overall_start_date,MAX(period_end) AS overall_end_date,-- 列出所有连续区间jsonb_agg(jsonb_build_object('period', period_start || '~' || period_end,'days', period_end-period_start+1)ORDER BY period_start) AS continuous_periods_detail
FROM period_stats
GROUP BY cust_no;--结果接
CUST001 14 2025-01-04 2025-01-20
---json_agg
[{"days": 7, "period": "2025-01-04~2025-01-10"},
{"days": 2, "period": "2025-01-11~2025-01-12"},
{"days": 3, "period": "2025-01-14~2025-01-16"},
{"days": 2, "period": "2025-01-19~2025-01-20"}]
