PostgreSQL窗口函数：分组内计算Open与Approved的日期差

1. 用窗口函数定义分组（基于'open'-'approved'周期） #

核心思路是识别每个'open'作为分组起点，后续直到下一个'open'前的所有记录都归为同一组。用SUM() OVER()窗口函数生成分组ID，比RANK()更直接适配你的需求：

假设表名为status_records，包含字段record_id（按时间排序的主键）、date（状态变更日期）、record_status（状态值），代码如下：

WITH grouped_data AS (
    SELECT 
        *,
        -- 每遇到一条'open'状态记录，分组ID自增1
        SUM(CASE WHEN record_status = 'open' THEN 1 ELSE 0 END) OVER (ORDER BY record_id) AS group_id
    FROM status_records
)
SELECT * FROM grouped_data;

这个逻辑会自动把从第一个'open'到下一个'open'前的所有记录（包括对应的'approved'）划入同一个分组，完全匹配你"以'open'开头、'approved'结尾"的分组规则。

2. 编写包含未审批特殊情况的日期差计算逻辑 #

分两种场景处理日期差：

• 分组内存在'approved'状态：计算'open'日期到'approved'日期的差值
• 分组无'approved'状态（最后一组未完成审批）：计算'open'日期到指定日期（15.10.2022）的差值

结合聚合函数和CASE语句实现：

-- 先将指定日期转为数据库兼容的DATE类型，示例为MySQL写法，其他数据库替换对应转换函数
SET @target_date = STR_TO_DATE('15.10.2022', '%d.%m.%Y');

WITH grouped_data AS (
    SELECT 
        *,
        SUM(CASE WHEN record_status = 'open' THEN 1 ELSE 0 END) OVER (ORDER BY record_id) AS group_id
    FROM status_records
),
group_summary AS (
    SELECT
        group_id,
        -- 提取分组内的open日期
        MAX(CASE WHEN record_status = 'open' THEN date END) AS open_date,
        -- 提取分组内的approved日期（无则返回NULL）
        MAX(CASE WHEN record_status = 'approved' THEN date END) AS approved_date,
        -- 标记分组是否完成审批
        MAX(CASE WHEN record_status = 'approved' THEN 1 ELSE 0 END) AS is_approved
    FROM grouped_data
    GROUP BY group_id
)
SELECT
    group_id,
    open_date,
    approved_date,
    CASE
        WHEN is_approved = 1 THEN DATEDIFF(approved_date, open_date)
        ELSE DATEDIFF(@target_date, open_date)
    END AS date_diff_days
FROM group_summary;

注意：

• DATEDIFF是MySQL的日期差函数，其他数据库替换为对应函数：PostgreSQL用DATE_PART('day', approved_date - open_date)，SQL Server用DATEDIFF(day, open_date, approved_date)
• 如果数据按多维度（比如工单ID）区分，需在窗口函数和分组中添加PARTITION BY ticket_id来隔离不同维度的分组

3. 仅对分组应用日期差计算 #

上述代码通过两步实现分组计算：

1. 先通过grouped_data给每条记录打上分组标签
2. 再通过group_summary按分组聚合，仅对每个分组执行一次日期差计算，避免单行重复计算

如果需要将计算结果关联回原表，可在最后添加JOIN：

-- 承接上面的group_summary
SELECT
    gd.*,
    gs.date_diff_days
FROM grouped_data gd
JOIN group_summary gs ON gd.group_id = gs.group_id;

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