如何在PostgreSQL 15中用纯SQL模拟多历史日期的任务时长查询？

问题场景 #

使用PostgreSQL 15，存在存储任务时间的history表，原本通过CURRENT_TIMESTAMP计算任务时长（age）来判断是否逾期发送通知。现在需要以当年年初到指定日期的每一天作为基准日期，批量执行相同的任务时长计算，并将所有结果合并为单个数据集，避免重复执行查询或用程序循环实现。此前尝试过窗口函数、DATEDIFF等方法未解决问题，后参考思路通过generate_series和CROSS JOIN LATERAL构建查询接近需求，并整理出完整解决方案。

完整解决方案步骤 #

1. 生成日历序列并关联原表

利用generate_series生成年初到指定日期的所有日期作为基准日期，再通过CROSS JOIN LATERAL关联history表，为每个基准日期匹配所有任务记录：

WITH date_series AS (
    SELECT generate_series(
        date_trunc('year', CURRENT_DATE)::DATE,  -- 当年年初
        '2024-12-31'::DATE,  -- 指定结束日期，可替换为变量或其他日期
        '1 day'::INTERVAL
    ) AS report_date
)
SELECT 
    h.job,
    h.timestamp AS task_time,
    ds.report_date
FROM date_series ds
CROSS JOIN LATERAL (
    SELECT job, timestamp FROM history
) h;

2. 基于基准日期计算任务时长

在上述基础上，使用AGE()函数以每个report_date为基准计算任务时长：

WITH date_series AS (
    SELECT generate_series(
        date_trunc('year', CURRENT_DATE)::DATE,
        '2024-12-31'::DATE,
        '1 day'::INTERVAL
    ) AS report_date
)
SELECT 
    h.job,
    h.timestamp AS task_time,
    ds.report_date,
    AGE(ds.report_date, h.timestamp) AS task_age  -- 以基准日期计算时长
FROM date_series ds
CROSS JOIN LATERAL (
    SELECT job, timestamp FROM history
) h;

3. 窗口函数获取每个任务在基准日的最新时间

如果需要每个任务在对应基准日期的最新任务时间（用于更精准的逾期判断），可以用窗口函数MAX() OVER (PARTITION BY job, report_date)来聚合：

WITH date_series AS (
    SELECT generate_series(
        date_trunc('year', CURRENT_DATE)::DATE,
        '2024-12-31'::DATE,
        '1 day'::INTERVAL
    ) AS report_date
),
task_daily AS (
    SELECT 
        h.job,
        h.timestamp AS task_time,
        ds.report_date,
        AGE(ds.report_date, h.timestamp) AS task_age
    FROM date_series ds
    CROSS JOIN LATERAL (
        SELECT job, timestamp FROM history
    ) h
)
SELECT 
    job,
    report_date,
    MAX(task_time) OVER (PARTITION BY job, report_date) AS latest_task_time,
    AGE(report_date, MAX(task_time) OVER (PARTITION BY job, report_date)) AS latest_task_age
FROM task_daily
GROUP BY job, report_date, task_time, task_age
ORDER BY report_date, job;

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