如何在SQLite中基于uts条件统计每场比赛的历史对战次数

现有数据表定义如下：

CREATE TABLE "matches" (
    "match_id"  INTEGER,
    "uts"   INTEGER,
    "player_id" INTEGER,
    "opponent_id"   INTEGER
);

需求是获取每场比赛中，参赛选手与对手在该场比赛之前的对战次数，其中uts为时间戳，用于判断比赛先后顺序。

示例数据：

39943359    1678449600   1    2 
39943361    1678449600   3    4   
39943363    1678451400   5    6     
39943365    1678451400   5    6  
39943367    1678453200   5    6
39943369    1678453200   1    3
39943371    1678455000   1    2  
39943373    1678455000   1    5

预期结果：

39943359    0
39943361    0   
39943363    0     
39943365    1  
39943367    2
39943369    0
39943371    1  
39943373    0

现有SQL语句如下，需要添加uts条件以实现需求：

SELECT match_id, COUNT(*)
FROM matches
WHERE rowid NOT IN (
  SELECT MIN(rowid) 
  FROM matches 
  GROUP BY player_id, opponent_id
)

你原来的SQL逻辑存在两个问题：一是用rowid判断首次对战不符合需求（应该用时间戳uts确定比赛先后），二是未考虑对战的双向性（比如player_id=1, opponent_id=2和player_id=2, opponent_id=1属于同一组对战，需要合并统计）。

正确的做法是使用自连接，针对每场比赛，统计所有时间更早且**双方选手相同（不管顺序）**的比赛数量：

SELECT 
    m.match_id,
    COUNT(prev.match_id) AS prior_matches_count
FROM matches m
LEFT JOIN matches prev 
    ON (
        -- 匹配同一组对战（兼容两种选手顺序）
        (prev.player_id = m.player_id AND prev.opponent_id = m.opponent_id)
        OR (prev.player_id = m.opponent_id AND prev.opponent_id = m.player_id)
    )
    AND prev.uts < m.uts  -- 仅统计当前比赛之前的对战
GROUP BY m.match_id
ORDER BY m.match_id;

逻辑说明： #

• 自连接匹配对战：通过LEFT JOIN关联自身表，覆盖当前比赛双方选手的所有历史对战场景，包含两种选手顺序的情况。
• 时间筛选：通过prev.uts < m.uts确保只统计当前比赛发生之前的对战，完全符合需求中的时间顺序要求。
• 计数统计：对每个match_id分组，统计符合条件的历史对战数量，无历史对战时COUNT会返回0，与预期结果一致。

如果你的业务中需要严格区分选手顺序（即1vs2和2vs1算不同对战），可以去掉OR后面的条件，仅保留(prev.player_id = m.player_id AND prev.opponent_id = m.opponent_id)即可。

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