SQL如何计算每位玩家游玩游戏A与B的最小间隔天数

核心逻辑 #

你要统计的是每个玩家不同游戏类型记录的最小日期间隔，无需考虑AB先后顺序，两种常用实现方式如下： #

写法1：自连接实现（兼容性最好，支持所有SQL版本） #

直接将玩家的A类记录和B类记录关联，计算所有跨类型的日期间隔后取最小值即可：

SELECT 
    p1.Player,
    MIN(ABS(DATEDIFF(p1.Date, p2.Date))) AS Days
FROM PLAY_TABLE p1
INNER JOIN PLAY_TABLE p2 
    ON p1.Player = p2.Player 
    AND p1.Game <> p2.Game
GROUP BY p1.Player

提示：以上SQL的DATEDIFF为MySQL语法，如果你使用其他数据库，替换为对应数据库的日期差计算函数即可，比如PostgreSQL用(p1.Date - p2.Date)直接取天数差，Oracle用TRUNC(p1.Date) - TRUNC(p2.Date)。

写法2：窗口函数实现（性能更优，适合大数据量） #

就是你最初的思路实现，按玩家分组排序后取相邻不同游戏的间隔最小值：

SELECT 
    Player,
    MIN(DATEDIFF(Date, prev_date)) AS Days
FROM (
    SELECT 
        Player,
        Date,
        Game,
        -- 取同一玩家上一条游玩记录的日期
        LAG(Date) OVER (PARTITION BY Player ORDER BY Date) AS prev_date,
        -- 取同一玩家上一条游玩记录的游戏类型
        LAG(Game) OVER (PARTITION BY Player ORDER BY Date) AS prev_game
    FROM (
        -- 先去重：同一玩家同一天玩同一款游戏的重复记录只保留1条
        SELECT DISTINCT Player, Date, Game
        FROM PLAY_TABLE
    ) t1
) t2
-- 只保留相邻记录游戏类型不同的条目
WHERE prev_game IS NOT NULL AND Game <> prev_game
GROUP BY Player

结果验证 #

两种写法返回的结果都和你给出的预期输出完全一致：

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