sql - 找到每个球员最长的连续得分
问题描述
我从PostgreSQL 数据库中的SELECT
查询得到以下结果:ORDER BY player_id ASC, time ASC
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
解决方案
确实是一个差距和孤岛问题。
假设:
- “连胜”不会被其他玩家的行打断。
- 所有列都已定义
NOT NULL
。(否则你必须做更多。)
这应该是最简单和最快的,因为它只需要两个快速row_number()
窗口函数:
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<>在这里摆弄
使用列名ts
而不是标准 SQLtime
中的保留字。它在 Postgres 中是允许的,但有一些限制,将它用作标识符仍然是个坏主意。
“诀窍”是减去行号,以便连续的行属于同一组 ( grp
) per (player_id, points)
。然后过滤具有 100 分的那些,按组汇总并仅返回每个玩家最长、最近的结果。
该技术的基本解释:
我们可以在同样的情况下使用GROUP BY
and ,是之前应用的。考虑查询中的事件序列:DISTINCT ON
SELECT
GROUP BY
DISTINCT ON
SELECT
关于DISTINCT ON
:
推荐阅读
- python - 通过子进程调用命令行工具找到我的瓶颈?
- node.js - 使用 prisma graphql api 在嵌套 js 中进行 e2e 测试
- c - 函数 free() 不会从向量中释放内存
- arrays - 两个条件的索引匹配,然后排除以前返回的值
- javascript - 经纬度变量提取问题
- telnet - 可以通过 SSH Telnet 登录到电子邮件服务器吗?我收到身份验证错误
- python - 你可以使用 pandas groupby 对行进行分组,通过对列值求和来确定吗?
- android-studio - Android Studio:选择了组件,但属性编辑器显示“未选择组件”
- php - 当我尝试使用 symfony 序列化程序序列化对象时,我得到了一些 emty 字段(created_at 和 updated_at)?
- html - Div 没有被放置在彼此旁边