mysql - 如何在没有循环或游标的情况下概括时间顺序数据的顺序 COUNT()？

Question

我已经阅读了所有的论点:告诉 SQL 你想要什么，而不是如何得到它。使用基于集合的方法而不是过程逻辑。不惜一切代价避免游标和循环。

不幸的是，我已经绞尽脑汁好几个星期了，我想不出如何想出一种基于集合的方法来为按时间顺序排列的数据的连续子集生成迭代 COUNT。

下面是我正在研究的问题的具体应用。

我使用包含多年逐场比赛数据的数据库进行与足球相关的研究，这些数据当然是按年份、比赛和比赛的时间顺序排列的。数据库加载到运行 MySQL 5.0 的 Web 服务器上。

这个特定问题所需的字段包含在 core 表中。以下是表格相关部分的一些示例数据:

   GID | PID  | OFF | DEF | QTR | MIN | SEC | PTSO | PTSD
  --------------------------------------------------------
   121 | 2455 | ARI | CHI |   2 |   4 |  30 |   17 |   10
   121 | 2456 | ARI | CHI |   2 |   4 |  15 |   17 |   10 
   121 | 2457 | ARI | CHI |   2 |   3 |  53 |   17 |   10 
   121 | 2458 | ARI | CHI |   2 |   3 |  31 |   20 |   10 

这些列分别表示:唯一的比赛标识符、唯一的比赛标识符、哪支球队在那场比赛中处于进攻端、哪支球队在那场比赛中处于防守端、比赛发生的节数和时间以及进攻方和防守方的比分入戏。换句话说，在(假设的)第 121 场比赛中，亚利桑那红雀队在第 2457 场比赛中投进一球(即第 2458 场比赛)。

我想做的是逐秒、逐秒地分析几年的数据，并计算在任何给定时间段内任何可能的得分差异发生的次数。以下查询按经过的秒数和得分差异排列数据:

SELECT core.GID, core.PID, core.QTR, core.MIN, core.SEC, core.PTSO, core.PTSD, 
    ((core.QTR - 1) * 900 + (900-(core.MIN * 60 + core.SEC))) AS secEl, 
    core.PTSO - core.PTSD AS oDif, (core.PTSO - core.PTSD) * -1 AS dDif
FROM core 
ORDER BY secEl ASC, oDif ASC;

结果看起来像这样:

   GID | PID    | OFF | DEF | QTR | MIN | SEC | PTSO | PTSD | secEl | oDif | dDif
 ---------------------------------------------------------------------------------
   616 | 100022 | CHI | MIN |   1 |  15 |  00 |    0 |    0 |     0 |    0 |    0
   617 | 100169 | HOU | DAL |   1 |  15 |  00 |    0 |    0 |     0 |    0 |    0
   618 | 100224 | PHI | SEA |   1 |  15 |  00 |    0 |    0 |     0 |    0 |    0
   619 | 100303 | JAX | NYJ |   1 |  15 |  00 |    0 |    0 |     0 |    0 |    0 

虽然这看起来很漂亮，但我的目标不是按时间顺序对数据进行排序。相反，我想按顺序遍历一场 NFL 比赛中可能出现的 4,500 秒(四个 15 分钟的小节加上一个 15 分钟的加时赛)中的每一秒，并计算每一秒中每个比分差异出现的次数秒。

换句话说，我不想只计算一支球队在 2002 年到 2013 年间在 1,800 秒(即第二节开始)时领先 21 分的次数。我想计算一支球队在一场比赛中任何点领先 21 分的次数。最重要的是，我想对曾经出现的每个分数差异(即 -50、-49、-48、...、0、1、2、... 48、49、50、...)执行此操作。 ..) 每场比赛的每一秒。

通过一系列嵌套循环，这将相对容易实现，但它不是最可重用的代码。

我想做的是构造集合逻辑，该逻辑将 COUNT 在过去的每一秒内发生的每个得分差异的实例， 不使用循环或游标。结果将列表如下:

   secondsElapsed | scoreDif | Occurrences
  -----------------------------------------
               10 |       -1 |          12
               10 |        0 |      125517
               10 |        1 |           0
               10 |        2 |           3

下面是一个示例查询，用于获取特定时间点(经过 3,000 秒)特定分数差异 (+21) 的实例总数:

SELECT ((core.QTR - 1) * 900 + (900-(core.MIN * 60 + core.SEC))) AS timeElapsed, 
    (core.PTSO - core.PTSD) AS diff, COUNT(core.PTSO - core.PTSD) AS occurrences 
FROM core 
WHERE ((core.QTR - 1) * 900 + (900-(core.MIN * 60 + core.SEC))) = 3000 
    AND ABS(core.PTSO - core.PTSD) = 21

该查询返回以下结果:

   timeElapsed | diff | occurrences
  ----------------------------------
          3000 |   21 |           5 

现在我想概括此查询以在经过的每 秒计算每个 差异的实例。

Answer 1

你的描述相当困惑，但如果你想“在不使用循环或游标的情况下，每秒钟计算所有可能的分数差异”，那么我会做类似的事情:

1) 构建一个工作表(临时表#或表数据类型@)并用您想要的时间增量填充它，例如

   QTR | MIN | SEC |  
    1 |  00 |  01
    1 |  00 |  02
..
    1 |  01 |  59
    1 |  02 |  00
    1 |  02 |  01
    1 |  02 |  02
..
    4 |  15 |  59 

2) 然后您将其用作查询的基础。交叉加入您感兴趣的游戏列表与工作表，为您提供每场比赛和比赛中每一分钟的表格。

3) 将 (2) 的结果左连接到上面的查询中？

有了这个结果集，您就可以查看整个游戏并根据需要求和\计数，而无需循环。