sql中最大连续行数

Question

我是SQL新手。我有两张与两个人有关的桌子网球比赛

表1 Name: matches # match information

tourney_id  tourney_name    surface tourney_date  mid winnner_id  loser_id  score   
-------------------------------------------------------------------------------------
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  270   201504  201595  "5-7 6-1 6-3"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  271   201514  201426  "6-3 6-4"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  272   201697  211901  "6-2 7-6(4)"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  273   201620  211539  "6-1 6-0"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  274   214981  203564  "6-2 7-6(2)"

表2:球员#信息

playerid  First Name    Last Name   Country
--------------------------------------------
200001     Martina      Hingis        SUI
200002     Mirjana      Lucic         CRO
200003     Justine      Henin         BEL

可以找到完整的表内容table1-matches

我需要找出哪位玩家连续赢得最多的比赛?

我使用窗口函数执行了这个SQL查询，但是没有成功

select 
*,
count(tourney_id) over (partition by winner_id),
count(tourney_id) over (partition by tourney_date)
from 
matches
order by 
tourney_date

提前感谢!

Answer 1

这是一种缺口和岛屿问题，我们首先需要调整输赢，以便获得每个岛屿的起点。

我们可以使用LAG来检查每个球员之前的比赛。然后窗口计数为我们提供每组获胜的数字。

WITH PrevValues AS (
SELECT
v.status,
v.id,
m.tourney_date,
StartOfGroup = CASE WHEN LAG(v.status, 1, '.')
OVER (PARTITION BY v.id ORDER BY m.tourney_date) <> v.status THEN 1 END
FROM matches m
CROSS APPLY (VALUES
('W', winnner_id),
('L', loser_id)
) v(status, id)
),
Groups AS (
SELECT *,
GroupId = COUNT(*) OVER (PARTITION BY v.id
ORDER BY m.tourney_date ROWS UNBOUNDED PRECEDING)
FROM PrevValues v
WHERE v.status = 'W'
),
PerGroup AS (
SELECT
v.id,
TotalWins = COUNT(*)
FROM Groups g
GROUP BY
g.id,
g.GroupId
)
SELECT
p.id,
MaxConsecutiveWins = MAX(TotalWins)
FROM PerGroup p
GROUP BY
p.id;