sql中的最大连续行
sql中的最大连续行
提问于 2021-10-07 07:45:51
我对SQL非常陌生。我有两张与两人网球比赛相关的表格
表1名称: matches # match信息
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
我需要找出哪个(S)玩家连续赢得了最多的比赛？
我尝试使用窗口函数执行此SQL查询，但无法成功
select
*,
count(tourney_id) over (partition by winner_id),
count(tourney_id) over (partition by tourney_date)
from
matches
order by
tourney_date提前感谢！
回答 1
Stack Overflow用户
发布于 2021-10-07 10:48:43
这是一种差距和岛屿问题，增加了复杂性，我们首先需要取消输赢的轴心，以便获得每个岛屿的开始。
我们可以使用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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69477267
复制相关文章
相似问题
腾讯云开发者
