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 finally have what you see here. This query is Designed for SCADA systems primarily and will do the following...
This query will perform a Time Weighted Average hourly between two date times as TWA, Minimum and Maximum Values during that Interval as Vmin and Vmax. It will also return Time of Minimum and Time of Maximum as Hmin and Hmax. (These are the date times of the minimum value occurrence and maximum value occurrence ).Starting and Ending Interval values as VSTART and VEND. This query will not fail on daylight savings in March or August. (This is why I am using TO_TIMESTAMP_TZ)
Note: this query is setup for 1 hour intervals and any desired interval is possible by replacing and adding just a few items. So Enjoy!!!
This Query works in my Oracle 11g instance and after writing this post i copy-ed the exact text below and pasted into my SQL Developer. So it works!! I am having trouble running this in sqlfiddle but soon i will figure this out and have a running test for you.
-- 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 select statement for a 1 hour Rolling Average that is Time Weighted! I find this very useful in the waste water industry as state regulations/reporting require 24 hour rolling averages and 72 minute rolling averages. If you need a 24 rolling average change 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 this as well.
ROUND( STDDEV ( VALUE ) , 2 ) as VDEV,
If you need the value prior to your start time and after your stop time you can place this with the other union all's.
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
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.
SQL Fiddle
Oracle 11g R2 Schema Setup:
Query 1:
Results:
EDIT
SQL Fiddle
Oracle 11g R2 Schema Setup:
Query 1:
Results: