如何按等级计算用户流失率（不使用窗口函数）

原始查询及结果 #

你当前统计各等级用户数的SQL语句如下：

SELECT
    level,
    COUNT(id) AS count_id
FROM
    dev
GROUP BY level
ORDER BY level ASC;

查询输出结果：

需求说明 #

需要计算每个等级的用户流失数，逻辑为上一等级的用户数减去当前等级的用户数，且不能使用窗口函数。

解决方案：自连接实现 #

通过将统计用户数的子查询自连接，关联当前等级与上一等级的数据，即可计算流失数：

SELECT
    curr.level,
    -- 等级1无上级，流失数设为0；其余等级用上一级用户数减当前用户数
    CASE 
        WHEN prev.count_id IS NULL THEN 0 
        ELSE prev.count_id - curr.count_id 
    END AS churn
FROM
    (SELECT level, COUNT(id) AS count_id FROM dev GROUP BY level) curr
LEFT JOIN
    (SELECT level, COUNT(id) AS count_id FROM dev GROUP BY level) prev
ON curr.level = prev.level + 1
ORDER BY curr.level ASC;

结果说明 #

执行后会得到如下结果（若当前等级用户数多于上一级，结果会为负数，可根据业务需求用GREATEST(prev.count_id - curr.count_id, 0)替换计算逻辑，将负数值转为0）：

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