SQL篇-常用窗口函数

最新推荐文章于 2024-04-25 14:08:20 发布
最新推荐文章于 2024-04-25 14:08:20 发布
阅读量5.3k 收藏 120
点赞数 17
分类专栏： SQL篇 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_27093831/article/details/116520337
版权

前言

1、什么是窗口函数

2、窗口函数的语法

3、窗口函数中的元素

1）函数名 window_function_name

2) 分区 partition_defintion

3) 排序 order_definition

4) 框架 frame_definition

4、窗口函数的应用

1）排名函数 rank()，dense_rank()、row_number() 静态窗口（不用frame）

2）聚合函数  avg()、count()、sum()、min()、max()、percent_rank()、cume_dist()

3）取值函数

前后函数lag()、lead()

头尾函数first_val() 、last_val()

其他函数nth_value()、ntile()

参考文献

前言

在我们日常数据分析中，常常遇到要在组内排名、组内取topn值等需求，例如下面的业务需求：

排名问题：每个品牌的商品按销售额来排名
topN问题：找出每个品牌排名前N的商品

面对这类需求，就需要使用sql的高级功能窗口函数了。窗口函数是个神奇的存在，可以把多行代码变一行处理，把复杂的子查询变简单。让我们来一起了解窗口函数吧！

1、什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），作用于一个数据集合。窗口函数的一个概念就是当前行，当前行属于某个窗口就是从整个数据集选取一部分数据进行聚合/排名等操作。

2、窗口函数的语法

window_function_name(window_name/expression)
OVER (

[partition_defintion]

[order_definition]

[frame_definition])

窗口数据集由"[partition_defintion]"，"[order_definition]"，"[frame_definition]"确定。

3、窗口函数中的元素

1）函数名 window_function_name

静态窗口函数不能用frame子句；滑动窗口函数指加入order by或frame子句后，函数区域变为到当前行的数据集。

静态窗口函数：

排名函数 rank（）、dense_rank()、row_number()；

滑动窗口函数：

聚合函数 sum、 avg、count、max、min、percent_rank()、cum_dist()；

取值函数 first_value()、last_value()、nth_value()、lag()、lead()、ntile()

函数名

参数

描述

rank()

无

这种排序允许并列，并且保留空缺，两个第1名，没有第2名，排名是1，1，3

dense_rank()

无

这种排序允许并列，但不留空，两个第1名，接下来就是第2名，排名是1，1，2

row_number()

无

这种排序不允许并列，且不留空，如果分数是100,100,99 ，这里的排名依旧是1，2，3

sum()

sum(expr)

求和，expr指字段名称或表达式

avg()

avg(expr)

取平均

count()

cout(expr)

统计记录数

min()

min(expr)

取最小值

max()

max(expr)

取最大值

percent_rank()

无

和之前的rank()函数相关，每条记录按照如下公式计算：（rank-1）/（rows-1）其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数

cum_dist()

无

返回一组值中值的累积分布，表示值小于或等于行的值的行数除以总行数的行数 ，返回值大于零且小于或等于1

first_value()

first_value(expr)

返回分组内的第一个值

last_value()

last_value(expr)

返回分组内的最后一个值，与last_value()合称为头尾函数

nth_value()

nth_value(expr,n)

nth_value()功能与first_value()/last_value()类似，返回分组中第N个expr的值，n为正数，若n为空，函数将返回错误，若n大于窗口内的所有函数，函数返回NULL

lag()

lag(expr, [n, [default]])

分区中位于当前行前n行的记录值,若n缺失默认值为1，如果没有返回default，没有default，返回NULL

lead()

lead(expr, [n, [default]])

分区中位于当前行后n行的记录值,函数功能与lag()相反，其余与lag()相同

ntile()

ntile(n)

再平均分配到n个集合中，返回每个记录的分桶号，范围从1到n

2) 分区 partition_defintion

