Analytics - Grouping ranges of data

View All Scripts Login to Run Script

Script Name Analytics - Grouping ranges of data
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script looks at the classical problem of identifying groups of data within a set of rows. Run this script standalone, or take it as part of the complete Analytics class at https://tinyurl.com/devgym-classes
Area SQL General / Analytics
Contributor Connor Mcdonald (Oracle)
Created Wednesday April 06, 2016

Statement 1
```
drop table LAB_SAMPLES
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942
Statement 2
```
drop sequence LAB_SAMPLES_SEQ
```
```
ORA-02289: sequence does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-02289

Statement 3

create sequence LAB_SAMPLES_SEQ

Sequence created.

Statement 4

create table LAB_SAMPLES
 ( sample_id          int default LAB_SAMPLES_SEQ.NEXTVAL,
   date_taken  date
 )

Table created.

Statement 5

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01')

1 row(s) inserted.

Statement 6

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-02')

1 row(s) inserted.

Statement 7

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-03')

1 row(s) inserted.

Statement 8

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-04')

1 row(s) inserted.

Statement 9

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-07')

1 row(s) inserted.

Statement 10

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-08')

1 row(s) inserted.

Statement 11

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-09')

1 row(s) inserted.

Statement 12

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-10')

1 row(s) inserted.

Statement 13

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-14')

1 row(s) inserted.

Statement 14

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-15')

1 row(s) inserted.

Statement 15

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-16')

1 row(s) inserted.

Statement 16

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-19')

1 row(s) inserted.

Statement 17

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-20')

1 row(s) inserted.

Statement 18

select * from LAB_SAMPLES order by 2


SAMPLE_ID DATE_TAKEN
1 01-DEC-15
2 02-DEC-15
3 03-DEC-15
4 04-DEC-15
5 07-DEC-15
6 08-DEC-15
7 09-DEC-15
8 10-DEC-15
9 14-DEC-15
10 15-DEC-15
11 16-DEC-15
12 19-DEC-15
13 20-DEC-15

13 rows selected.

SAMPLE_ID	DATE_TAKEN
1	01-DEC-15
2	02-DEC-15
3	03-DEC-15
4	04-DEC-15
5	07-DEC-15
6	08-DEC-15
7	09-DEC-15
8	10-DEC-15
9	14-DEC-15
10	15-DEC-15
11	16-DEC-15
12	19-DEC-15
13	20-DEC-15

Statement 19

select
  date_taken,
  lag(date_taken) over ( order by date_taken) prev
from lab_samples
order by 1


DATE_TAKEN PREV
01-DEC-15  - 
02-DEC-15 01-DEC-15
03-DEC-15 02-DEC-15
04-DEC-15 03-DEC-15
07-DEC-15 04-DEC-15
08-DEC-15 07-DEC-15
09-DEC-15 08-DEC-15
10-DEC-15 09-DEC-15
14-DEC-15 10-DEC-15
15-DEC-15 14-DEC-15
16-DEC-15 15-DEC-15
19-DEC-15 16-DEC-15
20-DEC-15 19-DEC-15

13 rows selected.

DATE_TAKEN	PREV
01-DEC-15	-
02-DEC-15	01-DEC-15
03-DEC-15	02-DEC-15
04-DEC-15	03-DEC-15
07-DEC-15	04-DEC-15
08-DEC-15	07-DEC-15
09-DEC-15	08-DEC-15
10-DEC-15	09-DEC-15
14-DEC-15	10-DEC-15
15-DEC-15	14-DEC-15
16-DEC-15	15-DEC-15
19-DEC-15	16-DEC-15
20-DEC-15	19-DEC-15

Statement 20

select
  date_taken,
  case
    when nvl(lag(date_taken) over (order by date_taken),date_taken) != date_taken-1
       then date_taken end loval
from lab_samples
order by 1


DATE_TAKEN LOVAL
01-DEC-15 01-DEC-15
02-DEC-15  - 
03-DEC-15  - 
04-DEC-15  - 
07-DEC-15 07-DEC-15
08-DEC-15  - 
09-DEC-15  - 
10-DEC-15  - 
14-DEC-15 14-DEC-15
15-DEC-15  - 
16-DEC-15  - 
19-DEC-15 19-DEC-15
20-DEC-15  - 

13 rows selected.

DATE_TAKEN	LOVAL
01-DEC-15	01-DEC-15
02-DEC-15	-
03-DEC-15	-
04-DEC-15	-
07-DEC-15	07-DEC-15
08-DEC-15	-
09-DEC-15	-
10-DEC-15	-
14-DEC-15	14-DEC-15
15-DEC-15	-
16-DEC-15	-
19-DEC-15	19-DEC-15
20-DEC-15	-

Statement 21

select date_taken, max(loval) over (order by date_taken) loval
from (
select date_taken,
 case
   when nvl(lag(date_taken) over (order by date_taken),date_taken) != 
    date_taken-1 then date_taken end loval
from lab_samples )
order by 1


DATE_TAKEN LOVAL
01-DEC-15 01-DEC-15
02-DEC-15 01-DEC-15
03-DEC-15 01-DEC-15
04-DEC-15 01-DEC-15
07-DEC-15 07-DEC-15
08-DEC-15 07-DEC-15
09-DEC-15 07-DEC-15
10-DEC-15 07-DEC-15
14-DEC-15 14-DEC-15
15-DEC-15 14-DEC-15
16-DEC-15 14-DEC-15
19-DEC-15 19-DEC-15
20-DEC-15 19-DEC-15

13 rows selected.

DATE_TAKEN	LOVAL
01-DEC-15	01-DEC-15
02-DEC-15	01-DEC-15
03-DEC-15	01-DEC-15
04-DEC-15	01-DEC-15
07-DEC-15	07-DEC-15
08-DEC-15	07-DEC-15
09-DEC-15	07-DEC-15
10-DEC-15	07-DEC-15
14-DEC-15	14-DEC-15
15-DEC-15	14-DEC-15
16-DEC-15	14-DEC-15
19-DEC-15	19-DEC-15
20-DEC-15	19-DEC-15

Statement 22

select min(date_taken) range_start, max(date_taken) range_end
from (
  select date_taken,max(loval) over (order by date_taken) loval
  from (
    select date_taken,
       case
         when nvl(lag(date_taken) over (order by date_taken),date_taken) != 
           date_taken-1 then date_taken end loval
    from lab_samples))
group by loval
order by 1


RANGE_START RANGE_END
01-DEC-15 04-DEC-15
07-DEC-15 10-DEC-15
14-DEC-15 16-DEC-15
19-DEC-15 20-DEC-15

4 rows selected.

RANGE_START	RANGE_END
01-DEC-15	04-DEC-15
07-DEC-15	10-DEC-15
14-DEC-15	16-DEC-15
19-DEC-15	20-DEC-15

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database