求统计每日新用户占比的SQL查询语句（含非活跃用户统计）

测试表结构 #

需求说明 #

编写SQL查询返回每日新用户占比，要求：

• 统计每日的总用户数（包括当日无活动记录但之前已存在的用户）
• 计算当日新用户（首次出现的用户）占总用户数的百分比
• 统计范围内的第一个日期，新用户占比显示为null

期望输出 #

问题分析 #

之前用DISTINCT的多查询方案无法满足需求，核心原因是没有关联所有已存在的用户到每个统计日期，导致无活动的用户未被计入当日总用户数。

解决方案SQL（MySQL示例） #

-- 提取每个用户的首次活动日期
WITH user_first_date AS (
    SELECT USERID, MIN(updated_date) AS first_date
    FROM your_table_name
    GROUP BY USERID
),
-- 提取所有需要统计的日期（表中出现过的所有日期）
date_range AS (
    SELECT DISTINCT updated_date AS date
    FROM your_table_name
)
-- 计算每日总用户数和新用户占比
SELECT
    dr.date AS Date,
    COUNT(DISTINCT uf.USERID) AS total_users,
    CASE
        -- 第一个统计日期的占比设为null
        WHEN dr.date = (SELECT MIN(updated_date) FROM your_table_name) THEN NULL
        -- 计算当日新用户占比并转为百分比格式
        ELSE CONCAT(ROUND(
            (COUNT(DISTINCT CASE WHEN uf.first_date = dr.date THEN uf.USERID END) / COUNT(DISTINCT uf.USERID)) * 100, 0
        ), '%')
    END AS `percentage of new`
FROM date_range dr
-- 关联所有日期和所有用户
CROSS JOIN user_first_date uf
-- 只保留截至当前日期已存在的用户（首次日期<=当前日期）
WHERE uf.first_date <= dr.date
GROUP BY dr.date
ORDER BY dr.date;

关键逻辑说明 #

1. user_first_date：确定每个用户的首次活动日期，作为判断新用户的唯一标准
2. date_range：获取表中所有有活动记录的日期，确保每个需要统计的日期都被覆盖
3. CROSS JOIN + WHERE：将每个日期与所有截至该日期已存在的用户关联，保证无活动的用户也被计入当日总用户数
4. 占比计算：通过CASE语句筛选当日新用户，计算占比并格式化，同时处理首个日期的特殊情况

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