PostgreSQL：如何匹配周期并生成账单精确还款日？

这个需求完全可以通过PostgreSQL的纯SQL语句实现，不需要额外编写外部代码处理。

核心思路 #

关联Bills和Periods两张表，为每个周期生成对应账单的实际还款日exact_date，并筛选出该日期落在周期起止范围内的记录。关键在于根据周期的时间范围，计算出账单还款日对应的具体日期，并判断其是否处于当前周期内。

前提准备 #

确保Periods表的start和end字段是date类型（如果当前是字符串格式，先转换），可执行以下语句处理：

ALTER TABLE Periods 
ALTER COLUMN start TYPE date USING TO_DATE(start, 'DD Mon YYYY'),
ALTER COLUMN end TYPE date USING TO_DATE("end", 'DD Mon YYYY');

实现SQL语句 #

SELECT
    p.start,
    p.end,
    b."账单名称",
    b.day_of_month,
    exact_date
FROM
    Periods p
CROSS JOIN Bills b
JOIN LATERAL (
    -- 生成周期内可能的还款日：先尝试周期起始月的日期，再尝试下一个月的日期
    SELECT make_date(EXTRACT(YEAR FROM p.start)::int, EXTRACT(MONTH FROM p.start)::int, b.day_of_month) AS dt
    UNION ALL
    SELECT make_date(EXTRACT(YEAR FROM (p.start + INTERVAL '1 month'))::int, EXTRACT(MONTH FROM (p.start + INTERVAL '1 month'))::int, b.day_of_month) AS dt
) AS possible_dates ON possible_dates.dt BETWEEN p.start AND p.end
ORDER BY
    p.start, b.day_of_month;

代码解释 #

1. CROSS JOIN：将每个周期与所有账单做笛卡尔关联，得到所有可能的周期-账单组合。
2. LATERAL子查询：生成两个候选还款日：
   • 第一个是周期起始月份对应的还款日（例如周期起始为2022-09-07，生成2022-09-[day_of_month]）
   • 第二个是周期起始月份的下一个月对应的还款日（例如2022-10-[day_of_month]）
3. 筛选条件：仅保留落在当前周期start和end之间的日期，即为最终的exact_date。
4. 排序：按周期起始日期和还款日排序，与示例结果顺序一致。

执行以上SQL后，即可得到需求中的目标结果集。

内容的提问来源于stack exchange，提问作者ginsberg