如何用MySQL获取周一按小时分组的平均价格？查询问题排查

原查询的问题分析 #

1. 周一筛选条件错误：MySQL的WEEKDAY()函数返回值规则是0=周一，1=周二，...，6=周日，你用WEEKDAY(date)=1实际筛选的是周二的数据，这是结果不符合预期的核心原因之一。
2. SELECT与GROUP BY不匹配：你在SELECT中直接返回原始date字段，但GROUP BY的是格式化后的小时字符串，这会导致数据库返回每组的任意一条date记录，进而出现“仅返回每月第一个周一”的异常结果。
3. 日期格式化符错误：%mm是用于格式化月份的符号，不是分钟，按小时分组只需提取小时部分即可。

修正后的SQL查询 #

如果需要直接返回小时数值，用HOUR()函数更高效：

SELECT 
  HOUR(date) AS hour_of_day,
  AVG(price) AS monday_average_price
FROM your_table
WHERE 
  YEAR(date) = 2022 
  AND WEEKDAY(date) = 0 -- 筛选周一数据
GROUP BY 
  HOUR(date) -- 按小时数分组
ORDER BY 
  hour_of_day;

如果需要返回HH:00格式的小时标识，可改用日期格式化：

SELECT 
  DATE_FORMAT(date, '%H:00') AS hour_of_day,
  AVG(price) AS monday_average_price
FROM your_table
WHERE 
  YEAR(date) = 2022 
  AND WEEKDAY(date) = 0
GROUP BY 
  DATE_FORMAT(date, '%H')
ORDER BY 
  hour_of_day;

补充说明 #

• 不需要为每个工作日单独编写查询，只要修改WEEKDAY()的参数即可：比如查询周二用WEEKDAY(date)=1，周三用2，以此类推。
• 若使用其他类型数据库（如PostgreSQL），日期函数规则可能不同，比如PostgreSQL用EXTRACT(DOW FROM date)时周一为1，需对应调整筛选条件。

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