如何基于日期范围关联重运行查询？用日期表替换CURRENT_DATE

核心思路 #

将存储日期序列的表与原查询通过横向连接（LATERAL/CROSS APPLY）关联，让原查询针对日期表中的每个日期执行一次，自动替换所有CURRENT_DATE的引用。

PostgreSQL 实现代码 #

假设你的日期表名为date_sequence，存储日期的字段为run_date：

SELECT
    ds.run_date,
    hey.*,
    CASE
        WHEN (hey.birthdate <= (ds.run_date - INTERVAL '366 days')) THEN 365
        ELSE EXTRACT(DAYS FROM (hey.max_date - hey.birthdate)) -- 修正原查询中不存在的`hey.time1`字段，若实际字段不同请自行替换
    END AS days
FROM
    date_sequence ds
LATERAL (
    SELECT
        a.salesforce_id,
        MAX(a.birthdate) AS birthdate,
        MAX(c2.completed) AS max_date,
        MIN(c2.completed) AS min_date,
        COUNT(c2.id) AS file_count
    FROM
        accounts a
    LEFT JOIN customers c ON a.customer_id = c.id
    LEFT JOIN files c2 ON c.id = c2.createdbycustomerid
    WHERE
        c2.completed IS NOT NULL
        AND c2.completed > (ds.run_date - INTERVAL '366 days')
    GROUP BY
        a.salesforce_id
) AS hey

SQL Server 实现代码 #

用CROSS APPLY替代PostgreSQL的LATERAL，并调整日期函数语法：

SELECT
    ds.run_date,
    hey.*,
    CASE
        WHEN (hey.birthdate <= DATEADD(day, -366, ds.run_date)) THEN 365
        ELSE DATEDIFF(day, hey.birthdate, hey.max_date) -- 对应EXTRACT(DAYS)的逻辑
    END AS days
FROM
    date_sequence ds
CROSS APPLY (
    SELECT
        a.salesforce_id,
        MAX(a.birthdate) AS birthdate,
        MAX(c2.completed) AS max_date,
        MIN(c2.completed) AS min_date,
        COUNT(c2.id) AS file_count
    FROM
        accounts a
    LEFT JOIN customers c ON a.customer_id = c.id
    LEFT JOIN files c2 ON c.id = c2.createdbycustomerid
    WHERE
        c2.completed IS NOT NULL
        AND c2.completed > DATEADD(day, -366, ds.run_date)
    GROUP BY
        a.salesforce_id
) AS hey

关键注意事项 #

1. 替换所有CURRENT_DATE：原查询中两处CURRENT_DATE都要替换为日期表的run_date，并根据数据库语法调整日期运算（比如MySQL用DATE_SUB）。
2. 横向连接的必要性：子查询需要引用日期表的run_date作为过滤条件，必须用LATERAL/CROSS APPLY，否则无法访问外部表字段。
3. 修正原查询错误：原查询中的hey.time1字段不存在，这里默认替换为max_date与birthdate的差值，若实际需求不同，请修改为正确的日期字段组合。
4. 日期表准备：确保date_sequence表包含所有需要运行的日期，无重复值。如果没有现成表，可以用递归CTE生成（比如按月/按日生成序列）。

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