如何通过SQL从交易平台用户行为表中识别弃置SEARCH操作？

要找出所有弃置的SEARCH操作，核心思路是先将用户的操作按LOGIN会话分组（即从一次LOGIN到下一次LOGIN之间的所有操作），再判断每个会话内是否存在ORDER操作——若不存在，该会话内的所有SEARCH即为弃置操作。

以下是高效且逻辑清晰的SQL实现：

WITH user_sessions AS (
    SELECT 
        customer_id,
        action,
        request_time,
        -- 按用户分组、时间排序，累计LOGIN次数作为会话ID
        SUM(CASE WHEN action = 'LOGIN' THEN 1 ELSE 0 END) OVER (
            PARTITION BY customer_id 
            ORDER BY request_time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_id
    FROM action_table
),
session_order_check AS (
    SELECT 
        customer_id,
        session_id,
        -- 标记会话是否包含ORDER操作：有则为1，无则为0
        MAX(CASE WHEN action = 'ORDER' THEN 1 ELSE 0 END) AS has_order
    FROM user_sessions
    GROUP BY customer_id, session_id
)
-- 统计每个用户的弃置SEARCH数量
SELECT 
    us.customer_id,
    COUNT(*) AS abandoned_search_count
FROM user_sessions us
JOIN session_order_check soc 
    ON us.customer_id = soc.customer_id 
    AND us.session_id = soc.session_id
WHERE us.action = 'SEARCH' 
    AND soc.has_order = 0
GROUP BY us.customer_id;

逻辑说明 #

1. 会话分组（user_sessions）：
   使用窗口函数SUM() OVER()对每个用户的操作按时间排序，累计LOGIN的次数生成唯一session_id。每次LOGIN会让session_id加1，确保同一次登录会话内的所有操作共享同一个ID。

2. 会话ORDER检查（session_order_check）：
   按用户和会话分组，用MAX(CASE...)判断该会话是否存在ORDER操作。存在则has_order为1，否则为0。

3. 筛选弃置SEARCH：
   关联两个CTE，筛选出属于无ORDER会话的SEARCH操作，最终按用户统计数量。

扩展：查看具体弃置SEARCH记录 #

如果需要列出所有弃置SEARCH的详细信息，修改最后一段SQL即可：

SELECT us.*
FROM user_sessions us
JOIN session_order_check soc 
    ON us.customer_id = soc.customer_id 
    AND us.session_id = soc.session_id
WHERE us.action = 'SEARCH' 
    AND soc.has_order = 0;

结果验证 #

针对示例数据，执行后会得到：

完全符合预期（用户A的会话包含ORDER，无弃置SEARCH；用户B的两个会话均无ORDER，共3次弃置SEARCH）。

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