如何查询email_Archive表指定用户近一个月日均发件量

表内无单独用户id字段，以下以username作为用户唯一标识返回，如需关联用户表获取真实用户id可自行调整关联逻辑。

实现思路 #

• 生成近一个月的完整日期序列，覆盖所有统计周期内的日期，解决无发件记录日期的补0问题
• 构造指定的目标用户列表，确保所有要求统计的用户都被纳入结果，哪怕全月无发件记录
• 统计每个用户每日的实际发件量，和日期、用户列表关联后补全无发件日期的0值
• 按用户分组计算总发件量除以统计周期总天数，得到日均发件量

可直接运行的MySQL 8.0+ 实现代码 #

WITH RECURSIVE date_range AS (
    -- 生成近一个月的所有日期序列
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AS send_date
    UNION ALL
    SELECT DATE_ADD(send_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE send_date < CURDATE()
),
target_users AS (
    -- 构造指定需要统计的用户列表
    SELECT 'A' AS username UNION ALL
    SELECT 'f' UNION ALL SELECT 'g' UNION ALL SELECT 'h' UNION ALL
    SELECT 'i' UNION ALL SELECT 'n' UNION ALL SELECT 'q' UNION ALL SELECT 'r'
),
daily_send_stats AS (
    -- 统计用户每日实际发件数
    SELECT 
        username,
        DATE(STR_TO_DATE(created, '%Y-%m-%d:%H:%i:%s')) AS send_date,
        COUNT(1) AS send_count
    FROM email_Archive
    WHERE 
        username IN ('A','f','g','h','i','n','q','r')
        AND STR_TO_DATE(created, '%Y-%m-%d:%H:%i:%s') >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
    GROUP BY username, DATE(STR_TO_DATE(created, '%Y-%m-%d:%H:%i:%s'))
)
-- 计算最终日均发件量
SELECT 
    tu.username AS user_id,
    ROUND(IFNULL(SUM(dss.send_count), 0) / (SELECT COUNT(*) FROM date_range), 2) AS avg_daily_send_count
FROM target_users tu
CROSS JOIN date_range dr
LEFT JOIN daily_send_stats dss 
    ON tu.username = dss.username AND dr.send_date = dss.send_date
GROUP BY tu.username;

注意事项 #

1. 若你提供的created字段格式YYYY-MM-DD:MM:ss存在笔误（日期与时间间隔符不是冒号、时分顺序不符等），可自行调整STR_TO_DATE函数的格式化参数适配
2. 统计周期默认按当前日期往前推30/31天计算，如需统计整自然月，可修改date_range的起止日期逻辑
3. 若使用MySQL 8.0以下版本或其他数据库（PostgreSQL、Oracle等），只需调整日期序列生成、日期处理的对应函数即可，核心逻辑不变
4. 若存在单独的用户表存储用户id与用户名的映射关系，可将target_users部分替换为从用户表筛选目标用户的逻辑，即可返回真实的user_id字段

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