如何在SQL中按用户类别将所有值除以首行对应值（留存率）

原始数据表 #

需求说明 #

需要新增一个Pct列，计算逻辑为：当前行的Count值除以同一Category下Day为D1的Count值，再乘以100并格式化为百分比形式，最终目标结果如下：

目标结果表 #

当前问题代码 #

你写出的SQL未按Category过滤基准值，子查询会返回所有Day=D1的Count值（运行时会报错或结果完全错误），代码如下：

SELECT
    day,
    category,
    count,
    count/(SELECT count FROM table WHERE day = 'D1')*100 AS pct
FROM 
    table
ORDER BY 1

修正解法 #

方法1：关联子查询加Category过滤

在子查询中添加category = t.category条件，确保每个行仅获取对应类别的D1基准值：

SELECT
    day,
    category,
    count,
    CONCAT(ROUND(count/(SELECT count FROM table WHERE day = 'D1' AND category = t.category)*100, 0), '%') AS pct
FROM 
    table t
ORDER BY day, category

方法2：预存基准值再关联

先查询出每个类别的D1基准值，再通过JOIN关联原表计算，数据量大时性能更优：

WITH category_baseline AS (
    SELECT category, count AS baseline_count
    FROM table
    WHERE day = 'D1'
)
SELECT
    t.day,
    t.category,
    t.count,
    CONCAT(ROUND(t.count / cb.baseline_count * 100, 0), '%') AS pct
FROM table t
JOIN category_baseline cb ON t.category = cb.category
ORDER BY t.day, t.category

说明：两种方法均可实现需求，ROUND用于取整，CONCAT用来拼接百分号；若不需要取整，可去掉ROUND函数。

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