MySQL在一支团队中的胜利连续记录

Question

MySQL在一支团队中的胜利连续记录

8

我有一个SQL查询，返回关于单个团队的以下表格：

date         gameid     pointsfor     pointsagainst

2011-03-20   15         1             10
2011-03-27   17         7             3
2011-04-03   23         6             5
2011-04-10   30         5             4
2011-04-17   35         4             8
2011-05-01   38         8             1
2011-05-08   43         3             7
2011-05-15   48         6             2
2011-05-22   56         10            2
2011-05-29   59         4             5
2011-06-05   65         2             3
2011-06-19   71         5             6
2011-06-19   74         12            2
2011-06-19   77         5             2
2011-06-19   80         5             4

从这个表格中，有谁能帮我计算最长的连胜和连败次数吗？

我看了一些其他的例子，但由于它们与我的情况不完全相同，所以我很难理解。任何帮助都将不胜感激。谢谢！

- Graeme Cowbeller

你能提供架构和数据吗？ - nawfal

模式相当复杂，因此我认为我的当前选择查询返回了所有所需的数据（如上所示）。是否有其他有用的数据？我期望的输出是：

这个特定团队拥有的最长连胜记录，例如这个团队在比赛ID = 17、23和30中获胜，因此连胜三场
最长连败纪录

如果这些是单独的查询，我不介意。 - Graeme Cowbeller

我想要在@johntotetwoo的评论中加上+9999 ツ - Michael Buen

