开窗函数总结

ようれ

已于 2022-07-18 14:08:05 修改

阅读量8k

点赞数 13

文章标签：数据库

于 2022-06-30 15:19:46 首次发布

本文链接：https://blog.csdn.net/m0_54423474/article/details/125526189

版权

1，概念

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

2，开窗函数格式

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

3，具体解释

3.1，分类

1，聚合开窗函数
即聚合函数 sum()，count()，max()，min()， avg() + over(partition by … order by …)
2，分组开窗函数
即row_number()，rank()，dense_rank()，ntile() + over(partition by … order by …)

3.2，OVER()里参数解释

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

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

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

4，range和rows的区别：
rows 是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无关，只与排序后的行号相关，就是我们常规理解的那样。
range 是逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range

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

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

current row :当前行
unbounded proceding 窗口上边界不设限(即区间的第一行)
unbounded following 窗口下边界不设限(即区间的最后一行)
N proceding 当前行之前的N行，可以是数字也可以是能计算数字的表达式
N following 当前行之后的N行，同上

3.3，开窗函数的执行顺序

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

4，示例解释

4.1，order by 含义解释

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

4.2，开窗函数示例

4.2.1，表结构

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

4.2.2，表的数据

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

4.2.3，开窗函数查询

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

1，结果如下：

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

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

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

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

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

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

5，first_value 开窗函数 按rows类型类型为窗口子句，获取指定窗口内的第一个值
   last_value 开窗函数 按rows类型类型为窗口子句，获取指定窗口内的最后一个值

#下述例子 获取每次窗口大小为 第一行（无边界）到当前行， 以id分组的第一个值
select g.*,first_value(price) 
over(partition by category_id ORDER BY price rows BETWEEN UNBOUNDED PRECEDING AND current ROW) '分区中第一个值',
last_value(price)
over(partition by category_id ORDER BY price rows BETWEEN UNBOUNDED PRECEDING AND current ROW) '分区中最后一个值'
from goods g

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

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

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

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

7，结果如下：


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

7，结果如下：

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

8，结果如下

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

9，结果如下：

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

,10，结果如下

ようれ

关注

13
点赞
踩
95

收藏

觉得还不错? 一键收藏
0
评论
开窗函数总结

4.2.1，表的数据4.2.3，开窗函数查询 1，结果如下： 2，结果如下，可以参照这个结果进行理解rows和range的区别3，结果如下，可以用于获取当前数据行的上次登录时间的需求4，结果如下，结合lead()函数可以获取用户上次登录时间与下次登录时间的需求 5，结果如下，可以用于指定时间内最新或最旧数据的需求。 6，结果如下，可用于求比例的需求 7，结果如下： 7，结果如下：8，结果如下 9，结果如下： ,10，结果
复制链接

扫一扫