MySQL8.0版本如何正确的使用窗口函数详解

目录

• 前言
• 窗口函数必学必会
• 1. 基本语法
• 1.1 匿名窗口
• 1.2 显式窗口
• 2. 包括哪些
• 2.1 聚合函数
• 2.2 专用窗口函数
• 结语

前言

mysql数据库从8.0开始支持窗口函数了，它是一种强大的数据分析工具，旨在帮助你快速获得场景数据。在正式介绍这类函数前，博主还是解释一下为什么这么称呼这类函数为“窗口”函数。窗口——一个数据记录的集合，也就是你的数据操作范围只限于这个数据集，再无其他。它与group by类似， 但是最大的区别是窗口函数会为每个查询行生成一个结果（add column）。

恭喜你，有这个认知后，博主可以正式介绍它了，请紧随博主，以防迷路。

窗口函数必学必会

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

1. 基本语法

1.1 匿名窗口

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

1.2 显式窗口

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

其中，窗口函数名必须指定，partition by（可选），order by（可选）。

2. 包括哪些

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

2.1 聚合函数

大多数的聚合函数皆可用作窗口函数，通常与GROUP BY子句使用，将统计值分组到子集中。

提示：除非另有说明，否则聚合函数会忽略NULL值。

如果在不包含GROUP BY子句的SQL中使用聚合函数，则相当于对所有行进行分组。对于数值参数，方差和标准偏差函数返回一个DOUBLE值。SUM（）和AVG（）函数如果为精确值参数（整数或DECIMAL）返回DECIMAL值，如果为近似值参数（FLOAT或DOUBLE）返回DOUBLE值。

如使一个聚合函数转换为一个窗口函数执行，需按如下格式执行（over子句）：

# 添加GQDoMover子句
SUM([DISTINCT] expr) [over_clause]

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

SELECT 
     jscountry, 
     SUM(profit) AS country_profit
FROM sales
GROUP BY country
ORDER BY country;

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

SELECT
     year, country, product, profit,
     SUM(profit) OVER() AS total_profit,
     SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;

是不是很简单？

2.2 专用窗口函数

我们已知窗口函数是对一个记录集执行类似聚合的操作。然而，虽然聚合操作将查询行分组为单个结果行，但窗口函数会为每个查询行生成一个结果。

over_clause表示over子句。

某些窗口函数允许使用null_treation子句，该子句指定在计算结果时如何处理null值，本选项款为可选。它是SQL标准的一部分，但MySQL实现只允许RESPECT NULL（这也是默认值）。这意味着在计算结果时会考虑NULL值。

博主这里提供5个示例（来自官网）。请注意SQL中的OVER子句。

示例1：

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);

示例2：

 SELECT
      time, subject, val,
      FIRST_VALUE(val)  OVER w AS 'first',
      LAST_VALUE(val)   OVER w AS 'last',
      NTH_VALUE(val, 2) OVER w AS 'second',
      NTH_VALUE(val, 4) OVER w AS 'fourth'
FROM observations
WINDOW w AS (PARTITION BY subject ORDER BY time ROWS UNBOUNDEjsD PRECEDING);

示例3：

SELECT
    t, val,
    LAG(val)        OVER w AS 'lag',
    LEAD(val)       OVER w AS 'lead',
    val - LAG(val)  OVER w ASwww.devze.com 'lag diff',
    val - LEAD(val) OVER w AS 'lead diff'
FROM series
WINDOW w AS (ORDER BY t);

示例4：

SELECT
     val,
     ROW_NUMBER() OVER w AS 'row_number',
     NTILE(2)     OVER w AS 'ntile2',
     NTILE(4)     OVER w AS 'ntile4'
FROM numbers
WINDOW w AS (ORDER BY val);

示例5：

SELECT
     val,
     ROW_NUMBER() OVER w AS 'row_number',
     RANK()       OVER w AS 'rank',
     DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);

结语

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

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

到此这篇关于MySQL8.0版本如何正确的使用窗口函数的文章就介绍到这了,更多相关MySQL8.0正确使用窗口函数内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！