如何编写SQL查询按日期分组计算value列的平均值？

原始数据 #

需求 #

按日期（YYYY-MM-DD格式）分组，计算value列的平均值，期望输出示例：

2023-01-18 - average: 22
2023-01-25 - average: 19
（注：示例结果为取整后的值，可根据需求调整小数位数）

分数据库查询语句 #

标准SQL #

SELECT 
    DATE(timestamp) AS date,
    ROUND(AVG(value), 0) AS average
FROM your_table_name
GROUP BY DATE(timestamp)
ORDER BY date;

MySQL #

SELECT 
    DATE_FORMAT(timestamp, '%Y-%m-%d') AS date,
    ROUND(AVG(value), 0) AS average
FROM your_table_name
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d')
ORDER BY date;

PostgreSQL #

SELECT 
    TO_CHAR(timestamp, 'YYYY-MM-DD') AS date,
    ROUND(AVG(value)) AS average
FROM your_table_name
GROUP BY TO_CHAR(timestamp, 'YYYY-MM-DD')
ORDER BY date;

SQL Server #

SELECT 
    CONVERT(VARCHAR(10), timestamp, 23) AS date,
    ROUND(AVG(value), 0) AS average
FROM your_table_name
GROUP BY CONVERT(VARCHAR(10), timestamp, 23)
ORDER BY date;

说明 #

• 核心逻辑：将timestamp字段转换为纯日期格式，以此作为分组维度
• AVG(value)计算每组平均值，ROUND()用于对结果取整（和示例匹配），若需保留小数，可修改第二个参数，例如ROUND(AVG(value), 2)保留两位小数
• ORDER BY date确保结果按日期顺序排列

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