PostgreSQL中含统计函数的子查询返回结果异常问题排查

让我们一步步拆解你遇到的问题：

1. 字段别名笔误（核心问题） #

你在第二个CTE ias 里把计数字段命名为 ase，但主查询的 SELECT 里写了 ase + asi —— 这里的 asi 根本不存在！这明显是手滑打错了，正确的写法应该是 eas.ase + ias.ase。这个笔误会导致数据库无法识别字段，要么直接报错，要么（如果有巧合的字段名）计算出完全错误的数值。

2. 多余且错误的GROUP BY #

你的两个CTE已经按日期完成了分组，每个日期在 eas 和 ias 里都只有一行数据。FULL JOIN之后，每个匹配的日期也只会有一行结果，完全不需要再做GROUP BY。你当前的GROUP BY包含 ase 和不存在的 asi，不仅毫无意义，还可能因为隐式字段处理导致数据丢失（比如你看到的4/8那行消失）。

3. 冗余的日期处理逻辑 #

在主查询的SELECT和ORDER BY里，你又对 eas.date 重复执行了 DATE_TRUNC('week', ...)::DATE - '1 day'，但 eas.date 本身已经是经过这个计算的结果了！重复计算不仅多余，还可能因为PostgreSQL的周起始设置（默认是周一）导致日期偏移，比如如果 eas.date 是周日，再次截断周会得到前一周的周一，减一天就变成前一周的周日，完全偏离了你原本的日期逻辑。

修正后的查询语句 #

WITH eas AS (
    SELECT 
        DATE_TRUNC('week', create_ts)::DATE - '1 day'::INTERVAL AS date,
        COUNT(name) AS ase 
    FROM local 
    WHERE type='sr' 
    GROUP BY DATE_TRUNC('week', create_ts)::DATE - '1 day'::INTERVAL
), 
ias AS (
    SELECT 
        DATE_TRUNC('week', create_ts)::DATE - '1 day'::INTERVAL AS date,
        COUNT(name) AS ase 
    FROM local 
    WHERE type='sr' AND state NOT IN ('AZ','CA') 
    GROUP BY DATE_TRUNC('week', create_ts)::DATE - '1 day'::INTERVAL
)
SELECT 
    COALESCE(eas.date, ias.date) AS l_create,
    COALESCE(eas.ase, 0) + COALESCE(ias.ase, 0) AS count
FROM eas 
FULL JOIN ias ON ias.date = eas.date 
ORDER BY l_create ASC;

修正说明： #

• 用 COALESCE(eas.date, ias.date) 确保即使某一边没有数据（比如某个日期只有eas有值，或者只有ias有值），也能正确显示日期。
• 用 COALESCE(eas.ase, 0) 和 COALESCE(ias.ase, 0) 避免某一边为null时加法结果为null，确保计数准确。
• 去掉了多余的GROUP BY，因为CTE已经完成分组，JOIN后无需重复操作。
• 明确指定字段来源 eas.ase 和 ias.ase，修正了笔误问题。

执行这个修正后的查询，就能得到你期望的结果：

3/18/18 0:00 88 
3/25/18 0:00 175 
4/1/18 0:00 173 
4/8/18 0:00 163

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