MariaDB · 新特性 · 窗口函数

简介

窗口函数(Window Function)是 SQL:2013 标准中提出的,在后续标准版本的更新中也多次扩展,最新的版本是 SQL:2011 中的标准。

从某些方面来说,窗口函数与聚合函数(Aggregate Function)有些类似,他们都是对一系列的行进行聚合计算,然而不同于聚合函数的是,窗口函数的输出并不是被聚集到单独的一行。像 SUM, AVG, COUNT 这些聚合函数最终输出都是一行记录。

窗口函数可以通过计算每行周围窗口上的集合值来分析数据,例如:计算一定记录范围内、一定值域内、或者一段时间内的累计和以及移动平均值等等。之所以使用窗口这个术语,是因为对结果的处理使用了一个滑动的查询结果集范围。

大部分数据库,尤其是商业数据库都支持SQL标准中定义的部分窗口函数,但是MySQL一直没有支持这个特性。窗口函数在MySQL社区一直呼声很高,却一直没有被实现,直到 MairaDB 10.2 响应了客户的需求,实现了部分窗口函数,并且在持续完善中,我们就来简单介绍一下 MairaDB 的窗口函数。

语法

MariaDB的窗口函数查询指定使用 OVER 关键字,默认情况下,用于计算的行集合(Window,窗口)是整个数据集,并且可以用 ORDER BY 子句排序。PARTITION BY 子句可以用于将窗口缩小到特定的集合内。

使用语法:

function (expression) OVER (
  [ PARTITION BY expression_list ]
  [ ORDER BY order_list [ frame_clause ] ] ) 

function:
  A valid window function

expression_list:
  expression | column_name [, expr_list ]

order_list:
  expression | column_name [ ASC | DESC ]
  [, ... ]

frame_clause:

例如,给出下面的原始数据:

CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT); 

INSERT INTO student VALUES
  ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
  ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
  ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
  ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);

下面两个查询可以分别返回按 test 和 name 分区处理的平均数:

SELECT name, test, score, AVG(score) OVER (PARTITION BY test)
  AS average_by_test FROM student;
+---------+--------+-------+-----------------+
| name    | test   | score | average_by_test |
+---------+--------+-------+-----------------+
| Chun    | SQL    |    75 |         65.2500 |
| Chun    | Tuning |    73 |         68.7500 |
| Esben   | SQL    |    43 |         65.2500 |
| Esben   | Tuning |    31 |         68.7500 |
| Kaolin  | SQL    |    56 |         65.2500 |
| Kaolin  | 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 |
+---------+--------+-------+-----------------+
| Chun    | SQL    |    75 |         74.0000 |
| Chun    | Tuning |    73 |         74.0000 |
| Esben   | SQL    |    43 |         37.0000 |
| Esben   | Tuning |    31 |         37.0000 |
| Kaolin  | SQL    |    56 |         72.0000 |
| Kaolin  | Tuning |    88 |         72.0000 |
| Tatiana | SQL    |    87 |         85.0000 |
| Tatiana | Tuning |    83 |         85.0000 |
+---------+--------+-------+-----------------+

用例

RANK

描述:

RANK() 可以显示给定行的序号,从1开始,顺序以 ORDER BY 字段排序后的序列为准。

语法:

RANK() OVER (
  [ PARTITION BY partition_expression ]
  [ ORDER BY order_list ]
)

例子

CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));

INSERT INTO student VALUES
     ('Maths', 60, 'Thulile'),
     ('Maths', 60, 'Pritha'),
     ('Maths', 70, 'Voitto'),
     ('Biology', 60, 'Bilal'),
     ('Biology', 70, 'Roger');

SELECT RANK() OVER (PARTITION BY course ORDER BY mark) AS rank,
    DENSE_RANK() OVER (PARTITION BY course ORDER BY mark) AS dense_rank,
    ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark) AS row_num,
    course, mark, name from student;
