计数不同于sql聚合

cvxl0en2 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(305)

我有一张这样的table：

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\ U 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;

sql mysql count Database pivot

来源：https://stackoverflow.com/questions/63157806/count-distinct-from-sql-aggregation

2条答案

按热度按时间

pcrecxhr1#

可以将条件聚合与 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;

赞(0）回复(0）举报 2021-07-27

ecfdbz9o2#

我认为您需要两个级别的条件聚合：

select 
    store_id,
    avg(has_a = 1 and has_b = 0) pct_sales_a,
    avg(has_a = 0 and has_b = 1) pct_sales_b,
    avg(has_a + has_b = 2) pct_sales_ab
from (
    select 
        store_id, 
        cust_id,
        max(indicator = 'A') has_a,
        max(indicator = 'B') has_b
    from t1
    group by store_id, cust_id
) t
group by store_id

db小提琴演示：

store_id | pct_sales_a | pct_sales_b | pct_sales_ab
-------: | ----------: | ----------: | -----------:
       1 |      1.0000 |      0.0000 |       0.0000
       2 |      0.0000 |      0.5000 |       0.5000

赞(0）回复(0）举报 2021-07-27

我来回答

计数不同于sql聚合

2条答案

相关问题

热门标签

最新问答