In Oracle 11g, how do you time-weight the hourly average between two dates?
I replaced my original question with this final answer. With the help of Mr MTO and Mr Power Stibbons and four months of working with my instance of the 11G oracle, I finally got what you see here. This query is specially designed for SCADA systems and will do the following ...
This query will execute the Weighted Average Hourly Time between two dates such as TWA, Minimum and Maximum Values during that interval as Vmin and Vmax. It will also return the low time and high time as Hmin and Hmax. (This is the time of the date the minimum value appears and the maximum value appears.) The start and end values of the interval as VSTART and VEND. This request will not fail in daylight in March or August. (This is why I am using TO_TIMESTAMP_TZ)
Note: This query is configurable for 1 hour and any desired interval is possible by replacing and adding only a few items. So enjoy!
This query works on my Oracle 11g instance and after writing this post, I copied the exact text below and pasted it into my SQL Developer. This is how it works !! I'm having trouble getting this running in sqlfiddle, but I'll review and test it soon.
-- Lets Begin the Query
WITH INPUTS AS (
SELECT RECNM,
TO_TIMESTAMP_TZ ( '01-JAN-15 00:00:00 AMERICA/LOS_ANGELES','DD-MON-RR HH24:MI:SS TZR' ) AS START_TIME,
TO_TIMESTAMP_TZ ( '06-NOV-15 23:59:59 AMERICA/LOS_ANGELES','DD-MON-RR HH24:MI:SS TZR' ) AS END_TIME
FROM POINTS
WHERE ACRONYM = 'WELL32-PSI'
) ,
ALL_INTERVALS AS (
SELECT RECNM,
START_TIME + NUMTODSINTERVAL ( ( LEVEL-1 ) , 'HOUR' ) AS TIME
FROM INPUTS
CONNECT BY
LEVEL-1 <=
EXTRACT ( DAY FROM END_TIME - START_TIME ) * 24 +
EXTRACT ( HOUR FROM END_TIME - START_TIME )
) ,
ALL_TIMES AS (
SELECT
TIME,
VALUE,
1 AS HAS_VALUE
FROM HST H
INNER JOIN INPUTS I
ON ( H.RECNM = I.RECNM
AND H.TIME BETWEEN CAST ( I.START_TIME AS TIMESTAMP )
AND CAST ( I.END_TIME AS TIMESTAMP ) )
UNION ALL
SELECT
TIME,
NULL,
0
FROM ALL_INTERVALS
ORDER BY TIME,1, 2 NULLS FIRST
) ,
LEAD_LAG_TIMES AS (
SELECT
TIME,
LAST_VALUE ( VALUE IGNORE NULLS ) OVER ( ORDER BY TIME ASC, VALUE ASC ) AS VALUE,
24 * 60 * 60 * EXTRACT ( DAY FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) +
60 * 60 * EXTRACT ( HOUR FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) +
60 * EXTRACT ( MINUTE FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) +
EXTRACT ( SECOND FROM LEAD ( TIME ) OVER ( ORDER BY TIME ASC,VALUE ASC ) -TIME ) AS DURATION
FROM ALL_TIMES
)
SELECT CAST ( TRUNC ( TIME,'HH24' ) AS TIMESTAMP WITH TIME ZONE ) AS TIME,
SUM ( VALUE * DURATION ) / SUM ( DURATION ) AS TWA,
MIN ( VALUE ) AS VMIN,
MAX ( TIME ) KEEP ( DENSE_RANK LAST ORDER BY VALUE DESC ) AS TMIN,
MAX ( VALUE ) AS VMAX,
MAX ( TIME ) KEEP ( DENSE_RANK LAST ORDER BY VALUE ASC ) AS TMAX,
SUM ( VALUE ) AS TOTAL,
MAX ( VALUE ) KEEP (DENSE_RANK FIRST ORDER BY TIME ASC) as VSTART,
MAX ( VALUE ) KEEP (DENSE_RANK LAST ORDER BY TIME ASC) as VEND,
SUM ( DURATION ) AS TOTAL_DURATION
FROM LEAD_LAG_TIMES
GROUP BY CAST ( TRUNC ( TIME,'HH24' ) AS TIMESTAMP WITH TIME ZONE )
ORDER BY TIME ASC
EDIT: You can include this in the final selection statement for a 1 hour Time Weighted Rolling Average! I find this very useful in the wastewater industry as government regulations / reporting require 24 hour moving averages and 72 minute moving averages. If you need 24 moving average changes ROWS 1 PROCECDING to ROWS 24 PROCEDING
ROUND( AVG ( SUM ( value * DURATION ) / sum ( DURATION ) ) OVER (ORDER BY CAST ( TRUNC ( TIME,'hh24' ) AS TIMESTAMP WITH TIME ZONE ), CAST ( TRUNC ( TIME,'hh24' ) AS TIMESTAMP WITH TIME ZONE ) ROWS 1 PRECEDING),2) AS ROLLING_1H_VAVG,
Standard deviation is fun, so add that as well.
ROUND( STDDEV ( VALUE ) , 2 ) as VDEV,
If you need a value before your start time and after your stop time, you can put it along with another union.
UNION ALL
SELECT
MAX(H.TIME) KEEP (DENSE_RANK FIRST ORDER BY H.TIME DESC) AS TIME,
MAX(H.VALUE) KEEP (DENSE_RANK FIRST ORDER BY H.TIME DESC),
1
FROM INPUTS I
INNER JOIN HST H
ON H.TIME < I.START_TIME
UNION ALL
SELECT
MIN(H.TIME) KEEP (DENSE_RANK FIRST ORDER BY H.TIME) AS TIME,
MIN(H.VALUE) KEEP (DENSE_RANK FIRST ORDER BY H.TIME),
1
FROM INPUTS I
INNER JOIN HST H
ON H.TIME > I.END_TIME
+3
source to share
2 answers
Using your sample data - it didn't have the full hour data so I made a weighted average per minute.
You have not specified what you want to do at the borders, so I took the weighted average of the immediately preceding and following values.
Oracle 11g R2 schema setup :
CREATE TABLE TEST ( Acronym, Date_Time, Value ) AS
SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:07.120000000', 63.7363 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:17.088000000', 64.5604 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:27.864000000', 66.3004 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:45.080000000', 66.804 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:00:55.056000000', 67.4908 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:01:11.384000000', 66.9872 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:01:30.424000000', 67.4451 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:01:40.408000000', 67.9487 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:01:50.408000000', 68.6813 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:01.304000000', 68.1777 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:11.304000000', 67.1245 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:21.264000000', 66.5293 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:31.232000000', 65.4762 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:45.736000000', 65.0183 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:02:59.312000000', 64.5604 FROM DUAL
UNION ALL SELECT '32-PRESS', TIMESTAMP '15-01-01 00:03:14.712000000', 64.1026 FROM DUAL;
Request 1 :
WITH temp AS (
SELECT ACRONYM,
DATE_TIME,
VALUE
FROM TEST
UNION
SELECT ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' ),
NULL
FROM TEST
GROUP BY
ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
UNION
SELECT ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' ) + INTERVAL '1' MINUTE,
NULL
FROM TEST
GROUP BY
ACRONYM,
TO_TIMESTAMP( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
ORDER BY
1,2
),
temp2 AS (
SELECT ACRONYM,
DATE_TIME,
COALESCE(
VALUE,
COALESCE(
LAG( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ),
LEAD( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
)
+
(
COALESCE(
LEAD( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ),
LAG( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
)
-
COALESCE(
LAG( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ),
LEAD( VALUE ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
)
)
*
EXTRACT( SECOND FROM ( DATE_TIME - LAG( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME ) ) )
/
EXTRACT( SECOND FROM (
LEAD( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
-
LAG( DATE_TIME, 1, DATE_TIME ) OVER ( PARTITION BY ACRONYM ORDER BY DATE_TIME )
) )
) AS VALUE,
LEAD( DATE_TIME ) OVER ( PARTITION BY ACRONYM ORDER BY 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( SECOND FROM ( NEXT_DATE_TIME - DATE_TIME ) ) ) / 60 AS VALUE
FROM temp2
WHERE NEXT_DATE_TIME IS NOT NULL
GROUP BY
ACRONYM,
TO_DATE( TO_CHAR( DATE_TIME, 'YYYY-MM-DD HH24:MI' ), 'YYYY-MM-DD HH24:MI' )
ORDER BY
1,2
Results :
| ACRONYM | DATE_TIME | VALUE |
|----------|---------------------------|-------------------|
| 32-PRESS | January, 01 0015 00:00:00 | 65.43946117333333 |
| 32-PRESS | January, 01 0015 00:01:00 | 67.56109262835211 |
| 32-PRESS | January, 01 0015 00:02:00 | 66.32093658633383 |
| 32-PRESS | January, 01 0015 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);
Request 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/60 AS time
FROM inputs
CONNECT BY
LEVEL - 1 <= EXTRACT( MINUTE FROM end_time - start_time )
),
all_times AS (
SELECT TIME,
VALUE,
1 AS HAS_VALUE
FROM HST h
INNER JOIN inputs i
ON ( h.RECNM = i.RECNM
AND h.TIME BETWEEN i.start_time
AND i.end_time )
UNION ALL
SELECT TIME,
NULL,
0
FROM all_minutes
ORDER BY 1, 2 NULLS FIRST
),
lag_lead_ignore_nulls AS (
SELECT TIME,
VALUE,
COUNT( VALUE ) OVER ( ORDER BY TIME ASC, VALUE ASC NULLS FIRST ) AS LAG_GRP,
COUNT( VALUE ) OVER ( ORDER BY TIME DESC, VALUE DESC NULLS LAST ) AS LEAD_GRP
FROM all_times
),
lag_lead_values AS (
SELECT TIME,
VALUE,
FIRST_VALUE( TIME ) OVER ( PARTITION BY LAG_GRP ORDER BY VALUE ASC NULLS LAST ) AS PREV_MEASURED_TIME,
FIRST_VALUE( VALUE ) OVER ( PARTITION BY LAG_GRP ORDER BY VALUE ASC NULLS LAST ) AS PREV_MEASURED_VALUE,
FIRST_VALUE( TIME ) OVER ( PARTITION BY LEAD_GRP ORDER BY VALUE ASC NULLS LAST ) AS NEXT_MEASURED_TIME,
FIRST_VALUE( VALUE ) OVER ( PARTITION BY LEAD_GRP ORDER BY VALUE ASC NULLS LAST ) AS NEXT_MEASURED_VALUE,
LEAD( TIME ) OVER ( ORDER BY TIME ASC ) AS NEXT_TIME
FROM lag_lead_ignore_nulls
),
interpolated_values AS (
SELECT CAST( TIME AS DATE ) TIME,
COALESCE(
VALUE,
PREV_MEASURED_VALUE
+ ( NEXT_MEASURED_VALUE - PREV_MEASURED_VALUE )
* (
60 * EXTRACT( MINUTE FROM TIME - PREV_MEASURED_TIME )
+ EXTRACT( SECOND FROM TIME - PREV_MEASURED_TIME )
)
/ (
60 * EXTRACT( MINUTE FROM NEXT_MEASURED_TIME - PREV_MEASURED_TIME )
+ EXTRACT( SECOND FROM NEXT_MEASURED_TIME - PREV_MEASURED_TIME )
)
) AS INTERPOLATED_VALUE,
60 * EXTRACT( MINUTE FROM NEXT_TIME - TIME )
+ EXTRACT( SECOND FROM NEXT_TIME - TIME ) AS DURATION
FROM lag_lead_values
)
SELECT TRUNC( TIME, 'MI' ) AS TIME,
SUM( INTERPOLATED_VALUE * DURATION ) / SUM( DURATION ) AS TWA,
SUM( DURATION ) AS TOTAL_DURATION
FROM interpolated_values
WHERE INTERPOLATED_VALUE IS NOT NULL
GROUP BY TRUNC( TIME, 'MI' )
ORDER BY TIME ASC
Results :
| TIME | TWA | TOTAL_DURATION |
|---------------------------|--------------------|----------------|
| January, 01 0015 00:00:00 | 65.38833333333333 | 60 |
| January, 01 0015 00:01:00 | 67.56302083333334 | 60 |
| January, 01 0015 00:02:00 | 66.30575757575758 | 60 |
| January, 01 0015 00:03:00 | 63.48385416666667 | 60 |
| January, 01 0015 00:04:00 | 62.02027777777778 | 60 |
| January, 01 0015 00:05:00 | 61.45441176470588 | 60 |
| January, 01 0015 00:06:00 | 60.79056603773585 | 60 |
| January, 01 0015 00:07:00 | 60.677358490566036 | 60 |
| January, 01 0015 00:08:00 | 60.56415094339623 | 60 |
| January, 01 0015 00:09:00 | 60.450943396226414 | 60 |
| January, 01 0015 00:10:00 | 60.62924528301887 | 60 |
| January, 01 0015 00:11:00 | 62.09051724137931 | 60 |
| January, 01 0015 00:12:00 | 62.18775510204082 | 60 |
| January, 01 0015 00:13:00 | 61.96530612244898 | 60 |
| January, 01 0015 00:14:00 | 61.28333333333333 | 60 |
| January, 01 0015 00:15:00 | 61.252027027027026 | 60 |
| January, 01 0015 00:16:00 | 60.27410714285714 | 60 |
| January, 01 0015 00:17:00 | 59.47416666666667 | 60 |
| January, 01 0015 00:18:00 | 59.34888888888889 | 60 |
| January, 01 0015 00:19:00 | 61.06 | 60 |
| January, 01 0015 00:20:00 | 62.86071428571429 | 60 |
| January, 01 0015 00:21:00 | 63.895 | 60 |
| January, 01 0015 00:22:00 | 64.61114754098361 | 60 |
| January, 01 0015 00:23:00 | 64.16431972789115 | 60 |
| January, 01 0015 00:24:00 | 63.52513020833333 | 60 |
| January, 01 0015 00:25:00 | 63.27789473684211 | 60 |
| January, 01 0015 00:26:00 | 63.002526315789474 | 60 |
| January, 01 0015 00:27:00 | 62.245045045045046 | 60 |
| January, 01 0015 00:28:00 | 62.23263157894737 | 60 |
| January, 01 0015 00:29:00 | 62.56314393939394 | 60 |
| January, 01 0015 00:30:00 | 62.81926605504587 | 60 |
| January, 01 0015 00:31:00 | 62.544587155963306 | 60 |
| January, 01 0015 00:32:00 | 62.29191176470588 | 60 |
| January, 01 0015 00:33:00 | 62.58641975308642 | 60 |
| January, 01 0015 00:34:00 | 62.73456790123457 | 60 |
| January, 01 0015 00:35:00 | 62.87131687242798 | 60 |
| January, 01 0015 00:36:00 | 62.02166666666667 | 60 |
| January, 01 0015 00:37:00 | 61.50328947368421 | 60 |
| January, 01 0015 00:38:00 | 61.70065789473684 | 60 |
| January, 01 0015 00:39:00 | 61.94731359649123 | 60 |
+2
source to share
This query generates the required values:
with input as (
select value, htime, to_char(htime, 'yyyy-mm-dd hh24:mi') mnt,
extract(day from d)+extract(hour from d)/24+
extract(minute from d)/(24*60)+extract (second from d)/(24*60*60) tm
from (select value, htime, htime-timestamp '1899-12-30 00:00:00' d from test))
select distinct mnt, round(
sum(tm*value) over (partition by mnt)/sum(tm) over (partition by mnt), 6) wav
from input order by mnt
Output:
MNT WAV
---------------- ----------
2015-01-01 12:00 65.77838
2015-01-01 12:01 67.765575
2015-01-01 12:02 66.147733
2015-01-01 12:03 64.1026
According to the documentation, the Excel Calendar starts at '1900-01-01', but I had to change that date slightly to reach "date zero" to get the numeric values for the time difference just like in a spreadsheet. Resting is just a matter of timestamping, casting that difference by a number and summing the results for each minute with the function sum()
in the analytic version.
If you have gaps in your data, you need to first create periods for each minute with a recursive query ( connect by
), and then lie down to join that query with padding data for blanks with a function lag(wav ignore nulls)
to collect the weighted average from the previous minute (s).
Edit: version padding spaces:
with input as (
select value, htime, to_char(htime, 'yyyy-mm-dd hh24:mi') mnt,
extract(day from d)+extract(hour from d)/24+
extract(minute from d)/(24*60)+extract (second from d)/(24*60*60) tm
from (select value, htime, htime-timestamp '1899-12-30 00:00:00' d from data)),
period as (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
from period connect by level+1<(m2-m1)*24*60),
calc as (
select distinct mnt,
round(sum(tm*value) over (partition by mnt)/sum(tm) over (partition by mnt), 6) wav
from minutes left join input using (mnt) order by mnt)
select mnt, wav, nvl(wav, lag(wav ignore nulls) over (order by mnt)) wavg from calc
The subquery input
prepares the data for further processing, period
selects the min and maximum minute from the table (here you can insert some values manually instead of a query from the table, for example "date" 2015-01-01 13:52:00 ")
minutes
generates ... minutes recursively for given period, calc
calculates weighted averages connecting inputs and minutes, the last selection fills in the last known averages for empty minutes - you can watch it for 6, 7, 12 minutes in SQLFiddle.
+1
source to share