MySQL计数一列的唯一性，而另一列在分组依据中具有单个值

乔恩·罗宾逊

请参阅下表。我想对不同的访问者（visitor_id）进行计数，并按组（group_id）进行分组-但仅计算那些始终'declined'为该访问者带来结果的访问者。

就像是

SELECT group_id, COUNT(DISTINCT visitor_id) AS always_declines
FROM customer_actions 
WHERE outcome='declined' [[AND HAS NEVER BEEN IN ('purchased')]]
GROUP BY group_id;

这是我的桌子的简化版：

SELECT * FROM customer_actions;
+----+------------+-----------+----------+
| id | visitor_id | outcome   | group_id |
+----+------------+-----------+----------+
|  1 |          5 | purchased |        1 |
|  2 |          5 | purchased |        1 |
|  3 |          6 | purchased |        1 |
|  4 |          7 | declined  |        1 |
|  5 |          6 | declined  |        1 |
|  6 |          7 | purchased |        1 |
|  7 |          8 | declined  |        1 |
|  8 |          8 | declined  |        1 |
+----+------------+-----------+----------+
8 rows in set (0.00 sec)

因此，基本上，如果结果奏效，我正在寻找返回的第一行和唯一行（在这种情况下）：

group_id = 1

always_declines = 1（对应于仅拒绝访问的访客8）

戈登·利诺夫（Gordon Linoff）

解决此问题的一种方法是两个聚合。首先，按组和访问者进行汇总，以找到合适的访问者。然后计算剩余的行：

SELECT group_id, count(*) AS always_declines
FROM (SELECT group_id, visitor_id
      FROM customer_actions 
      GROUP BY group_id, visitor_id
      HAVING SUM(outcome <> 'declined') = 0
     ) gv
GROUP BY group_id;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。