В Oracle 11gR1, как я могу включить метки времени прямо за пределами где где между меткой времени

Question

В Oracle 11gR1, как я могу включить метки времени прямо за пределами где где между меткой времени

(Учитывая эту базу данных SQL Fiddle)

Как я могу выбрать предыдущее ЗНАЧЕНИЕ прямо за предложением "где между" в запросе ниже?

Если это возможно, это устранит мой другой запрос, необходимый для поиска этого значения в моем макросе VBA excel ADODB. Поскольку я в настоящее время зацикливаюсь, хотя расширяю мой поиск "ГДЕ МЕЖДУ TIMESTAMP и TIMESTAMP" экспоненциально, пока не будет найдено значение до времени.

Средневзвешенное время (TWA) в последнем операторе выбора не будет точным, если у меня нет реального ЗНАЧЕНИЯ в начале самого первого интервала (все остальные интервалы в порядке, и запрос выполняется нормально). Данные, отправляемые в мой экземпляр Oracle 11GR1, являются случайными, и это вообще неизвестно, поскольку могут существовать большие пропуски. Таким образом, значение не будет присутствовать в течение нескольких часов, дней, месяцев или даже лет.

 -- 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

пример

 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

0

sql oracle oracle11g oracle11gr1

Источник

user4985028 11 ноя '15 в 18:07

1 ответ

Решение

Другие вопросы по тегам sql oracle oracle11g oracle11gr1

user266304 11 ноя '15 в 19:00 2015-11-11 19:00 · Accepted Answer · 2015-11-11 19:00

Не совсем уверен, что он делает именно то, что вам нужно, но вы можете включить последнюю запись до и после периода, добавив еще два запроса к вашему ALL_TIMES КТР:

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

Первый получает время и значение для последней строки перед вашим диапазоном; второй получает то же самое для первого ряда после диапазона.

Из вашего исходного (ну слегка подправленного) запроса я получаю:

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

С этими дополнительными союзами я получаю:

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

Я вижу лондонские времена из-за CAST ( TRUNC ( TIME,'MI' ) AS TIMESTAMP WITH TIME ZONE ); TRUNC делает это ДАТА, без информации о часовом поясе; CAST затем преобразует его в мой часовой пояс сессии. (Вот почему возможно избыточный CAST(... TO TIMEZONE) была также проблема для меня). Если вы всегда будете запускать его в целевой TZ, то это может не иметь значения, но в противном случае вам придется сделать некоторые манипуляции, чтобы сохранить вменяемые значения.