MySQL 8与MariaDB:两者窗口函数和CTE的比较

21世纪技术官导读:窗口(Window)函数和CTE已经是很多流行数据库产品的主流。随着MySQL 8和MariaDB 10.2.0的发布,两家 DBMS 供应商亦开始了竞争的序幕。

每个MySQL DBA或程序员都应该在日常工作中学习和应用新添加的MariaDB和MySQL窗口函数和公共数据表表达式(Common Table Expressions,CTE)。

CTE和窗口函数可以帮我们解决许多查询难题,这些查询在以前的版本中一直是很困难的问题。

掌握它们将为查询解决方案又打开了一扇大门,这些解决方案更加健壮,执行速度更快,并且比以前使用旧技术的解决方案更容易维护。

今天,我们将比较两个数据库中的窗口函数和公用数据表表达式(CTE)。

窗口函数

各位都熟悉COUNT(),SUM()和AVG()等这些集合函数,但是使用窗口函数来处理的就会少得很多。与在整个表上运行的集合函数不同,窗口函数是对一组数据进行计算,为每一记录返回一个单一的聚合值。

为什么使用窗口函数而不是常规集合函数,窗口函数的主要优点是不会导致单个输出记录而进行分组。相反地,记录保留其单独的身份,并将聚合值添加到每条记录(行)。

MariaDB中的窗口函数

窗口函数已被添加到ANSI/ISO标准SQL:2003中,然后又在ANSI/ISO标准SQL:2008中进行了扩展。

多年以来,DB2,Oracle,Sybase,PostgreSQL和其他数据库供应商已经全面实施。其它供应商在后面也都增加了对窗口函数的支持。例如,微软就没能将窗口函数添加到SQL Server,直到SQL 2012才加入。

开源社区对窗口功能的众多愿望和要求,MariaDB 10.2.0中终于引入了这一特性。现在,MariaDB包含诸如ROW_NUMBER , RANK , DENSE_RANK , PERCENT_RANK , CUME_DIST , NTILE , COUNT , SUM , AVG , BIT_OR , BIT_AND和BIT_XOR等窗口函数。

语法

窗口函数查询加入OVER关键字表征,随后指定用于计算的一组记录。

默认情况下,用于计算的一组行(“窗口”)是整个数据集,可以使用ORDER BY子句进行排序。 然后使用PARTITION BY子句将窗口缩小到数据集内的特定组。

以下举例说明,我们以学生考试成绩表为例:

+------------+---------+--------+

| name | test | score |

+------------+---------+--------+

| Steve | SQL | 75 |

+------------+---------+--------+

| Robert | SQL | 43 |

+------------+---------+--------+

| Tracy | SQL | 56 |

+------------+---------+--------+

| Tatiana | SQL | 87 |

+------------+---------+--------+

| Steve | Tuning | 73 |

+------------+---------+--------+

| Robert | Tuning | 31 |

+------------+---------+--------+

| Tracy | Tuning | 88 |

+------------+---------+--------+

| Tatiana | Tuning | 83 |

+------------+---------+--------+

以下两个查询分别返回按test与按name的平均test分九,即按测试分和名称进行汇总:

SELECT name, test, score, AVG(score) OVER (PARTITION BY test)

AS average_by_test FROM test_scores;

+----------+--------+-------+-----------------+

| name | test | score | average_by_test |

+----------+--------+-------+-----------------+

| Steve | SQL | 75 | 65.2500 |

| Steve | Tuning | 73 | 68.7500 |

| Robert | SQL | 43 | 65.2500 |

| Robert | Tuning | 31 | 68.7500 |

| Tracy | SQL | 56 | 65.2500 |

| Tracy | Tuning | 88 | 68.7500 |

| Tatiana | SQL | 87 | 65.2500 |

| Tatiana | Tuning | 83 | 68.7500 |

+----------+--------+-------+-----------------+

SELECT name, test, score, AVG(score) OVER (PARTITION BY name)

AS average_by_name FROM student;

+---------+--------+-------+-----------------+

| name | test | score | average_by_name |

+---------+--------+-------+-----------------+

| Steve | SQL | 75 | 74.0000 |

| Steve | Tuning | 73 | 74.0000 |

| Robert | SQL | 43 | 37.0000 |

| Robert | Tuning | 31 | 37.0000 |

| Tracy | SQL | 56 | 72.0000 |