我的期望输出最初只是一些非常基本的东西，像这样：`连胜 6'但是现在我看到了你的查询Michael，我真的很喜欢它能给出日期和游戏列表。 - Graeme Cowbeller

7个回答

1

有一个解决方案，但我认为你不会喜欢它，因为它需要自连接，而你的表不是一个表，而是一个查询。

内部查询将把日期转换为范围 - 也就是说，对于表中的每个日期，它将找到第一个具有不同结果的日期，或者在最后一场比赛的情况下，这场比赛的日期。这些数据将按不同连胜的第一个日期进行聚合，以展平和计算连胜；外部查询然后通过结果找到极端值。

select case Outcome 
            when -1 then 'Losses'
            when 1 then 'Wins'
            else 'Undecided'
        end Title
      , max(Streak) Streak
from
(
  select min(date) date, date_to, Outcome, count(*) Streak
  from
  (
    select t1.date, 
           sign (t1.pointsfor - t1.pointsagainst) Outcome, 
           ifnull (min(t2.date), t1.date) date_to
     from table1 t1
     left join table1 t2
       on t1.date < t2.date
      and sign (t1.pointsfor - t1.pointsagainst) 
       <> sign (t2.pointsfor - t2.pointsagainst)
    group by t1.date, sign (t1.pointsfor - t1.pointsagainst)
  ) a
  group by date_to, Outcome
) a
group by Outcome

为了避免需要用可能很繁琐的查询替换table1，您可以使用临时表，或者在辅助表中已经以适当的格式存在数据。在Sql fiddle上有实时测试，还有另一个子查询驱动版本，可能性能更好 - 您应该尝试两者。

- Nikola Markovinović

哦，天啊，我认为这就是数据库管理员确保工作安全的方式。 - Asherah

@Len，恕我直言，这个查询确实可以工作...但是对于MySQL来说，它非常难以理解...自己连接自己会导致处理的重复比较1和????，然后2和????，然后3和????等等...一旦完成，再次选择group by，只为了进行另一个max streak的group by。 - DRapp

你的查询类似于运行总计，有些问题不适合基于集合的方法。http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/running-sums-redux.aspx - Michael Buen

有时候纯粹的SQL或者通用的方法就不够用了。你必须发挥特定数据库的优势。为此，我使用http://sqlfiddle.com。我的机器上没有安装MySQL，从MySQL中回收磁盘空间是失败的 :-) - Michael Buen

@MichaelBuen，你没有注意到我回答中的Sql Fiddle链接吗？是的，你完全正确。 - Nikola Markovinović

显示剩余3条评论

1

MySQL没有CTE和窗口函数（例如SUM OVER，ROW_NUMBER OVER等）。但它有一个可取之处。变量！

使用这个：

select 
   min(date) as start_date,
   max(date) as end_date,
   count(date) as streak,
   group_concat(gameid) as gameid_list
from
( 
  select *,      
    IF(
        pointsfor > pointsagainst 
        and 
        @pointsfor > @pointsagainst, 
           @gn, @gn := @gn + 1)                
    as group_number,

    @date as old_date, @gameid as old_gameid, 
    @pointsfor as old_pointsfor,
    @pointsagainst as old_pointsagainst,

    @date := date, @gameid := gameid, 
    @pointsfor := pointsfor, @pointsagainst := pointsagainst      
  from tbl
  cross join 
  (
    select 
      @date := CAST(null as date) as xa,
      @gameid := null + 0 as xb, -- why CAST(NULL AS INT) doesn't work?
      @pointsfor := null + 0 as xc, @pointsagainst := null + 0 as xd, @gn := 0
  ) x
  order by date
) as y
group by group_number
order by streak desc;

输出：

START_DATE                    END_DATE                      STREAK  GAMEID_LIST
March, 27 2011 08:00:00-0700  April, 10 2011 08:00:00-0700  3       17,23,30
June, 19 2011 08:00:00-0700   June, 19 2011 08:00:00-0700   3       74,77,80
May, 15 2011 08:00:00-0700    May, 22 2011 08:00:00-0700    2       48,56
March, 20 2011 08:00:00-0700  March, 20 2011 08:00:00-0700  1       15
April, 17 2011 08:00:00-0700  April, 17 2011 08:00:00-0700  1       35
May, 01 2011 08:00:00-0700    May, 01 2011 08:00:00-0700    1       38
May, 08 2011 08:00:00-0700    May, 08 2011 08:00:00-0700    1       43
May, 29 2011 08:00:00-0700    May, 29 2011 08:00:00-0700    1       59
June, 05 2011 08:00:00-0700   June, 05 2011 08:00:00-0700   1       65
June, 19 2011 08:00:00-0700   June, 19 2011 08:00:00-0700   1       71

实时测试：http://www.sqlfiddle.com/#!2/bbe78/8

关于我在sqlfiddle上的解决方案，它有两个查询。1. 顶部的模拟。2. 下面的最终查询

- Michael Buen

我不明白:D 不过，它在其他数据库上运行良好，例如Postgres、Oracle和SQL Server。遗憾的是，MySQL不能。幸运的是，有一个解决方法：select null+0 as x。 - Michael Buen

简单来说，MySQL 中的 CAST 和 CONVERT 函数接受的类型名称与用于声明变量和列的名称略有不同。这对我来说似乎完全违反直觉，但事实似乎就是如此。 - Andriy M

嗨，Michael，我在我的查询浏览器中运行你的查询时遇到了问题。由于某种原因，当我运行它一次时，它返回不正确的数据，但如果我再次点击运行，它会返回正确的数据。你知道这是为什么吗？再次感谢！ - Graeme Cowbeller

另外，如果有影响的话，我已经用返回表格的查询替换了“tbl”：（SELECT date，gameid，pointsfor，pointsagainst FROM result WHERE teamid = 6 AND bye = 0 AND COMPLETED = 1 AND seasonid > 7 AND roundwd = 0 AND (pointsfor！= 0 OR pointsagainst！= 0) AND gametype！= 'Friendly' ORDER BY date ASC）AS tbl - Graeme Cowbeller

嗯... MySql 怎么会这么不稳定呢？你运行的是我的确切查询还是你已经修改过的查询？如果你能重现错误，请截屏并在此处发布截屏 URL；然后将查询粘贴到 sqlfiddle 中，再在此处发布 sqlfiddle URL，我很乐意进行分析。 - Michael Buen

显示剩余3条评论

1

MySQL的最新版本具有CTE和窗口功能。

以下是解决方案。

第一步，通过为胜利和失败分配自己的连胜组号来对其进行分组：

with t as 
(
    select
        *,      
        pointsfor - pointsagainst > 0 is_winner,
        case when pointsfor - pointsagainst > 0 
            and lag(pointsfor) over(order by date, pointsfor - pointsagainst desc) 
                - lag(pointsagainst) over(order by date, pointsfor - pointsagainst desc) > 0 
        then
            0
        else
            1
        end as is_new_group
    from tbl
)
select *, sum(is_new_group) over(order by date, pointsfor - pointsagainst desc) as streak_group
from t

输出：

date                |gameid |pointsfor |pointsagainst |is_winner |is_new_group |streak_group |
--------------------|-------|----------|--------------|----------|-------------|-------------|
2011-03-20 15:00:00 |15     |1         |10            |0         |1            |1            |
2011-03-27 15:00:00 |17     |7         |3             |1         |1            |2            |
2011-04-03 15:00:00 |23     |6         |5             |1         |0            |2            |
2011-04-10 15:00:00 |30     |5         |4             |1         |0            |2            |
2011-04-17 15:00:00 |35     |4         |8             |0         |1            |3            |
2011-05-01 15:00:00 |38     |8         |1             |1         |1            |4            |
2011-05-08 15:00:00 |43     |3         |7             |0         |1            |5            |
2011-05-15 15:00:00 |48     |6         |2             |1         |1            |6            |
2011-05-22 15:00:00 |56     |10        |2             |1         |0            |6            |
2011-05-29 15:00:00 |59     |4         |5             |0         |1            |7            |
2011-06-05 15:00:00 |65     |2         |3             |0         |1            |8            |
2011-06-19 15:00:00 |74     |12        |2             |1         |1            |9            |
2011-06-19 15:00:00 |77     |5         |2             |1         |0            |9            |
2011-06-19 15:00:00 |80     |5         |4             |1         |0            |9            |
2011-06-19 15:00:00 |71     |5         |6             |0         |1            |10           |

最终查询。计算连胜的次数：

with t as 
(
    select
        *,      
        pointsfor - pointsagainst > 0 is_winner,
        case when pointsfor - pointsagainst > 0 
            and lag(pointsfor) over(order by date, pointsfor - pointsagainst desc) 
                - lag(pointsagainst) over(order by date, pointsfor - pointsagainst desc) > 0 
        then
            0
        else
            1
        end as is_new_group
    from tbl
)
, streak_grouping as
(
    select
        *, sum(is_new_group) over(order by date, pointsfor - pointsagainst desc) as streak_group
    from t
)
select 
    min(date) as start_date,
    max(date) as end_date,
    count(*) as streak,
    group_concat(gameid order by gameid) as gameid_list
from streak_grouping
group by streak_group
order by streak desc, start_date

输出：

start_date          |end_date            |streak |gameid_list |
--------------------|--------------------|-------|------------|
2011-03-27 15:00:00 |2011-04-10 15:00:00 |3      |17,23,30    |
2011-06-19 15:00:00 |2011-06-19 15:00:00 |3      |74,77,80    |
2011-05-15 15:00:00 |2011-05-22 15:00:00 |2      |48,56       |
2011-03-20 15:00:00 |2011-03-20 15:00:00 |1      |15          |
2011-04-17 15:00:00 |2011-04-17 15:00:00 |1      |35          |
2011-05-01 15:00:00 |2011-05-01 15:00:00 |1      |38          |
2011-05-08 15:00:00 |2011-05-08 15:00:00 |1      |43          |
2011-05-29 15:00:00 |2011-05-29 15:00:00 |1      |59          |
2011-06-05 15:00:00 |2011-06-05 15:00:00 |1      |65          |
2011-06-19 15:00:00 |2011-06-19 15:00:00 |1      |71          |

- Michael Buen

0

感谢大家的帮助。最终我使用了建议中提到的 PHP 循环。如果有人想知道，这是我的代码：

$streakSQL = "SELECT date, gameid, pointsfor, pointsagainst FROM result WHERE teamid = ".$_GET['teamid']." AND bye = 0 AND COMPLETED = 1 AND seasonid > 7 AND roundwd = 0 AND (pointsfor != 0 OR pointsagainst != 0)";
            $streak = mysql_query($streakSQL);

            $winstreak = 0;
            $maxwinstreak = 0;
            $losestreak = 0;
            $maxlosestreak = 0;
            while($streakRow = mysql_fetch_array($streak))
            {
                //calculate winning streak
                if($streakRow['pointsfor'] > $streakRow['pointsagainst'])
                { 
                    $winstreak++; 
                    if($winstreak > $maxwinstreak)
                    {
                        $maxwinstreak = $winstreak;
                    }
                }
                else{ $winstreak = 0; }
                //calculate losing streak
                if($streakRow['pointsfor'] < $streakRow['pointsagainst'])
                { 
                    $losestreak++; 
                    if($losestreak > $maxlosestreak)
                    {
                        $maxlosestreak = $losestreak;
                    }
                }
                else{ $losestreak = 0; }
            }
            echo "Biggest Winning Streak: ".$maxwinstreak;
            echo "<br />Biggest Losing Streak: ".$maxlosestreak;

- Graeme Cowbeller

0

你所面对的问题是要跟踪胜负趋势，这需要使用某种循环和运行计数器来计算，而不是使用 SQL。SQL 查询处理单个行、分组、排序等；你试图使用一种不适合解决此类问题的语言。

- Reza S

不再需要这样做了。符合ANSI SQL 2008标准的数据库现在同样能够解决这个问题，可以通过CTE和窗口函数组合来解决。请参考以下另一个此类问题案例：https://dev59.com/WWPVa4cB1Zd3GeqP53VG#10449751 - Michael Buen

0

你需要创建一个游标，读取所有行，计算数据...每次想要获取最长连胜时都要这样做...

我建议一个解决方法可以让事情变得更容易。你可以在表中添加一个名为“streakFor”的列。每次插入一行时：

//pseudo code
if pointsFor > pointsAgainst
    if last_streakFor > 0 
        then streakFor++ 
        else streakFor = 1

else
    if last_streakFor > 0 
        then streakFor = -1 
        else streakFor--

last_streakFor 是最后插入行中的 streakFor
然后您插入具有列 streakFor 的行

现在您可以

select max(streakFor) from yourTable where yourConditions，这将为您提供“pointsFor”的最长连胜和“pointsAgainst”的最长连败
select min(streakFor) from yourTable where yourConditions，这将为您提供“pointsAgainst”的最长连胜和“pointsFor”的最长连败

- jazzytomato

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- DRapp · Accepted Answer

您需要实现一些MySQL变量来帮助处理这个问题，比多次查询/连接/分组更加高效。这将对所有记录进行单次遍历，然后再获取每种胜利/失败（或平局）类型的最大值。我假设您提供的数据是针对单个时间的，而日期是比赛的明显顺序... Points For是您感兴趣的团队，Points Against是对手。也就是说，我的“别名”名称将是“YourResultSingleTeam”。

内部查询将预先确定游戏状态为“W”中还是“L”oss。然后，查看该值是否与之前的团队实例相同。如果是，则将1添加到现有的胜利/失败计数器中。如果不是，则将计数器重置为1。然后，将当前游戏的状态保留在“LastStatus”值中，以便与下一场比赛进行比较。

完成之后，只需按游戏结果状态进行简单的游戏结果max()分组即可。

select
      StreakSet.GameResult,
      MAX( StreakSet.WinLossStreak ) as MaxStreak
   from
      ( select YR.Date,
               @CurStatus := if( YR.PointsFor > YR.PointsAgainst, 'W', 'L' ) as GameResult,
               @WinLossSeq := if( @CurStatus = @LastStatus, @WinLossSeq +1, 1 ) as WinLossStreak,
               @LastStatus := @CurStatus as carryOverForNextRecord
            from 
               YourResultSingleTeam YR,
               ( select @CurStatus := '',
                        @LastStatus := '',
                        @WinLossSeq := 0 ) sqlvars
            order by
               YR.Date ) StreakSet
   group by
      StreakSet.GameResult

如Nikola所提供的，如果您想考虑“平局”，我们只需通过更改@CurStatus到case/when条件来进行调整。

@CurStatus := case when YR.PointsFor > YR.PointsAgainst then 'W'
                   when YR.PointsFor < YR.PointsAgainst then 'L'
                   else 'T' end as GameResult,