mysql - 计算不同的和重复的mysql

Question

请问mysql中如何统计distinct和repetitions

例如表格看起来像这样

主表

id      product_id   purchaser_id  
1       1               1
2       1               1          
3       1               1 
4       1               2
 5      1               2
 6      1               3
 7      2               1  
 8      2              2 
9       2             2
10      2             2
11       2            2
12      2             3
13       2           3
14      2            3
15     2             3
16     2             4
17      2            4

使用这段代码

SELECT a.product_id,a.purchaser_id, 
       COUNT(DISTINCT a.purchaser_id) AS unique_purchasers, 
       COALESCE(c.totalCount,0) AS repeat_purchasers 
FROM main_table a 
    LEFT JOIN ( SELECT product_id, COUNT(totalCOunt) totalCount 
                FROM ( SELECT product_id, purchaser_id, COUNT(*) totalCOunt 
                        FROM main_table GROUP BY product_id, purchaser_id 
                        HAVING COUNT(*) > 1 ) s GROUP BY product_id ) c 
            ON a.product_id = c.product_id GROUP BY product_id, purchaser_id

结果是

product_id  purchaser_id    unique_purchasers   repeat_purchasers   
1   1   1   2
1   2   1   2
1   3   1   2
2   1   1   3
2   2   1   3
2   3   1   3
2   4   1   3

但结果应该是这样的(意思是如果没有重复则为0)

product_id  purchaser_id    unique_purchasers   repeat_purchasers   
1   1   1   2
1   2   1   1
1   3   1   0
2   1   1   0
2   2   1   3
2   3   1   3
2   4   1   1

谢谢

Answer 1

试试这个:

SELECT a.product_id,a.purchaser_id, 
       COUNT(DISTINCT a.purchaser_id) AS unique_purchasers,
       c.totalcount as repeat_purchasers
FROM main_table a 
INNER JOIN (SELECT product_id, purchaser_id, COUNT(*)-1 totalCOunt 
                        FROM main_table GROUP BY product_id, purchaser_id 
                ) C ON C.product_id=a.product_id AND a.purchaser_id=C.purchaser_id             
GROUP BY product_id, purchaser_id

结果:

PRODUCT_ID  PURCHASER_ID    UNIQUE_PURCHASERS   REPEAT_PURCHASERS
1           1               1                   2
1           2               1                   1
1           3               1                   0
2           1               1                   0
2           2               1                   3
2           3               1                   3
2           4               1                   1

在 Fiddle 中查看结果.