analytics-the-Tabitosan-method.sql

REM Script: Analytics - the Tabitosan method
REM Example of the tabitosan method at the granularity of minutes rather than days.

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

drop table LAB_SAMPLES purge;

drop sequence LAB_SAMPLES_SEQ purge;

create sequence LAB_SAMPLES_SEQ;

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

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01' + 123/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01' + 124/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01' + 125/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01' + 126/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01' + 567/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01' + 568/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-01' + 569/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-02' + 123/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-02' + 124/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-02' + 456/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-02' + 789/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-04' + 345/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-04' + 346/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-04' + 347/1440);

insert into LAB_SAMPLES ( date_taken) values (date '2015-12-04' + 789/1440);

select * from LAB_SAMPLES order by 2;

select date_taken, 
 row_number() over(order by date_taken)/1440 as rn 
 from LAB_SAMPLES 
order by 1;

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

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)/1440 as delta 
 from LAB_SAMPLES 
 ) 
group by delta 
order by 1;