MySQL窗口函数第2部分

发表于： 2022-09-16 22:52:42
分类：数据库文章分享
浏览：浏览(93)

作为SQ:2003标准的一部分首次引入，并在MySQL 8.0中可用，MySQL中的窗口函数非常引人注目，但当您第一次开始使用它们时，语法也可能有点吓人。这篇文章是我们将讨论窗口函数的系列文章的第二篇，包括分解语法和使用不同窗口函数的示例。本文中的所有代码示例都使用我们在第1部分中创建的数据库表和数据。

当我们在这些示例中前进时，我们应该记住，大多数（如果不是全部）功能都可以用任何编程语言处理。然而，我更喜欢让数据库做它最擅长的事情——检索和操作数据。

LAG（）和LEAD（）示例

两个密切相关的窗口函数允许我们查看一行数据，即当前行之前的n行或当前行之后的n行。它们分别是LAG（）和LEAD（）。

如果我们想显示当前玩家以及上一个和下一个玩家的积分总和，我们将使用以下查询：

SELECT `full_name`,
       `group_name`,
       RANK() OVER( PARTITION BY `group_name`
          ORDER BY `points` DESC
          ) group_rank,
      `points`,

       `points` +
       LAG( `points`, 1, `points` ) OVER ( PARTITION BY `group_name`
          ORDER BY `points` DESC
          ) with_player_above,
         
      `points` +
      LEAD( `points`, 1, `points` ) OVER ( PARTITION BY `group_name`
         ORDER BY `points` DESC
         ) with_player_below

FROM `player`
ORDER BY group_name, group_rank;

如您所见，每个函数都有三个参数：

我们希望检索的列。
我们希望偏移的行数。在我们的示例中，我们希望查看当前行之前和之后的一行，因此传递值1。
如果函数调用的结果为空，则返回的默认值。默认值可以是硬编码值或列名。

在结果集的每一行中，我们将从LAG（）和LEAD（）返回的值添加到当前行中points列的当前值。

此图显示了上述查询的结果。

我们调用LAG（）和LEAD（）的结果（由红色和黄色箭头表示）可能很有趣。如果我们省略了第三个参数（默认值为结果为空），则每个结果都将为空。由于如果结果为空，我们将返回当前行中的点的值，因此每个点的值都是加倍的。

NTILE（）示例

让我们假设我们需要根据玩家的总积分将他们分成三组。此子分组允许我们查看他们组的前三分之一、中三分之一和后三分之一中的玩家。

为此，我们使用NTILE（）函数。

SELECT `full_name`,
   `points`,
   `group_name`,
   RANK() OVER( PARTITION BY `group_name`
      ORDER BY `points` DESC
      ) player_group_rank,
   NTILE(3) OVER ( PARTITION BY `group_name` 
       ORDER BY `points` DESC, `full_name`
       ) ntile_rank
FROM `player`
ORDER BY group_name, player_group_rank, full_name;

我们将一个参数传递给NTILE（），即我们要将数据分成的组数。在我们的示例中，我们使用3，因为我们希望看到玩家分成三组。

当我们运行这个查询时，我们看到如下结果：

PERCENT_RANK（）示例

在更广泛的数据集中，查看每行数据的百分位排名可能会有所帮助。PERCENT_RANK（）将计算得分高于当前玩家的玩家的百分比。

我们可以使用以下查询返回该信息：

SELECT `full_name`,
   `points`,
   `group_name`,
   RANK() OVER( PARTITION BY `group_name`
      ORDER BY `points` DESC
   ) player_group_rank,
   ROUND(
      PERCENT_RANK() OVER ( PARTITION BY `group_name`
         ORDER BY `points` DESC
   ) * 100 ,2 ) pct_rank
FROM `player`
ORDER BY group_name, player_group_rank, full_name;

PERCENT_RANK（）不带任何参数，为了使信息更易于阅读，我们将结果乘以100，然后四舍五入到小数点后2位。

此查询的结果可以在下图中看到。

在结果集中值得注意的一点是，A组中没有100%的其他分数更高的分数。这是因为Jimmie邻居和Kevin Hardy并列小组最后一名。

使用SUM（）作为窗口函数

正如我在本系列的第1部分中指出的，如果我们添加OVER（）子句，一些聚合函数可以用作窗口函数。让我们来看看我们如何做到这一点。

如果我们想显示一个给定玩家的积分占总积分的百分比。为此，我们可以使用以下查询：

SELECT `full_name`,
   `points`,
   `group_name`,
   RANK() OVER( PARTITION BY `group_name`
      ORDER BY `points` DESC
   ) player_group_rank,
   ROUND(
      (points / SUM(points) OVER ( PARTITION BY `group_name`) * 100), 4
   ) point_pct
