MySQL窗口功能教程

DebugUsery
124 阅读8分钟

MySQL窗口功能

MySQL窗口函数是一个使用基本查询来操作行值的函数。窗口函数必须有一个OVER 子句。因此，任何没有OVER 子句的函数都不是一个窗口函数。

OVER 子句具有以下潜力。

  • 它使用PARTITION BY 子句定义了一个行的分组。
  • 它使用ORDER BY 子句对各组中的行进行排序。

先决条件

读者应具备以下知识，以理解本文的内容。

  • 对数据库和MySQL中使用的函数有基本了解。
  • 对MySQL窗口函数的初级理解。

简介

有3种类型的窗口函数。

  1. 聚合窗口函数 - 它们在数据库查询期间与OVER子句一起使用。下面是一些在MySQL中使用聚合窗口函数的例子。
  • SUM( )- 它操作一个表的行或列，并在加上该行或列的值后返回总数。
  • MAX( )- 它操作一个行或列并返回最大的值。其他聚合窗口函数是：MIN() 和COUNT() ；分别负责返回最小的值和行或列中的值的数量。
  1. 值窗口函数--它们用于生成操作数据行的查询，并返回每行的值。值窗口函数的一些例子是LEAD 和LAG 函数，下面将讨论这些函数。其他的例子是FIRST_VALUE( ) 和LAST_VALUE( ) 函数，也在下面的例子中使用。
  2. 排名窗口函数--这个函数的一些实例是ROW_NUMBER() ，DENSE_RANK() ，和RANK() 函数。这两个函数在下面的例子中都有详细讨论。

价值窗口函数和排名窗口函数统称为内置窗口函数。

下面是一些在数据库操作中常用的MySQL窗口函数的例子。

MySQL窗口函数的例子

1.LEAD

该函数用于计算分组行中的一个值。它们主要实现行和列之间的比较。

SELECT
cloth_type, week, sales_value,
LEAD(sales_value) OVER(PARTITION BY cloth_type ORDER BY week)
AS next_week_value
FROM cloth_type;

上面的函数将给出sales_value 列中售出的衣服的值。

2.排名

这个函数主要用于创建报告。它按指定的顺序计算每行的等级。排名通常是从1开始的数字。

在一个多行共享相同数值的情况下，这些行被赋予相同的等级。这导致跳过下一行的数字，因此RANK( ) 函数返回的值通常不是连续的数字。

SELECT
RANK () OVER(ORDER BY score) AS number-ranked,
name, score
FROM sales:

上面的函数输出一个根据ranking_score 排名的销售项目列表。

3.LAG

LAG( ) 函数与 函数几乎相似，因为它们都是计算行差。由于 返回后一行的值， 返回前一行的值。LEAD( ) LEAD( ) LAG( )

4.DENSE-RANK

这个子句与RANK() 函数类似；只是它不允许有间隙。

SELECT
DENSE_RANK( ) OVER(ORDER BY score DESC) AS dense_number_ranked,
name, score
FROM sales:

5.行号（ROW NUMBER

行号是按照行的出现顺序分配给行的。行首先按升序排列，然后再分配连续的数字。

具有相同数值的行不会被分配一个共同的号码。这意味着在给行编号时不考虑平局。

SELECT
ROW_NUMBER() OVER(ORDER BY score) AS number,
name, score
FROM sales;

上面的函数使用ranking score ，输出了一个按升序排列的销售列表。每个项目都被赋予了一个排名号，从1开始。

6.OVER

OVER 子句描述了如何对表的行进行分割和排序。这个子句包含在上面所有的窗口函数中。

其他常用的子句是Frame 子句和ORDER BY 子句。

MySQL窗口函数比聚合函数的优势

与聚合函数不同，MySQL窗口函数保留了行的身份，并向每一行添加聚合值。

尽管MySQL窗口函数在数据库操作中显示了巨大的前景，但也有一些问题被记录下来，从而证明了这些函数并不完美。

下面将讨论这些问题，并对每个问题提出了一些建议性的避免机制。

MySQL窗口函数的隐患

1.MySQL窗口函数不能在某些条款中使用

MySQL窗口函数不能用于以下列表中的前五个子句。它们只能用于SELECT 和ORDER BY 。

这是由于查询和语法中操作的逻辑顺序不同造成的。

下面是SQL中操作的处理顺序。

1. FROM
2. WHERE
3. GROUP BY
4. AGGREGATED FUNCTIONS
5. HAVING
6. WINDOW FUNCTIONS
7. SELECT
8. DISTINCT
9. EXCEPT
10. ORDER BY
11. OFFSET
12. FETCH

从上面的列表中，我们可以看出，子句WHERE ，GROUP BY 和HAVING 在窗口函数之前被处理。

因此，在处理它们时，窗口函数仍未被处理，因此不能对它们进行任何引用。

如何避免

尽管有这样的挑战，但有一种变通方法可以使我们在上述的条款中使用窗口函数。

这可以通过使用WITH QUERY EXPRESSION 或子查询来实现，如下面的例子所示。

WITH student_marks AS
(
SELECT student_id, student_name, marks,
row_number( ) over (PARTITION BY  student _id ORDER BY marks DESC )
AS rn
FROM student
)
SELECT student_id, student_name, marks
FROM student_marks WHERE rn = 1;
解释

