如何按分组筛选：找出含product无purchase的session_id与user_id

需求说明 #

需要从数据集中筛选出**包含product类型event_type但未包含purchase类型event_type**的session_id及其对应的user_id。

原始数据集 #

user_id session_id  event_type
59378   00000e74-6a4d-495d-aab5-2ac51165b34a    cart
59378   00000e74-6a4d-495d-aab5-2ac51165b34a    department
59378   00000e74-6a4d-495d-aab5-2ac51165b34a    purchase
59378   00000e74-6a4d-495d-aab5-2ac51165b34a    product
43155   00001e3b-f089-4907-8f8a-09951de4de27    product
43155   00001e3b-f089-4907-8f8a-09951de4de27    cart
43155   00001e3b-f089-4907-8f8a-09951de4de27    department
43155   00001e3b-f089-4907-8f8a-09951de4de27    home
66131   0000bc7a-b34f-484f-a534-e9ffaacbb0c7    department
66131   0000bc7a-b34f-484f-a534-e9ffaacbb0c7    cart
66131   0000bc7a-b34f-484f-a534-e9ffaacbb0c7    product
66131   0000bc7a-b34f-484f-a534-e9ffaacbb0c7    home
66131   0000bc7a-b34f-484f-a534-e9ffaacbb0c7    purchase
83743   0001f1a2-d817-4639-8f4b-1949a7482af4    department
83743   0001f1a2-d817-4639-8f4b-1949a7482af4    product
83743   0001f1a2-d817-4639-8f4b-1949a7482af4    cart
3493    0002d27b-eebb-4715-aac6-9d9f938e52a8    cart
3493    0002d27b-eebb-4715-aac6-9d9f938e52a8    department
3493    0002d27b-eebb-4715-aac6-9d9f938e52a8    product
3493    0002d27b-eebb-4715-aac6-9d9f938e52a8    purchase
7068    00033390-0e62-4540-b2d2-8fc2085f74ef    department
7068    00033390-0e62-4540-b2d2-8fc2085f74ef    product
7068    00033390-0e62-4540-b2d2-8fc2085f74ef    cart
21834   00035b69-ed84-499e-bc0f-89ecfea2a731    product
21834   00035b69-ed84-499e-bc0f-89ecfea2a731    cart
21834   00035b69-ed84-499e-bc0f-89ecfea2a731    department

期望结果 #

user_id session_id 
43155   00001e3b-f089-4907-8f8a-09951de4de27
83743   0001f1a2-d817-4639-8f4b-1949a7482af4
7068    00033390-0e62-4540-b2d2-8fc2085f74ef
21834   00035b69-ed84-499e-bc0f-89ecfea2a731

解决方案 #

方法1：分组条件聚合 #

通过按user_id和session_id分组，统计每个分组内product和purchase事件的数量，筛选出符合条件的分组：

SELECT user_id, session_id
FROM your_table_name
GROUP BY user_id, session_id
HAVING COUNT(CASE WHEN event_type = 'product' THEN 1 END) > 0
   AND COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) = 0;

方法2：NOT EXISTS子查询 #

先定位所有包含product事件的记录，再排除掉对应会话存在purchase事件的情况，用DISTINCT去重：

SELECT DISTINCT user_id, session_id
FROM your_table_name t1
WHERE event_type = 'product'
  AND NOT EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t2.session_id = t1.session_id
      AND t2.event_type = 'purchase'
  );

两种方法都能得到符合要求的结果，可根据数据集规模选择：分组聚合适合数据量中等的场景，NOT EXISTS在大表中配合索引性能更优。

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