FROM `player`
ORDER BY group_name, player_group_rank, full_name;

请注意，我们如何将OVER（）子句添加到SUM（），按组名称对数据进行分区。调用SUM（）返回的值将返回组中每个玩家的总点数。为了确定给定玩家的分数占总分数的百分比，我们将玩家的分数除以调用SUM（）的结果，然后乘以100。然后将该值四舍五入到小数点后4位。

此查询的结果与下图类似。

结果显示，A组15名球员中，托德·夏普贡献了9.2368%的积分。如果您想知道，我特意将这些值四舍五入到小数点后四位，以便我们可以看到A组中前两名球员百分比之间的差异。

有关使用其他聚合函数作为窗口函数的更多信息，请参阅MySQl文档。

使用窗框

当我们在窗口函数中使用PARTITION BY子句时，我们告诉MySQL我们想要如何对数据进行分组。使用窗口函数，我们可以获得更细粒度的想要返回的数据集。frame子句提供了这种粒度。

在处理frame子句时，我们可以限制用于特定窗口函数的数据。使用frame子句，我们定义了数据子集中包含的行的范围。例如，在该定义中，我们使用以下设置框架的边界：

UNBOUNDED Previous-分区中当前行之前的每一行
无界跟随-分区中跟随当前行的每一行
n previous-当前行之前的行数。
n FOLLOWING-n当前行之后的行数。
当前行-当前行。

指定范围时，我们使用起点和终点。如果没有定义范围，默认范围如下：

如果窗口函数中没有ORDER BY子句，则使用整个分区。
如果窗口函数中存在ORDER BY子句，则范围在无界的前一行和当前行之间。

行示例

当使用frame子句时，可以用两种不同的方式定义它们。首先，行决定要包括多少行。例如，如果我们只想使用当前行前面的两行，我们可以在定义中使用前面的行2。如果我们想使用该值来确定要使用多少行，我们将在定义中使用范围。例如，如果我们想将帧限制为10点以内的玩家，我们将使用范围10 FOLLOWING。

使用行的一个示例是计算每个玩家返回的运行总数。对此的查询将是：

SELECT `full_name`,
       `points`,
       `group_name`,
       RANK() OVER( PARTITION BY `group_name`
          ORDER BY `points` DESC
          ) player_group_rank,
      SUM( points ) OVER ( PARTITION BY `group_name`
          ORDER BY `points` DESC
          ROWS UNBOUNDED PRECEDING
          ) running_total
FROM `player`
ORDER BY group_name, player_group_rank;

请注意，我们仅使用窗口框架的起点。在这种情况下，端点是当前行。我们对SUM（）的调用是将当前行和分区中每个前一行中的点相加。

此查询的结果类似于下图。

上图显示了新组启动时，运行总数重新启动。因此，如果我们想对所有玩家进行一次运行总计，我们将在调用SUM（）中删除分区。

范围示例

使用行作为窗口框架的一部分很简单。然而，范围更为复杂。当我们使用范围时，我们提取值与条件匹配的所有行。对于结果集中的每一行，我们可以在窗口函数中使用不同的行数。

下面是一个查询，将返回有多少玩家比当前玩家落后10分或更少。

SELECT `full_name`,
       `points`,
       `group_name`,
       RANK() OVER( PARTITION BY `group_name`
          ORDER BY `points` DESC
          ) player_group_rank,
       COUNT( * ) OVER ( PARTITION BY `group_name`
          ORDER BY `points` DESC
          RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING
          ) - 1 within_ten_points
FROM `player`
ORDER BY group_name, player_group_rank;

在本例中，我们使用COUNT（）作为窗口函数。如果您查看窗口框架，您可以看到我们的范围是当前行的点值到该值10点以内的任何行。我们从结果中减去1，因为该范围定义将包括当前行，并且我们不希望在计数中包括当前玩家。

此查询的结果如下所示：

有趣的一点是，Ardella显示一名球员在10分以内，但排名低于她的球员落后超过10分。这是因为Ardella和Precious Cummings是并列的，因此在计算中使用了Preciouse的点。

重要的是要注意，我们只能使用数字或日期顺序中的值的范围。因此，例如，如果COUNT（）中的ORDER BY子句使用full_name，我们就不能使用RANGE。

总结

虽然窗口函数的功能可以在任何编程语言中复制，但使用它们直接从数据库返回数据可以使开发人员的工作更轻松。窗口函数的语法可能有点令人生畏，但将其分解为各个部分可以使其更易于阅读和理解。我希望本系列将使您更容易处理该故障。

查看文档以了解有关窗口函数的更多信息。

原文标题：MySQL Window Functions Part 2
原文作者：Scott Stroz
原文链接：https://blogs.oracle.com/mysql/post/mysql-window-functions-part-2

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论