SQL实现间隔7天统计100天跨度内数值平均值的查询方法

核心前提 #

• 业务表需包含两个必填字段：
  • date：日期类型，存储营收对应的发生日期
  • value：数值类型，存储对应日期的营收金额
• 计算规则：覆盖从当前日期往前推100天的时间范围，将范围内所有日期按所属星期几分组，分别计算每组的营收平均值，最终得到周一到周日每天对应的周期内平均营收（比如过去100天所有周三的平均营收）。

实现代码 #

核心逻辑非常直接：先过滤100天内的有效数据减少计算量，再按日期对应的星期维度分组求平均即可，以下以最常用的MySQL语法为例：

SELECT
    DAYNAME(`date`) AS week_day, -- 返回星期的英文名称，结果可读性更强
    AVG(`value`) AS avg_revenue
FROM `your_biz_table` -- 替换为实际使用的业务表名
WHERE 
    -- 过滤最近100天的数据，可根据业务需要调整时间基准
    `date` >= DATE_SUB(CURDATE(), INTERVAL 100 DAY)
    AND `date` <= CURDATE()
GROUP BY 
    DAYOFWEEK(`date`), -- 按星期的数值编码分组，避免不同语言环境下星期名称重复导致分组错误
    week_day
ORDER BY DAYOFWEEK(`date`); -- 按星期自然顺序排序结果

跨数据库适配提示 #

• PostgreSQL：将星期提取函数替换为TO_CHAR(date, 'FMDay')，日期过滤条件调整为date >= CURRENT_DATE - INTERVAL '100 days'
• SQL Server：星期提取使用DATENAME(WEEKDAY, date)，日期过滤条件调整为date >= DATEADD(DAY, -100, CAST(GETDATE() AS DATE))
• 如果只需要单独查询某一个星期几的平均营收（比如仅查周三），直接在WHERE条件中追加对应星期的筛选即可，比如MySQL环境下追加AND DAYOFWEEK(date) = 4即可（注意不同数据库的星期计数起始值不同，MySQL默认周日为计数1，周三对应数值4，使用前可先校验取值对应关系）。

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