将数据限制在oracle中的over语句中 [英] limit data within an over statement in oracle

问题描述

我想在时间戳上汇总一列.

I want to Aggregate a column over timestamps.

这里有个例子:

该表包含诸如col1，col2，...，col_ts之类的列(时间戳列).

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

现在，当时间戳之间的差异小于或等于5分钟时，我只希望获得2个前置和2个跟随行.

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

当在时间戳值为"14.09.15 15:51:00"的行中时，我希望对15:50到15:53的值进行求和，因为15:50和15之间的差是: 44大于5分钟.

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.

是否可以在over子句中编写这样的条件?

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?

推荐答案

好的，我在这里看到了问题.谢谢弗洛林.那么一些预处理呢?我可以找到解决方案，但是我不确定是否有更快的解决方案:

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

结果:

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

你怎么看?

这篇关于将数据限制在oracle中的over语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！