sql - 找出每位玩家最长的连续满分

Question

我在 PostgreSQL 数据库中使用 ORDER BY player_id ASC, time ASC 查询 SELECT 得到以下结果:

player_id  points  time

395        0       2018-06-01 17:55:23.982413-04
395        100     2018-06-30 11:05:21.8679-04
395        0       2018-07-15 21:56:25.420837-04
395        100     2018-07-28 19:47:13.84652-04
395        0       2018-11-27 17:09:59.384-05
395        100     2018-12-02 08:56:06.83033-05
399        0       2018-05-15 15:28:22.782945-04
399        100     2018-06-10 12:11:18.041521-04
454        0       2018-07-10 18:53:24.236363-04
675        0       2018-08-07 20:59:15.510936-04
696        0       2018-08-07 19:09:07.126876-04
756        100     2018-08-15 08:21:11.300871-04
756        100     2018-08-15 16:43:08.698862-04
756        0       2018-08-15 17:22:49.755721-04
756        100     2018-10-07 15:30:49.27374-04
756        0       2018-10-07 15:35:00.975252-04
756        0       2018-11-27 19:04:06.456982-05
756        100     2018-12-02 19:24:20.880022-05
756        100     2018-12-04 19:57:48.961111-05

我试图找到每个球员的最长连胜纪录，其中 points = 100，决胜局是最近开始的连胜纪录。我还需要确定该球员的最长连胜开始时间。预期结果将是:

player_id  longest_streak  time_began

395        1               2018-12-02 08:56:06.83033-05
399        1               2018-06-10 12:11:18.041521-04
756        2               2018-12-02 19:24:20.880022-05

Answer 1

A gaps-and-islands确实有问题。

假设:

• “连胜”不会被其他玩家的行打断。
• 所有列都定义为 NOT NULL。 (否则你必须做更多。)

这应该是最简单和最快的，因为它只需要两个快速 row_number() window functions :

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;

db<> fiddle here

使用列名 ts 而不是 time，这是一个 reserved word在标准 SQL 中。它在 Postgres 中是允许的，但有限制，将它用作标识符仍然是一个坏主意。

“技巧”是减去行号，使连续的行落在每个 (player_id, points) 的同一组 (grp) 中。 然后筛选出 100 分的分数，按组汇总并仅返回每个玩家最长、最近的结果。
该技术的基本解释:

• Select longest continuous sequence

我们可以在同一个SELECT中使用GROUP BY和DISTINCT ON，应用GROUP BY之前 DISTINCT ON。考虑 SELECT 查询中的事件序列:

• Best way to get result count before LIMIT was applied

关于 DISTINCT ON:

• Select first row in each GROUP BY group?