PostgreSQL 13：统计近30天枚举值变更数量（时态表）

问题背景 #

我拥有一张非时态表headcount和一张存储行更新时间及历史值的时态历史表headcount_history，需要统计非时态表中近30天内发生变更的hiring_status枚举值的数量。

示例表结构与数据 #

create temporary table headcount (id int, hiring_status text, pay float);

create temporary table headcount_history (id int, hiring_status text, pay float, updated_at date);

insert into headcount values 
    (1, 'interviewing', 1000.00),
    (2, 'hired', 1000.00),
    (3, 'scouting', 1000.00),
    (4, 'interviewing', 100.12),
    (5, 'scouting', 123.42);

insert into headcount_history values 
    (1, 'scouting', 1000.00, '2022-07-25'),
    (1, 'scouting', 1005.00, '2022-07-26'),
    (1, 'scouting', 1005.00, '2022-07-27'),
    (1, 'interviewing', 1005.00, '2022-07-28'),

    (2, 'scouting', 1000.00, '2022-03-20'),
    (2, 'interviewing', 1000.00,'2022-04-20'),
    (2, 'hired', 3230.00, '2022-04-23'),
    (2, 'hired', 1000.00, '2022-04-25'),

    (3, 'scouting', 1000.00, '2022-03-20'),
    (3, 'interviewing', 1000.00,'2022-04-20'),
    (3, 'scouting', 1000.00, '2022-07-25'),

    (4, 'scouting', 1000.00, '2022-06-25'),
    (4, 'interviewing', 100.12, '2022-07-25'),

    (5, 'scouting', 1000.0, '2022-06-29'),
    (5, 'scouting', 123.42, '2022-07-10');

预期输出 #

解决方案 #

思路说明 #

1. 从历史表筛选近30天的记录，按id和updated_at排序保证时序正确；
2. 用LAG()窗口函数获取每个id的上一条记录状态，对比当前状态筛选出变更行；
3. 通过条件聚合统计各状态的变更次数，确保所有枚举值都能显示（即使次数为0）。

实现SQL #

WITH recent_changes AS (
    SELECT 
        id,
        hiring_status,
        LAG(hiring_status) OVER (PARTITION BY id ORDER BY updated_at) AS prev_status
    FROM headcount_history
    WHERE updated_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    COUNT(CASE WHEN hiring_status = 'scouting' THEN 1 END) AS scouting,
    COUNT(CASE WHEN hiring_status = 'interviewing' THEN 1 END) AS interviewing,
    COUNT(CASE WHEN hiring_status = 'hired' THEN 1 END) AS hired
FROM recent_changes
WHERE hiring_status != prev_status
  AND prev_status IS NOT NULL; -- 排除每个id的第一条无前置状态的记录

代码解释 #

• recent_changes子查询：筛选近30天的历史数据，通过LAG()获取每个id的上一次状态；
• 主查询：用CASE条件聚合分别统计各状态的变更次数，过滤掉状态未变更的行和初始无前置状态的记录；
• 最终输出直接匹配预期列格式，未发生变更的状态自动显示为0。

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