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

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

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

准备MySQL数据：

DROPTABLEIFEXISTS`test`;CREATETABLE`test`(`test_name`varchar(255),`test_number`int(11),`test_date`date);INSERTINTO`test`VALUES('张三',1,'2022-09-29');INSERTINTO`test`VALUES('张三',3,'2022-09-30');INSERTINTO`test`VALUES('张三',6,'2022-10-01');INSERTINTO`test`VALUES('张三',8,'2022-10-02');INSERTINTO`test`VALUES('李四',5,'2022-09-29');INSERTINTO`test`VALUES('李四',6,'2022-10-02');INSERTINTO`test`VALUES('李四',7,'2022-10-04');INSERTINTO`test`VALUES('李四',8,'2022-10-06');

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

一、数字连续

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

select            test_name
                ,test_number
                ,row_number()over(partitionby test_name orderby test_number) rn  --排序的序号,test_number - row_number()over(partitionby test_name orderby test_number) diff  --差值from            test

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

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

select            a.test_name
from(select            test_name
                ,test_number
                ,row_number()over(partitionby test_name orderby test_number) rn
                ,test_number - row_number()over(partitionby test_name orderby test_number) diff
from            test
) a
groupby           a.test_name
                ,a.diff
havingcount(1)=4

结果如下：

二、日期连续

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

select            test_name
                ,test_date
                ,row_number()over(partitionby test_name orderby test_date) rn
                ,date_sub(test_date,interval row_number()over(partitionby test_name orderby test_date)day) diff
from            test

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

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

select            a.test_name
from(select            test_name
                ,test_date
                ,row_number()over(partitionby test_name orderby test_date) rn
                ,date_sub(test_date,interval row_number()over(partitionby test_name orderby test_date)day) diff
from            test
) a
groupby        a.test_name
                ,a.diff
havingcount(1)=4

结果如下：

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