如何在PostgreSQL中使用递归CTE计算累积日期？

当然可行，递归CTE（Common Table Expression）天生适合处理这种依赖前一条记录结果的序列计算场景，下面是具体实现步骤：

需求梳理 #

每条记录的暂停日期需按以下规则计算：

• 若当前记录的「记录日期」早于上一条记录的「暂停日期」，则当前暂停日期 = 上一条暂停日期 + 宽限期天数
• 若当前记录的「记录日期」晚于或等于上一条记录的「暂停日期」，则当前暂停日期 = 自身记录日期 + 宽限期天数

示例数据准备 #

先创建测试表并插入示例数据：

-- 创建测试表
CREATE TABLE items (
    item VARCHAR(10),
    recorded_date TIMESTAMP,
    grace_period INT
);

-- 插入示例数据
INSERT INTO items (item, recorded_date, grace_period) VALUES
('A', '2022-12-14 00:00:00.000', 30),
('B', '2022-12-29 00:00:00.000', 30),
('C', '2023-06-02 08:40:14.933', 30),
('D', '2023-06-02 08:54:48.080', 30),
('E', '2023-06-03 06:42:42.077', 30);

递归CTE实现代码 #

WITH RECURSIVE ranked_items AS (
    -- 给记录按日期排序并生成序号，确保递归顺序正确
    SELECT
        item,
        recorded_date,
        grace_period,
        ROW_NUMBER() OVER (ORDER BY recorded_date) AS rn
    FROM items
),
recursive_suspension AS (
    -- 递归起始：第一条记录直接计算暂停日期
    SELECT
        item,
        recorded_date,
        grace_period,
        (recorded_date + INTERVAL '1 day' * grace_period)::DATE AS suspension_date,
        rn
    FROM ranked_items
    WHERE rn = 1

    UNION ALL

    -- 递归逻辑：根据前一条结果计算当前记录的暂停日期
    SELECT
        ri.item,
        ri.recorded_date,
        ri.grace_period,
        CASE
            WHEN ri.recorded_date > rs.suspension_date 
                THEN (ri.recorded_date + INTERVAL '1 day' * ri.grace_period)::DATE
            ELSE (rs.suspension_date + INTERVAL '1 day' * ri.grace_period)::DATE
        END AS suspension_date,
        ri.rn
    FROM ranked_items ri
    JOIN recursive_suspension rs ON ri.rn = rs.rn + 1
)
-- 输出最终结果
SELECT item, recorded_date, grace_period, suspension_date
FROM recursive_suspension
ORDER BY rn;

代码逻辑说明 #

1. ranked_items CTE：通过ROW_NUMBER()函数给所有记录按recorded_date排序并生成序号，保证递归时按时间顺序处理每条记录。
2. recursive_suspension CTE：
   • 基例部分：取序号为1的第一条记录，直接用自身记录日期加上宽限期得到暂停日期。
   • 递归部分：每次关联上一条记录（序号+1），通过CASE判断当前记录日期与上一条暂停日期的大小，选择对应的计算方式生成当前暂停日期。
3. 最后从递归CTE中查询结果并按序号排序，得到符合需求的输出。

执行结果 #

执行上述代码后，会得到如下结果：

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