Oracle MySQL Hive sql判断数字、日期是否连续 row_number()over() 窗口函数_oracle判断日期是否连续

工作中可能会遇到判断日期时间是不是连续的，数字是不是连续的，这时候可以给字段做个row_number排序，然后字段和排序的数字相减，会得到一个固定值，如果相减后的结果一致，则表名是连续的

注：本人数据库使用的是mysql 8.0.30版本，row_number()over()窗口函数在MySQL 8以下是没有的，没有MySQL高版本的，也可以用Hive和Oracle测试，HIve、Oracle和此教程sql大同小异

准备MySQL数据：

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `test_name` varchar(255),
  `test_number` int(11),
  `test_date` date
);
INSERT INTO `test` VALUES ('张三', 1, '2022-09-29');
INSERT INTO `test` VALUES ('张三', 3, '2022-09-30');
INSERT INTO `test` VALUES ('张三', 6, '2022-10-01');
INSERT INTO `test` VALUES ('张三', 8, '2022-10-02');
INSERT INTO `test` VALUES ('李四', 5, '2022-09-29');
INSERT INTO `test` VALUES ('李四', 6, '2022-10-02');
INSERT INTO `test` VALUES ('李四', 7, '2022-10-04');
INSERT INTO `test` VALUES ('李四', 8, '2022-10-06');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

准备了两组数据，每人持有四个数字和四个日期，找出四个数字连续的和日期连续的人。

一、数字连续

实现思路：先用row_number()over()窗口函数，对人名（test_name）进行分组，对数字（test_number）进行正序排序，得到每个数字的排列顺序，用数字减去排序的数字，如果数字是连续的，顺序也是连续的，那么两者相减会得到一个相同的数字。

select			test_name
				,test_number
				,row_number() over(partition by test_name order by test_number) rn  --排序的序号
				,test_number - row_number() over(partition by test_name order by test_number) diff  --差值
from            test
1
2
3
4
5

得到的结果如下:很显然李四的数字是连续的，差值也是同一个数字

根据人名和差值分组，找出count()等于4个的

select			a.test_name
from(
select			test_name
				,test_number
				,row_number() over(partition by test_name order by test_number) rn
				,test_number - row_number() over(partition by test_name order by test_number) diff
from            test
) a
group by   	    a.test_name
			    ,a.diff
having          count(1) = 4
1
2
3
4
5
6
7
8
9
10
11

结果如下：

二、日期连续

实现思路：同理，对日期排序，日期减去排序的数字，得到一个固定的日期

select			test_name
				,test_date
				,row_number() over(partition by test_name order by test_date) rn
				,date_sub(test_date,interval row_number() over(partition by test_name order by test_date) day) diff
from            test
1
2
3
4
5

得到的结果如下：张三日期减去排序的数字，得到一个相同的日期

根据人名和相减得到的日期分组，找到count()等于4个的

select			a.test_name
from(
select			test_name
				,test_date
				,row_number() over(partition by test_name order by test_date) rn
				,date_sub(test_date,interval row_number() over(partition by test_name order by test_date) day) diff
from            test
) a
group by        a.test_name
				,a.diff
having          count(1) = 4
1
2
3
4
5
6
7
8
9
10
11

结果如下：

注：这里仅做简单演示，个人根据需求进行优化