SQL实现按日期分组计算教程步骤完成率并新增百分比列

基础逻辑说明 #

你需要计算的是新手教程漏斗的各步骤转化率，核心逻辑是先取每日loadIn步骤的完成人数作为当日进入教程的总基数（对应100%完成率），再用当日各步骤的完成人数除以这个基数，得到对应步骤的完成百分比。
假设你的原始数据表名为tutorial_step_stats，包含字段：

• date：统计日期
• stepName：教程步骤名称
• completionCount：对应步骤当日完成玩家数

主流SQL环境实现代码（推荐） #

目前绝大多数常用数据分析环境（MySQL 8.0+、PostgreSQL、Hive、SparkSQL、ClickHouse等）都支持窗口函数，用这个写法逻辑最简洁、执行效率最高：

SELECT
    date,
    stepName,
    completionCount,
    ROUND(
        completionCount * 100.0 / MAX(CASE WHEN stepName = 'loadIn' THEN completionCount END) OVER (PARTITION BY date),
        2
    ) AS completionPercentage
FROM tutorial_step_stats
-- 按日期、完成率倒序排，直接呈现漏斗递减效果
ORDER BY date, completionPercentage DESC;

语法细节说明（适合SQL新手理解） #

• MAX(CASE WHEN stepName = 'loadIn' THEN completionCount END) OVER (PARTITION BY date)：这是窗口函数的标准用法，按日期维度分组，把同日期下loadIn步骤的完成数匹配到当日所有步骤的行上，不需要写复杂的多表关联，不容易出错。
• 计算时乘100.0而非100，是为了规避部分SQL环境整数除法直接截断小数的问题，保证能算出带小数的百分比结果。
• ROUND(...,2)是把计算结果四舍五入保留2位小数，如果你需要更高/更低的精度，直接修改第二个参数的数值就行。

注意：如果你表里loadIn步骤的实际存储值有拼写差异（比如大小写不同、前后带空格），把SQL里判断条件的'loadIn'替换成表中实际对应的步骤名称即可。

老版本SQL兼容写法 #

如果你用的是不支持窗口函数的老版本环境（比如MySQL 5.x），可以用子查询关联的方式实现，逻辑和结果完全一致：

SELECT
    t1.date,
    t1.stepName,
    t1.completionCount,
    ROUND(t1.completionCount * 100.0 / t2.daily_base_count, 2) AS completionPercentage
FROM tutorial_step_stats t1
LEFT JOIN (
    -- 先单独查询每日loadIn步骤的基准人数
    SELECT 
        date,
        completionCount AS daily_base_count
    FROM tutorial_step_stats
    WHERE stepName = 'loadIn'
) t2 ON t1.date = t2.date
ORDER BY t1.date, completionPercentage DESC;

结果校验规则 #

跑数后可以先做快速校验，避免逻辑错误：

• 每日loadIn步骤对应的completionPercentage一定是100.00，符合你设定的基准规则
• 后续步骤的完成率随流程推进呈递减趋势，和你观察到的用户流失规律一致
• 如果出现完成率超过100%的异常值，优先检查原始数据是否存在单日多条loadIn记录、stepName命名不统一的问题

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