Oralce 查询连续日期
在线数据
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 |
