如何结合CASE WHEN创建SQL百分比计算公式？

核心问题分析 #

你之前的代码未对分子、分母做全局聚合，只是逐行返回单个标记值（1或NULL），直接计算会导致每行的分子/分母都是1/1（满足条件的行）或NULL/NULL（不满足的行），因此结果始终为100%；另外整数除法会丢失精度，需用浮点型数值触发正确计算。

方案1：仅返回统计结果（分数+百分比） #

如果只需要整体的统计值，直接用聚合函数计算分子和分母：

SELECT
    -- 拼接分数格式（如2/3）
    CONCAT(
        SUM(CASE WHEN "Form_Questions"."question_one_answer" IS NOT NULL THEN 1 ELSE 0 END),
        '/',
        COUNT(*)
    ) AS 分数,
    -- 计算百分比并格式化（如66%）
    ROUND(
        (SUM(CASE WHEN "Form_Questions"."question_one_answer" IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*)),
        0
    ) || '%' AS 百分比
FROM "dbo"."Customer" AS "Customer"    
INNER JOIN "dbo"."CustomerVisit" AS "CUSTOMERVISIT"
    ON "Customer"."customer_id" = "CustomerVisit"."customer_id"
INNER JOIN "dbo"."Form_Questions"
    ON "CUSTOMERVISIT"."customervisit_id" = "Form_Questions"."customervisit_id"
INNER JOIN "dbo"."VisitType" AS "VISITTYPE"
    ON "CUSTOMERVISIT"."visittype_id" = "VISITTYPE"."visittype_id"
WHERE "Visittype"."visittype" = 'Form Questions'

方案2：保留客户明细+全局统计值 #

如果需要在保留客户每行数据的同时显示整体统计，用窗口函数实现全局聚合（无需GROUP BY）：

SELECT DISTINCT
    "Form_Questions"."question_one_answer",    
    "Visittype"."visittype",    
    "CustomerVisit"."Visit_start_time",    
    "Customer"."age",    
    "Customer"."customer_id",
    -- 全局分数统计
    CONCAT(
        SUM(CASE WHEN "Form_Questions"."question_one_answer" IS NOT NULL THEN 1 ELSE 0 END) OVER (),
        '/',
        COUNT(*) OVER ()
    ) AS 分数,
    -- 全局百分比统计
    ROUND(
        (SUM(CASE WHEN "Form_Questions"."question_one_answer" IS NOT NULL THEN 1 ELSE 0 END) OVER () * 100.0 / COUNT(*) OVER ()),
        0
    ) || '%' AS 百分比
FROM "dbo"."Customer" AS "Customer"    
INNER JOIN "dbo"."CustomerVisit" AS "CUSTOMERVISIT"
    ON "Customer"."customer_id" = "CustomerVisit"."customer_id"
INNER JOIN "dbo"."Form_Questions"
    ON "CUSTOMERVISIT"."customervisit_id" = "Form_Questions"."customervisit_id"
INNER JOIN "dbo"."VisitType" AS "VISITTYPE"
    ON "CUSTOMERVISIT"."visittype_id" = "VISITTYPE"."visittype_id"
WHERE "Visittype"."visittype" = 'Form Questions'

关键注意事项 #

• 浮点运算：用100.0而非100触发浮点除法，避免整数除法导致的精度丢失（如2/3整数运算结果为0）。
• 去重处理：如果存在同一客户多次完成访问的情况，将COUNT(*)替换为COUNT(DISTINCT "Customer"."customer_id")，分子用COUNT(DISTINCT CASE WHEN ... THEN "Customer"."customer_id" END)，确保统计的是不同客户数量而非访问次数。
• 窗口函数：OVER ()表示对整个结果集做全局聚合，无需添加GROUP BY子句。

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