如何在SQL查询中计算各里程碑的转化率百分比？

你尝试的语句存在两个问题：

• 语法错误：COUNT(lpersonid WHERE groupname = 'Entrance') 不符合SQL语法规范，COUNT函数不支持这种条件写法
• 逻辑偏差：需要获取Entrance分组的全局Distinct personid数量作为分母，而非当前分组内的统计值

以下是两种可行的正确实现方式：

方法一：子查询获取分母值 #

直接通过子查询单独计算Entrance分组的人数，作为转化率的分母：

SELECT 
    CASE WHEN groupname = 'Entrance' THEN '01'
         WHEN groupname = 'Lunch'    THEN '02' 
         WHEN groupname = 'Closings' THEN '03' END AS "MilestoneOrder",
    groupname AS "Milestone",
    COUNT(DISTINCT personid) AS "Counts",
    -- 计算转化率并格式化为百分比（保留两位小数）
    ROUND(
        COUNT(DISTINCT personid) * 100.0 / 
        (SELECT COUNT(DISTINCT personid) 
         FROM PersonCounts 
         WHERE groupname = 'Entrance' 
           AND DateComparison IN('firstenterbeforetransact', 'firstentersamedayastransact')),
        2
    ) || '%' AS "ConversionPercentage"
FROM PersonCounts
WHERE DateComparison IN('firstenterbeforetransact', 'firstentersamedayastransact')
GROUP BY groupname
ORDER BY MilestoneOrder

方法二：窗口函数实现（性能更优） #

利用窗口函数一次性获取Entrance的人数，避免重复扫描表：

SELECT 
    CASE WHEN groupname = 'Entrance' THEN '01'
         WHEN groupname = 'Lunch'    THEN '02' 
         WHEN groupname = 'Closings' THEN '03' END AS "MilestoneOrder",
    groupname AS "Milestone",
    COUNT(DISTINCT personid) AS "Counts",
    -- 通过窗口函数获取Entrance的全局人数作为分母
    ROUND(
        COUNT(DISTINCT personid) * 100.0 /
        SUM(CASE WHEN groupname = 'Entrance' THEN COUNT(DISTINCT personid) ELSE 0 END) OVER(),
        2
    ) || '%' AS "ConversionPercentage"
FROM PersonCounts
WHERE DateComparison IN('firstenterbeforetransact', 'firstentersamedayastransact')
GROUP BY groupname
ORDER BY MilestoneOrder

最终输出表格 #

关键说明 #

• 使用100.0触发浮点运算，避免整数除法导致结果失真
• ROUND(...,2)控制百分比的小数位数，|| '%'直接拼接百分号（部分数据库需用CONCAT函数替代）
• 窗口函数方法仅需扫描一次数据表，在数据量较大时性能优于子查询

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