От новичка до гуру: Курсы программирования на CyberDuff

Как в Oracle 11g вы вычисляете время средневзвешенных данных ежечасно между двумя датами?

Я заменил свой первоначальный вопрос этим окончательным ответом. С помощью г-на MTO и г-на Пондера Стиббонса и четырех месяцев экспериментов с моим экземпляром oracle 11G я наконец-то получил то, что вы видите здесь. Этот запрос предназначен в первую очередь для систем SCADA и будет выполнять следующие действия...

Этот запрос будет ежечасно выполнять взвешенное по времени среднее между двумя датами и временем как TWA, минимальное и максимальное значения в течение этого интервала как Vmin и Vmax. Он также вернет время минимума и время максимума как Hmin и Hmax. (Это дата и время появления минимального значения и появления максимального значения). Значения начального и конечного интервалов как VSTART и VEND. Этот запрос не будет ошибкой при переходе на летнее время в марте или августе. (Вот почему я использую TO_TIMESTAMP_TZ)

Примечание: этот запрос настроен для интервалов в 1 час, и любой желаемый интервал возможен путем замены и добавления всего нескольких элементов. Так что наслаждайтесь!!!

Этот запрос работает в моем экземпляре Oracle 11g, и после написания этого поста я скопировал точный текст ниже и вставил в свой SQL Developer. Так это работает!! У меня возникли проблемы с запуском этого в sqlfiddle, но скоро я выясню это и проведу для вас текущий тест.

скрипт SQL

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

РЕДАКТИРОВАТЬ: Вы можете включить это в окончательный оператор выбора для 1-часового скользящего среднего, взвешенного по времени! Я нахожу это очень полезным в отрасли очистки сточных вод, поскольку государственные нормативы/отчетность требуют 24-часовых скользящих средних и 72-минутных скользящих средних. Если вам нужно скользящее среднее из 24, измените ROWS 1 PROCECDING на 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,

Стандартное отклонение — это весело, так что добавьте и это.

ROUND( STDDEV ( VALUE ) , 2 ) as VDEV,

Если вам нужно значение до времени начала и после времени остановки, вы можете поместить его с другим союзом all.

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
sql oracle oracle11g sqlplus
09.06.2015

  • Моя версия базы данных: Oracle Database 11g Release 11.1.0.7.0 — Production. Еще забыл добавить, что это мой первый пост! Ваше здоровье! 09.06.2015
  • В моем простом среднем, которое я вычислил в Excel, 12:00:01 должно быть 12:01:00 и так далее для остальных трех. 09.06.2015
  • Поскольку у вас есть доли секунды, ваши условия должны быть лучше to_timestamp('01/01/2015:00:00:00','mm/dd/YYYY:HH24:MI:SS') AND TO_TIMESTAMP('01/01/2015:23:59:59.999999999','mm/dd/YYYY:HH24:MI:SSxff') 09.06.2015
  • Как вы рассчитали вес для строки в примере «средневзвешенное значение времени в минуту»? 09.06.2015
  • Спасибо Вернфрид, я изменю это. KSA вы можете вычислить средневзвешенное время по сумме (время * значение)/сумма (время). Я сделал это в excel за каждую минуту вручную. 09.06.2015

Ответы:


1

Используя ваши образцы данных - у него не было данных за полный час, поэтому я сделал средневзвешенное значение за минуту.

Вы не указали, что вы хотите делать на границах, поэтому я взял средневзвешенное значение непосредственно предшествующего и последующего значений.

скрипт SQL

Настройка схемы Oracle 11g R2:

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;

Запрос 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

Результаты:

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

ИЗМЕНИТЬ

скрипт SQL

Настройка схемы Oracle 11g R2:

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

Запрос 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

Результаты:

