不连续数据区间天数累计sql

计算不连续数据区间天数并且剔除重复天数

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_gaps AS (
    SELECT 
        cust_no,
        start_date,
        end_date,
        LAG(end_date) OVER (PARTITION BY cust_no ORDER BY start_date) AS prev_date,
        CASE 
            WHEN start_date <= LAG(end_date) OVER (PARTITION BY cust_no ORDER BY start_date)
            THEN 0 --连续天数
            ELSE 1 --不连续开始新的区间
        END AS is_new_period
    FROM loan_data
),
-- 计算连续区间分组
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_group
    FROM date_with_gaps
),
-- 计算每个连续区间的统计信息
period_stats AS (
	SELECT 
        cust_no,
        period_group,
        MIN(start_date) AS period_start,
        MAX(end_date) AS period_end
    FROM date_groups
    GROUP BY cust_no, period_group
    order 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"}]