如何计算每位用户历次旅行之间的最大与最小间隔天数？

要计算每位用户每次旅行之间的最大和最小间隔天数，核心是先整理出每个用户的所有旅行日期，再计算相邻日期的间隔，最后聚合统计。以下是具体实现步骤和SQL代码：

步骤说明 #

1. 拆分用户旅行记录：Travels表中每条记录对应两位用户的旅行，需要将user1和user2的记录分别提取，合并成每个用户的旅行日期列表。
2. 计算相邻旅行间隔：使用窗口函数LAG()获取每个用户上一次旅行的日期，计算两次旅行的天数差。
3. 聚合统计间隔极值：对每个用户的间隔天数求最小值和最大值，关联People表得到用户名。

完整SQL代码 #

WITH user_travel_dates AS (
    -- 合并所有用户的旅行日期（包含user1和user2）
    SELECT user1 AS user_id, date FROM Travels
    UNION ALL
    SELECT user2 AS user_id, date FROM Travels
),
user_travel_intervals AS (
    -- 计算每个用户相邻旅行的间隔天数
    SELECT 
        user_id,
        TIMESTAMPDIFF(day, LAG(date) OVER (PARTITION BY user_id ORDER BY date), date) AS interval_days
    FROM user_travel_dates
)
-- 统计每个用户的最小/最大间隔天数，关联用户表获取名称
SELECT 
    p.id,
    p.user,
    MIN(ut.interval_days) AS min_interval_days,
    MAX(ut.interval_days) AS max_interval_days
FROM People p
LEFT JOIN user_travel_intervals ut ON p.id = ut.user_id
WHERE ut.interval_days IS NOT NULL  -- 排除首次旅行（无前置间隔）
GROUP BY p.id, p.user
ORDER BY p.id;

对原有代码问题的说明 #

• 未处理user2的旅行记录：原有SQL只关联了错误字段t1.PERSON_1（应为user1），遗漏了用户作为user2参与的旅行。
• 关联逻辑错误：直接将People表和子查询关联但未建立正确的关联条件，导致结果不符合预期。
• 未按用户拆分旅行日期：没有先整理每个用户的完整旅行时间线，无法准确计算相邻间隔。

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