使用WITH 查询从学生表中选择列，并为每行处理行号值。这是在子查询中完成的。

被称为 "rn "的行号被用来进行行过滤。

2. 在计算运行总数时，得到了一个带有隐含RANGE选项的窗口框架。

这发生在计算运行总数的过程中。它发生在用户指定了窗口顺序子句，但没有指定窗口框架单位，如ROWS或RANGES及其相关的窗口框架范围。

这在逻辑上会导致一个错误，以及数据库性能的问题。

下面的例子中指定了ROWS 窗口框架单位。

SELECT  book_id, borrowing_id, value,
SUM ( value) OVER (PARTITION BY book_id
ORDER BY borrowing_id  ROWS UNBOUNDED PRECEDING )
AS total
FROM dbo.Booking;

下面的例子中指定了RANGE 窗口框架单元。

SELECT  book_id, borrowing_id, value,
SUM ( value) OVER (PARTITION BY book_id
ORDER BY borrowing_id
RANGE UNBOUNDED PRECEDING ) AS total
FROM dbo.Booking;

在下面的例子中，没有指定窗口框架单位。

SELECT  book_id, borrowing_id, value,
SUM ( value) OVER (PARTITION BY book_id
ORDER BY borrowing_id
ROWS UNBOUNDED PRECEDING ) AS total
FROM dbo.Booking;

在例子三中，根据SQL标准的规定，你将得到默认的RANGE UNBOUNDED PRECEDING 。

如何避免

然而，这可以通过以下方式避免。

  • 始终根据你的需要将窗口顺序子句指定为ROW 或RANGE ，以避免默认设置。但是，建议你指定ROW ，避免RANGE 。

3.为FIRST_VALUE和LAST_VALUE定义一个隐含的框架

FIRST_VALUE和LAST_VALUE是属于偏移子句的窗口函数，通常返回框架中第一和最后一行的表达式。

尽管大多数初学的人通常认为这些函数对整个窗口进行操作，但FIRST_VALUE 和LAST_VALUE 只支持一个框架。

这些是支持一个框架的函数，指定窗口顺序平面和相关范围是必要的，以避免得到RANGE UNBOUNDED PRECEDING 。

下面的例子显示了FIRST_VALUE 和LAST_VALUE 的使用情况。

SELECT buyer_id, date_of_order, order_id, order_value,
FIRST_VALUE(order_value) OVER (PARTITION BY buyer_id
ORDER BY date_of_order, order_id) AS firstorder_value,
LAST_VALUE(order_value) OVER (PARTITION BY buyer_id
ORDER BY date_of_order, order_id) AS laststorder_value,
FROM Sales.order_value
ORDER BY buyer_id, date_of_order, order_id;

对于FIRST_VALUE() ，将实现预期的结果。然而，对于LAST_VALUE() ，在没有磁盘惩罚的基础上，将返回当前行的值。

对于这样的结果，第一次使用的人可能会认为服务器被破坏了，存在错误。但事实并非如此，因为眼前的结果是SQL标准默认设置的结果。

如何避免

只要把以下几点做到位，就可以避免这种隐患。

  • 在窗口框架的情况下，一定要明确规格，以避免按照SQL标准的默认设置。
  • 为了避免使用FIRST_VALUE() 的无磁盘惩罚，使用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 框架来获得第一行的值。
  • 为了避免LAST_VALUE() ，使用ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING 框架来获得最后一行的值。

4.MySQL窗口函数不能用于更新或删除语句

根据MySQL标准，窗口函数不能用于删除或更新行中的语句。这主要是因为DELETE和UPDATE子句与SELECT 和ORDER 不兼容。UPDATE 语句使用SET ，因此SELECT 不能与它处于同一查询级别。

如何解决

这可以通过以下方式解决。

  • 使用DELETE() 和UPDATE() 作为主查询的子查询，如下所示。
 WITH student_marks AS
 (
 SELECT [ marks],  LAG( [ marks]) OVER (ORDER BY [student_id]) AS marks_lag
 FROM student
 )
 UPDATE  student_marks
 SET [marks] = marks_lag

这样一来，子查询中使用的UPDATE 函数就可以实现了；DELETE函数也是如此。

5.一些窗口函数结构被解析器识别，尽管它们不被支持

一些窗口框架单元，如GROUPS 框架，在查询处理过程中被解析，但后来却开始抛出错误信息。

下面是其余的窗口框架单元，它们被解析但不被MySQL支持。

1. IGNORE NULLS
2. FROM LAST
3. EXCLUDE

虽然这些窗口框架单元不被MySQL支持，但其中一些是被支持的。

1. ROWS AND RANGE
2. RESPECT NULL
3. FROM FIRST
如何避免

为了避免由于缺乏MySQL支持而抛出的错误，我们应该热衷于只使用MySQL支持的窗口框架单元。

这是解决这些错误的唯一方法。

结论

尽管我们已经看到了与窗口函数相关的几个陷阱，但它们在执行MySQL查询时仍然发挥着重要作用，甚至有人给出了各种解决方案，试图解决上述讨论的问题。

因此，这表明窗口函数对MySQL查询是多么关键。

avatar
文章
阅读
粉丝