MySQL系列之怎样精确的使用窗口函数（基于8.0版本）

前言

各位，博主开始敲黑板了，有没有不相识、或者完全没听说过窗口函数的盆友？文末有个投票，可以到场一下哦~
MySQL数据库从8.0开始支持窗口函数了，它是一种强盛的数据分析工具，旨在资助你快速得到场景数据。在正式先容这类函数前，博主还是解释一下为什么这么称呼这类函数为“窗口”函数。窗口——一个数据记录的集合，也就是你的数据操作范围只限于这个数据集，再无其他。它与group by雷同， 但是最大的区别是窗口函数会为每个查询行天生一个效果（add column）。
恭喜你，有这个认知后，博主可以正式先容它了，请紧随博主，以防迷路。

窗口函数必学必会

既然窗口函数是服务于数据分析的，那么先来看看它长什么样，有句话说得好：“没吃过猪肉，还没见过猪跑么”。当我们Get一个新知时，也要怀着同样的预期和先行一步的姿态去对待它。那咱们先看看它的语法结构吧。
1. 基本语法

1.1 匿名窗口

1. SELECT
2.         <窗口函数名> over (partition by <分组列名> order by <排序列名>)
3. FROM `你的表名`

复制代码

1.2 表现窗口

1. SELECT
2.         <窗口函数名> OVER win
3. FROM `你的表名`
4. WINDOW win AS (partition by <分组列名> order by <排序列名>)

复制代码

其中，窗口函数名必须指定，partition by（可选），order by（可选）。
2. 包括哪些

窗口函数主要包含两大类：常见的聚合函数（count、sum、avg等）和专用的窗口函数（比如排序等）。
2.1 聚合函数

大多数的聚合函数皆可用作窗口函数，通常与GROUP BY子句使用，将统计值分组到子集中。
聚合函数用途说明AVG()返回平均值BIT_AND()按位 AND 运算，代表逻辑与BIT_OR()按位 OR 运算，代表逻辑或BIT_XOR()按位 XOR 运算，代表逻辑异或COUNT()返回行数COUNT(DISTINCT)返回去重后的行数GROUP_CONCAT()分组后，返回一个自动连接的字符串JSON_ARRAYAGG()返回一个json数组JSON_OBJECTAGG()返回一个json对象MAX()返回最大值MIN()返回最小值STD()返回团体标准偏差STDDEV()返回团体标准偏差STDDEV_POP()返回团体标准偏差STDDEV_SAMP()返回样本标准偏差SUM()返回总和VAR_POP()返回团体标准方差VAR_SAMP()返回样本方差VARIANCE()返回团体标准方差   提示：除非另有说明，否则聚合函数会忽略NULL值。
  如果在不包含GROUP BY子句的SQL中使用聚合函数，则相当于对全部行进行分组。对于数值参数，方差和标准偏差函数返回一个DOUBLE值。SUM（）和AVG（）函数如果为精确值参数（整数或DECIMAL）返回DECIMAL值，如果为近似值参数（FLOAT或DOUBLE）返回DOUBLE值。
如使一个聚合函数转换为一个窗口函数执行，需按如下格式执行（over子句）：

1. # 添加over子句
2. SUM([DISTINCT] expr) [over_clause]

复制代码

示例1-普通聚合：
这是一个普通聚合函数写法（来自官网）：

1. SELECT
2.      country,
3.      SUM(profit) AS country_profit
4. FROM sales
5. GROUP BY country
6. ORDER BY country;

复制代码

示例2-窗口函数：
这是一个转为窗口函数写法（来自官网）：

1. SELECT
2.      year, country, product, profit,
3.      SUM(profit) OVER() AS total_profit,
4.      SUM(profit) OVER(PARTITION BY country) AS country_profit
5. FROM sales
6. ORDER BY country, year, product, profit;

复制代码

是不是很简单？
2.2 专用窗口函数

