Presto SQL按设备分组统计各组样本量及占比问题

问题分析 #

你的需求核心是：基于固定的每组100台设备基数，统计所有指定设备类型在对照组（control）和实验组（treatment）中的实际设备数量及占比，同时要包含原表中未出现的设备类型（如tablet、TV）。原查询的问题在于：

• 统计的是交易记录数而非去重设备数
• 错误从表中查询组总数量，而非使用给定的100台基数
• 未覆盖原表不存在的设备类型

完整SQL查询 #

WITH device_dim AS (
    -- 构造需要统计的所有设备类型维度
    SELECT * FROM VALUES ('ios'), ('android'), ('tablet'), ('TV') AS t(attribute)
),
group_device_stats AS (
    -- 统计原表中各设备在两组的去重设备数量
    SELECT
        device AS attribute,
        COUNT(DISTINCT CASE WHEN group_name = 'control' THEN uuid END) AS control_sample,
        COUNT(DISTINCT CASE WHEN group_name = 'treatment' THEN uuid END) AS treatment_sample
    FROM your_table
    GROUP BY device
)
SELECT
    d.attribute,
    -- 用COALESCE处理原表无数据的设备，样本量设为0
    COALESCE(g.control_sample, 0) AS control_sample,
    COALESCE(g.treatment_sample, 0) AS treatment_sample,
    -- 基于固定基数100计算占比，保留两位小数
    ROUND(COALESCE(g.control_sample, 0) / 100.0, 2) AS score_control,
    ROUND(COALESCE(g.treatment_sample, 0) / 100.0, 2) AS score_treatment
FROM device_dim d
-- 左连接确保所有设备类型都出现在结果中
LEFT JOIN group_device_stats g ON d.attribute = g.attribute;

关键说明 #

1. 设备维度表（device_dim）：通过VALUES子句生成所有需要统计的设备类型，确保tablet、TV这类原表未出现的设备也能被纳入结果。
2. 去重设备统计：使用COUNT(DISTINCT uuid)统计每组的实际设备数，避免同一设备多条交易记录导致的重复计数。
3. 固定基数占比计算：直接使用给定的每组100台设备作为分母，而非从表中查询总记录数，符合需求设定。
4. NULL值处理：用COALESCE将原表无数据的设备样本量设为0，避免计算占比时出现NULL。
5. 格式匹配：用ROUND函数将占比保留两位小数，与期望结果格式一致。

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