MySQL计算同一用户首次日期与后续非连续日期的差值

Hey George, 因为你使用的MySQL 5.6.34还不支持LAG()、ROW_NUMBER()这类窗口函数，所以我准备了两种兼容该版本的实现方案，都能满足你“同一user_id下计算当前行date与首次date的日期差，切换用户重置为0”的需求：

测试数据准备 #

先把你的测试表和数据用可执行的SQL还原出来，方便验证：

CREATE TABLE user_activity (
    date DATE,
    user_id INT,
    timestamp DATETIME
);

INSERT INTO user_activity VALUES
('2018-04-27', 181, '2018-04-27 08:28:33'),
('2018-04-28', 181, '2018-04-28 03:01:22'),
('2018-05-03', 181, '2018-05-03 03:04:48'),
('2018-05-04', 181, '2018-05-04 03:38:12'),
('2018-05-07', 181, '2018-05-07 05:03:57'),
('2018-05-08', 181, '2018-05-08 03:02:43'),
('2018-05-09', 181, '2018-05-09 00:19:39'),
('2018-05-10', 181, '2018-05-10 00:12:45'),
('2018-05-08', 183, '2018-05-08 01:46:18'),
('2018-05-09', 183, '2018-05-09 01:54:40'),
('2018-05-01', 184, '2018-05-01 00:15:25'),
('2018-05-03', 184, '2018-05-03 02:21:48'),
('2018-05-08', 184, '2018-05-08 02:40:42'),
('2018-05-09', 184, '2018-05-09 01:45:29');

方案一：关联子查询（简单易读，适合小数据集） #

这个方案的逻辑是先通过子查询获取每个user_id的首次出现日期，再用DATEDIFF()函数计算当前行日期与首次日期的差值：

SELECT
    ua.date,
    ua.user_id,
    ua.timestamp,
    DATEDIFF(ua.date, user_first.first_date) AS date_diff
FROM user_activity ua
JOIN (
    SELECT user_id, MIN(date) AS first_date
    FROM user_activity
    GROUP BY user_id
) user_first ON ua.user_id = user_first.user_id
ORDER BY ua.user_id, ua.date;

运行结果示例 #

方案二：用户变量（高效，适合大数据集） #

如果你的表数据量很大，关联子查询可能会有性能瓶颈，这时候可以用MySQL用户变量来跟踪当前用户和首次日期，避免重复查询：

SELECT
    date,
    user_id,
    timestamp,
    DATEDIFF(date, first_date) AS date_diff
FROM (
    SELECT
        date,
        user_id,
        timestamp,
        @first_date := CASE
            WHEN @prev_user != user_id THEN date
            ELSE @first_date
        END AS first_date,
        @prev_user := user_id
    FROM user_activity,
    (SELECT @prev_user := NULL, @first_date := NULL) vars
    ORDER BY user_id, date
) AS temp;

逻辑说明 #

1. 初始化两个变量@prev_user（记录上一行的user_id）和@first_date（记录当前用户的首次日期）
2. 按user_id和date排序，确保同一用户的记录按日期顺序处理
3. 当当前行的user_id和上一行不同时，更新@first_date为当前行的date；否则保持@first_date不变
4. 最后用DATEDIFF()计算差值

这个方案只需要扫描表一次，性能比关联子查询更优。

内容的提问来源于stack exchange，提问作者George C. Serban