對于給定shopId，我正在嘗試計算符合所有coupons與給定關聯的唯一用戶的數量shop.
我有一個具有以下架構的表：
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
在下面的資料集上，如果我提供shopId18，我想獲得結果：
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;
uj5u.com熱心網友回復：
你可以嘗試COUNT使用DISTINCT
SELECT couponId ,COUNT(DISTINCT userId) count
FROM "table"
WHERE "shopId" = 18 AND status = 'qualified'
GROUP BY couponId
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/431106.html
標籤：sql PostgreSQL 通过...分组 数数 清楚的