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 pricerows 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，结果如下