如何用Standard SQL计算多场景列的类型计数及占比

现有一张包含unique_ID、first_scenario、second_scenario、third_scenario字段的表，需要计算三个场景列中各type的总数，以及其占该场景总计数的百分比，最终合并成指定格式的结果表。目前已实现单场景的计数与占比计算，但不知道如何合并多场景结果，现有单场景计算代码如下：

SELECT
first_scenario AS types,
COUNT(first_scenario),
CEIL(first_scenario)*100/SUM(COUNT(first_scenario)) OVER() AS fs_percent_of_total
FROM my_table
GROUP BY 1

原表示例：

期望结果表：

可以分别计算每个场景的统计结果，再通过FULL JOIN以types为关联键合并三个结果集，同时修正原代码中CEIL函数的错误使用（原代码中直接对first_scenario字段用CEIL逻辑错误，应基于计数计算占比后处理小数）。

完整SQL代码如下：

-- 计算第一个场景的计数和占比
WITH fs_stats AS (
    SELECT
        first_scenario AS types,
        COUNT(*) AS first_scenario,
        ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER(), 1) AS fs_percent_of_total
    FROM my_table
    GROUP BY first_scenario
),
-- 计算第二个场景的计数和占比
ss_stats AS (
    SELECT
        second_scenario AS types,
        COUNT(*) AS second_scenario,
        ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER(), 1) AS ss_percent_of_total
    FROM my_table
    GROUP BY second_scenario
),
-- 计算第三个场景的计数和占比
ts_stats AS (
    SELECT
        third_scenario AS types,
        COUNT(*) AS third_scenario,
        ROUND(COUNT(*) * 1.0 / SUM(COUNT(*)) OVER(), 1) AS ts_percent_of_total
    FROM my_table
    GROUP BY third_scenario
)
-- 合并三个统计结果
SELECT
    COALESCE(fs.types, ss.types, ts.types) AS Types,
    COALESCE(fs.first_scenario, 0) AS first_scenario,
    COALESCE(fs.fs_percent_of_total, 0) AS fs_percent_of_total,
    COALESCE(ss.second_scenario, 0) AS second_scenario,
    COALESCE(ss.ss_percent_of_total, 0) AS ss_percent_of_total,
    COALESCE(ts.third_scenario, 0) AS third_scenario,
    COALESCE(ts.ts_percent_of_total, 0) AS ts_percent_of_total
FROM fs_stats fs
FULL JOIN ss_stats ss ON fs.types = ss.types
FULL JOIN ts_stats ts ON COALESCE(fs.types, ss.types) = ts.types
ORDER BY Types;

代码说明 #

1. CTE子查询：为三个场景分别创建统计子查询，计算每个type的数量和占该场景总数的百分比，用ROUND保留一位小数匹配期望结果。
2. FULL JOIN合并：使用FULL JOIN确保所有type都被保留，即使某个场景中没有该type。
3. COALESCE处理空值：将空值替换为0，避免结果中出现NULL。
4. 排序：最后按Types排序，让结果更整齐。

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