窗口按照指定字段进行分区，分区语句为partition by <指定字段>，窗口函数功能在分区内执行，并在跨越分区边界时重新初始化。如果没有指定 partition by 语句，且没有后面的frame元素限制，就把所有数据当做一整个区。

3) 排序 order_definition

按照指定字段进行排序，排序语句为order by <指定字段>[asc/desc]。和partition by 子句配合使用，就是对分区后的数据进行排序；如果单独使用且没有后面的frame元素的限制，就是对整个区的所有数据进行排序。

4) 框架 frame_definition

窗口框架的作用是对分区进一步细分，框架frame是当前分区的一个子集，在分区里面再进一步细分窗口，子句用来定义子集的规则，通常用来作为滑动窗口使用，某些窗口函数属于静态窗口，frame子句就没有作用。

frame_unit有两种，分别是ROWS和RANGE，ROWS是基于行号，RANGE是基于值的范围。

使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字：

  • CURRENT ROW: 当前行
  • UNBOUNDED PRECEDING:   区间的第一行
  • UNBOUNDED FOLLOWING：区间的最后一行
  • expr  NPRECEDING:   当前行之前的N行，可以是数字，也可以是一个能计算出数字的表达式
  • expr  NFOLLOWING：当前行之后的N行，可以是数字，也可以是一个能计算出数字的表达式

4、窗口函数的应用

接下来，结合实例，介绍以上几种窗口函数的用法