|                      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 |
09.06.2015
  • Спасибо МТО за помощь!! Очень признателен!!!! Как можно отредактировать опережение и отставание, если их нельзя использовать в оракуле 11.1g? Я недавно читал о различиях между 11.1 и 11.2. 11.06.2015
  • LAG и LEAD доступны в Oracle 11.1 — я дал ссылку на документацию по этому версия. Единственное отличие от 11.1 и 11.2 заключается в том, что в 11.2 вы можете использовать RESPECT NULLS и IGNORE NULLS, но здесь это не нужно. 11.06.2015
  • Привет МТО, Надеюсь, все хорошо! Я играл с вашим запросом почти всю ночь, пытаясь понять, как заполнить данные о недостающих минутах, часах, днях и даже неделях. Иногда он пропускает недостающие минуты, а иногда вместо него выводит нули, но работает очень быстро!!!! любые указатели? sqlfiddle.com/#!4/5850c/3 Я не могу получить вывод из скрипки, но очень хорошо работает в sqlplus в моей базе данных. 12.06.2015
  • @ID10T_ERROR Добавлено редактирование для выполнения линейной интерполяции для заполнения значений нескольких пропущенных минут. Я думаю, что это будет работать на 11.1 (но не могу проверить). Если вы работаете на 11.2, то его можно переписать в гораздо более простом формате, используя LAG( ... IGNORE NULLS ) OVER (...), но из вашего предыдущего комментария я не думаю, что вы, поэтому я сделал это более сложным (и обратно совместимым) способом. 12.06.2015

    • 2

    Этот запрос сгенерировал желаемые значения:

    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

    Выход:

    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

    SQLFiddle

    Согласно документации календарь Excel начинается с "1900-01-01", но я пришлось немного изменить эту дату, чтобы достичь «нулевой даты», чтобы получить числовые значения для разницы во времени точно так же, как в электронной таблице. Остальное - это только вопрос вычитания временных меток, приведения этой разницы к числу и суммирования результатов за каждую минуту с помощью функции sum() в аналитической версии.

    Если у вас есть пробелы в данных, вам нужно сначала создать периоды для каждой минуты с помощью рекурсивного запроса (connect by), а затем соединить этот запрос с моими данными для заполнения пробелов с помощью функции lag(wav ignore nulls), собирающей средневзвешенное значение за предыдущую минуту (минуты).

    Изменить: версия, заполняющая пробелы:

    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

    SQLFiddle

    Подзапрос input подготавливает данные для дальнейшей обработки, period выбирает минимальную и максимальную минуты из таблицы (вы можете вставить некоторые значения вручную вместо запроса из таблицы, например, "date '2015-01-01 13: 52:00'"), minutes генерирует... минуты рекурсивно для заданного периода, calc подсчитывает средневзвешенные значения, объединяющие ввод и минуты, последний выбор заполняет последние известные средние значения для пустых минут - вы можете наблюдать за минутами 6, 7, 12 в SQLFiddle.

    10.06.2015
  • sqlfiddle.com/#!4/8c3fe/1 Спасибо, задумайтесь, Стиббонс! Я получаю очень близко к тому, что я ищу!! Я добавил больше данных в ваш запрос Fidder, и вы были правы в отношении пробелов, которые мне нужно будет отредактировать в этом выражении, чтобы перенести предыдущее значение, несмотря на пропущенную минуту. 10.06.2015
  • Хорошо, я посмотрю на это. Важен ли столбец acronym — следует ли рассчитывать средневзвешенное значение отдельно для каждой аббревиатуры? 10.06.2015
  • аббревиатура, а recnm — это имя устройства. аббревиатура — это мемоник, а recnm — фактический номер записи. они статичны. 1136 означает 32-ПРЕСС. Если бы я хотел выбрать конкретное устройство, я бы сказал, где points.recnm=1136 или где points.acronym, например «32-PRESS», поскольку они одинаковы. В таблице HST не сохранена аббревиатура. Tt было просто использовать в качестве примера. HST имеет только hst.recnm. 10.06.2015
  • Я собираюсь посмотреть на это позже на работе и сделать версии, чтобы я мог измерять среднее значение по секундам (минуты сделаны, чем вы, кстати), по часам, дням, а затем по годам. У меня много данных за много лет в моей системе. Будет весело поиграть с этим. :-) 10.06.2015
  • Удачи. Я хотел быть джедаем, но мне сказали, что у меня есть маленькие мидихлорианы :/ 10.06.2015
  • Мистер Пондер Стиббонс, два очень быстрых вопроса. Я должен добавить оператор where, чтобы выбирать данные только из «1136» «Акроним», если я использую ваш оператор, чтобы выбрать все записи из моей таблицы. просто для тренировки я помещаю ваше заявление в Oracle SQL Developer и в sqlplus, и я получаю сообщение об ошибке. ОШИБКА в строке 15: ORA-00907: отсутствует правая скобка. Я использую Oracle Database 11g Release 11.1.0.7.0 — Production. Я заметил некоторые проблемы в прошлом, потому что скрипка 11gR2. Ошибка находится прямо в последней строке IGNORE NULLS, точнее I в IGNORE. 10.06.2015
  • 1) добавьте where acronym='1136' в строку 5 2) используйте last_value вместо lag, кажется, что "игнорировать пустые значения" было добавлено позже. Я только что протестировал last_value() на Oracle10g и работал нормально. SQLFiddle. 10.06.2015
  • Еще раз спасибо, Пондер Стиббонс! Я заставил его работать отлично. Возвращенный запрос от моего obdc oracle 11g идеально соответствует моим данным scada. Я также смог сделать это за секунды, просто изменив приведенное выше. 10.06.2015
  • Поздравляем :-) Пожалуйста, проголосуйте за второй ответ, данный пользователем @MT0, он приложил много усилий/времени, пытаясь вам помочь. 11.06.2015
  • Я слишком новичок, чтобы голосовать :(...... В настоящее время я работаю над добавлением часов с минутами и секундами. Чем больше выборка данных между датами в сочетании с большими интервалами, средневзвешенное значение моего времени отклоняется на 3 фунта на квадратный дюйм от фактический TWA Я считаю, что у меня есть поток рекурсивного вызова. 11.06.2015
  • Соединение по уровню исчерпает память, если время между датами очень велико. Есть ли способ обойти использование соединения по уровню? 11.06.2015
  • Никогда не сталкивался с такой ошибкой, вероятно, данные, сгенерированные рекурсивным запросом, очень велики. Попробуйте уменьшить границы периода, если возможно, возможно, вы найдете эта ссылка полезна. 12.06.2015
    • Новые материалы
    Я предполагаю, что вы имеете в виду методы обработки категориальных данных.
    Я предполагаю, что вы имеете в виду методы обработки категориальных данных. Пожалуйста, проверьте мой пост Инструментарий специалиста по данным для кодирования категориальных переменных в..
    Игра в прятки с данными
    Игра в прятки с данными Я хотел бы, чтобы вы сделали мне одолжение и ответили на следующие вопросы. Гуглить можно в любое время, здесь никто не забивается. Сколько регионов в Гане? А как..
    «Раскрытие математических рассуждений с помощью Microsoft MathPrompter и моделей больших языков»
    TL;DR: MathPrompter от Microsoft показывает, как использовать математические рассуждения с большими языковыми моделями; 4-этапный процесс для улучшения доверия и рассуждений в математических..
    Раскройте свой потенциал в области разработки мобильных приложений: Абсолютная бесплатная серия
    Глава 6: Работа в сети и выборка данных Глава 1: Введение в React Native Глава 2: Основы React Native Глава 3: Создание пользовательского интерфейса с помощью React Native Глава 4:..
    Все о кейсах: Camel, Snake, Kebab & Pascal
    В программировании вы сталкивались с ними при именовании переменной, класса или функции. Поддержание согласованности типов и стилей случаев делает ваш код более читабельным и облегчает совместную..
    Как работает дистанционное парное программирование и почему оно может изменить вашу жизнь
    Серебряная пуля от одиночества и отвлекающих факторов во время обучения программированию Независимо от того, работаете ли вы или учитесь удаленно, велика вероятность, что одиночество и..
    🔥🔥👉Питон 🆚Javascript👈🔥🔥
    Сегодня Python и Javascript потрясают мир в области ИТ. Все хотят выучить Python и Javascript, чтобы получить высокооплачиваемую работу. Различные фреймворки Python и Javascript расширяют..