如何编写SQL生成按国家统计的表字段填充率报表？

需求回顾 #

需要生成按国家分组的填充率报表，包含：

• people表中Name、Address字段的填充率（非空记录数/国家总用户数）
• 每个国家中出现频率最高的2个feature的名称及填充率（拥有该feature的用户数/国家总用户数）

解决方案SQL #

WITH people_fill_rates AS (
    SELECT
        CountryCode,
        -- 计算Name字段填充率：非空（含非空字符串）记录数 / 国家总用户数
        COUNT(CASE WHEN Name IS NOT NULL AND TRIM(Name) != '' THEN ID END)::FLOAT / COUNT(ID) AS Name_fillrate,
        -- 计算Address字段填充率
        COUNT(CASE WHEN Address IS NOT NULL AND TRIM(Address) != '' THEN ID END)::FLOAT / COUNT(ID) AS Address_fillrate
    FROM people
    GROUP BY CountryCode
),
feature_stats AS (
    SELECT
        p.CountryCode,
        f.feature_name,
        -- 统计该国家拥有当前feature的用户数
        COUNT(DISTINCT f.ID) AS feature_user_count,
        -- 计算feature填充率：拥有该feature的用户数 / 国家总用户数
        COUNT(DISTINCT f.ID)::FLOAT / COUNT(DISTINCT p.ID) OVER (PARTITION BY p.CountryCode) AS fillrate,
        -- 按feature的用户覆盖数降序排名，取每个国家Top2
        ROW_NUMBER() OVER (PARTITION BY p.CountryCode ORDER BY COUNT(DISTINCT f.ID) DESC) AS rn
    FROM people p
    LEFT JOIN feature f ON p.ID = f.ID
    WHERE f.feature_name IS NOT NULL -- 过滤无有效feature的记录
    GROUP BY p.CountryCode, f.feature_name
),
top_features_pivot AS (
    SELECT
        CountryCode,
        MAX(CASE WHEN rn = 1 THEN feature_name END) AS feature_name1,
        MAX(CASE WHEN rn = 1 THEN fillrate END) AS feature_1_fillrate,
        MAX(CASE WHEN rn = 2 THEN feature_name END) AS feature_name2,
        MAX(CASE WHEN rn = 2 THEN fillrate END) AS feature_2_fillrate
    FROM feature_stats
    WHERE rn <= 2
    GROUP BY CountryCode
)
-- 关联所有统计结果，输出最终报表
SELECT
    pfr.CountryCode,
    pfr.Name_fillrate AS Name,
    pfr.Address_fillrate AS Address,
    tfp.feature_name1,
    tfp.feature_1_fillrate,
    tfp.feature_name2,
    tfp.feature_2_fillrate
FROM people_fill_rates pfr
LEFT JOIN top_features_pivot tfp ON pfr.CountryCode = tfp.CountryCode
ORDER BY pfr.CountryCode;

代码解释 #

1. people_fill_rates CTE #

负责计算people表中每个国家Name和Address字段的填充率：

• 使用CASE语句筛选非空且非空白的记录，避免空字符串被误判为有效内容
• 用::FLOAT强制转换结果为小数，确保填充率以浮点数形式展示

2. feature_stats CTE #

完成feature的统计与排名：

• 关联people和feature表，按国家+feature名称分组
• 用COUNT(DISTINCT f.ID)统计每个feature在对应国家的用户覆盖数（避免同一用户多次统计）
• 窗口函数COUNT(DISTINCT p.ID) OVER (PARTITION BY p.CountryCode)获取当前国家的总用户数，用于计算填充率
• ROW_NUMBER()为每个国家的feature按用户覆盖数降序排名，标记出Top2的feature

3. top_features_pivot CTE #

将行格式的Top2 feature数据转为宽表：

• 使用CASE配合MAX函数，将排名1和2的feature名称、填充率分别映射到对应列，匹配预期输出的结构

4. 最终关联查询 #

将people表的填充率统计结果与Top2 feature的统计结果按国家关联，输出完整报表

思路说明 #

你提到的两种思路中，方案采用了思路1的实现方式：先分别按国家聚合计算各部分填充率，再关联结果。这种方式逻辑清晰，适合按国家维度的聚合统计需求；如果后续需要扩展到其他分组维度（比如按地区+国家），只需调整GROUP BY和窗口函数的分区字段即可。

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