如何按周或自然月正确计算员工的平均积分？

你的原查询之所以得到的是工作项级别的平均，是因为它直接将员工一周内所有记录的points值做了平均，没有先按日聚合。要实现按每日数据的平均，我们需要通过两次分组聚合来完成：

1. 按周统计的实现 #

需求1：基于每日总积分的周平均 #

如果你的需求是先计算员工每天的总积分，再求这些日总积分的周平均值，可以用下面的SQL：

SELECT 
  WEEK(date) AS week_number,
  employeeid,
  AVG(daily_total_points) AS weekly_avg_daily_points
FROM (
  -- 子查询：先获取每个员工每天的总积分
  SELECT 
    DATE(date) AS day_date, -- 若date是datetime类型，用DATE()截断时间确保按自然日分组
    employeeid,
    SUM(points) AS daily_total_points
  FROM `daily_schedule`
  GROUP BY day_date, employeeid
) AS daily_summary
GROUP BY week_number, employeeid;

需求2：基于每日工作项平均积分的周平均 #

如果你的需求是先计算员工每天的工作项平均积分，再求这些日平均值的周平均，可以调整子查询的聚合函数：

SELECT 
  WEEK(date) AS week_number,
  employeeid,
  AVG(daily_avg_points) AS weekly_avg_daily_points
FROM (
  -- 子查询：先获取每个员工每天的工作项平均积分
  SELECT 
    DATE(date) AS day_date,
    employeeid,
    AVG(points) AS daily_avg_points
  FROM `daily_schedule`
  GROUP BY day_date, employeeid
) AS daily_summary
GROUP BY week_number, employeeid;

2. 按自然月统计的实现 #

按自然月统计时，需要注意跨年份的情况（比如2023年1月和2024年1月是不同的月份），所以分组时要同时包含年份和月份：

SELECT 
  YEAR(date) AS year,
  MONTH(date) AS month,
  CONCAT(YEAR(date), '-', LPAD(MONTH(date), 2, '0')) AS year_month, -- 格式化成年月字符串（如2024-05）
  employeeid,
  AVG(daily_total_points) AS monthly_avg_daily_points
FROM (
  SELECT 
    DATE(date) AS day_date,
    employeeid,
    SUM(points) AS daily_total_points
  FROM `daily_schedule`
  GROUP BY day_date, employeeid
) AS daily_summary
GROUP BY year, month, employeeid;

关键说明 #

• 子查询的作用是将数据聚合到「员工-单日」的维度，确保我们拿到的是每天的积分数据（而非单条工作项数据）；
• 外层查询再按周/月分组，对这些每日数据求平均，就得到了你想要的“基于每日统计的周/月平均积分”；
• 如果你的date字段是datetime类型，一定要用DATE(date)来截断时间部分，避免因为时间不同导致同一自然日被分成多个组。

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