(Given this Database SQL Fiddle)
How can I select the previous VALUE right outside my where between clause in the query below?
If this is possible it would eliminate my other query needed to find this value in my VBA excel ADODB macro. As i currently loop though expanding my "WHERE BETWEEN TIMESTAMP and TIMESTAMP" search exponentially until the before time stamped value is found.
The Time Weighted Average (TWA) in the final select statement will not be accurate if i do not have a real VALUE at the beginning of the very first interval.(All other intervals in between are ok and the query runs fine). The data being pushed to my Oracle 11GR1 instance is random and this is not known at all as large gaps could exist. Thus a value will not be present for hours, days, months or sometimes even years.
-- Lets Begin the Query
WITH INPUTS AS (
SELECT RECNM,
TO_TIMESTAMP_TZ ( '15-01-01 00:10:00 AMERICA/LOS_ANGELES','RR-MM-DD HH24:MI:SS TZR' ) AS START_TIME,
TO_TIMESTAMP_TZ ( '15-01-01 00:30:59 AMERICA/LOS_ANGELES','RR-MM-DD HH24:MI:SS TZR' ) AS END_TIME
FROM POINTS
WHERE ACRONYM = 'WELL32-PSI'
) ,
ALL_INTERVALS AS (
SELECT RECNM,
START_TIME + NUMTODSINTERVAL ( ( LEVEL-1 ) , 'MINUTE' ) AS TIME
FROM INPUTS
CONNECT BY
LEVEL-1 <=
EXTRACT ( DAY FROM END_TIME - START_TIME ) * 24 * 60 +
EXTRACT ( HOUR FROM END_TIME - START_TIME ) * 60 +
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 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,'MI' ) AS TIMESTAMP WITH TIME ZONE ) AS TIME,
SUM ( VALUE * DURATION ) / SUM ( DURATION ) AS TWA,
SUM ( DURATION ) AS TOTAL_DURATION
FROM LEAD_LAG_TIMES
GROUP BY CAST ( TRUNC ( TIME,'MI' ) AS TIMESTAMP WITH TIME ZONE )
ORDER BY TIME ASC
Example
2015-01-01 00:00:00 AMERICA/LOS_ANGELES 63.3
2015-01-01 00:00:08 AMERICA/LOS_ANGELES 63.7
2015-01-01 00:00:17 AMERICA/LOS_ANGELES 64.6
2015-01-01 00:00:28 AMERICA/LOS_ANGELES 66.3
2015-01-01 00:00:45 AMERICA/LOS_ANGELES 66.8
2015-01-01 00:00:55 AMERICA/LOS_ANGELES 67.5
2015-01-01 00:01:11 AMERICA/LOS_ANGELES 67.0
2015-01-01 00:01:30 AMERICA/LOS_ANGELES 67.4
2015-01-01 00:01:40 AMERICA/LOS_ANGELES 67.9
2015-01-01 00:01:50 AMERICA/LOS_ANGELES 68.7
2015-01-01 00:02:01 AMERICA/LOS_ANGELES 68.2
2015-01-01 00:02:11 AMERICA/LOS_ANGELES 67.1
2015-01-01 00:02:21 AMERICA/LOS_ANGELES 66.5
2015-01-01 00:02:31 AMERICA/LOS_ANGELES 65.5
2015-01-01 00:02:46 AMERICA/LOS_ANGELES 65.0
2015-01-01 00:02:59 AMERICA/LOS_ANGELES 64.6
2015-01-01 00:03:15 AMERICA/LOS_ANGELES 64.1
2015-01-01 00:03:25 AMERICA/LOS_ANGELES 63.2
2015-01-01 00:03:35 AMERICA/LOS_ANGELES 62.7
2015-01-01 00:04:05 AMERICA/LOS_ANGELES 62.2
2015-01-01 00:04:32 AMERICA/LOS_ANGELES 61.8
2015-01-01 00:05:40 AMERICA/LOS_ANGELES 61.3
2015-01-01 00:05:55 AMERICA/LOS_ANGELES 60.8-----Not Included in where between but this value is needed
2015-01-01 00:10:20 AMERICA/LOS_ANGELES 60.3--------- Included in where between
2015-01-01 00:10:38 AMERICA/LOS_ANGELES 60.9
2015-01-01 00:10:48 AMERICA/LOS_ANGELES 61.3
2015-01-01 00:10:58 AMERICA/LOS_ANGELES 61.8
2015-01-01 00:11:27 AMERICA/LOS_ANGELES 62.3
2015-01-01 00:13:54 AMERICA/LOS_ANGELES 61.8
2015-01-01 00:14:10 AMERICA/LOS_ANGELES 61.4
2015-01-01 00:14:41 AMERICA/LOS_ANGELES 60.9
2015-01-01 00:15:18 AMERICA/LOS_ANGELES 61.4
2015-01-01 00:15:51 AMERICA/LOS_ANGELES 60.9
2015-01-01 00:16:19 AMERICA/LOS_ANGELES 60.4
2015-01-01 00:16:32 AMERICA/LOS_ANGELES 59.9
2015-01-01 00:17:04 AMERICA/LOS_ANGELES 59.4
2015-01-01 00:17:27 AMERICA/LOS_ANGELES 59.9
2015-01-01 00:17:37 AMERICA/LOS_ANGELES 59.4
2015-01-01 00:17:58 AMERICA/LOS_ANGELES 59.0
2015-01-01 00:18:22 AMERICA/LOS_ANGELES 59.4
2015-01-01 00:18:50 AMERICA/LOS_ANGELES 59.9
2015-01-01 00:19:00 AMERICA/LOS_ANGELES 60.3
2015-01-01 00:19:25 AMERICA/LOS_ANGELES 60.8
2015-01-01 00:19:34 AMERICA/LOS_ANGELES 61.4
2015-01-01 00:19:45 AMERICA/LOS_ANGELES 62.1
2015-01-01 00:19:55 AMERICA/LOS_ANGELES 62.5
2015-01-01 00:20:30 AMERICA/LOS_ANGELES 63.0
2015-01-01 00:20:51 AMERICA/LOS_ANGELES 63.5
2015-01-01 00:21:03 AMERICA/LOS_ANGELES 63.9
2015-01-01 00:22:04 AMERICA/LOS_ANGELES 64.4
2015-01-01 00:22:28 AMERICA/LOS_ANGELES 64.8
2015-01-01 00:23:17 AMERICA/LOS_ANGELES 64.4
2015-01-01 00:23:27 AMERICA/LOS_ANGELES 63.9
2015-01-01 00:24:31 AMERICA/LOS_ANGELES 63.4
2015-01-01 00:26:06 AMERICA/LOS_ANGELES 63.0
2015-01-01 00:27:20 AMERICA/LOS_ANGELES 62.5
2015-01-01 00:27:30 AMERICA/LOS_ANGELES 61.9
2015-01-01 00:28:08 AMERICA/LOS_ANGELES 62.4
2015-01-01 00:28:37 AMERICA/LOS_ANGELES 62.0
2015-01-01 00:29:21 AMERICA/LOS_ANGELES 62.5
2015-01-01 00:29:38 AMERICA/LOS_ANGELES 62.9
2015-01-01 00:31:27 AMERICA/LOS_ANGELES 62.5
2015-01-01 00:32:01 AMERICA/LOS_ANGELES 62.0
2015-01-01 00:32:25 AMERICA/LOS_ANGELES 62.5
2015-01-01 00:35:07 AMERICA/LOS_ANGELES 62.9
2015-01-01 00:35:56 AMERICA/LOS_ANGELES 62.5
2015-01-01 00:36:06 AMERICA/LOS_ANGELES 62.0
2015-01-01 00:36:59 AMERICA/LOS_ANGELES 61.5
2015-01-01 00:39:31 AMERICA/LOS_ANGELES 62.0
2015-01-01 00:40:12 AMERICA/LOS_ANGELES 61.5
2015-01-01 00:40:22 AMERICA/LOS_ANGELES 60.9
2015-01-01 00:40:35 AMERICA/LOS_ANGELES 60.5
2015-01-01 00:40:55 AMERICA/LOS_ANGELES 60.0
2015-01-01 00:41:22 AMERICA/LOS_ANGELES 60.5
2015-01-01 00:41:46 AMERICA/LOS_ANGELES 60.1
2015-01-01 00:42:31 AMERICA/LOS_ANGELES 60.6
Not quite sure if it does exactly what you need, but you can include the last record before and after you period by add two more queries to your ALL_TIMES CTE:
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
The first one gets the time and value for the last row before your range; the second gets the same for the first row after the range.
From your original (well, tweaked slightly) query I get:
TIME TWA TOTAL_DURATION
------------------------------------------ ------ --------------
01-JAN-15 00.10.00.000000000 EUROPE/LONDON 40.5 60
01-JAN-15 00.11.00.000000000 EUROPE/LONDON 62.1 60
...
01-JAN-15 00.29.00.000000000 EUROPE/LONDON 62.5 60
01-JAN-15 00.30.00.000000000 EUROPE/LONDON
With those extra unions I get:
TIME TWA TOTAL_DURATION
------------------------------------------ ------ --------------
01-JAN-15 00.05.00.000000000 EUROPE/LONDON 60.8 245
01-JAN-15 00.10.00.000000000 EUROPE/LONDON 60.8 60
01-JAN-15 00.11.00.000000000 EUROPE/LONDON 62.1 60
...
01-JAN-15 00.29.00.000000000 EUROPE/LONDON 62.5 60
01-JAN-15 00.30.00.000000000 EUROPE/LONDON 62.9 87
01-JAN-15 00.31.00.000000000 EUROPE/LONDON
I'm seeing London times because of the CAST ( TRUNC ( TIME,'MI' ) AS TIMESTAMP WITH TIME ZONE ); the TRUNC makes it a DATE, with no time zone information obviously; the CAST then converts it to my session time zone. (Which is why the possibly-redundant CAST(... TO TIMEZONE) was also a problem for me). If you'll always run it in the target TZ then it may not matter, but otherwise you'll need to do some manipulation to keep sane values.