PostgreSQL如何按周截断时间戳分组，解决列不存在报错并筛选近1年数据

报错原因 #

你之前的语句报错核心原因有2个：

• 子查询中使用date_trunc('week', evt_block_time)处理时间字段后，没有给计算结果设置别名，子查询输出的字段不再叫evt_block_time，外层查询和子查询末尾的ORDER BY evt_block_time自然找不到对应列
• 你要按周分组统计，不能直接在明细数据上套原窗口函数，否则同一周的多条事件会生成重复的周度记录，不符合分组统计的预期

可直接运行的正确SQL #

以下语句同时实现「按周截断时间、按周分组统计累计交易对数量、仅查询最近52周数据」三个需求：

WITH weekly_stats AS (
  SELECT
    date_trunc('week', evt_block_time) AS stat_week,
    -- 统计每周各版本新增的交易对数量
    COUNT(*) FILTER (WHERE uniswap_version = 'v1') AS v1_weekly_add,
    COUNT(*) FILTER (WHERE uniswap_version = 'v2') AS v2_weekly_add
  FROM (
    SELECT 'v2' AS uniswap_version, evt_block_time
    FROM uniswap_v2."Factory_evt_PairCreated"
    UNION ALL
    SELECT 'v1' AS uniswap_version, evt_block_time
    FROM uniswap."Factory_evt_NewExchange"
  ) all_pair_creations
  -- 提前过滤最近52周的数据，减少计算量
  WHERE evt_block_time >= NOW() - INTERVAL '52 weeks'
  GROUP BY stat_week
)
SELECT
  stat_week,
  -- 按周排序计算累计总量
  SUM(v1_weekly_add) OVER (ORDER BY stat_week) AS v1_pairs,
  SUM(v2_weekly_add) OVER (ORDER BY stat_week) AS v2_pairs,
  -- 不需要看每周新增量可以删掉下面两行
  v1_weekly_add,
  v2_weekly_add
FROM weekly_stats
ORDER BY stat_week;

逻辑说明 #

• 采用CTE先做周度聚合，保证最终返回结果是每周一条记录，完全符合按周分组的要求
• 时间过滤条件下推到最内层数据源查询阶段，提前丢弃不需要的历史数据，查询效率更高
• 所有计算字段都设置了明确别名，不会再出现「列不存在」的报错
• 如果你不需要展示每周新增的交易对数量，删除最终SELECT块里的v1_weekly_add、v2_weekly_add两个字段即可

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