如何用SQL计算用户首条与第二条记录的时间差？求助实现

问题描述 #

想要计算用户首条记录与第二条记录之间的时间差，思路是为每条记录添加排名（RN），再用RN=2的记录时间减去RN=1的记录时间，但无法通过子查询实现该计算。

示例数据 #

已编写的SQL代码 #

SELECT 
  user_id,
  date,
  row_number() over(partition by user_id order by date) as RN
FROM event_table
GROUP BY user_id, date

解决方案 #

方法一：使用LEAD窗口函数（更简洁） #

通过LEAD窗口函数直接获取同一用户的下一条记录日期，结合排名筛选首条记录，即可计算时间差：

SELECT 
  user_id,
  date AS first_record_date,
  LEAD(date, 1) OVER (PARTITION BY user_id ORDER BY date) AS second_record_date,
  -- 这里用分钟为单位，可替换为SECOND/HOUR/DAY等
  TIMESTAMPDIFF(MINUTE, date, LEAD(date, 1) OVER (PARTITION BY user_id ORDER BY date)) AS time_diff_minutes
FROM (
  SELECT 
    user_id,
    date,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS RN
  FROM event_table
) ranked_records
WHERE RN = 1
AND second_record_date IS NOT NULL; -- 排除只有一条记录的用户

方法二：子查询筛选后关联 #

分别筛选出排名为1和2的记录，通过user_id关联后计算时间差：

SELECT 
  t1.user_id,
  t1.date AS first_record_date,
  t2.date AS second_record_date,
  TIMESTAMPDIFF(MINUTE, t1.date, t2.date) AS time_diff_minutes
FROM (
  SELECT user_id, date
  FROM (
    SELECT 
      user_id,
      date,
      ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS RN
    FROM event_table
  ) t
  WHERE RN = 1
) t1
INNER JOIN (
  SELECT user_id, date
  FROM (
    SELECT 
      user_id,
      date,
      ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS RN
    FROM event_table
  ) t
  WHERE RN = 2
) t2 ON t1.user_id = t2.user_id;

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