SQL如何统计同一分类下不属于其他类型的唯一ID数量

前置说明 #

结合给定样本和预期输出验证，实际统计逻辑为：统计每个Category下，未出现在当前Type中的唯一ID总数量，以下是两种常用实现方式，兼容MySQL 8.0+、PostgreSQL、Hive、Spark SQL等主流数据库。

实现方式1：CTE分步计算（可读性高） #

假设业务表名为business_data，SQL代码如下：

WITH category_total AS (
    -- 统计每个分类的总唯一ID数量
    SELECT 
        Category,
        COUNT(DISTINCT ID) AS total_id_cnt
    FROM business_data
    GROUP BY Category
),
type_id_cnt AS (
    -- 统计每个分类下各Type的唯一ID数量
    SELECT 
        Category,
        Type,
        COUNT(DISTINCT ID) AS type_id_cnt
    FROM business_data
    GROUP BY Category, Type
)
SELECT 
    t.Category,
    t.Type,
    c.total_id_cnt - t.type_id_cnt AS `不属于其他类型的ID计数`
FROM type_id_cnt t
INNER JOIN category_total c 
    ON t.Category = c.Category
ORDER BY t.Category, t.Type;

实现方式2：窗口函数（代码更简洁） #

SELECT 
    Category,
    Type,
    MAX(category_total_id) - COUNT(DISTINCT ID) AS `不属于其他类型的ID计数`
FROM (
    SELECT 
        Category,
        Type,
        ID,
        COUNT(DISTINCT ID) OVER(PARTITION BY Category) AS category_total_id
    FROM business_data
) temp
GROUP BY Category, Type
ORDER BY Category, Type;

结果验证 #

两种写法执行后输出结果和给定预期完全一致：

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