MYSQL如何统计相同userID两次出现在表中的行间隔数量

逻辑说明 #

• 第一步：先给全表所有记录按Datetime升序排序，生成全局唯一行号global_rn
• 第二步：按UserID分区，同样按Datetime升序排序，取每条用户记录上一次出现时对应的全局行号
• 第三步：用当前记录的全局行号减去上一次出现的全局行号再减1，即可得到两次记录之间的行间隔数，用户连续出现时间隔为0，和预期逻辑一致

实现SQL（适配Hive/Spark SQL/MySQL 8.0+等支持窗口函数的数据库） #

WITH t1 AS (
    -- 生成全表全局行号
    SELECT 
        UserID,
        Name,
        Datetime,
        ROW_NUMBER() OVER(ORDER BY Datetime ASC) AS global_rn
    FROM testing.tbl
),
t2 AS (
    -- 关联每个用户上一次出现的全局行号
    SELECT 
        UserID,
        global_rn,
        LAG(global_rn,1) OVER(PARTITION BY UserID ORDER BY global_rn ASC) AS last_global_rn
    FROM t1
)
-- 计算最终相邻间隔
SELECT 
    UserID,
    (global_rn - last_global_rn - 1) AS row_gap
FROM t2
WHERE last_global_rn IS NOT NULL
ORDER BY UserID, global_rn;

样例执行结果 #

对应提供的测试数据，执行后输出和预期完全匹配：

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