Oracle 查询连续日期

在线数据

http://sqlfiddle.com/

create table test
(
  id    VARCHAR2(32),
  vcode VARCHAR2(50),
  qdate date
);
insert into test select * from (
select '1' id, 'A001' as vcode, date '2019-05-01' as qdate from dual union
select '2' id, 'A001' as vcode, date '2019-05-02' as qdate from dual union
select '3' id, 'A001' as vcode, date '2019-05-03' as qdate from dual union
select '4' id, 'A001' as vcode, date '2019-05-05' as qdate from dual union
select '5' id, 'A001' as vcode, date '2019-05-07' as qdate from dual union
select '6' id, 'A001' as vcode, date '2019-05-09' as tqdateime from dual union
select '7' id, 'A001' as vcode, date '2019-05-10' as qdate from dual union
select '8' id, 'B001' as vcode, date '2019-05-06' as qdate from dual union
select '9' id, 'B001' as vcode, date '2019-05-07' as qdate from dual union
select '10' id, 'B001' as vcode, date '2019-05-08' as qdate from dual) 

生成表如下：

D	VCODE	QDATE
1	A001	2019-05-01T00:00:00Z
10	B001	2019-05-08T00:00:00Z
2	A001	2019-05-02T00:00:00Z
3	A001	2019-05-03T00:00:00Z
4	A001	2019-05-05T00:00:00Z
5	A001	2019-05-07T00:00:00Z
6	A001	2019-05-09T00:00:00Z
7	A001	2019-05-10T00:00:00Z
8	B001	2019-05-06T00:00:00Z
9	B001	2019-05-07T00:00:00Z

按照名字vcode分组，按照日期qdate排序

select
t.*,
row_number() over(partition by vcode order by qdate) as rn
from test t;

结果如下：

ID	VCODE	QDATE	RN
1	A001	2019-05-01T00:00:00Z	1
2	A001	2019-05-02T00:00:00Z	2
3	A001	2019-05-03T00:00:00Z	3
4	A001	2019-05-05T00:00:00Z	4
5	A001	2019-05-07T00:00:00Z	5
6	A001	2019-05-09T00:00:00Z	6
7	A001	2019-05-10T00:00:00Z	7
8	B001	2019-05-06T00:00:00Z	1
9	B001	2019-05-07T00:00:00Z	2
10	B001	2019-05-08T00:00:00Z	3

提取日期减去行号，得到的结果rn连续相同时即为日期连续组

select
t.*,
trunc(qdate) - row_number() over(partition by vcode order by qdate) as rn
from test t;

结果如下：

ID	VCODE	QDATE	RN
1	A001	2019-05-01T00:00:00Z	2019-04-30T00:00:00Z
2	A001	2019-05-02T00:00:00Z	2019-04-30T00:00:00Z
3	A001	2019-05-03T00:00:00Z	2019-04-30T00:00:00Z
4	A001	2019-05-05T00:00:00Z	2019-05-01T00:00:00Z
5	A001	2019-05-07T00:00:00Z	2019-05-02T00:00:00Z
6	A001	2019-05-09T00:00:00Z	2019-05-03T00:00:00Z
7	A001	2019-05-10T00:00:00Z	2019-05-03T00:00:00Z
8	B001	2019-05-06T00:00:00Z	2019-05-05T00:00:00Z
9	B001	2019-05-07T00:00:00Z	2019-05-05T00:00:00Z
10	B001	2019-05-08T00:00:00Z	2019-05-05T00:00:00Z

根据vcode和rn分组，得到的count即为连续的天数

select vcode, rn, count(*)
from (
select
t.*,
trunc(qdate) - row_number() over(partition by vcode order by qdate) as rn
from test t
) group by vcode, rn;

结果如下：

VCODE	RN	COUNT(*)
B001	2019-05-05T00:00:00Z	3
A001	2019-05-01T00:00:00Z	1
A001	2019-05-02T00:00:00Z	1
A001	2019-05-03T00:00:00Z	2
A001	2019-04-30T00:00:00Z	3

通过having即可筛选出连续天数>=2的vcode

select vcode, rn, count(*)
from (
select
t.*,
trunc(qdate) - row_number() over(partition by vcode order by qdate) as rn
from test t
) group by vcode, rn having count(1) >= 2;

结果如下：

VCODE	RN	COUNT(*)
B001	2019-05-05T00:00:00Z	3
A001	2019-05-03T00:00:00Z	2
A001	2019-04-30T00:00:00Z	3