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

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!