在 MySQL 中计算跨年的运行或连续 [英] Calculate run or streak across years in MySQL
问题描述
我正在尝试计算 MySQL 数据库中的连胜纪录.我创建了下表来存储赢/输数据:
I am trying to calculate winning streaks in a MySQL DB. I created the following table to store the win/loss data:
"year" "team_id" "week" "result"
"2007" "1" "1" "W"
"2007" "1" "2" "L"
"2007" "1" "3" "W"
"2007" "1" "4" "W"
"2007" "1" "5" "W"
"2007" "1" "6" "W"
"2007" "1" "7" "W"
"2007" "1" "8" "W"
"2007" "1" "9" "W"
"2007" "1" "10" "L"
. . .
此数据跨越 4 年，跨越 12 个团队，每年 13-16 周.
This data spans 4 years across 12 teams, for 13-16 weeks per year.
为了计算连续获胜，我使用以下查询:
To calculate the winning streaks, I use the following query:
SELECT
team_id,
result,
year,
MIN(week) as StartDate,
MAX(week) as EndDate,
COUNT(*) as Games
FROM (
SELECT
year,
week,
team_id,
result,
( SELECT
COUNT(*)
FROM
win_loss_temp wl2
WHERE
wl1.team_id = wl2.team_id
and wl2.result <> wl1.result
and wl2.year <= wl1.year
AND wl2.week <= wl1.week) as rungroup
FROM
win_loss_temp wl1) A
WHERE result = 'W'
GROUP BY year, team_id, result, rungroup
ORDER BY Games desc
LIMIT 15;
结果如下:
team_id result year StartDate EndDate Games
----------------------------------------------------------
5 W 2007 1 12 12
1 W 2007 3 9 7
5 W 2008 2 7 6
. . .
这是正确的/预期的输出......在给定的一年内.
This is correct/expected output...within a single given year.
我的问题跨越多年.假设一支球队以 3 连胜结束了 2007 年，然后赢得了 2008 年的前 4 场比赛.这应该被记录为总共 7 场连胜(7 场胜利，没有失利).但到目前为止，我无法弄清楚如何修改上述查询以适应跨年.
My issue is spanning multiple years. Say a team finishes 2007 on a 3-game win streak, and then wins the first 4 games of 2008. That should be recorded as a 7-game win streak in total (7 wins without recording a loss). But so far I cannot figure out how to modify the above query to accommodate spanning years.
我什至尝试在表中创建一个与周连接的年份的新字段(例如 2007.13 表示 2007 年第 13 周)，但没有成功使用该字段而不是分别使用年和周.得到了时髦的结果.
I even tried creating a new field in the table that was year concatenated with week (e.g. 2007.13 for Week 13 of 2007), but had no success using that field instead of year and week separately. Gave funky results.
谢谢.
推荐答案
重新审视这个以修改我以前不完整(直到现在我不知道)的答案.感谢这个外部资源，它解决了完全是我的问题，我能够调整查询以完美满足我的特定需求.
Revisiting this to revise my previously incomplete (unbeknownst to me until now) answer. Thanks to this external resource, which addresses my question completely, I was able to tweak the query to fit my specific need perfectly.
首先，我创建了一个视图 run_groups 定义如下:
First, I created a view run_groups defined as such:
select
`GR`.`team_id` AS `team_id`,
((`GR`.`year` * 100) + `GR`.`week`) AS `GameDate`,
`GR`.`result` AS `Result`, (
select
count(0)
from
`jaddl`.`game_results` `G`
where
((`G`.`result` <> `GR`.`result`) and
(`G`.`team_id` = `GR`.`team_id`) and
(`G`.`playoffs` = `GR`.`playoffs`) and
(((`G`.`year` * 100) + `G`.`week`) <= ((`GR`.`year` * 100) + `GR`.`week`)))) AS `RunGroup`
from
`jaddl`.`game_results` `GR`
where
(`GR`.`playoffs` = 0)
然后我可以利用该视图创建另一个视图，这为我提供了我的最终结果，我可以查询这些结果以进行数据挖掘......我的最终游戏.
Then I can utilize that view to create another view, which gives me my final results which I can query against for data mining purposes...my end game.
select
`run_groups`.`team_id` AS `team_id`,
`run_groups`.`Result` AS `Result`,
min(`run_groups`.`GameDate`) AS `StartDate`,
max(`run_groups`.`GameDate`) AS `EndDate`,
count(0) AS `Games`
from
`jaddl`.`run_groups`
group by `run_groups`.`team_id`, `run_groups`.`Result`, `run_groups`.`RunGroup`
order by count(0) desc, `run_groups`.`Result` desc, min(`run_groups`.`GameDate`)
输出数据如下:
team_id Result StartDate EndDate Games
1 -1 201507 201606 13
7 -1 201603 201702 13
5 1 200701 200712 12
1 -1 201202 201213 12
2 1 200908 201005 11
12 1 201209 201305 10
5 1 201401 201410 10
4 -1 200813 200908 9
11 -1 201112 201207 9
(1 的 result 是赢，-1 是输.)瞧！我需要查询所有与连胜相关的统计数据的数据集.感谢@eggyal 的出色建议，他使用 year*100+week 而不是将年和周与前导零连接为字符串.
(A result of 1 is a win, -1 is a loss.) And voila! The dataset I need to query for all streak-related statistics. Shoutout to @eggyal for his excellent suggestion of using year*100+week instead of concatenating years and weeks with leading zeros as a string.
这篇关于在 MySQL 中计算跨年的运行或连续的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
