mysql开窗函数

实验前测试数据

登录后复制

CREATE TABLE `test`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `type` int(0) NULL DEFAULT NULL,
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sale` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 1, 'aaa', 100);
INSERT INTO `test` VALUES (2, 1, 'bbb', 200);
INSERT INTO `test` VALUES (3, 1, 'ccc', 200);
INSERT INTO `test` VALUES (4, 1, 'ddd', 300);
INSERT INTO `test` VALUES (5, 2, 'eee', 400);
INSERT INTO `test` VALUES (6, 2, 'fff', 200);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.

1. mysql 开窗函数需要 mysql 8.0以后才能用

• 分组是组内数据统计合并成一行
• 开窗函数是在分区内,统计当前行和前后数据行的关系，不会合并成一行

2. 开窗函数格式
   开窗取值函数 over(partition by 分区字段 order by 排序字段 函数取值范围 )
4. row_number() 区内生成唯一的编号
5. select *,row_number() over( partition by type order by sale ) as temp from test;

6. 

8. rank() 区内生成编号，分区字段值一样的生成一样的编号，并且编号不是连续的
9. select *,rank() over( partition by type order by sale ) as temp from test;

10. 

12. dense_rank() 区内生成不间断的编号，分区字段并且值一样的生成相同的编号
13. select *, dense_rank() over( partition by type order by sale ) as temp from test;

14. 

15. lead(fieldName,n,"默认值") 取后面的第几个字段。不带n默认是下一个，第一个字段可以是一个固定值
    select *, lead(id) over( partition by type order by sale ) as temp from test;

select *, lead(id,2) over( partition by type order by sale ) as temp from test;

select *, lead(id,2,"默认值") over( partition by type order by sale ) as temp from test;

8. lag(fieldName,n,"默认值") 取前面的第几个字段。不带n默认是上一个，第一个字段可以是一个固定值
9. select *, lag(id) over( partition by type order by sale ) as temp from test;
   select *, lag(id,2) over( partition by type order by sale ) as temp from test;
   select *, lag(id,2,"默认值") over( partition by type order by sale ) as temp from test;

10. 

12. first_value(fieldName),取分区内第一个值,这里需要注意这里的取值范围是 第一个到当前行(并且如果当前当前分区有多个值，会取到这个值的最后一行)
13. select *, first_value(id) over( partition by type order by sale ) as temp from test;

14. 

15. last_value(fieldName),取分区内最后一个值,这里需要注意这里的取值范围是 第一个到当前行(并且如果当前当前分区有多个值，会取到这个值的最后一行)
    select *, last_value(id) over( partition by type order by sale ) as temp from test;

11. nth_value(id,2,"默认值"),取当前分区的第几行的值，取值范围默认是第一行到当前行(并且如果当前当前分区有多个值，会取到这个值的最后一行)
12. select *, nth_value(id,2) over( partition by type order by sale ) as temp from test;

13. 

15. ntile(n) 把分区内数据分成多少分，分不均的时候，会依次分给前面的
16. select *, ntile(3) over( partition by type order by sale ) as temp from test;

17. 

19. max(fieldName) 分区内取最大值，范围默认是第一个到当前行，同值那行取到最后一个
20. select *, max(id) over( partition by type order by sale ) as temp from test;

21. 

23. min(fieldName)，最大值 ，范围默认是第一个到当前行，同值那行取到最后一个
24. select *, min(id) over( partition by type order by sale ) as temp from test;

25. 

27. avg，分区内取平均值，范围默认是第一个到当前行，同值那行取到最后一个
28. select *, avg(id) over( partition by type order by sale ) as temp from test;

29. 

31. sum，分区内求和，范围默认是第一个到当前行，同值那行取到最后一个
32. select *, sum(id) over( partition by type order by sale ) as temp from test;

33. 

35. count，分区内求数量，范围默认是第一个到当前行，同值那行取到最后一个
36. select *, count(id) over( partition by type order by sale ) as temp from test;

37. 

39. 开窗从句默认是第一个到当前行，同值那行取到最后一个， 等价于 range between unbounded preceding and current row
40. select *, count(id) over( partition by type order by sale range between unbounded preceding and current row ) as temp from test;

41. 

43. row 和 range的不同在于处理相同值排序的时候 ，价格相同range取得最后一个id，rows取的当前那个id
44. select *, count(id) over( partition by type order by sale rows between unbounded preceding and current row ) as temp from test;

45. 

46. 开窗从句，控制分区内，函数取值范围的。 格式 range|rows between 开始位置 and 结束位置

• 取第一个 unbounded preceding
• 取当前位置 current ROW

1. 取最后一个 unbounded following
2. 取当前位置前面n preceding
3. 取当前位置后面第n following

38. 开窗从句,取前一个到当前
    select *, sum(id) over( partition by type order by sale rows between 1 preceding and current row ) as temp from test;
39. 开窗从句,取前一个到最后
    select *, sum(id) over( partition by type order by sale rows between 1 preceding and unbounded following ) as temp from test;
40. 开窗从句,取当前到当前
    select *, sum(id) over( partition by type order by sale rows between current ROW and current ROW ) as temp from test;
41. 开窗从句,取全部
    select *, sum(id) over( partition by type order by sale rows between unbounded preceding and unbounded following ) as temp from test;

原创作者: u_15958225 转载于: https://blog.51cto.com/u_15958225/11802619