如何使用SQLite查询玩家最后连续正面游程长度及相关衍生问题

问题A：获取指定玩家最后一次正面朝上的游程长度 #

可直接通过SQLite窗口函数实现，不需要拉取全量投币记录到应用层计算，查询语句如下（假设toss字段中正面对应true/1，反面对应false/0，和你Room返回List<Boolean>的逻辑一致）：

WITH ordered_toss AS (
    SELECT toss,
           ROW_NUMBER() OVER (ORDER BY auto_timestamp DESC) AS rn
    FROM outcome
    WHERE player = :player
),
first_non_head AS (
    SELECT COALESCE(
        MIN(rn) - 1,
        (SELECT COUNT(*) FROM ordered_toss)
    ) AS run_length
    FROM ordered_toss
    WHERE toss = 0
)
SELECT run_length FROM first_non_head;

如果你的toss字段存的是'H'/'T'字符串，只需将WHERE toss = 0修改为WHERE toss = 'T'即可。若玩家没有投币记录、或从未投出正面，查询返回0，可根据业务需要调整COALESCE的默认值。
对应Room DAO可以直接定义返回Integer类型：

@Query("WITH ordered_toss AS (SELECT toss, ROW_NUMBER() OVER (ORDER BY auto_timestamp DESC) AS rn FROM outcome WHERE player = :player), first_non_head AS (SELECT COALESCE(MIN(rn) - 1, (SELECT COUNT(*) FROM ordered_toss)) AS run_length FROM ordered_toss WHERE toss = 0) SELECT run_length FROM first_non_head")
Integer getLastHeadRunLength(String player);

问题B：计算指定玩家子集的最后一次正面游程长度最小值 #

先批量计算子集中所有玩家的游程长度，再取最小值即可：

WITH player_toss AS (
    SELECT player,
           toss,
           ROW_NUMBER() OVER (PARTITION BY player ORDER BY auto_timestamp DESC) AS rn
    FROM outcome
    WHERE player IN (:playerList)
),
player_run AS (
    SELECT p.player,
           COALESCE(
               MIN(CASE WHEN p.toss = 0 THEN p.rn - 1 END),
               (SELECT COUNT(*) FROM player_toss WHERE player = p.player)
           ) AS run_length
    FROM player_toss p
    GROUP BY p.player
)
SELECT MIN(run_length) AS min_run_length FROM player_run;

问题C：筛选指定玩家子集中最后一次正面游程长度小于m的玩家 #

在批量计算游程的基础上增加过滤条件即可：

WITH player_toss AS (
    SELECT player,
           toss,
           ROW_NUMBER() OVER (PARTITION BY player ORDER BY auto_timestamp DESC) AS rn
    FROM outcome
    WHERE player IN (:playerList)
),
player_run AS (
    SELECT p.player,
           COALESCE(
               MIN(CASE WHEN p.toss = 0 THEN p.rn - 1 END),
               (SELECT COUNT(*) FROM player_toss WHERE player = p.player)
           ) AS run_length
    FROM player_toss p
    GROUP BY p.player
)
SELECT player FROM player_run WHERE run_length < :m;

注意事项 #

1. 所有查询完全兼容你现有的outcome表结构，不需要修改字段定义。
2. 相比应用层循环统计，SQL直接计算的效率更高，尤其当玩家投币记录量较大时，不需要传输全量结果集到应用层，性能优势更明显。
3. 所有查询均兼容SQLite 3.25及以上版本（Room默认使用的SQLite版本均满足要求）。

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