如何在SQL中按日期月份与ID分组计算平均值？

你想要的效果是保留原表的每一行数据，同时新增一列展示对应ID在当月的VOLUME平均值，这个用SQL的窗口函数就能轻松搞定，不用复杂的子查询关联。

核心思路 #

1. 先从DATE字段中提取年份+月份的标识（因为你的DATE是YYYYMMDD格式的字符串，直接截取前6位就行）；
2. 使用窗口函数AVG() OVER()，按「年份月份」和ID分组计算平均值，这样每一行都会显示其所在分组的平均值。

具体SQL代码（以MySQL为例） #

SELECT 
    DATE,
    ID,
    VOLUME,
    -- 按月份（YYYYMM）和ID分组，计算当前组的VOLUME平均值
    AVG(VOLUME) OVER (PARTITION BY LEFT(DATE, 6), ID) AS `VOLUME AVERAGE`
FROM 
    your_table_name;

代码解释 #

• LEFT(DATE, 6)：从DATE字符串的左侧截取6位，得到YYYYMM格式的月份标识（比如20080630会变成200806）；
• PARTITION BY LEFT(DATE, 6), ID：把数据按「月份」和「ID」分成不同的组，相同ID、同一月份的行归为一组；
• AVG(VOLUME) OVER(...)：窗口函数会计算当前行所在分组的VOLUME平均值，并且保留原表的所有行数据——这和普通GROUP BY只返回分组汇总行的区别很大，正好匹配你给出的期望输出格式。

针对其他数据库的适配 #

如果你的DATE字段是日期类型而非字符串，或者使用的是其他数据库，调整月份提取的函数即可：

• PostgreSQL/Oracle：用TO_CHAR(DATE, 'YYYYMM')代替LEFT(DATE, 6)
• SQL Server：用LEFT(CONVERT(VARCHAR(8), DATE), 6)或者FORMAT(DATE, 'yyyyMM')

验证示例数据 #

以你给出的ID=A、200806的行为例：
VOLUME值为45、23、34、33，总和135，平均值135/4=33.75，和你期望输出里的结果完全一致。

补充：如果只需要分组汇总行 #

如果你不需要保留原表的每一行，只想要每个ID每月的平均值汇总，可以用普通的GROUP BY：

SELECT 
    LEFT(DATE, 6) AS MONTH,
    ID,
    AVG(VOLUME) AS `VOLUME AVERAGE`
FROM 
    your_table_name
GROUP BY 
    LEFT(DATE, 6), ID
ORDER BY 
    MONTH, ID;

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