A 间隙和岛屿 /questions/tagged/gaps-and-islands确实有问题。
假设：
- “连胜”不会被其他玩家的行打断。
- 所有列均已定义
NOT NULL。 （否则你必须做更多的事情。）
这应该是最简单和最快的，因为它只需要两个快速row_number()窗函数 https://www.postgresql.org/docs/current/functions-window.html:
SELECT DISTINCT ON (player_id)
player_id, count(*) AS seq_len, min(ts) AS time_began
FROM (
SELECT player_id, points, ts
, row_number() OVER (PARTITION BY player_id ORDER BY ts)
- row_number() OVER (PARTITION BY player_id, points ORDER BY ts) AS grp
FROM tbl
) sub
WHERE points = 100
GROUP BY player_id, grp -- omit "points" after WHERE points = 100
ORDER BY player_id, seq_len DESC, time_began DESC;
数据库小提琴
使用列名ts代替time，这是一个保留字 https://www.postgresql.org/docs/current/sql-keywords-appendix.html在标准 SQL 中。它在 Postgres 中是允许的，但有限制，并且使用它作为标识符仍然是一个坏主意。
“技巧”是减去行号，以便连续的行属于同一组（grp) per (player_id, points). Then过滤 100 分的分数，按组汇总并仅返回每个玩家最长、最新的结果。
该技术的基本解释：
- 选择最长的连续序列 https://dba.stackexchange.com/a/35389/3684
我们可以用GROUP BY and DISTINCT ON在相同的SELECT, GROUP BY被申请;被应用before DISTINCT ON。考虑事件的顺序SELECT query:
- 在应用 LIMIT 之前获取结果计数的最佳方法 https://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied/8242764#8242764
About DISTINCT ON:
- 选择每个 GROUP BY 组中的第一行？ https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564