我们已知窗口函数是对一个记录集执行雷同聚合的操作。然而，固然聚合操作将查询行分组为单个效果行，但窗口函数会为每个查询行天生一个效果。
窗口函数用途说明ROW_NUMBER()为效果集中的每行记录分配唯一的一连整数序号RANK()为效果集中的每行记录分配一个排名DENSE_RANK()为效果集中的每行分配一个排名，但不会跳过相同的排名PERCENT_RANK()用于盘算某行在效果集中的相对排名比，其值介于0-1间，表示相对位置CUME_DIST()用于盘算某行在效果集中的累积分布值，其值介于0-1间，表示累计分布比例LAG(expr,n)返回当前行的前 n 行的expr值LEAD(expr,n)返回当前行的后 n 行的expr的值FIRST_VALUE(expr)返回第一个expr的值LAST_VALUE(expr)返回末了一个expr的值NTILE()返回当前行在其分区内的桶数NTH_VALUE()返回窗口内第N行的参数值 over_clause表示over子句。
某些窗口函数允许使用null_treation子句，该子句指定在盘算效果时怎样处置惩罚null值，本选项款为可选。它是SQL标准的一部分，但MySQL实现只允许RESPECT NULL（这也是默认值）。这意味着在盘算效果时会考虑NULL值。
博主这里提供2个示例（来自官网）。请注意SQL中的OVER子句。
示例1：

1. SELECT
2.      val,
3.      ROW_NUMBER()   OVER w AS 'row_number',
4.      CUME_DIST()    OVER w AS 'cume_dist',
5.      PERCENT_RANK() OVER w AS 'percent_rank'
6. FROM numbers
7. WINDOW w AS (ORDER BY val);

复制代码

示例2：

1. SELECT
2.       time, subject, val,
3.       FIRST_VALUE(val)  OVER w AS 'first',
4.       LAST_VALUE(val)   OVER w AS 'last',
5.       NTH_VALUE(val, 2) OVER w AS 'second',
6.       NTH_VALUE(val, 4) OVER w AS 'fourth'
7. FROM observations
8. WINDOW w AS (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING);

复制代码

示例3：

1. SELECT
2.     t, val,
3.     LAG(val)        OVER w AS 'lag',
4.     LEAD(val)       OVER w AS 'lead',
5.     val - LAG(val)  OVER w AS 'lag diff',
6.     val - LEAD(val) OVER w AS 'lead diff'
7. FROM series
8. WINDOW w AS (ORDER BY t);

复制代码

示例4：

1. SELECT
2.      val,
3.      ROW_NUMBER() OVER w AS 'row_number',
4.      NTILE(2)     OVER w AS 'ntile2',
5.      NTILE(4)     OVER w AS 'ntile4'
6. FROM numbers
7. WINDOW w AS (ORDER BY val);

复制代码

示例5：

1. SELECT
2.      val,
3.      ROW_NUMBER() OVER w AS 'row_number',
4.      RANK()       OVER w AS 'rank',
5.      DENSE_RANK() OVER w AS 'dense_rank'
6. FROM numbers
7. WINDOW w AS (ORDER BY val);

复制代码

结语

窗口函数的主要作用是对查询效果集中的行进行分组、排序，并在每个分组内进行聚合、排名、盘算等操作，但不会改变原始查询效果的行数或次序。‌ 窗口函数主要用于数据分析场景，其最大的特点是输入值是从SELECT语句效果集中的一行或多行的“窗口”中获取的‌。窗口函数的具体应用场景包括：

• 分组排序‌：可以对数据进行分组排序，求和、求平均值、计数等‌；
  
• 排名盘算‌：盘算分组内的排名或累积求和等‌；
  
• 数据分析‌：提供强盛的数据分析支持，如盘算同比/环比增长率等‌；
  

走过的、途经的盆友们，点点赞，收收藏，并加以引导，以备不时之需哈~

---

出色回放

---

MySQL系列之数据导入导出
MySQL系列之索引入门（上）
MySQL系列之索引入门（下）

---

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。