| Tracy | Tuning | 88 | 72.0000 |

| Tatiana | SQL | 87 | 85.0000 |

| Tatiana | Tuning | 83 | 85.0000 |

+---------+--------+-------+-----------------+

在这两种情况下,我们注意到原始分数仍可用于每一条记录。

MySQL 8的窗口函数

MySQL在后来才发布和采用窗口函数的标准,现在已经作为MySQL 8.0的一部分。

MySQL遵循与其它DBMS厂商相同的ANSI/ISO标准。窗口函数查询也由OVER关键字作为表征,PARTITION BY 子句用于将窗口缩小为结果集中的特定组。

目前支持的函数包括:

下面我们使用CUME_DIST()函数做为示例,此函数返回一组值的累积分布。

CUME_DIST()通常与ORDER BY联用,将分区行排序为所需的顺序。没有ORDER BY,所有行都是值为1的对等体。

以下的查询,针对于val列中的值集合显示每行的CUME_DIST值以及类似的PERCENT_RANK()函数返回的百分比等值。还可以使用ROW_NUMBER()函数来做为显示参考:

SELECT

val,

ROW_NUMBER() OVER w AS 'row_number',

CUME_DIST() OVER w AS 'cume_dist',

PERCENT_RANK() OVER w AS 'percent_rank'

FROM numbers

WINDOW w AS (ORDER BY val);

其SQL查询结果如下:

+------+------------+--------------------+--------------+

| val | row_number | cume_dist | percent_rank |

+------+------------+--------------------+--------------+

| 5 | 9 | 1 | 1 |

+------+------------+--------------------+--------------+

OVER子句可以与很多聚合函数联用,包括如下函数:

(1)AVG()

(2)COUNT()

(3)MAX()

(4)MIN()

(5)STDDEV_POP() , STDDEV() , STD()

(6)STDDEV_SAMP()

(7)SUM()

(8)VAR_POP() , VARIANCE()

(9)VAR_SAMP()

这些函数既可以用作窗口函数,也可以做为非窗口函数,这取决于OVER子句是否存在。

MySQL还支持仅用作窗口函数的非聚合函数。对于这些函数,OVER子句是强制性存在的。来看如下函数:

(1)CUME_DIST()

(2)DENSE_RANK()

(3)FIRST_VALUE()

(4)LAG()

(5)LAST_VALUE()

(6)LEAD()

(7)NTH_VALUE()

(8)NTILE()

(9)PERCENT_RANK()

(10)RANK()

(11)ROW_NUMBER()

作为非聚合窗口函数的示例,此查询用了ROW_NUMBER()函数,它会生成其分区中每条记录的行号。

在这种情况下,记录会按国家编号。在默认状态下,分区的行是无序的,因此记录编号也并非确定的。要对分区行排序,需要在窗口定义中包含ORDER BY子句。该查询使用无序和有序的分区(row_num1 和 row_numw 列)来声明以及包含ORDER BY子句的差异。来看如下查询:

SELECT

year, country, product, profit,

ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,

ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2

FROM sales;

+------+---------+------------+--------+----------+----------+

| year | country | product | profit | row_num1 | row_num2 |

+------+---------+------------+--------+----------+----------+

| 2000 | Finland | Computer | 1500 | 2 | 1 |

| 2000 | Finland | Phone | 100 | 1 | 2 |

| 2001 | Finland | Phone | 10 | 3 | 3 |

| 2000 | India | Calculator | 75 | 2 | 1 |

| 2000 | India | Calculator | 75 | 3 | 2 |

| 2000 | India | Computer | 1200 | 1 | 3 |

| 2000 | USA | Calculator | 75 | 5 | 1 |

| 2000 | USA | Computer | 1500 | 4 | 2 |

| 2001 | USA | Calculator | 50 | 2 | 3 |

| 2001 | USA | Computer | 1500 | 3 | 4 |

| 2001 | USA | Computer | 1200 | 7 | 5 |

| 2001 | USA | TV | 150 | 1 | 6 |

| 2001 | USA | TV | 100 | 6 | 7 |

+------+---------+------------+--------+----------+----------+

通用数据表表达式(CTE)

通用表表达式(CTE)我们可以理解为在单个SELECT,INSERT,UPDATE,DELETE或CREATE VIEW等语句执行范围内定义的临时结果集。CTE类似于派生表,但是它不作为对象存储,仅在查询期间存在。与派生表不同,CTE可以是自用,即可以在同一查询中多次引用。

