PostgreSQL查询改写求助：用CTE重构日期范围查询报错

错误原因 #

你之前的语法错误是把CTE的名称当成字段直接运算了——end_date是你定义的CTE名称，不是具体的日期字段值，必须先从该CTE中取出日期值再做减法运算。

方案一：双步骤CTE #

先通过CTE获取表中最新的结束日期，再在主查询中基于该值计算开始日期：

WITH latest_end_date AS (
    -- 先拿到files表中最新的date作为结束日期
    SELECT MAX(date) AS end_date
    FROM files
)
SELECT *
FROM files
-- 基于结束日期计算7天前的开始日期，同时过滤范围
WHERE date >= (SELECT end_date - INTERVAL '7 day' FROM latest_end_date)
  AND date <= (SELECT end_date FROM latest_end_date);

方案二：在CTE中直接计算完整日期范围 #

这种写法更简洁，把开始、结束日期都在CTE里计算好，主查询直接用范围过滤：

WITH date_range AS (
    SELECT
        MAX(date) AS end_date,
        -- 直接在CTE里算出开始日期
        MAX(date) - INTERVAL '7 day' AS start_date
    FROM files
)
SELECT f.*
FROM files f
-- 关联只有一行数据的date_range，相当于引入范围变量
CROSS JOIN date_range dr
WHERE f.date BETWEEN dr.start_date AND dr.end_date;

备选：用变量实现（适合客户端工具） #

如果习惯用变量存储日期值，PostgreSQL在psql等客户端中可以这么写：

-- 先把最新日期存入变量
\set end_date `SELECT MAX(date) FROM files`
-- 用变量过滤数据
SELECT *
FROM files
WHERE date >= :end_date::date - INTERVAL '7 day'
  AND date <= :end_date::date;

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