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
- 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号（企鹅号）传播渠道之一，根据《腾讯内容开放平台服务协议》转载发布内容。
- 如有侵权，请联系 cloudcommunity@tencent.com 删除。
相关快讯
扫码
添加站长 进交流群
领取专属 10元无门槛券
私享最新 技术干货
腾讯云开发者
