Ограничить данные внутри оператора over в оракуле

Question

Я хочу объединить столбец по меткам времени.

Вот пример:

Таблица содержит столбцы, такие как col1, col2, ..., col_ts (столбец отметки времени).

SELECT
SUM(col1) OVER (ORDER BY col_ts ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) SUM1,
SUM(col2) OVER (ORDER BY col_ts ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) SUM2
FROM ...

Теперь я хочу, чтобы суммировались только 2 PRECEDING и 2 FOLLOWING ROWS, когда разница между временными метками составляет <= 5 минут.

Например, давайте посмотрим на эти значения меток времени:

14.09.15 15:44:00
14.09.15 15:50:00
14.09.15 15:51:00
14.09.15 15:52:00
14.09.15 15:53:00

Когда были в строке со значением метки времени "14.09.15 15:51:00", я хочу, чтобы сумма превышала значения с 15:50 до 15:53, потому что разница между 15:50 и 15:44 больше чем 5 минут.

Есть ли способ написать такое условие в предложении over?

Или есть кто-нибудь с хорошим и эффективным решением для этого?

Answer 1

Хорошо, я вижу проблему здесь. спасибо Флорин. так что насчет предварительной обработки? Я мог бы найти решение, но я не уверен, что есть более быстрое решение:

select col_ts, 
       n, 
       SUM(n) OVER (ORDER BY col_ts ROWS BETWEEN LEFT_VALUE PRECEDING AND RIGHT_VALUE FOLLOWING) MY_SUM,
       SUM(n) OVER (ORDER BY col_ts RANGE BETWEEN interval '5' second PRECEDING AND interval '5' second FOLLOWING) OLD_SUM
from (
       select col_ts,
              n,
              CASE
              WHEN (LEAD(col_ts,1) OVER (ORDER BY col_ts ) - col_ts) <= INTERVAL '5' second 
              THEN 
                   CASE
                   WHEN (LEAD(col_ts,2) OVER (ORDER BY col_ts ) - LEAD(col_ts,1) OVER (ORDER BY col_ts )) <= INTERVAL '5' second 
                   THEN 2 
                   ELSE 1
                   END
             ELSE 0
             END AS RIGHT_VALUE,
             CASE 
             WHEN (col_ts - LAG(col_ts,1) OVER (ORDER BY col_ts ) ) <= INTERVAL '5' second 
             THEN 
                  CASE 
                  WHEN (LAG(col_ts,1) OVER (ORDER BY col_ts ) - LAG(col_ts,2) OVER (ORDER BY col_ts )) <= INTERVAL '5' second 
                  THEN 2 
                  ELSE 1
                  END
            ELSE 0
            END AS LEFT_VALUE
      from fg_test
  );

Результат:

COL_TS                           N   MY_SUM      OLD_SUM
---------------------------  -----  -------  -----------
15.09.15 09:34:24,069000000      1        6            6
15.09.15 09:34:28,000000000      2       10           15
15.09.15 09:34:29,000000000      3       15           15
15.09.15 09:34:30,000000000      4       14           14
15.09.15 09:34:31,000000000      5       12           14
15.09.15 09:34:37,000000000      6        6            6

что ты думаешь?

Answer 2

Я думаю, что это слишком много для sql. Вы можете ограничить число или элементы в окне, вы можете как-то ограничить (см. Ниже) значения, но не оба одновременно.

drop table fg_test;
create table fg_test(col_ts timestamp, n number);

insert into fg_test values (systimestamp, 1);
insert into fg_test values (systimestamp+4/1440/60, 2);
insert into fg_test values (systimestamp+5/1440/60, 3);
insert into fg_test values (systimestamp+6/1440/60, 4);
insert into fg_test values (systimestamp+7/1440/60, 5);
insert into fg_test values (systimestamp+13/1440/60, 6);

select col_ts, n, 
  SUM(n) OVER (ORDER BY col_ts ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM1,
  SUM(n) OVER (ORDER BY col_ts RANGE BETWEEN current row AND interval '5' second FOLLOWING) SUMNEW
from fg_test;

Результаты:

COL_TS                                   N       SUM1       SUM2
------------------------------- ---------- ---------- ----------
14-SEP-15 06.16.28.825395000 PM          1          3          3 
14-SEP-15 06.16.33.000000000 PM          2          6         14 
14-SEP-15 06.16.34.000000000 PM          3          9         12 
14-SEP-15 06.16.35.000000000 PM          4         12          9 
14-SEP-15 06.16.36.000000000 PM          5         15          5 
14-SEP-15 06.16.42.000000000 PM          6         11          6 

(извините, что не взял точный пример, как в вашем вопросе)

Альтернатива - написать PL/SQL(открыть курсор и выполнить некоторую обработку).