SQL 窗口函数

最新推荐文章于 2024-08-03 23:10:08 发布
最新推荐文章于 2024-08-03 23:10:08 发布
阅读量549 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40721300/article/details/116134023
版权

什么是窗口函数？

窗口函数(Window Function)是SQL2003标准中定义的一项新特性，并在 SQL2011、SQL2016 中又加以完善，添加了若干处拓展。窗口函数不同于我们熟悉的普通函数和聚合函数，它为每行数据进行一次计算：输入多行（一个窗口）、返回一个值。在报表等分析型查询中，窗口函数能优雅地表达某些需求，发挥不可替代的作用。

window_function (expression) OVER (
   [ PARTITION BY part_list ]
   [ ORDER BY order_list ]
   [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )
窗口函数出现在 SELECT 子句的表达式列表中，它最显著的特点就是 OVER 关键字。
PARTITION BY 表示将数据先按 part_list 进行分区。
ORDER BY 表示将各个分区内的数据按 order_list 进行排序(在区域内排序)。
最后一项表示 Frame 的定义，即：当前窗口包含哪些数据？
ROWS 选择前后几行
例如 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示往前3行到往后3行,一共7行数据（或小于7行，如果碰到了边界)
RANGE 选择数据范围
例如 RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING 表示所有值在[c−3,c+3]这个范围内的行,c为当前行的值。

如果不指定 PARTITION BY，则不对数据进行分区；换句话说，所有数据看作同一个分区。
如果不指定 ORDER BY，则不对各分区做排序，通常用于那些顺序无关的窗口函数，例如 SUM()。
如果不指定 Frame 子句，则默认采用以下的 Frame 定义：
若不指定 ORDER BY，默认使用分区内所有行 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
若指定了 ORDER BY，默认使用分区内第一行到当前值 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

案例

CREATE TABLE `ban_ji` (
  `id` int(11) NOT NULL,
  `achievement` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '成绩',
  `ban_ji` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '班级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (1, '90', '1');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (2, '91', '1');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (3, '89', '2');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (4, '88', '2');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (5, '99', '2');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (6, '90', '1');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (7, '59', '3');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (8, '50', '3');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (9, '10', '3');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (10, '99', '1');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (11, '98', '1');
INSERT INTO `ban_ji`(`id`, `achievement`, `ban_ji`) VALUES (12, '97', '1');

Example1

SELECT a.id,a.ban_ji,a.achievement,
rank() OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc) as ranking,
dense_rank() OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc) as denseRank,
row_number() OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc) as rowNum
FROM ban_ji a

rank函数:这个例子中是1位，1位，3位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，但是现在前2名是并列的名次，结果是:1，1，3。
dense_rank函数:这个例子中是1位，1位，3位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，但是现在前3名是并列的名次，结果是:1，1，2。
row_number函数;这个例子中是1位，2位，3位,也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1，2，3。
Example2

rows between的作用:控制窗口函数的范围来满足业务需求。
current row就是指当前行,unbounded following就是指最后一行。unbounded preceding 就是第一行。
n preceding 上 n 行数据。n following 下 n行数据。
SELECT a.id,a.ban_ji,a.achievement,
SUM(a.achievement) OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc
rows between current row and unbounded following) as rowNum,
SUM(a.achievement) OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc 
rows between unbounded preceding and current row) as rowNum2,
SUM(a.achievement) OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc 
rows between 1 preceding and current row) as rowNum3,
SUM(a.achievement) OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc 
rows between current row and 1 following) as rowNum4
FROM ban_ji a
Frame 定义并非所有窗口函数都适用，比如 ROW_NUMBER()、RANK()、LEAD() 等。这些函数总是应用于整个分区，而非当前 Frame。

窗口函数可以分为以下 3 类

聚合（Aggregate）：AVG(), COUNT(), MIN(), MAX(), SUM()…
取值（Value）：FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()…
排序（Ranking）：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

FIRST_VALUE 当前窗口第一个值,LAST_VALUE 当前窗口最后一个值。
SELECT a.id,a.ban_ji,a.achievement,
FIRST_VALUE(a.id) OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc) as rowNum,
LAST_VALUE(a.id) OVER(PARTITION BY a.ban_ji ORDER BY a.achievement asc) as rowNum2
FROM ban_ji a

注意:在测试LAST_VALUE的时候,感觉id为1的数据返回不对。原因未知。

窗口函数 VS 聚合函数

似乎窗口函数和 Group By 聚合函数都能做到同样的事情。但是，它们之间的相似点也仅限于此了！这其中的关键区别在于：窗口函数仅仅只会将结果附加到当前的结果上，它不会对已有的行或列做任何修改。而 Group By 的做法完全不同：对于各个 Group 它仅仅会保留一行聚合结果。

