计算字段的不同出现次数，按另一个字段分组

Question

对于给定的 shopId，我正在尝试计算有资格获得与给定商店关联的所有优惠券的唯一用户数量。

我有具有以下架构的表：

id |    type   | orderId | userId | couponId |  status   | shopId |                
----+------------------+---------+--------+---------+-----------+-----
 39 | purchase |      89 |     33 |       1 | qualified |           18 
 43 | purchase |      90 |      5 |       3 | qualified |           18 
  1 | purchase |      68 |     32 |       1 | qualified |           18 
  2 | purchase |      69 |     32 |       3 | qualified  |           18 
  3 | purchase |      70 |     32 |       3 | qualified  |           18 
  4 | purchase |      71 |     38 |       1 | redeemed  |           18 
  5 | purchase |      72 |     39 |       2 | qualified  |           18 
  6 | purchase  |      73 |     30 |      9 | redeemed  |           11 

在下面的数据集中，如果我提供 shopId 18，我想获得结果：

couponId | count 
1           2    (2 unique users (33, 32) qualified for coupon 1)
2           2    (1 unique user (39))
3           2    (2 unique users (5,32)

下面的查询允许我计算有资格获得优惠券的不同用户的总数对于给定的商店，但我如何通过优惠券进一步细分 ID？

SELECT COUNT(*) FROM (SELECT DISTINCT "userId" FROM "table" WHERE "shopId" = 18 AND status = 'qualified') AS temp;

Answer 1

您可以尝试将 COUNT 与 DISTINCT 一起使用

SELECT couponId ,COUNT(DISTINCT userId) count 
FROM "table" 
WHERE "shopId" = 18 AND status = 'qualified'
GROUP BY couponId