-- 创建成绩表
drop table if exists score;
CREATE TABLE IF NOT EXISTS `score` (
`学号` INT (5) zerofill NOT NULL,
`姓名` VARCHAR (10) NOT NULL,
`课程号` VARCHAR (15) NOT NULL,
`成绩` INT (5) NOT NULL DEFAULT 0
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='成绩表';
-- 查看表结构	
DESC score;

接下来插入数据

-- 插入数据
INSERT INTO `score`(`学号`, `姓名`, `课程号`, `成绩`) 
VALUES 
( '0005', '范若若', '00001', '99' ),( '0005', '范若若', '00002', '81' ),( '0005', '范若若', '00003', '81' ),
( '0001', '范闲',   '00001', '95' ),( '0001', '范闲',   '00002', '91' ),( '0001', '范闲',   '00003', '90' ),
( '0004', '范思辙', '00001', '93' ),( '0004', '范思辙', '00002', '93' ),( '0004', '范思辙', '00003', '97' ),
( '0003', '王启年', '00001', '94' ),( '0003', '王启年', '00002', '84' ),( '0003', '王启年', '00003', '96' ),
( '0002', '林婉儿', '00001', '96' ),( '0002', '林婉儿', '00002', '86' ),( '0002', '林婉儿', '00003', '91' ),
( '0006', '叶轻眉', '00001', '100'),( '0006', '叶轻眉', '00002', '88' ),( '0006', '叶轻眉', '00003', '88' ),
( '0007', '庆帝',   '00001', '98' ),( '0007', '庆帝',   '00002', '88' ),( '0007', '庆帝',   '00003', '93' );
-- 查询成绩表
select * from score order by 学号;

MySQL从8.0版本开始也和Sql Server、Oracle一样支持在查询中使用窗口函数，本文使用 MySQL 8.0.23版本通过实例来介绍窗口函数的应用。

1）排名函数 rank()，dense_rank()、row_number() 静态窗口（不用frame）

-- 按照姓名分区,成绩升序排名。 排名函数 rank()，dense_rank()、row_number()
SELECT 姓名,课程号,成绩,
    rank() OVER (PARTITION BY 姓名 ORDER BY 成绩) AS r,
    dense_rank() OVER (PARTITION BY 姓名 ORDER BY 成绩) AS dr,
    row_number() OVER (PARTITION BY 姓名 ORDER BY 成绩) AS rn
FROM score;

2）聚合函数  avg()、count()、sum()、min()、max()、percent_rank()、cume_dist()

首先介绍常见聚合函数中的avg()：求平均、count()：统计个数、sum()：求和、min()：求最小值、max()：求最大值，下面使用窗口函数和聚合函数求每个人成绩的均值、个数、总分、最小值和最大值。

-- 求每个人成绩的均值、个数、总分、最小值和最大值
SELECT *,
	   avg(成绩)   over w AS score_avg,
       count(成绩) over w AS score_count,
	   sum(成绩)   over w AS score_sum,
       min(成绩)   over w AS score_min,
	   max(成绩)   over w AS score_max
FROM score
WINDOW w AS (partition by 姓名)
order by 学号;

在上面这个例子中，通过对姓名分区后，聚合了各个课程的成绩，例如score_sum列 对每个人分区后，加总各个课程的分数得到总分，但结果中保留了每一行的信息，出现了重复的总分行。我们可以直接使用group by函数得到唯一的的信息，避免重复。

-- 直接使用group by 函数
SELECT 学号,姓名,
	   round(avg(成绩),0)    AS score_avg,
       count(成绩)  AS score_count,
	   sum(成绩)    AS score_sum,
       min(成绩)    AS score_min,
	   max(成绩)    AS score_max
FROM score
group by 姓名
order by 学号;

下面介绍不太常用的两个聚合函数percent_rank()：累计百分比、cume_dist()：累计分布值函数

percent_rank()，和之前的rank()函数相关，每条记录按照如下公式计算：（rank-1）/（rows-1）其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

cume_dist()，和之前的rank()函数也相关，每条记录按照如下公式计算：相对位置（行排名）/rows

例如：叶轻眉的课程号00002和00003的成绩一样都为88，属于同一个等级1。因此这些行都以第2行为基准：2/3=0.66666，如下图所示：

-- percent_rank累计百分比、cume_dist累计分布值
SELECT *,
       rank() over w r,
	   percent_rank()  over w AS pr,
	   cume_dist()   over w AS cd
FROM score
WINDOW w AS (partition by 姓名 order by 成绩);

注意：这两个函数不同的地方在于起始点，percent_rank 这个函数从0开始，而cume_list函数从1开始计算百分比。

3）取值函数

  • 前后函数lag()、lead()

用途：分区中位于当前行前n行/后n行的记录值。

使用场景：查询上一个订单距离当前订单的时间间隔等

-- 按照课程号分区，成绩列每条记录的前一行lag()/后一行lead()
SELECT *,
		lag(成绩, 1) over w as  first_row,
		lead(成绩,1) over w as  last_row
FROM score
WINDOW w AS (partition by 课程号);

注：lag(expr, [n, [default]])分区中位于当前行前n行的记录值,若n缺失默认值为1，如果没有返回default，没有default，返回NULL；lead()的语法和其相反，其余类似。

  • 头尾函数first_val() 、last_val()

用途：返回分区中的第一个/最后一个记录值

-- 按照课程号分区的第一个和最后一个成绩分数
SELECT *,
		first_value(成绩) over w as first_values,
		last_value(成绩) over w as last_values
FROM score
WINDOW w AS (partition by 课程号);

  • 其他函数nth_value()、ntile()

(1)nth_value()

用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名；

应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额,对头尾函数进行补充；


-- 按照课程号分区取每个分区成绩的第2和第3个值
SELECT *,
		nth_value(成绩, 2) over w as  second_score,
		nth_value(成绩, 3) over w as  third_score
FROM score
WINDOW w AS (partition by 课程号);

(2)ntile()

用途：将分区中的有序数据分为n个桶，记录桶号；

应用场景：将每个用户的订单按照订单金额分成3组。

此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用NTILE(N)对数据进行分组，由于记录数不一定被N整除，所以数据不一定完全平均。

-- 按照课程号分区并按成绩升序排序,将各分区分为3桶
SELECT *,
		ntile(3) over w as nt
FROM score
WINDOW w AS (partition by 课程号 order by 成绩 asc);

窗口函数非常有意思，对于一些使用常规思维无法实现的SQL需求，大家可以尝试一下窗口函数，相信会有意想不到的收获。而在MySQL8.0以前的版本上无法使用窗口函数，大家也可以试一试自己写窗口函数。

4）frame语句

ROWS基于行号的应用

  • PRECEDING:   区间的前面
  • FOLLOWING：区间的后面
-- 计算每分区内当前行的前1行(共两行)的平均值以及和,计算成绩的移动平均数与求和。
SELECT 姓名,课程号,成绩,
	   avg(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC ROWS 1 preceding) AS moving_avg,
       sum(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC ROWS 1 preceding) AS moving_sum
FROM score;

RANGE是基于值范围的应用

-- 计算每个分区内从第一行到当前行的平均值以及和
SELECT 姓名,课程号,成绩,
	avg(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS moving_avg,
    sum(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS moving_sum
FROM score;


-- 计算每个分区内从当前行到最后一行的平均值以及和
SELECT 姓名,课程号,成绩,
    avg(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS moving_avg,
    sum(成绩) OVER (PARTITION BY 姓名 ORDER BY 成绩 ASC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS moving_sum
FROM score;

参考文献

  1. https://zhuanlan.zhihu.com/p/165210822?utm_source=wechat_session
  2. https://www.cnblogs.com/DataArt/p/9961676.html?ivk_sa=1024320u
  3. https://blog.csdn.net/lkforce/article/details/102938990
  4. https://zhuanlan.zhihu.com/p/111755529

 

浮生_若梦_小洛
关注
  • 17
    点赞
  • 踩
  • 120
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL开窗函数
zwldn的博客
05-21 1623
数据分析必备函数：开窗函数提供了在查询结果中进行排序、排名、聚合和分析的灵活性，在数据分析和报表生成中非常有用，可以实现更复杂的计算和分析需求。
SQL-窗口函数
Sky-JT的博客
12-21 225
本文主要记录SQL窗口函数的相关笔记
SQL窗口函数
热门推荐
每天学习一点点
04-18 4万+
SQL窗口函数为在线分析处理（OLAP）和商业智能（BI）提供了复杂分析和报表统计的功能，例如产品的累计销售额统计、分类排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组操作来实现。 窗口函数（Window Function）可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。聚合函数和窗口函数的区别如下图所示。 以SUM函数为例演示这两种函数的差异，以下语句中的SUM()是一个聚合函数： S.
sql的窗口函数
zhangyida01的博客
10-13 2601
学习窗口函数的笔记
SQL 中的窗口函数
qq_43430343的博客
02-17 3093
SQL 中的窗口函数（Window Functions）是一种特殊的函数，它可以在查询结果的某个区间内执行计算，而不仅仅是对单个行进行计算。
sql — 窗口函数
最新发布
qq_47343046的博客
04-25 2445
统计窗口函数则用于进行统计计算，包括count、sum、avg、min、max、first_value、last_value、lag、lead、cume_dist。排序窗口函数主要用于对数据进行排序和排名，包括row_number、rank、dense_rank、percent_rank、ntile；与常规聚合函数不同，窗口函数可以在不影响查询结果集的情况下，对结果集中的每一行应用函数，生成额外的信息，例如排名、累计和等。count函数计算结果集中行的数量，可以结合分组函数使用，用于统计分组内的行数。
SQL 窗口函数简介
张知文的博客
06-17 372
窗口函数对一组行进行操作，并为基础查询中的每一行返回一个值。 术语窗口描述了函数在其上进行操作的行集。 窗口函数使用窗口中各行的值来计算返回的值。
Oracle函数及常用sql
01-16
本篇文章将深入探讨Oracle中的基本函数以及常用SQL查询，以帮助初学者快速入门，并为日常使用提供参考。 一、Oracle基本函数 1. **字符串函数**： - `CONCAT()`：连接两个或多个字符串。 - `SUBSTR()`：从字符串...
oracle优化篇及常用函数
07-09
6. **分组和窗口函数**：如`GROUP BY`用于分组，`HAVING`过滤分组后的结果，`OVER()`配合窗口函数如`RANK()`、`DENSE_RANK()`、`ROW_NUMBER()`进行行排序和分组计算。 了解并熟练运用这些函数，可以编写出更高效、...
常用SQL_sql_
09-30
以上只是SQL语言中`SELECT`查询语句的一些基本知识点，实际应用中还有许多高级特性和优化技巧，如窗口函数、自连接、递归查询等，都需要根据实际需求深入学习和掌握。通过熟练运用这些知识，可以高效地管理和操作...
SQL Server与Oracle常用函数对比
12-28
- SQL Server的`OVER()`和`PARTITION BY`用于窗口函数，Oracle也支持类似的功能。 - `RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()`在两个系统中都能找到。 9. **集合函数**： - SQL Server的`UNION`, `INTERSECT...
从SQL Server中的表中删除重复的行
04-07
一种常用的方法是使用`CTE`(公共表表达式)配合`ROW_NUMBER()`窗口函数。假设我们希望保留每组重复行中的第一行，可以这样操作： ```sql WITH Duplicates AS ( SELECT Name, Email, ROW_NUMBER() OVER (PARTITION ...
Mysql_窗口函数_OVER()的分组参数PARTITION BY
feizuiku0116的博客
08-21 9345
一.PARTITION BY与GROUP BY区别 group by是分组函数，partition by是分析函数 在执行顺序上：from > where > group by > having > order by，而partition by应用在以上关键字之后，可以简单理解为就是在执行完select之后，在所得结果集之上进行partition by分组 partition by相比较于group by，能够在保留全部数据的基础上，只对其中某些字段做分组排序（类似exc
SQL常用窗口函数
酱汁儿
07-18 5757
SQL常用窗口函数
这应该是最全的总结了！SQL中的开窗函数汇总！
Python数据挖掘
09-09 3095
MySQL从8.0版本开始支持窗口函数了，窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组（GROUP BY）的某种聚合值，它和聚合函数的不同之处是：窗口函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。窗口函数经常会在leetCode的题目中使用到窗口函数的格式函数（Function）的类型不是所有的函数（Function）都支持开窗函数。
SQL中的窗口函数
wjs19861110的博客
07-02 524
窗口函数(Window Function)是SQL中的一种特殊函数，它可以对结果集进行分组和排序，并返回每个组的聚合值。DENSE_RANK():为结果集中的每一行分配一个唯一的数字，按照指定的排序规则排序，如果有多行具有相同的排名值，则只分配一个排名值。ROW_NUMBER():为结果集中的每一行分配一个唯一的数字，按照指定的排序规则排序。RANK():为结果集中的每一行分配一个唯一的数字，按照指定的排序规则排序。SUM()、AVG()、COUNT()等：返回当前行的聚合值。
hiveSQL常用的函数
06-02
HiveSQL常用的函数包括： 1. 聚合函数：如SUM、AVG、COUNT、MAX、MIN等，用于对数据进行统计分析。 2. 字符串函数：如CONCAT、SUBSTR、TRIM、LOWER、UPPER等，用于对字符串进行操作。 3. 数学函数：如ABS、CEIL、FLOOR、ROUND、POWER等，用于对数字进行计算。 4. 时间函数：如YEAR、MONTH、DAY、HOUR、MINUTE等，用于对时间进行处理。 5. 条件函数：如CASE WHEN、IF、COALESCE、NULLIF等，用于对数据进行条件判断和处理。 6. 集合函数：如COLLECT_SET、COLLECT_LIST、MAP、STRUCT等，用于对复杂数据类型进行处理。 7. 窗口函数：如RANK、DENSE_RANK、ROW_NUMBER、LEAD、LAG等，用于对数据进行分组和排序操作。 这些函数可以帮助我们在HiveSQL中进行数据处理和分析。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值