mysql - 计数与 SQL 聚合不同

Question

我有一张看起来像这样的表:

store_id   cust_id   amount    indicator
1          1000      2.05      A
1          1000      3.10      A
1          2000      3.10      A
2          1000      5.10      B
2          2000      6.00      B
2          1000      1.05      A

我想要做的是通过仅查看唯一的客户 ID(即，在商店 1 向客户 1000 的两次销售只计算一次)，找到每个商店的指标 A、B 的销售额百分比。像这样的东西:

store_id   pct_sales_A   pct_sales_B   pct_sales_AB
1          1.0           0.00          0.00
2          0.0           0.50          0.50

我知道我可以使用子查询来查找每种交易类型的计数，但我无法仅计算不同的客户 ID。这是 pct_sales_A 列的(不正确的)方法:

SELECT
     store_id,
     COUNT(DISTINCT(CASE WHEN txns_A>0 AND txns_B=0 THEN cust_ID ELSE NULL))/COUNT(*) AS pct_sales_A --this is wrong
     FROM (SELECT store_id, cust_id,
           COUNT(CASE WHEN indicator='A' THEN amount ELSE 0 END) as txns_A,
           COUNT(CASE WHEN indicator='B' THEN amount ELSE 0 END) as txns_B
           FROM t1
           GROUP BY store_id, cust_id
           )
     GROUP BY store_id;

Answer 1

您可以通过 COUNT(DISTINCT) 使用条件聚合:

SELECT store_id,
       COUNT(DISTINCT CASE WHEN indicator = 'A' THEN cust_id END) * 1.0 / COUNT(DISTINCT cust_id) as ratio_a,
       COUNT(DISTINCT CASE WHEN indicator = 'B' THEN cust_id END) * 1.0 / COUNT(DISTINCT cust_id) as ratio_a,
FROM t1
GROUP BY store_id;

根据您的评论，您需要两个级别的聚合:

SELECT store_id,
       AVG(has_a) as ratio_a,
       AVG(has_b) as ratio_b,
       AVG(has_a * has_b) as ratio_ab
FROM (SELECT store_id, cust_id,
             MAX(CASE WHEN indicator = 'A' THEN 1.0 ELSE 0 END) as has_a,
             MAX(CASE WHEN indicator = 'B' THEN 1.0 ELSE 0 END) as has_b
      FROM t1
      GROUP BY store_id, cust_id
     ) sc
GROUP BY store_id;