开窗函数总结

1，概念

开窗函数与聚合函数一样，都是对行的集合组进行聚合计算。它用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同SEO靠我一行中同时返回基础行的列和聚合列。

 2，开窗函数格式

 函数名(列) OVER(partition by … order by …rows|range)

3，具体解释

3.1，分类

1，聚合开窗函数

即 聚合函数 SEO靠我sum()，count()，max()，min()， avg() + over(partition by … order by …)

2，分组开窗函数

即row_number()，rank()，denseSEO靠我_rank()，ntile() + over(partition by … order by …)

3.2，OVER()里参数解释

1，partition by  字段 相当于group by 字段 起到SEO靠我分组作用

2，order by 字段 即根据某个字段进行排序，默认包含该分组的所有行的数据，进行聚合或          排序操作

3，ROWS|RANGE 窗口子句，跟在 order by 子句后面用来SEO靠我限制当前行聚合或排序操作的范        围

4，range和rows的区别：

rows   是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无           SEO靠我         关，只与排序后的行号相关，就是我们常规理解的那样。

     range 是逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range

范围   (查看示例代码2SEO靠我,3即可理解)

 

5，窗口子句的几个范围语法的格式：

          current row :当前行

          unbounded proceding  窗口上边界不设限(即区间的第一行)

unbounded  following  SEO靠我 窗口下边界不设限(即区间的最后一行)

          N proceding   当前行之前的N行，可以是数字也可以是能计算数字的表达式

          N  following   当前行之后的N行 ，同上 

3.3，开窗函数的执行顺序SEO靠我

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where group by order by 的执行

4，示例解释

4.1，order by 含义解释

eSEO靠我g：sum(a) over (order by b) 的含义如图:按照b列排序，将a依次相加，每次是[窗口第一行，当前行] 数据的累加得到结果，如上: （4+1+6图上写错了）

4.2，开窗函数示例

4.SEO靠我2.1，表结构

