SQL按邮政编码分组统计去重ID数量及欺诈ID占比的高效查询方法

我们默认基于通用业务表结构编写，假设表名为user_transaction，核心字段如下：

• zip_code：邮政编码字段
• user_id：用户唯一ID字段
• is_fraud：欺诈标记字段，欺诈记录取值为1，非欺诈记录取值为0

最优查询语句 #

SELECT
    zip_code,
    COUNT(user_id) AS total_distinct_id,
    ROUND(SUM(fraud_flag) / COUNT(user_id), 4) AS fraud_id_ratio
FROM (
    SELECT
        zip_code,
        user_id,
        CASE WHEN MAX(is_fraud) = 1 THEN 1 ELSE 0 END AS fraud_flag
    FROM user_transaction
    GROUP BY zip_code, user_id
) t
GROUP BY zip_code
ORDER BY zip_code;

实现说明 #

1. 性能优势：仅需扫描1次原表，先按zip_code+user_id做第一层聚合，避免后续多次扫描全表，是性能最优的实现方案
2. 欺诈ID判定逻辑：内层通过MAX(is_fraud)判断单个ID是否为欺诈，只要该ID存在至少1条欺诈记录就返回1，所有记录均为非欺诈则返回0，完全匹配需求规则
3. 统计逻辑：外层按zip_code聚合，COUNT(user_id)直接得到该邮编下去重后的总ID数，SUM(fraud_flag)得到该邮编下的欺诈ID总数，两者相除即可得到欺诈ID占比，可按需调整ROUND函数的小数保留位数

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