例如，我有下一张桌子.
| user_id | event | action |
|---|---|---|
| 1111111 | name_1 | null |
| 1111111 | name_2 | null |
| 2222222 | name_1 | null |
| 2222222 | name_2 | null |
| 2222222 | name_3 | yes |
| 3333333 | name_1 | null |
| 3333333 | name_2 | null |
| 3333333 | name_3 | no |
我需要 Select ID和基于事件和操作的新列
因此，所需的输出:
| user_id | group |
|---|---|
| 1111111 | group_1 |
| 2222222 | group_3 |
| 3333333 | group_2 |
使用条件聚合方法，我们可以try :
SELECT
user_id,
CASE WHEN COUNT(DISTINCT event) = 3 AND
MAX(CASE WHEN event = 'name_3' THEN action END) = 'no' THEN 2
WHEN COUNT(DISTINCT event) = 3 AND
MAX(CASE WHEN event = 'name_3' THEN action END) = 'yes' THEN 3
WHEN COUNT(DISTINCT event) = 2 AND
COUNT(CASE WHEN event = 'name_1' THEN 1 END) > 0 AND
COUNT(CASE WHEN event = 'name_2' THEN 1 END) > 0 THEN 1 END AS "group"
FROM yourTable
GROUP BY user_id
ORDER BY user_id;
请注意，这种方法非常有效，因为它只需要对整个表进行一次遍历.