如何通过子查询与分组统计各品类下带标记客户数及对应总客户数

问题根因 #

现有SQL中的子查询未和外层的category字段做关联，属于独立的全局统计逻辑，所以每次返回的都是全表所有flag为'y'的客户总数，无法按品类拆分统计结果。该需求不需要嵌套子查询实现，使用条件聚合写法逻辑更简洁，执行效率也更高。

推荐实现方案（条件聚合） #

SELECT 
    p.category,
    COUNT(DISTINCT CASE WHEN c.flag = 'y' THEN c.customer_id END) AS flagged_customer_count,
    COUNT(DISTINCT c.customer_id) AS total_customer_count
FROM Product p
LEFT JOIN `Orders` o ON p.product_id = o.product_id
LEFT JOIN `Customer` c ON o.customer_id = c.customer_id
GROUP BY p.category;

执行结果 #

基于你提供的测试数据，执行上述SQL后返回结果如下：

逻辑说明 #

• 条件聚合利用COUNT函数忽略NULL值的特性，通过CASE WHEN筛选出flag为'y'的客户参与计数
• 添加DISTINCT是为了避免同一客户购买同品类多个商品时被重复统计，符合客户数的统计要求；如果业务场景需要按购买次数统计，直接去掉DISTINCT即可

子查询实现方案（不推荐） #

如果确实需要通过子查询实现，需要在子查询中关联外层的品类字段，写法如下：

SELECT 
    p.category,
    (SELECT COUNT(DISTINCT c2.customer_id) 
     FROM `Customer` c2
     INNER JOIN `Orders` o2 ON o2.customer_id = c2.customer_id
     INNER JOIN `Product` p2 ON p2.product_id = o2.product_id
     WHERE p2.category = p.category AND c2.flag = 'y') AS flagged_customer_count,
    COUNT(DISTINCT c.customer_id) AS total_customer_count
FROM Product p
LEFT JOIN `Orders` o ON p.product_id = o.product_id
LEFT JOIN `Customer` c ON o.customer_id = c.customer_id
GROUP BY p.category;

该写法需要多次重复关联表，执行效率低于条件聚合方案，仅做参考。
内容的提问来源于stack exchange，提问作者Matt Schnelker