查找高于特定值的连续行

Question

我有一个体育统计数据库。在其中一张表格中，我有每个玩家的逐场比赛统计数据。

PK, PlayerID, OpponentID, GameID, Points, Rebounds, etc...

我想知道如何返回查询，例如，大多数连续比赛至少得到 20 分或连续比赛有 10 个篮板等......(我还有许多其他表格也适用，仅以此为例。

GameID 按时间顺序排列，因此这是确定连续游戏的方法。

我认为这涉及CTE，但我不精通该主题。

Answer 1

您正在寻找实现差距和孤岛问题解决方案的查询。

你的问题很笼统，所以让我给你举一个这样的查询的例子，比如：找到连续20+分比赛最多的球员;还要找出系列赛的第一场和最后一场比赛，以及最高/最低分。

下面是用于此目的的查询：

select top 1 with ties
PlayerID,
min(GameID) first_game,
max(GameID) last_game,
min(Points) min_points,
max(Points) max_points,
count(*) consecutive_games
from (
select
s.*,
row_number() over(partition by PlayerID order by GameID) rn,
sum(case when Points >= 20 then 1 else 0 end) over(partition by PlayerID order by GameID) sm
from PlayerStats s
) x
where Points >= 20
group by PlayerID, rn - sm
order by consecutive_games desc;

这通过执行按游戏排序的条件和(每个游戏递增超过 20 分(并将其与游戏序列进行比较来工作。当总和和游戏顺序之间的差异发生变化时，一组新的游戏开始;其余的只是聚合。您可以直接运行子查询以查看它返回的内容;您还可以删除top 1子句以查看+20点游戏系列的完整列表(。

使用此示例数据：

玩家编号 |游戏编号 |点 -------: |-----: |-----:  1 |     1 |    10  1 |     2 |    25  1 |     3 |    24  1 |     4 |    32  1 |     5 |     2  1 |     6 |    27  1 |     7 |    42  1 |     8 |    32  1 |     9 |    21  1 |    10 |    20

查询返回：

玩家ID | first_game | last_game | min_points | max_points | consecutive_games -------: |---------: |--------: |---------: |---------: |----------------:  1 |         6 |       10 |        20 |        42 |                5

您应该能够将相同的逻辑应用于其他统计数据。

DB小提琴上的演示