In Oracle 11g How Do You Time Weight Average Data Hourly Between Two Dates?
I have replaced my original question with this final answer. With the help from Mr. MTO and Mr. Ponder Stibbons and four months of playing around with my oracle 11G instance, I fin
Solution 1:
Using your sample data - it didn't have a full hour's data so I've done a weighted average per minute.
You haven't specified what you want to do at the boundaries so I've taken the weighted average of the immediately preceding and succeeding values.
Oracle 11g R2 Schema Setup:
CREATETABLE TEST ( Acronym, Date_Time, Value ) ASSELECT'32-PRESS', TIMESTAMP'15-01-01 00:00:07.120000000', 63.7363FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:00:17.088000000', 64.5604FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:00:27.864000000', 66.3004FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:00:45.080000000', 66.804FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:00:55.056000000', 67.4908FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:01:11.384000000', 66.9872FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:01:30.424000000', 67.4451FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:01:40.408000000', 67.9487FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:01:50.408000000', 68.6813FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:02:01.304000000', 68.1777FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:02:11.304000000', 67.1245FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:02:21.264000000', 66.5293FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:02:31.232000000', 65.4762FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:02:45.736000000', 65.0183FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:02:59.312000000', 64.5604FROM DUAL
UNIONALLSELECT'32-PRESS', TIMESTAMP'15-01-01 00:03:14.712000000', 64.1026FROM DUAL;
Query 1:
WITH temp AS (
SELECT ACRONYM,
DATE_TIME,
VALUEFROM TEST
UNIONSELECT ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' ),
NULLFROM TEST
GROUPBY
ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
UNIONSELECT ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' ) +INTERVAL'1'MINUTE,
NULLFROM TEST
GROUPBY
ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
ORDERBY1,2
),
temp2 AS (
SELECT ACRONYM,
DATE_TIME,
COALESCE(
VALUE,
COALESCE(
LAG( VALUE ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME ),
LEAD( VALUE ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME )
)
+
(
COALESCE(
LEAD( VALUE ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME ),
LAG( VALUE ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME )
)
-COALESCE(
LAG( VALUE ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME ),
LEAD( VALUE ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME )
)
)
*EXTRACT( SECONDFROM ( DATE_TIME -LAG( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME ) ) )
/EXTRACT( SECONDFROM (
LEAD( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME )
-LAG( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME )
) )
) ASVALUE,
LEAD( DATE_TIME ) OVER ( PARTITIONBY ACRONYM ORDERBY DATE_TIME ) AS NEXT_DATE_TIME
FROM temp
)
SELECT ACRONYM,
TO_DATE( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' ) AS DATE_TIME,
SUM( VALUE*EXTRACT( SECONDFROM ( NEXT_DATE_TIME - DATE_TIME ) ) ) /60ASVALUEFROM temp2
WHERE NEXT_DATE_TIME ISNOTNULLGROUPBY
ACRONYM,
TO_DATE( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
ORDERBY1,2|ACRONYM|DATE_TIME|VALUE||----------|---------------------------|-------------------||32-PRESS|January,010015 00:00:00|65.43946117333333||32-PRESS|January,010015 00:01:00|67.56109262835211||32-PRESS|January,010015 00:02:00|66.32093658633383||32-PRESS|January,010015 00:03:00|64.20983764043636|EDIT
Oracle 11g R2 Schema Setup:
CREATE TABLE POINTS ( RECNM NUMBER, ACRONYM VARCHAR2(20) );
INSERT INTO POINTS VALUES(1136, '32-PRESS');
INSERT INTO POINTS VALUES(1138, 'OTHER_POINT');
CREATE TABLE HST ( RECNM NUMBER, TIME TIMESTAMP, VALUE NUMBER );
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:00',63.3);
INSERT INTO HST VALUES(1138, TIMESTAMP '15-01-01 00:00:00',0.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:07',63.7);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:17',64.6);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:28',66.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:45',66.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:00:55',67.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:01:11',67.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:01:30',67.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:01:40',67.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:01:50',68.7);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:01',68.2);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:11',67.1);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:21',66.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:31',65.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:46',65.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:02:59',64.6);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:03:15',64.1);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:03:25',63.2);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:03:35',62.7);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:04:05',62.2);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:04:32',61.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:05:40',61.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:05:55',60.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:10:20',60.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:10:38',60.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:10:48',61.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:10:58',61.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:11:27',62.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:13:54',61.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:14:10',61.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:14:41',60.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:15:18',61.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:15:51',60.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:16:19',60.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:16:32',59.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:17:04',59.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:17:27',59.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:17:37',59.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:17:58',59.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:18:22',59.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:18:50',59.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:00',60.3);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:25',60.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:34',61.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:45',62.1);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:19:55',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:20:30',63.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:20:51',63.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:21:03',63.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:22:04',64.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:22:28',64.8);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:23:17',64.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:23:27',63.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:24:31',63.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:26:06',63.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:27:20',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:27:30',61.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:28:08',62.4);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:28:37',62.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:29:21',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:29:38',62.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:31:27',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:32:01',62.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:32:25',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:35:07',62.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:35:56',62.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:36:06',62.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:36:59',61.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:39:31',62.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:40:12',61.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:40:22',60.9);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:40:35',60.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:40:55',60.0);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:41:22',60.5);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:41:46',60.1);
INSERT INTO HST VALUES(1136, TIMESTAMP '15-01-01 00:42:31',60.6);
Query 1:
WITH inputs AS (
SELECT RECNM,
TIMESTAMP'15-01-01 00:00:00'AS start_time,
TIMESTAMP'15-01-01 00:40:00'AS end_time
FROM POINTS
WHERE ACRONYM ='32-PRESS'
),
all_minutes AS (
SELECT RECNM,
start_time + (LEVEL-1)/24/60AStimeFROM inputs
CONNECTBY
LEVEL -1<=EXTRACT( MINUTEFROM end_time - start_time )
),
all_times AS (
SELECTTIME,
VALUE,
1AS HAS_VALUE
FROM HST h
INNERJOIN inputs i
ON ( h.RECNM = i.RECNM
AND h.TIME BETWEEN i.start_time
AND i.end_time )
UNIONALLSELECTTIME,
NULL,
0FROM all_minutes
ORDERBY1, 2 NULLS FIRST
),
lag_lead_ignore_nulls AS (
SELECTTIME,
VALUE,
COUNT( VALUE ) OVER ( ORDERBYTIMEASC, VALUEASC NULLS FIRST ) AS LAG_GRP,
COUNT( VALUE ) OVER ( ORDERBYTIMEDESC, VALUEDESC NULLS LAST ) AS LEAD_GRP
FROM all_times
),
lag_lead_values AS (
SELECTTIME,
VALUE,
FIRST_VALUE( TIME ) OVER ( PARTITIONBY LAG_GRP ORDERBYVALUEASC NULLS LAST ) AS PREV_MEASURED_TIME,
FIRST_VALUE( VALUE ) OVER ( PARTITIONBY LAG_GRP ORDERBYVALUEASC NULLS LAST ) AS PREV_MEASURED_VALUE,
FIRST_VALUE( TIME ) OVER ( PARTITIONBY LEAD_GRP ORDERBYVALUEASC NULLS LAST ) AS NEXT_MEASURED_TIME,
FIRST_VALUE( VALUE ) OVER ( PARTITIONBY LEAD_GRP ORDERBYVALUEASC NULLS LAST ) AS NEXT_MEASURED_VALUE,
LEAD( TIME ) OVER ( ORDERBYTIMEASC ) AS NEXT_TIME
FROM lag_lead_ignore_nulls
),
interpolated_values AS (
SELECTCAST( TIMEASDATE ) TIME,
COALESCE(
VALUE,
PREV_MEASURED_VALUE
+ ( NEXT_MEASURED_VALUE - PREV_MEASURED_VALUE )
* (
60*EXTRACT( MINUTEFROMTIME- PREV_MEASURED_TIME )
+EXTRACT( SECONDFROMTIME- PREV_MEASURED_TIME )
)
/ (
60*EXTRACT( MINUTEFROM NEXT_MEASURED_TIME - PREV_MEASURED_TIME )
+EXTRACT( SECONDFROM NEXT_MEASURED_TIME - PREV_MEASURED_TIME )
)
) AS INTERPOLATED_VALUE,
60*EXTRACT( MINUTEFROM NEXT_TIME -TIME )
+EXTRACT( SECONDFROM NEXT_TIME -TIME ) AS DURATION
FROM lag_lead_values
)
SELECT TRUNC( TIME, 'MI' ) ASTIME,
SUM( INTERPOLATED_VALUE * DURATION ) /SUM( DURATION ) AS TWA,
SUM( DURATION ) AS TOTAL_DURATION
FROM interpolated_values
WHERE INTERPOLATED_VALUE ISNOTNULLGROUPBY TRUNC( TIME, 'MI' )
ORDERBYTIMEASC|TIME|TWA|TOTAL_DURATION||---------------------------|--------------------|----------------||January,010015 00:00:00|65.38833333333333|60||January,010015 00:01:00|67.56302083333334|60||January,010015 00:02:00|66.30575757575758|60||January,010015 00:03:00|63.48385416666667|60||January,010015 00:04:00|62.02027777777778|60||January,010015 00:05:00|61.45441176470588|60||January,010015 00:06:00|60.79056603773585|60||January,010015 00:07:00|60.677358490566036|60||January,010015 00:08:00|60.56415094339623|60||January,010015 00:09:00|60.450943396226414|60||January,010015 00:10:00|60.62924528301887|60||January,010015 00:11:00|62.09051724137931|60||January,010015 00:12:00|62.18775510204082|60||January,010015 00:13:00|61.96530612244898|60||January,010015 00:14:00|61.28333333333333|60||January,010015 00:15:00|61.252027027027026|60||January,010015 00:16:00|60.27410714285714|60||January,010015 00:17:00|59.47416666666667|60||January,010015 00:18:00|59.34888888888889|60||January,010015 00:19:00|61.06|60||January,010015 00:20:00|62.86071428571429|60||January,010015 00:21:00|63.895|60||January,010015 00:22:00|64.61114754098361|60||January,010015 00:23:00|64.16431972789115|60||January,010015 00:24:00|63.52513020833333|60||January,010015 00:25:00|63.27789473684211|60||January,010015 00:26:00|63.002526315789474|60||January,010015 00:27:00|62.245045045045046|60||January,010015 00:28:00|62.23263157894737|60||January,010015 00:29:00|62.56314393939394|60||January,010015 00:30:00|62.81926605504587|60||January,010015 00:31:00|62.544587155963306|60||January,010015 00:32:00|62.29191176470588|60||January,010015 00:33:00|62.58641975308642|60||January,010015 00:34:00|62.73456790123457|60||January,010015 00:35:00|62.87131687242798|60||January,010015 00:36:00|62.02166666666667|60||January,010015 00:37:00|61.50328947368421|60||January,010015 00:38:00|61.70065789473684|60||January,010015 00:39:00|61.94731359649123|60|Solution 2:
This query generated desired values:
with input as (
selectvalue, htime, to_char(htime, 'yyyy-mm-dd hh24:mi') mnt,
extract(dayfrom d)+extract(hourfrom d)/24+extract(minutefrom d)/(24*60)+extract (secondfrom d)/(24*60*60) tm
from (selectvalue, htime, htime-timestamp'1899-12-30 00:00:00' d from test))
selectdistinct mnt, round(
sum(tm*value) over (partitionby mnt)/sum(tm) over (partitionby mnt), 6) wav
from input orderby mnt
Output:
MNTWAV--------------------------2015-01-01 12:0065.778382015-01-01 12:0167.7655752015-01-01 12:0266.1477332015-01-01 12:0364.1026According to documentation Excel calendar starts with '1900-01-01',
but I had to modify this date slightly to achieve "date zero" to get number values for time difference exactly like in spreadsheet.
Rest is only the matter of substracting timestamps, casting this difference to number and summing results for each minute with function sum() in analytic version.
If you have gaps in data you need at first create periods for each minute with recursive query (connect by) and then left join this query with mine filling data for gaps with function lag(wav ignore nulls) gathering weighted average from previous minute(s).
Edit: Version filling gaps:
with input as (
selectvalue, htime, to_char(htime, 'yyyy-mm-dd hh24:mi') mnt,
extract(dayfrom d)+extract(hourfrom d)/24+extract(minutefrom d)/(24*60)+extract (secondfrom d)/(24*60*60) tm
from (selectvalue, htime, htime-timestamp'1899-12-30 00:00:00' d from data)),
periodas (select to_date(min(mnt), 'yyyy-mm-dd hh24:mi') m1,
to_date(max(mnt), 'yyyy-mm-dd hh24:mi') m2 from input),
minutes as (
select to_char(to_date(m1) + (level -1)/(24*60), 'yyyy-mm-dd hh24:mi') mnt
fromperiodconnectby level+1<(m2-m1)*24*60),
calc as (
selectdistinct mnt,
round(sum(tm*value) over (partitionby mnt)/sum(tm) over (partitionby mnt), 6) wav
from minutes leftjoin input using (mnt) orderby mnt)
select mnt, wav, nvl(wav, lag(wav ignore nulls) over (orderby mnt)) wavg from calc
Subquery inputprepares data for further treatment, period selects min and max minute from table
(you can insert some values by hand here instead of querying from table, for example "date '2015-01-01 13:52:00'"),
minutes generates... minutes recursively for given period, calc counts weighted averages joining input and minutes,
last select fills last known averages for empty minutes - you can observe it for minutes 6, 7, 12 in SQLFiddle.
Post a Comment for "In Oracle 11g How Do You Time Weight Average Data Hourly Between Two Dates?"