Postgres中按周分区的Window functions(count/average)更优实现咨询

当然可以不用新增周数列，直接在窗口函数的PARTITION BY子句中嵌入日期计算逻辑，就能实现按周的分区聚合。根据你对“周”的定义（是遵循标准ISO周，还是从指定日期2022-01-01开始的每7天一组），可以选择不同的实现方式：

1. 按标准周（ISO周或数据库默认周）聚合 #

PostgreSQL的date_trunc('week', date_column)函数可以直接将日期截断到周的起始日，你可以把这个函数的结果直接放到PARTITION BY中，无需额外列：

SELECT
  "Date",
  "Value",
  AVG("Value") OVER (PARTITION BY date_trunc('week', "Date"::DATE)) AS weekly_avg
FROM your_table;

注意：date_trunc('week')的行为受数据库datestyle设置影响，默认ISO周以周一为起始日，且第一周需包含当年1月4日。如果你的业务周定义不同，可能需要调整。

2. 按自定义起始日（2022-01-01）的7天周期聚合 #

如果需要严格从2022-01-01开始每7天划分为一周，可以通过计算日期与起始日的天数差，再取整得到周分组标识，同样直接嵌入PARTITION BY：

SELECT
  "Date",
  "Value",
  AVG("Value") OVER (
    PARTITION BY FLOOR(("Date"::DATE - '2022-01-01'::DATE) / 7)
  ) AS custom_weekly_avg
FROM your_table;

逻辑说明： #

• ("Date"::DATE - '2022-01-01'::DATE)计算当前日期与起始日的天数差
• 除以7后取整（FLOOR），得到该日期所属的周序号（从0开始）
• 如果希望周序号从1开始，只需改为FLOOR(...) + 1

针对你提供的数据集，执行上述SQL后会得到：

• 2022-01-01和2022-01-02同属一组，平均值为12.5
• 2022-01-10和2022-01-11同属一组，平均值为6.5
• 2022-01-20单独一组，平均值为9
• 2022-01-25单独一组，平均值为8

这种方式无需修改原表结构，直接通过窗口函数的分区逻辑完成计算，是更简洁的实现方式。

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