如何对子查询中的差值数据按日/月/周/年进行分组

别急，我来帮你搞定这个时间分组的问题！既然你的数据是每小时采集的，那按日、月、周、年分组其实就是把时间字段「截断」到对应的维度，再聚合你的差值数据就行。下面我给你列几个主流数据库的实用写法，你对着自己用的数据库调整就行：

MySQL 写法 #

• 按日分组：把datetime字段截断到日期部分，搭配聚合函数（比如求和、平均，看你图表需求）

SELECT 
    DATE(采集时间字段) AS 统计日期,
    SUM(差值字段) AS 总差值, -- 换成AVG(差值字段)可求平均，按需调整
    COUNT(*) AS 数据条数
FROM 你的表名
GROUP BY DATE(采集时间字段)
ORDER BY 统计日期;

• 按周分组：用WEEK函数获取周数，第二个参数设为1可以把周一作为一周的起始日

SELECT 
    YEAR(采集时间字段) AS 统计年份,
    WEEK(采集时间字段, 1) AS 统计周数,
    CONCAT(YEAR(采集时间字段), '-W', LPAD(WEEK(采集时间字段,1),2,'0')) AS 周标识, -- 生成类似2024-W05的友好格式
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY 统计年份, 统计周数
ORDER BY 统计年份, 统计周数;

• 按月分组：用DATE_FORMAT转成年月格式，方便图表展示

SELECT 
    DATE_FORMAT(采集时间字段, '%Y-%m') AS 统计月份,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY DATE_FORMAT(采集时间字段, '%Y-%m')
ORDER BY 统计月份;

• 按年分组：直接提取年份即可

SELECT 
    YEAR(采集时间字段) AS 统计年份,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY YEAR(采集时间字段)
ORDER BY 统计年份;

PostgreSQL 写法 #

• 按日分组：用DATE_TRUNC截断到天，再转成DATE类型

SELECT 
    DATE_TRUNC('day', 采集时间字段)::DATE AS 统计日期,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY DATE_TRUNC('day', 采集时间字段)::DATE
ORDER BY 统计日期;

• 按周分组：PostgreSQL默认遵循ISO标准（周一为周起始），用DATE_TRUNC('week')即可

SELECT 
    DATE_TRUNC('week', 采集时间字段)::DATE AS 周起始日期,
    CONCAT(EXTRACT(YEAR FROM DATE_TRUNC('week', 采集时间字段)), '-W', LPAD(EXTRACT(WEEK FROM DATE_TRUNC('week', 采集时间字段))::TEXT,2,'0')) AS 周标识,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY DATE_TRUNC('week', 采集时间字段)
ORDER BY 周起始日期;

• 按月分组：截断到月份，搭配TO_CHAR生成友好的年月格式

SELECT 
    DATE_TRUNC('month', 采集时间字段)::DATE AS 月份起始日,
    TO_CHAR(采集时间字段, '%Y-%m') AS 统计月份,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY DATE_TRUNC('month', 采集时间字段), TO_CHAR(采集时间字段, '%Y-%m')
ORDER BY 月份起始日;

• 按年分组：提取年份进行分组

SELECT 
    EXTRACT(YEAR FROM 采集时间字段) AS 统计年份,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY EXTRACT(YEAR FROM 采集时间字段)
ORDER BY 统计年份;

SQL Server 写法 #

• 按日分组：把datetime字段转成DATE类型即可

SELECT 
    CAST(采集时间字段 AS DATE) AS 统计日期,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY CAST(采集时间字段 AS DATE)
ORDER BY 统计日期;

• 按周分组：先设置周一为周起始，再提取年份和周数

SET DATEFIRST 1; -- 可选：设置周一为一周的第一天，默认是周日
SELECT 
    YEAR(采集时间字段) AS 统计年份,
    DATEPART(WEEK, 采集时间字段) AS 统计周数,
    CONCAT(YEAR(采集时间字段), '-W', RIGHT('0' + CAST(DATEPART(WEEK, 采集时间字段) AS VARCHAR),2)) AS 周标识,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY YEAR(采集时间字段), DATEPART(WEEK, 采集时间字段)
ORDER BY 统计年份, 统计周数;

• 按月分组：用FORMAT生成yyyy-MM格式的月份标识

SELECT 
    FORMAT(采集时间字段, 'yyyy-MM') AS 统计月份,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY FORMAT(采集时间字段, 'yyyy-MM')
ORDER BY 统计月份;

• 按年分组：提取年份分组

SELECT 
    YEAR(采集时间字段) AS 统计年份,
    SUM(差值字段) AS 总差值
FROM 你的表名
GROUP BY YEAR(采集时间字段)
ORDER BY 统计年份;

几个关键注意点 #

• 记得把代码里的采集时间字段、差值字段、你的表名替换成你实际的字段和表名
• 聚合函数（SUM/AVG/COUNT等）要根据图表需求选：比如要展示每日差值总和用SUM，看平均水平用AVG
• 如果你的时间字段是字符串类型，要先转成日期类型再分组，比如MySQL里用STR_TO_DATE(时间字符串字段, '%Y-%m-%d %H:%i:%s')转换

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