MySQL 5如何实现按user_id分组、依据前一行event值生成目标列

需求明确：按user_id分组，判断同一分组内当前行的上一行event值是否为0，是则第三列为1，否则为0，分组内第一行默认返回0。

前置说明 #

SQL表本身是无序的，所以必须存在一个可以明确标识行先后顺序的字段（比如自增主键id），才能保证取到的「上一行」和你Pandas中的行顺序一致，以下示例用自增主键id作为排序依据，你可以替换为自己的排序字段。

测试表构建 #

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    event INT
);
-- 插入示例数据
INSERT INTO test(user_id, event) VALUES
(1,1),
(1,0),
(1,1),
(2,1),
(2,0);

MySQL5 实现代码 #

MySQL5不支持窗口函数，使用用户变量实现逻辑：

SELECT 
    user_id,
    event,
    third_column
FROM (
    SELECT 
        user_id,
        event,
        CASE 
            -- 同一用户分组内，判断上一行event是否为0
            WHEN @prev_user = user_id THEN IF(@prev_event = 0, 1, 0)
            -- 新用户分组第一行，直接返回0
            ELSE 0
        END AS third_column,
        -- 更新变量存储当前行的user_id和event，供下一行判断使用
        @prev_user := user_id,
        @prev_event := event
    FROM test
    -- 初始化用户变量
    CROSS JOIN (SELECT @prev_user := NULL, @prev_event := NULL) AS init
    -- 必须排序：先按user_id分组，再按行顺序排序
    ORDER BY user_id, id
) AS tmp;

执行结果 #

和你提供的Pandas输出完全一致：

注意事项 #

• 务必保证ORDER BY子句的正确性，先按user_id排序，再按行顺序字段排序，否则会导致逻辑结果错误。
• 如果后续升级到MySQL 8+，可以直接用LAG窗口函数简化实现，代码如下：

SELECT 
    user_id,
    event,
    IF(LAG(event) OVER(PARTITION BY user_id ORDER BY id) = 0, 1, 0) AS third_column
FROM test
ORDER BY user_id, id;

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