+------+------------+---------+---------+------+---------+
| rank | dense_rank | row_num | course  | mark | name    |
+------+------------+---------+---------+------+---------+
|    1 |          1 |       1 | Maths   |   60 | Thulile |
|    1 |          1 |       2 | Maths   |   60 | Pritha  |
|    3 |          2 |       3 | Maths   |   70 | Voitto  |
|    1 |          1 |       1 | Biology |   60 | Bilal   |
|    2 |          2 |       2 | Biology |   70 | Roger   |
+------+------------+---------+---------+------+---------+

CUME_DIST

描述:

CUME_DIST() 可以返回一行数据的累积分布(cumulative distribution)。计算公式如下

(number of rows <= current row) / (total rows)

语法:

CUME_DIST() OVER (
  [ PARTITION BY partition_expression ]
  [ ORDER BY order_list ]
)

例子:

create table t1 (
  pk int primary key,
  a int,
  b int
);

insert into t1 values
( 1 , 0, 10),
( 2 , 0, 10),
( 3 , 1, 10),
( 4 , 1, 10),
( 8 , 2, 10),
( 5 , 2, 20),
( 6 , 2, 20),
( 7 , 2, 20),
( 9 , 4, 20),
(10 , 4, 20);

select pk, a, b,
    rank() over (order by a),
    percent_rank() over (order by a),
    cume_dist() over (order by a)
from t1;
+----+------+------+--------------------------+----------------------------------+-------------------------------+
| pk | a    | b    | rank() over (order by a) | percent_rank() over (order by a) | cume_dist() over (order by a) |
+----+------+------+--------------------------+----------------------------------+-------------------------------+
|  1 |    0 |   10 |                        1 |                     0.0000000000 |                  0.2000000000 |
|  2 |    0 |   10 |                        1 |                     0.0000000000 |                  0.2000000000 |
|  3 |    1 |   10 |                        3 |                     0.2222222222 |                  0.4000000000 |
|  4 |    1 |   10 |                        3 |                     0.2222222222 |                  0.4000000000 |
|  5 |    2 |   20 |                        5 |                     0.4444444444 |                  0.8000000000 |
|  6 |    2 |   20 |                        5 |                     0.4444444444 |                  0.8000000000 |
|  7 |    2 |   20 |                        5 |                     0.4444444444 |                  0.8000000000 |
|  8 |    2 |   10 |                        5 |                     0.4444444444 |                  0.8000000000 |
|  9 |    4 |   20 |                        9 |                     0.8888888889 |                  1.0000000000 |
| 10 |    4 |   20 |                        9 |                     0.8888888889 |                  1.0000000000 |
+----+------+------+--------------------------+----------------------------------+-------------------------------+

select pk, a, b,
       percent_rank() over (order by pk),
       cume_dist() over (order by pk)
from t1 order by pk;
+----+------+------+-----------------------------------+--------------------------------+
| pk | a    | b    | percent_rank() over (order by pk) | cume_dist() over (order by pk) |
+----+------+------+-----------------------------------+--------------------------------+
|  1 |    0 |   10 |                      0.0000000000 |                   0.1000000000 |
|  2 |    0 |   10 |                      0.1111111111 |                   0.2000000000 |
|  3 |    1 |   10 |                      0.2222222222 |                   0.3000000000 |
|  4 |    1 |   10 |                      0.3333333333 |                   0.4000000000 |
|  5 |    2 |   20 |                      0.4444444444 |                   0.5000000000 |
|  6 |    2 |   20 |                      0.5555555556 |                   0.6000000000 |
|  7 |    2 |   20 |                      0.6666666667 |                   0.7000000000 |
|  8 |    2 |   10 |                      0.7777777778 |                   0.8000000000 |
|  9 |    4 |   20 |                      0.8888888889 |                   0.9000000000 |
| 10 |    4 |   20 |                      1.0000000000 |                   1.0000000000 |
+----+------+------+-----------------------------------+--------------------------------+

