如何在SQL（含ClickHouse）中按分组统计跨组重复产品数据

需求说明 #

原始数据表（表名假设为company_products）：

需要生成统计表格，其中：

• total_products：该公司的产品记录总数
• total_unique_products：仅属于该公司的产品数量（即产品不会出现在其他公司的记录中）

目标结果：

通用SQL实现 #

核心思路：先统计每个产品被多少个公司拥有，再关联回原表计算各公司的独有产品数。

WITH product_company_count AS (
    SELECT product, COUNT(DISTINCT company) AS company_count
    FROM company_products
    GROUP BY product
)
SELECT
    cp.company,
    COUNT(cp.product) AS total_products,
    SUM(CASE WHEN pcc.company_count = 1 THEN 1 ELSE 0 END) AS total_unique_products
FROM company_products cp
JOIN product_company_count pcc ON cp.product = pcc.product
GROUP BY cp.company
ORDER BY cp.company;

ClickHouse优化实现 #

ClickHouse提供了更高效的聚合函数和语法，可以简化计算：

方法1：CTE+关联查询 #

WITH product_company_count AS (
    SELECT product, uniqExact(company) AS company_count
    FROM company_products
    GROUP BY product
)
SELECT
    company,
    count(product) AS total_products,
    sumIf(1, company_count = 1) AS total_unique_products
FROM company_products
ANY LEFT JOIN product_company_count USING (product)
GROUP BY company
ORDER BY company;

• uniqExact：比COUNT(DISTINCT)在ClickHouse中性能更优，适合精确去重统计
• sumIf：ClickHouse语法糖，等价于SUM(CASE WHEN ... THEN 1 ELSE 0 END)，代码更简洁
• ANY LEFT JOIN：避免重复匹配，提升查询效率

方法2：窗口函数实现 #

无需单独的聚合CTE，直接在子查询中标记每个产品的所属公司数量：

SELECT
    company,
    COUNT(product) AS total_products,
    SUM(CASE WHEN cnt = 1 THEN 1 ELSE 0 END) AS total_unique_products
FROM (
    SELECT
        company,
        product,
        COUNT(DISTINCT company) OVER (PARTITION BY product) AS cnt
    FROM company_products
)
GROUP BY company
ORDER BY company;

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