PostgreSQL按日期分组计算多周期成交量均值的SQL实现

核心逻辑为两层聚合，避免直接对周期内所有小时数据平均导致的权重偏差（例如某天入库数据条数更多，会拉高该天对最终结果的影响权重，不符合按天等权计算的需求）：

1. 第一层聚合：按「id + 自然日」分组，计算每个id对应每天的volume日均值
2. 第二层聚合：筛选出指定周期覆盖的所有日均值结果，再按id分组计算周期总平均值

通用查询语句 #

使用CTE拆分计算逻辑，仅需替换周期参数即可快速实现1天/5天/10天等不同周期的统计：

-- 替换INTERVAL后的参数即可切换统计周期，例如 '1 day'、'5 day'、'10 day'
WITH daily_stat AS (
    SELECT
        id,
        -- 将时间戳截断到自然日维度，作为天维度的分组标识
        date_trunc('day', updated_at::timestamptz) AS stat_date,
        AVG(volume) AS daily_avg_volume
    FROM price_table
    -- 提前过滤时间范围，减少不必要的计算量
    WHERE updated_at::timestamptz >= NOW() - INTERVAL '7 day'
    GROUP BY id, stat_date
)
SELECT
    id,
    AVG(daily_avg_volume) AS period_avg_volume
FROM daily_stat
GROUP BY id
-- 如需按结果排序可放开下方注释，调整排序规则
-- ORDER BY period_avg_volume DESC;

常见调整场景 #

• 按完整自然日统计：上述SQL默认从当前时间向前推N*24小时计算，如果需要仅统计完整自然日（例如7天周期排除当天未到24点的不完整数据），可将CTE内的WHERE条件替换为：

WHERE updated_at::timestamptz >= CURRENT_DATE - INTERVAL '7 day'
  AND updated_at::timestamptz < CURRENT_DATE

• 同时返回name字段：由于同一个id对应的name是固定值，只需在两层查询的SELECT、GROUP BY子句中同步添加name字段即可，不会影响聚合结果的准确性。
• 原有查询存在两处语法错误：GROUP BY id后多余了and关键字，ORDER BY avg(price)引用了未查询、未聚合的price字段，直接运行会抛出错误。

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