With Oracle 18C
I would like to create a statistics table for some fields existing on the historical table
Given a date as starting point the query should create all the statistics for the dates bigger than the date provided
The data used to calculate the statistics is based on all records with a date older than the one being analyzed.
For example if we are analyzing the date 2019-08-17 all the records older than this date should be taken for statistics calculation.
Before starting to provide some solution, please first ask any questions about any information that I forgot to mention
Below you can see some representation of what is required.
Table Definition
CREATE TABLE HISTORY (
"DDATE" DATE,
"N1" NUMBER(2, 0),
"N2" NUMBER(2, 0),
"N3" NUMBER(2, 0),
"N4" NUMBER(2, 0),
"N5" NUMBER(2, 0),
"N6" NUMBER(2, 0)
)
Insert Statement
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-27 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,2,34,38,44,48);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-23 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,8,19,22,41,43);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-18 00:00:00','YYYY-MM-DD HH24:MI:SS'),2,4,15,17,44,49);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-18 00:00:00','YYYY-MM-DD HH24:MI:SS'),2,8,13,15,21,27);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-09 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,5,13,19,25,26);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-27 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,6,8,19,20,43);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-22 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,22,35,36,41,43);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-17 00:00:00','YYYY-MM-DD HH24:MI:SS'),5,6,8,24,35,45);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),5,10,11,15,23,31);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),6,9,10,18,31,38);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-30 00:00:00','YYYY-MM-DD HH24:MI:SS'),7,8,13,16,28,41);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),7,21,23,26,43,44);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-08 00:00:00','YYYY-MM-DD HH24:MI:SS'),8,9,12,30,33,49);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),9,13,25,28,40,43);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),10,12,15,20,21,30);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-27 00:00:00','YYYY-MM-DD HH24:MI:SS'),11,12,14,30,37,40);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-24 00:00:00','YYYY-MM-DD HH24:MI:SS'),13,18,23,27,40,48);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-10 00:00:00','YYYY-MM-DD HH24:MI:SS'),14,17,26,35,39,46);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-06 00:00:00','YYYY-MM-DD HH24:MI:SS'),14,19,33,38,39,41);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-06 00:00:00','YYYY-MM-DD HH24:MI:SS'),15,19,34,40,42,43);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2222-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),9,9,9,9,9,9);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),13,20,31,34,40,45);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,9,11,23,42,46);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-11 00:00:00','YYYY-MM-DD HH24:MI:SS'),7,11,12,32,34,48);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),9,21,36,37,39,40);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),5,9,20,27,31,46);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-06-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),9,14,18,25,33,40);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-02 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,18,29,40,42,46);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-04 00:00:00','YYYY-MM-DD HH24:MI:SS'),8,24,27,30,36,46);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-11 00:00:00','YYYY-MM-DD HH24:MI:SS'),4,6,11,23,40,49);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-16 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,5,10,15,27,30);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),5,10,13,16,18,27);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,5,11,14,15,43);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-03 00:00:00','YYYY-MM-DD HH24:MI:SS'),1,18,23,29,38,42);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-08 00:00:00','YYYY-MM-DD HH24:MI:SS'),4,5,9,27,35,45);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-13 00:00:00','YYYY-MM-DD HH24:MI:SS'),3,10,14,31,47,49);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-19 00:00:00','YYYY-MM-DD HH24:MI:SS'),9,15,19,22,29,35);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-22 00:00:00','YYYY-MM-DD HH24:MI:SS'),7,12,20,26,28,44);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-08-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),8,16,22,25,36,43);
Insert into HISTORY (DDATE,N1,N2,N3,N4,N5,N6) values (to_date('2019-07-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),11,16,20,25,31,40);
I started the SQL statement to implement what I am describing above, this might help you to understand better
SELECT DISTCALC.MAX\_DATE ANALISYS\_DATE
,N1
,MAX(DISTCALC.DDATE) LASTDRAW
,CFIELD\_REP
,MIN(ABS(DISTCALC.distance)) MinDist
,ROUND(MEDIAN(DISTCALC.distance)-STDDEV\_POP(DISTCALC.distance)) LOWER\_LIMIT
,ROUND(MEDIAN(DISTCALC.distance)) DistMedian
,ROUND(MEDIAN(DISTCALC.distance)+STDDEV\_POP(DISTCALC.distance)) HIGHER\_LIMIT
,MAX(DISTCALC.distance) MaxDist
FROM (SELECT MAX(t.DDATE) OVER() MAX\_DATE
,t.DDATE
,t.N1
,count(\*) over (partition by N1) CFIELD\_REP
,NVL(t.DDATE -lag (t.DDATE) over (partition by N1 order by t.DDATE ASC), 0) Distance
FROM HISTORY t
WHERE t.DDATE \< TO\_DATE('2019-08-29','YYYY-MM-DD')
ORDER BY DDATE DESC) DISTCALC
WHERE DISTCALC.distance > 0
OR (DISTCALC.DDATE = DISTCALC.MAX\_DATE AND DISTCALC.distance = 0)
OR (DISTCALC.CFIELD\_REP = 1 AND DISTCALC.distance = 0)
GROUP BY N1,CFIELD\_REP, DISTCALC.MAX\_DATE;