MySQL含空日期时按列计算日平均值的实现方法

要实现包含无数据日期（视为金额0）的日平均计算，核心是先生成统计周期内的所有连续日期，再与业务表关联补全空值，最后用总金额除以总天数。以下是几种可行方案：

方案1：使用递归CTE（MySQL 8.0+） #

如果已知统计的起止日期，直接生成该区间的连续日期：

WITH date_range AS (
    SELECT '2021-09-21' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_range
    WHERE dt < '2021-09-28'
)
SELECT 
    SUM(IFNULL(t.amount, 0)) / COUNT(d.dt) AS daily_average
FROM date_range d
LEFT JOIN trans t ON d.dt = t.date;

如果需要动态匹配表中存在的最小/最大日期，无需手动指定起止：

WITH date_range AS (
    SELECT MIN(date) AS dt FROM trans
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_range
    WHERE dt < (SELECT MAX(date) FROM trans)
)
SELECT 
    SUM(IFNULL(t.amount, 0)) / COUNT(d.dt) AS daily_average
FROM date_range d
LEFT JOIN trans t ON d.dt = t.date;

逻辑说明：

• 递归CTE生成从起始到结束的所有连续日期
• 左连接trans表，无匹配的日期用IFNULL将amount转为0
• 总金额除以总日期数，得到包含空日期的日平均值

方案2：数字表生成连续日期（兼容MySQL 5.x） #

若MySQL版本不支持CTE，可通过数字表生成足够多的日期：

SELECT 
    SUM(IFNULL(t.amount, 0)) / COUNT(d.dt) AS daily_average
FROM (
    SELECT DATE_ADD((SELECT MIN(date) FROM trans), INTERVAL n DAY) AS dt
    FROM (
        SELECT a.n + b.n * 10 + c.n * 100 AS n
        FROM (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
        CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    ) nums
    WHERE DATE_ADD((SELECT MIN(date) FROM trans), INTERVAL n DAY) <= (SELECT MAX(date) FROM trans)
) d
LEFT JOIN trans t ON d.dt = t.date;

逻辑说明：

• 通过交叉连接数字表生成0-999的连续数字
• 基于表中最小日期，加上数字得到连续日期
• 过滤出最大日期以内的日期后，左连接业务表计算平均值

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