DROP TABLE IF EXISTS `goods`; CREATE TABLE `goods` (`id` int(0) NOT NULL AUTO_INCREMESEO靠我NT,`category_id` int(0) NULL DEFAULT NULL,`category` varchar(15) CHARACTER SET utf8 COLLATE utf8_genSEO靠我eral_ci NULL DEFAULT NULL,`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULTSEO靠我 NULL,`price` decimal(10, 2) NULL DEFAULT NULL,`stock` int(0) NULL DEFAULT NULL,`upper_time` datetimSEO靠我e(0) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 12 SEO靠我CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

4.2.2，表的数据

INSERT INTO `goods` VASEO靠我LUES (1, 1, 女装/女士精品, T恤, 39.90, 1000, 2020-11-10 00:00:00); INSERT INTO `goods` VALUES (2, 1SEO靠我, 女装/女士精品, 连衣裙, 79.90, 2500, 2020-11-10 00:00:00); INSERT INTO `goods` VALUES (3, 1, 女装/女士精品SEO靠我, 卫衣, 89.90, 1500, 2020-11-10 00:00:00); INSERT INTO `goods` VALUES (4, 1, 女装/女士精品, 牛仔裤, 89.SEO靠我90, 3500, 2020-11-10 00:00:00); INSERT INTO `goods` VALUES (5, 1, 女装/女士精品, 百褶裙, 29.90, 500, SEO靠我2020-11-10 00:00:00); INSERT INTO `goods` VALUES (6, 1, 女装/女士精品, 呢绒外套, 399.90, 1200, 2020-11SEO靠我-10 00:00:00); INSERT INTO `goods` VALUES (7, 2, 户外运动, 自行车, 399.90, 1000, 2020-11-10 00:00:0SEO靠我0); INSERT INTO `goods` VALUES (8, 2, 户外运动, 山地自行车, 1399.90, 2500, 2020-11-10 00:00:00); SEO靠我 INSERT INTO `goods` VALUES (9, 2, 户外运动, 登山杖, 59.90, 1500, 2020-11-10 00:00:00); INSERT ISEO靠我NTO `goods` VALUES (10, 2, 户外运动, 骑行装备, 399.90, 3500, 2020-11-10 00:00:00); INSERT INTO `goodSEO靠我s` VALUES (11, 2, 户外运动, 运动外套, 799.90, 500, 2020-11-10 00:00:00); INSERT INTO `goods` VALUES SEO靠我(12, 2, 户外运动, 滑板, 499.90, 1200, 2020-11-10 00:00:00); INSERT INTO `goods` VALUES (15, 1, 女装/SEO靠我女士精品1, 连衣裙1, 40.90, 2500, 2020-11-10 00:00:00);

4.2.3，开窗函数查询

1，按商品类别分组，根据价格排序 按rows类型为窗口子句，窗口大小为上无边界，下SEO靠我边界为当前行的下一行，这个范围内进行计算个数 select g.*,count(price) over(partition by category_id ORDER BY price SEO靠我 rows BETWEEN UNBOUNDED PRECEDING AND 1 following) from goods g

 1，结果如下：

2，按商品类别分组，根据价格排序，按rangeSEO靠我类型为窗口子句，窗口大小价格满足[0,当前行的price+1] 范围内的所有数据，进行count操作 select g.*,count(price) over(partition bySEO靠我 category_id ORDER BY price range BETWEEN UNBOUNDED PRECEDING AND 1 following) 无边界到当前行下一行数据,SEO靠我 #窗口大小价格满足[0,当前行的price] 范围内的所有数据 进行count操作 count(price) over(partition by category_iSEO靠我d ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND current ROW) 无边界到当前行数据 from goods g

2，SEO靠我结果如下，可以参照这个结果进行理解rows和range的区别

3，lag(col,n,default) 用于统计窗口内往上第n个值,即取每个分区内某列的前面的第n个值。 # col:列名SEO靠我 # n:往上第n行 # default:往上第n行为NULL时候，取默认值,不指定则取NULL #lag()函数 按rows类型类型为窗口子句 获取上SEO靠我无边界到当前行范围内，当前行往上数第一个值 select g.*,lag(price,1,0) over(partition by category_id ORDER BY priceSEO靠我 rows BETWEEN UNBOUNDED PRECEDING AND current ROW) from goods g

3，结果如下，可以用于获取当前数据行的 上次登录时间 的需SEO靠我求

4，lead(col,n,default) 用于统计窗口内往下第n个值,即每个分区内某列的后n个值。 # col:列名 # n:往下第n行 # defSEO靠我ault:往下第n行为NULL时候，取默认值,不指定则取NULL #lag()函数 按rows类型类型为窗口子句 获取上无边界到当前行范围内，当前行往下数第一个值 seSEO靠我lect g.*,lead(price,1,0) over(partition by category_id ORDER BY pricerows BETWEEN UNBOUNDED PRECEDINSEO靠我G AND current ROW) from goods g

4，结果如下，结合lead()函数 可以获取用户 上次登录时间与下次登录时间的 需求

5，first_value 开窗函数 按rows类型类SEO靠我型为窗口子句，获取指定窗口内的第一个值last_value 开窗函数 按rows类型类型为窗口子句，获取指定窗口内的最后一个值#下述例子 获取每次窗口大小为 第一行（无边界）到当前行， 以id分组的第SEO靠我一个值 select g.*,first_value(price) over(partition by category_id ORDER BY price rows SEO靠我BETWEEN UNBOUNDED PRECEDING AND current ROW) 分区中第一个值, last_value(price) over(partitiSEO靠我on by category_id ORDER BY price rows BETWEEN UNBOUNDED PRECEDING AND current ROW) 分区中最后一个值 SEO靠我from goods g

5，结果如下，可以用于指定时间内最新或最旧数据的需求。

6，cume_dist 返回小于等于当前值的行数/分组内总行数。 # 计算某个窗口或分区中某个值的累积分布SEO靠我。假定升序排序，则使用以下公式确定累积分布： # 小于等于当前值x的行数 / 窗口或partition分区内的总行数。 # 其中，x 等于 order by 子句中指定SEO靠我的列的当前行中的值。 # 1、g中指定partition,所以是以指定的字段进行分组进行统计，比如id为1的price=29.9， # 则小于等于29.9的只有1行数据SEO靠我，整个分组为6行，即1/6 = 0.166,其余返回结果同理可得。 select g.*,cume_dist() over(partition by category_id ORDERSEO靠我 BY price) from goods g

6，结果如下，可用于求比例的需求

7，row_number开窗函数 #从1开始对分区内的数据排序 select g.*,roSEO靠我w_number() over(partition by category_id ORDER BY price) from goods g

 7，结果如下：

7， rank开窗函数 # raSEO靠我nk 开窗函数基于 over 子句中的 order by 确定一组值中一个值的排名。如果存在partition by , # 则为每个分区组中的每个值排名。排名可能不是连续的。例如，如SEO靠我果两个行的排名为 1，则下一个排名为 3。 select g.*,rank() over(partition by category_id ORDER BY price) from gSEO靠我oods g

7，结果如下：

8，dense_rank开窗函数 #dense_rank与rank有一点不同,当排名一样的时候,接下来的行是连续的。如两个行的排名为 1，则下一个排名为 2。SEO靠我 select g.*,dense_rank() over(partition by category_id ORDER BY price) from goods g

8，结果如下

9，pSEO靠我ercent_rank开窗函数 #计算给定行的百分比排名。可以用来计算超过了百分之多少的人。 #即：(当前行的rank值-1)/(分组内的总行数-1) SEO靠我select g.*,percent_rank() over(partition by category_id ORDER BY price) from goods g

 9，结果如下：

10， ntileSEO靠我开窗函数 # 函数功能：NTILE(n)，将每个分区内排序后的结果均分成N份。本质是将每个分区拆分成更小的分区。 # 如果切片不均匀，默认增加第一个切片的分布。 SEO靠我 # NTILE不支持ROWS BETWEEN。 select g.*,ntile(4) over(partition by category_id ORDER BY pricSEO靠我e) from goods g

,10，结果如下