select pk, a, b,
        percent_rank() over (partition by a order by a),
        cume_dist() over (partition by a order by a)
from t1;
+----+------+------+-------------------------------------------------+----------------------------------------------+
| pk | a    | b    | percent_rank() over (partition by a order by a) | cume_dist() over (partition by a order by a) |
+----+------+------+-------------------------------------------------+----------------------------------------------+
|  1 |    0 |   10 |                                    0.0000000000 |                                 1.0000000000 |
|  2 |    0 |   10 |                                    0.0000000000 |                                 1.0000000000 |
|  3 |    1 |   10 |                                    0.0000000000 |                                 1.0000000000 |
|  4 |    1 |   10 |                                    0.0000000000 |                                 1.0000000000 |
|  5 |    2 |   20 |                                    0.0000000000 |                                 1.0000000000 |
|  6 |    2 |   20 |                                    0.0000000000 |                                 1.0000000000 |
|  7 |    2 |   20 |                                    0.0000000000 |                                 1.0000000000 |
|  8 |    2 |   10 |                                    0.0000000000 |                                 1.0000000000 |
|  9 |    4 |   20 |                                    0.0000000000 |                                 1.0000000000 |
| 10 |    4 |   20 |                                    0.0000000000 |                                 1.0000000000 |
+----+------+------+-------------------------------------------------+----------------------------------------------+

