如何基于MySQL用户表在Grafana中绘制累计用户增长曲线图

核心需求解决逻辑 #

你当前的查询仅统计每日新增用户，要得到每日累计总量，需要完成两个核心逻辑：

1. 对每日新增用户数做累加计算
2. 补全没有新增用户的日期断层，保证趋势图连续

适用场景1：MySQL 8.0+ 支持窗口函数（推荐） #

直接用递归CTE生成连续日期+窗口函数实现累计求和，性能更优：

WITH RECURSIVE dates AS (
    -- 取统计范围内的最小起始日期
    SELECT MIN(DATE(created)) as dt FROM custom_domain_customdomain 
    WHERE is_cname_verified = '1' AND is_txt_verified = '1'
    UNION ALL
    -- 递归生成连续日期直到当前日期
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates 
    WHERE dt < CURDATE()
),
daily_new AS (
    -- 计算每日新增验证用户数
    SELECT 
        DATE(created) as dt,
        COUNT(id) as new_cnt
    FROM custom_domain_customdomain
    WHERE is_cname_verified = '1' AND is_txt_verified = '1'
    GROUP BY DATE(created)
)
SELECT
    -- 输出Grafana可识别的时间戳格式
    UNIX_TIMESTAMP(d.dt) AS "time",
    -- 窗口函数计算累计用户数，无新增的日期新增数补0
    SUM(COALESCE(dn.new_cnt, 0)) OVER (ORDER BY d.dt) AS "Verified"
FROM dates d
LEFT JOIN daily_new dn ON d.dt = dn.dt
ORDER BY d.dt

适用场景2：MySQL 5.x 不支持窗口函数 #

用关联子查询实现累计计算：

SELECT
    UNIX_TIMESTAMP(d.dt) AS "time",
    -- 子查询统计截止当前日期的累计用户数
    (
        SELECT COUNT(id) FROM custom_domain_customdomain 
        WHERE DATE(created) <= d.dt 
        AND is_cname_verified = '1' AND is_txt_verified = '1'
    ) AS "Verified"
FROM (
    -- 生成连续日期序列，@num控制生成的日期数量，可按需调整
    SELECT DATE_ADD(
        (SELECT MIN(DATE(created)) FROM custom_domain_customdomain WHERE is_cname_verified = '1' AND is_txt_verified = '1'),
        INTERVAL @num:=@num+1 DAY
    ) AS dt
    FROM custom_domain_customdomain t, (SELECT @num:=-1) num
    HAVING dt <= CURDATE()
) d
ORDER BY d.dt

Grafana配置注意 #

• 面板类型选择「时间序列」即可自动绘制用户增长趋势图
• 面板时间范围选择要覆盖你统计的用户起始注册日期，避免累计值计算不全

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