limit data within an over statement in oracle

问题:

I want to Aggregate a column over timestamps.

Here an example:

Table contains columns like col1, col2, ..., col_ts (timestamp column).

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

Now i want only the 2 PRECEDING and the 2 FOLLOWING ROWS SUMMED when the difference between the timestamps are <= 5 minutes.

For example, lets look at these timestamp values:

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

When were are at the row with the timestamp value "14.09.15 15:51:00", i want the SUM OVER the values from 15:50 until 15:53, because the difference between 15:50 and 15:44 is bigger than 5 minutes.

Is there a way to write such a condition in the over clause?

Or is there anyone with a good and performant solution to this?

回答1:

ok, i see the problem here. thanks florin. so what about some preprocessing? i could find a solution, but i am not sure if there is a faster solution:

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

Result:

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

what do you think?

回答2:

I think it's too much for sql. You can restrict the number or elements in window, you can restrict somehow(see bellow) the values, but not both at the same time.

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;

Results:

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 

(sorry for not taking the exact example as in your question)

The alternative is to write some PL/SQL(open a cursor and do some processing).