Analytics - Ranking part 3

View All Scripts Login to Run Script

Script Name Analytics - Ranking part 3
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script demonstrates the Tabibitosan method for grouping sets of data. 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 Thursday December 17, 2015

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,
       row_number() over(order by date_taken) as rn
       from LAB_SAMPLES
order by 1

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

13 rows selected.

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

Statement 20

select date_taken,
       date_taken-row_number() over(order by date_taken) as delta
       from LAB_SAMPLES
order by 1

 
DATE_TAKEN DELTA
01-DEC-15 30-NOV-15
02-DEC-15 30-NOV-15
03-DEC-15 30-NOV-15
04-DEC-15 30-NOV-15
07-DEC-15 02-DEC-15
08-DEC-15 02-DEC-15
09-DEC-15 02-DEC-15
10-DEC-15 02-DEC-15
14-DEC-15 05-DEC-15
15-DEC-15 05-DEC-15
16-DEC-15 05-DEC-15
19-DEC-15 07-DEC-15
20-DEC-15 07-DEC-15

13 rows selected.

DATE_TAKEN	DELTA
01-DEC-15	30-NOV-15
02-DEC-15	30-NOV-15
03-DEC-15	30-NOV-15
04-DEC-15	30-NOV-15
07-DEC-15	02-DEC-15
08-DEC-15	02-DEC-15
09-DEC-15	02-DEC-15
10-DEC-15	02-DEC-15
14-DEC-15	05-DEC-15
15-DEC-15	05-DEC-15
16-DEC-15	05-DEC-15
19-DEC-15	07-DEC-15
20-DEC-15	07-DEC-15

Statement 21

select min(date_taken) date_from,
       max(date_taken) date_to,
       count(*) num_samples
from (
  select date_taken,
         date_taken-row_number() over(order by date_taken) as delta
         from LAB_SAMPLES
     )
group by delta
order by 1

 
DATE_FROM DATE_TO NUM_SAMPLES
01-DEC-15 04-DEC-15 4
07-DEC-15 10-DEC-15 4
14-DEC-15 16-DEC-15 3
19-DEC-15 20-DEC-15 2

4 rows selected.

DATE_FROM	DATE_TO	NUM_SAMPLES
01-DEC-15	04-DEC-15	4
07-DEC-15	10-DEC-15	4
14-DEC-15	16-DEC-15	3
19-DEC-15	20-DEC-15	2

Additional Information

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