总结

  • 支持 ROWS 和 RANGE类型的Frame

    • 各种类型的Frame界定都支持,包括 RANGE PRECEDING | FOLLOWING n个Frame范围(不同于PostgreSQL 和 SQL Server)
    • RANGE类型的Frame 还不支持 DATE[TIME] 数据类型和四则运算,但MDEV-9727正在进行开发
  • 还不支持 GROUPS类型的Frame(但好像还没有流行的数据库有支持这玩意的)
  • 不支持禁用Frame(好像也没其他数据库支持)
  • 不支持显式的 NULLS FIRST 和 NULLS LAST
  • 不支持窗口函数嵌套处理(就是VALUE_OF(expr AT row_marker [, default_value) 这种语法)
  • 下面这些窗口函数都支持:
    • “Streamable(流式)” 窗口函数:ROW_NUMBER, RANK, DENSE_RANK
    • 一旦分区中的行数知道后,就可以流式处理的窗口函数:PERCENT_RANK, CUME_DIST, NTILE
  • 目前支持窗口函数的聚合函数有: COUNT, SUM, AVG, BIT_OR, BIT_AND, BIT_XOR
  • 包含 DISTINCT 子句的聚合函数不支持作为窗口函数

大家可以在这里查看具体支持哪些函数以及未来新增了哪些函数

时间: 2025-01-27 02:41:50

MariaDB · 新特性 · 窗口函数的相关文章

iOS8新特性扩展(Extension)应用之四——自定义键盘控件

iOS8新特性扩展(Extension)应用之四--自定义键盘控件         iOS8系统的开放第三方键盘,使得用户在输入法的选择上更加自主灵活,也更加贴近不同语言的输入风格.这篇博客,将介绍如何开发一个第三方的键盘控件. 一.了解UIInputViewController类         UIInputViewController是系统扩展支持键盘扩展的一个类,通过这个类,我们可以自定义一款我们自己的键盘提供给系统使用.         首先,我们先来看一下这个类中的一些属性和方法:

Flash MX 2004新特性实例学习一

    Flash MX 2004的试用版终于可以下载了,它帮助文件中自带的例子很好地反映了2004中新增加的功能.下面我们通过学习这些例子的制作,来熟悉在2004中新增加的功能.这些例子都是从Flash MX 2004的帮助文档中来的.在我的windows2000中的保存路径是C:Documents and SettingsAdministratorLocal SettingsApplication DataMacromediaFlash MX 2004enConfigurationSampl

iOS11开发新特性

索引 iOS11开发新特性之网络部分 前言 ECN 什么是 ECN ? ECN 的实现细节 IPv6 Networking stack changes 网络协议分层 MutliPath TCP NEDNSProxyProvider WKWebView Cookie 管理 URLSession Adaptable Connectivity API URLSessionTask Scheduling API iOS11开发新特性之Xcode9 新特性 功能更全的 Git 支持 创建新的颜色 asse

【译】Java 8的新特性—终极版

文/杜琪(简书作者) 原文链接:http://www.jianshu.com/p/5b800057f2d8 著作权归作者所有,转载请联系作者获得授权,并标注"简书作者". 声明:本文翻译自Java 8 Features Tutorial – The ULTIMATE Guide,翻译过程中发现并发编程网已经有同学翻译过了:Java 8 特性 – 终极手册,我还是坚持自己翻译了一版(写作驱动学习,加深印象),有些地方参考了该同学的. Java 8 前言: Java 8 已经发布很久了,很

SQL Server 2005新特性

一.企业级数据管理 在当今的互联世界中,数据和管理数据的系统必须始终为用户可用且能够确保安全,有了SQL Server 2005,组织内的用户和IT专家将从减少应用程序宕机时间.提高可伸缩性及性能.更紧密的安全控制中获益.SQL Server 2005 也包括了很多新的和改进的功能来帮助企业的IT团队更有效率的工作.SQL Server 2005 包括了几个在企业级数据管理中关键的增强: 易管理 可用性 可伸缩性 安全性 1.易管理 SQL Server 2005 能够更为简单的部署.管理和优化

PHP5的XML新特性

php5|xml 面向的读者 这篇文章的面向对象是所有对PHP5的XML新功能感兴趣的各个水平的PHP开发者.我们假定读者掌握XML的基本知识.然而,如果你已经在你的PHP当中使用了XML,那么这篇文章也会让你受益非浅. 介绍 在当今的互联网世界,XML已经不再是一个时髦词了,它已经被广泛的接受和规范的使用了.因此相对于PHP4,PHP5对于XML的支持更受到了重视.在PHP4中你面对的几乎都是非标准,API中断,内存泄漏以及其它不完全的功能.尽管有些不足已经在PHP4.3中得到改进,开发者们还

ThinkPHP3.2.3数据库设置新特性

 前篇文章,我们总结了下ThinkPHP3.2中所产生的新变化,本文我们来详细看下关于数据库这块有哪些新特性,非常细致,有需要的小伙伴参考下.     ThinkPHP3.2.3版本数据库驱动采用PDO完全重写,配置和使用上面也比之前版本更加灵活和强大,我们来了解下如何使用. 首先,3.2.3的数据库配置信息有所调整,完整的数据库设置包括:   代码如下: /* 数据库设置 */ 'DB_TYPE' => '', // 数据库类型 'DB_HOST' => '', // 服务器地址 'DB_N

jQuery 1.4:15个新特性和优化增强

jQuery 1.4 最近发布了. 超乎大家的预期,这次并非简单的修修补补,1.4 包含了很多新特性.功能增强和性能提升!本文即向您介绍这些可能对你十分有用的新特性和优化增强. 你可以立刻下载jQuery 1.4试用: http://code.jquery.com/jquery-1.4.js 1. 传参给 jQuery(-) 之前,jQuery可以通过 attr 方法设置元素的属性,既可传属性的名和值,也可以是包含几组特定 属性名值对 的 对象.在 jQuery 1.4 中,你可以把一个参数对象

jQuery 1.4官方文档详细讲述新特性功能

为了庆祝jQuery的四周岁生日, jQuery的团队荣幸的发布了jQuery Javascript库的最新主要版本! 这个版本包含了大量的编程,测试,和记录文档的工作,我们为此感到很骄傲. 我要以个人的名义感谢 Brandon Aaron, Ben Alman, Louis-Rémi Babe, Ariel Flesler, Paul Irish, Robert Kati?, Yehuda Katz, Dave Methvin, Justin Meyer, Karl Swedberg, and