如何筛选仅存在login、scroll事件或两类事件兼有的用户？

需求说明 #

现有events表，包含customer_id、event_name、event_date字段，需要筛选出仅产生login事件、scroll事件，或同时产生这两类事件的用户（即用户无其他类型的事件）。

表结构与测试数据 #

CREATE TABLE events (
  customer_id INT,
  event_name VARCHAR(20),
  event_date DATE
);

INSERT INTO events (customer_id, event_name, event_date)
VALUES
  (1, 'click', '2023-04-17'),
  (2, 'login', '2023-04-17'),
  (3, 'scroll', '2023-04-18'),
  (4, 'login', '2023-04-16'),
  (5, 'click', '2023-04-15'),
  (1, 'scroll', '2023-04-14'),
  (2, 'click', '2023-04-13'),
  (3, 'login', '2023-04-12');

正确结果应返回customer_id为3和4的用户。

实现方案 #

你尝试的SQL逻辑本身是正确的，可能是测试环境或数据展示偏差导致结果不符合预期。以下提供几种更简洁且可靠的实现方式：

方法一：聚合筛选（推荐通用写法） #

通过分组后检查用户的所有事件是否都属于允许范围，且至少存在一个有效事件：

SELECT customer_id
FROM events
GROUP BY customer_id
-- 确保没有非login/scroll的事件
HAVING SUM(CASE WHEN event_name NOT IN ('login', 'scroll') THEN 1 ELSE 0 END) = 0
-- 确保至少有一个login或scroll事件
AND SUM(CASE WHEN event_name IN ('login', 'scroll') THEN 1 ELSE 0 END) > 0;

在支持布尔值直接运算的数据库（如MySQL）中，可简化为：

SELECT customer_id
FROM events
GROUP BY customer_id
HAVING SUM(event_name NOT IN ('login', 'scroll')) = 0
AND SUM(event_name IN ('login', 'scroll')) > 0;

方法二：NOT EXISTS子查询 #

使用关联子查询排除存在无效事件的用户，同时确保有有效事件，大数据量场景下性能更优：

SELECT DISTINCT e.customer_id
FROM events e
-- 排除存在非login/scroll事件的用户
WHERE NOT EXISTS (
    SELECT 1
    FROM events e2
    WHERE e2.customer_id = e.customer_id
    AND e2.event_name NOT IN ('login', 'scroll')
)
-- 确保至少有一个有效事件
AND EXISTS (
    SELECT 1
    FROM events e2
    WHERE e2.customer_id = e.customer_id
    AND e2.event_name IN ('login', 'scroll')
);

方法三：集合差运算（适用于支持EXCEPT的数据库） #

通过差集筛选出有有效事件且无无效事件的用户：

SELECT DISTINCT customer_id FROM events
WHERE event_name IN ('login', 'scroll')
EXCEPT
SELECT DISTINCT customer_id FROM events
WHERE event_name NOT IN ('login', 'scroll');

内容的提问来源于stack exchange，提问作者eeealesha