源来有你_
关注
专栏目录
SQL-窗口函数
pingfandeshijie的专栏
10-11 391
1:窗口函数的作用 业务需求场景： 组内排名：部门业绩排名 找出某个部门业绩前几位的员工等 2：窗口函数使用 窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。 <窗口函数> over （ partition by <用于分组的列名> ...
SQL窗口函数不懂？五大应用场景让你一步到位
cindy407的博客
04-08 2909
在窗口分析函数出现之前，存在很多 SQL 难以解决的问题，很多都要通过复杂的相关子查询完成，或到Excel中用函数处理 2003年ISO SQL标准加入了窗口分析函数，使得这些难题轻松被攻克，大大提升了效率，减少了Excel卡死导致前功尽弃的情况 本文从聚合/排序/极值/移动/切片五大场景来讲解窗口分析函数 窗口分析函数，可以指定数据窗口进行统计分析，它和普通聚合函数的区别： ① 窗口函数对每个组...
SQL中的窗口函数
最新发布
zhangyifang_009的博客
08-03 6534
窗口函数是SQL中的一项高级特性，用于在不改变查询结果集行数的情况下，对每一行执行聚合计算或者其他复杂的计算，也就是说窗口函数可以跨行计算，可以扫描所有的行，并把结果填到每一行中。这些函数通常与OVER()子句一起使用，可以定义窗口或分区，并在上面执行计算，使用窗口函数，可以使许多难以处理的棘手问题变得较为容易。窗口函数的特点输入多行（一个窗口），返回一个值：窗口函数为每行数据进行一次计算，但不会改变原始查询结果集的行数计算方式灵活：可以使用字句将数据分区，并使用order by。
SQL窗口函数
每天学习一点点
04-18 4万+
SQL窗口函数为在线分析处理（OLAP）和商业智能（BI）提供了复杂分析和报表统计的功能，例如产品的累计销售额统计、分类排名、同比/环比分析等。这些功能通常很难通过聚合函数和分组操作来实现。 窗口函数（Window Function）可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个结果。聚合函数和窗口函数的区别如下图所示。 以SUM函数为例演示这两种函数的差异，以下语句中的SUM()是一个聚合函数： S.
SQL窗口函数速查表.pdf
05-31
### SQL窗口函数速查表知识点概述 #### 一、窗口函数基本概念 窗口函数是SQL中一种非常强大的功能，它可以对一组行（即一个窗口）执行聚合计算，并为每行返回一个值。与传统的聚合函数（如SUM、AVG等）不同的是，...
SQL 窗口函数：ROWS与RANGE
Zhongjie1986的博客
07-02 5944
SQL Window Funcions https://www.youtube.com/watch?v=H6OTMoXjNiM https://www.youtube.com/watch?v=TzsrO4zTQj8 分析函数的语法结构一般是： 分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。 即由以下三部分组成： 分析函数名：如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等； over： 关键字，表示前面的
sql的窗口函数
zhangyida01的博客
10-13 2936
学习窗口函数的笔记
SQL 中的窗口函数
qq_43430343的博客
02-17 3185
SQL 中的窗口函数（Window Functions）是一种特殊的函数，它可以在查询结果的某个区间内执行计算，而不仅仅是对单个行进行计算。
sql — 窗口函数
qq_47343046的博客
04-25 4704
统计窗口函数则用于进行统计计算，包括count、sum、avg、min、max、first_value、last_value、lag、lead、cume_dist。排序窗口函数主要用于对数据进行排序和排名，包括row_number、rank、dense_rank、percent_rank、ntile；与常规聚合函数不同，窗口函数可以在不影响查询结果集的情况下，对结果集中的每一行应用函数，生成额外的信息，例如排名、累计和等。count函数计算结果集中行的数量，可以结合分组函数使用，用于统计分组内的行数。
Lua中的错误处理与调试类
mozhi
02-23 1037
一、错误处理 pcall：返回两个参数值，第一个标记为true/false，第二个为错误信息；
sql：窗口函数
热门推荐
qq_41805514的博客
08-17 5万+
窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。 窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。 窗口函数语法：其中[]中的内容可以省略 &lt;窗口函数&gt; over ([partition by &lt;列清单&gt;] order by &lt...
sql 窗口函数
06-01
SQL 窗口函数（Window Functions）是一种在关系型数据库中进行分析和聚合数据的高级技术。与普通的聚合函数（如 SUM、AVG、MAX、MIN）不同的是，窗口函数可以在结果集中计算聚合函数，而不会改变结果集的行数。窗口函数可以用于计算排名、累计总数、移动平均值等。 以下是一些常用的 SQL 窗口函数： 1. ROW_NUMBER：为结果集中的每一行生成一个唯一的整数值，用于计算排名。 2. RANK 和 DENSE_RANK：用于计算排名，RANK 函数会跳过相同的排名，而 DENSE_RANK 函数不会。 3. LAG 和 LEAD：用于在结果集中获取前一行和后一行的值，常用于计算时间序列数据的变化量。 4. SUM、AVG、MAX、MIN 等聚合函数：可以和 OVER 关键字一起使用，计算所有行或者一组行的聚合值。 5. NTILE：用于将结果集分成指定数量的桶，并为每个桶分配一个整数值。 以下是一个示例 SQL 查询，使用 ROW_NUMBER 和 SUM 窗口函数计算每个订单的总金额和排名： ``` SELECT order_id, SUM(amount) OVER (PARTITION BY order_id) AS total_amount, ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rank FROM orders ``` 希望这些信息可以帮助你了解 SQL 窗口函数的基本知识。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值