CTE可以用来做如下的事情:

1)创建一个递归查询;

2)当无需用视图时替代视图处理;

3)通过从标量子查询/非确定性或外部访问的函数派生的列启用分组;

4)在同一个语句可多次引用结果表数据。

使用CTE提供了改进的可读性和易于维护复杂查询的优点。 查询可以分为独立的,简单的逻辑构建块。 然后可以使用这些简单块构建更复杂的临时CTE,直到生成最终结果集。

递归CTE

递归公用表表达式(CTE)是用于分层查询的标准SQL:1999的实现。

递归CTE的第一个实现是在2007年开始出现的。标准中的递归CTE与IBM DB2版本2中的现有实现相对接近。递归CTE最终由Microsoft SQL Server(自SQL Server 2008 R2),Firebird 2.1, PostgreSQL 8.4+,SQLite 3.8.3+,Oracle 11g第2版和IBM Informix 11.50+版本。

如果没有公共表表达式或连接子句,通过用户定义的递归函数实现分层查询,这会导致非常复杂的SQL出现。

MariaDB中的CTE

在MariaDB中,非递归CTE基本上被认为是一个查询本地视图,这种语法比嵌套FROM (SELECT …)更具有可读性。CTE可以引用另一个CTE,可以从多个地方引用。

因此,CTE与派生表类似。请看如下具有派生表的SQL:

SELECT * FROM

( SELECT * FROM employees

WHERE dept = 'Engineering' ) AS engineers

WHERE

...

具有CTE的SQL:

WITH engineers AS

( SELECT * FROM employees

WHERE dept = 'Engineering' )

SELECT * FROM engineers

WHERE ...

在SQL使用递归或子查询的性能和可读性都很差。CTE的优点之一是它们允许查询引用它本身,因此也可看作递归SQL。递归CTE将重复执行数据的子集,直到获得完整的结果集,这对于处理分层或树形结构的数据特别有用。

使用递归CTE,您可以实现使用标准SQL非常难的处理,并且执行速度更快。它可以帮助我们解决许多类型的业务问题,甚至将一些复杂的SQL/应用程序逻辑简化为只对数据库的简单递归调用。

递归CTE的一些示例用途是查找数据间的差异,创建组织结构图并创建测试数据。

WITH RECURSIVE表示递归CTE。 它有一个名称,后面跟着一个主体(主要查询),如下SQL所示:

Below is a recursive CTE that counts from 1 to 50.

WITH cte

AS (SELECT 1 AS n -- anchor member

UNION ALL

SELECT n + 1 -- recursive member

FROM cte

WHERE n

)

SELECT n

FROM cte;

上面的语句打印从1到49的数字序号。

MySQL 8中的CTE

MySQL 8.0通过标准的WITH关键字添加了CTE,这WITH在竞争产品中实现的方式大致相同。

使用公用表表达式,要用具有一个或多个逗号分隔子条款的WITH子句。每个子句都提供了一个生成结果集并将名称和子查询关联。以下示例是在WITH子句中定义名为cte1和cte2的CTE,并在WITH子句cte2的最上部SELECT来引用它们:

WITH

cte1 AS (SELECT a, b FROM table1),

cte2 AS (SELECT c, d FROM table2)

SELECT b, d FROM cte1 JOIN cte2

WHERE cte1.a = cte2.c;

执行语句后立即包含SELECT语句,如下形式:

INSERT … WITH … SELECT …

REPLACE … WITH … SELECT …

CREATE TABLE … WITH … SELECT …

CREATE VIEW … WITH … SELECT …

DECLARE CURSOR … WITH … SELECT …

EXPLAIN … WITH … SELECT …

递归通用表表达式可以引用其自己的子查询。请看如下SQL:

WITH RECURSIVE cte (n) AS

(

SELECT 1

UNION ALL

SELECT n + 1 FROM cte WHERE n

)

SELECT * FROM cte;

+------+

| n |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

+------+

小结

一段时间以来,窗口函数和通用表表达式(CTE)已经成为主流数据库产品的标配。随着MySQL 8.0和MariaDB 10.2.0的发布,两个数据库厂商已经都赶到了最流行的趋势,如MS SQL Server与Oracle。

作者:高明

编译:21世纪技术官

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180314G14S8O00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券