MySQL select since sum >= 3?

Question

表:

+++++++++++++++++++++++++
+ id | event | group_id +
+  1 |  '+1' |        1 +
+  2 |  'pt' |        1 +
+  3 |  'pt' |        1 +
+  4 |  '+1' |        1 +
+  5 |  'pt' |        1 +
+  6 |  '+1' |        1 +
+  7 |  'pt' |        1 +
+  8 |  '+1' |        1 +
+++++++++++++++++++++++++

我需要选择 SUM(CASE WHEN event = '+1' THEN 1 ELSE 0 END) 因为 SUM(CASE WHEN event = 'pt' THEN 1 ELSE 0 END) = 3。

我试过这个:

SELECT group_id, SUM(CASE WHEN event = '+1' THEN 1 ELSE 0 END) AS event_sum
FROM Table
WHERE SUM(CASE WHEN event = 'pt' THEN 1 ELSE 0 END) >= 3
ORDER BY id
GROUP BY group_id

但这不是 group 函数的工作方式，那么我怎样才能实现这个输出呢？当条件 SUM(CASE WHEN event = 'pt' THEN 1 ELSE 0 END) = 3 为真时，我是否需要使用用户变量来选择 id？我相信没有用户变量也可以做到这一点，这可能吗？

期望的输出:

++++++++++++++++++++++++
+ group_id | event_sum +
+        1 |         2 +
++++++++++++++++++++++++

Answer 1

您想要所有 +1 事件的数量自您获得至少 3 个“pt”事件的那一刻起。

对于“since”，我猜您想按 ID 对事件进行排序。

对于 pt，您需要一个运行总计。要在 MySQL 中实现这一点，Stack Overflow 上有几个答案(this 是一个)。

SET @pts:=0;
SELECT
   *,
   (@pts := @pts + (CASE WHEN event='pt' THEN 1 ELSE 0)) AS pts
FROM Table 
ORDER BY id;

但是您希望在每次更改 groupid 时重置计数，因此:

SET @pts:=0,@gid:=-1;
SELECT *,
    (@pts := IF (@gid != groupid, 0, @pts + IF(event='pt', 1, 0))) AS pts, 
    @gid:=groupid
FROM eventi ORDER BY groupid, id;

因此在每个循环中您保存当前的 groupid 值，但在这样做之前，您检查它是否相同。如果不是，那么您将积分归零。

+------+-------+---------+------+---------------+
| id   | event | groupid | pts  | @gid:=groupid |
+------+-------+---------+------+---------------+
|    1 | pt    |       1 |    0 |             1 |
|    2 | pt    |       1 |    1 |             1 |
|    3 | +1    |       1 |    1 |             1 |
|    4 | pt    |       1 |    2 |             1 |
|    5 | +1    |       1 |    2 |             1 |
|    6 | +1    |       1 |    2 |             1 |
|    7 | pt    |       2 |    0 |             2 |
|    8 | pt    |       2 |    1 |             2 |
|    9 | +1    |       2 |    1 |             2 |
|   10 | pt    |       2 |    2 |             2 |
|   11 | +1    |       2 |    2 |             2 |
|   12 | +1    |       2 |    2 |             2 |
+------+-------+---------+------+---------------+

从这里您可以看到实际 pts 的数量相差 1(检查的顺序与递增的顺序相反)。

现在你可以进行分组了:

SET @pts:=0,@gid:=-1; 
SELECT groupid, SUM(IF(pts >= 3 AND event='+1', 1,0)) AS event_sum FROM (
    SELECT *,
       (@pts := IF(@gid!=groupid, 0, @pts+IF(event='pt',1,0)))+1 AS pts,
       @gid:=groupid
    FROM eventi ORDER BY groupid, id 
) AS a GROUP BY groupid;


+---------+-----------+
| groupid | event_sum |
+---------+-----------+
|       1 |         2 |
|       2 |         2 |
+---------+-----------+

您也可以在同一个查询中合并 SET:

SELECT ...  AS a, (SELECT @pts:=0,@gid:=-1) AS i GROUP BY groupid;

这是一个 test SQLfiddle .