Oracle帮助:动态选择t-5到t-1天平均数据

Question

select distinct trunc(a,'dd') day,
avg(g) over ( order by trunc(a,'dd') RANGE between 5 preceding and current row ) a1
from( 
select to_date(concat(concat(a,' '),b),'yyyymmdd hh24mi') a,  A1SPREAD21.c, A1SPREAD21.d,
 A1SPREAD21.e, A1SPREAD21.f,A1SPREAD21.g, A1SPREAD21.h, A1SPREAD21.i, A1SPREAD21.j
from  A1SPREAD21) t1
order by 1

SQL代码如下所示。但不幸的是，我想每天计算5天的平均数据，比如在T天，我想使用T-5到T1的间隔数据。有人能帮忙吗？

表格

20100419    1034    IF1005  IF1006  3361.60 3388.60 -27     4695    527 316 24
20100419    1035    IF1005  IF1006  3365    3392.20 -27.20  4713    530 402 23
20100419    1036    IF1005  IF1006  3366    3392.80 -26.80  4722    527 408 16
20100419    1037    IF1005  IF1006  3367    3394    -27     4682    533 454 35
20100419    1038    IF1005  IF1006  3366.40 3395    -28.60  4741    529 301 28
20100419    1039    IF1005  IF1006  3366.40 3395    -28.60  4770    530 179 17

编辑：数据存放在dropbox，xlsx fromat https://www.dropbox.com/s/67y8mm0gims96us/a1spread21.xlsx

select avg(g) 
from  A1SPREAD21 
where a between 20110101 and 20110110 

结果是-27.00，而sql可以给出20110111个数据-27.00。

我想要的是每个交易日(可以在表中，而不是作为日历日)得到之前的t-5到t-1平均值。

Answer 1

我想这个条款应该是这样的：

avg(g) over (order by a RANGE between NUMTODSINTERVAL(5, 'day') PRECEDING
AND NUMTODSINTERVAL(1, 'day') PRECEDING)

Answer 2

select distinct trunc(a,'dd') day,
avg(g) over ( order by trunc(a,'dd') RANGE between 6 preceding and 1 preceding ) a1
from( 
select to_date(concat(concat(a,' '),b),'yyyymmdd hh24mi') a,  A1SPREAD21.c, A1SPREAD21.d,
 A1SPREAD21.e, A1SPREAD21.f,A1SPREAD21.g, A1SPREAD21.h, A1SPREAD21.i, A1SPREAD21.j
from  A1SPREAD21) t1
order by 1

测试过了

SELECT DISTINCT TRUNC ( a, 'dd' ) DAY,
  AVG ( g ) OVER ( ORDER BY TRUNC ( A, 'dd' ) RANGE BETWEEN 6 PRECEDING AND
  1 preceding ) a1, g 
   FROM
  (
     SELECT sysdate - 10  a,  1 as g from dual union all
     SELECT SYSDATE - 5 ,  3  FROM dual UNION ALL
     SELECT SYSDATE - 3 ,  45 FROM dual UNION ALL
     SELECT SYSDATE - 6 ,  56 FROM dual UNION ALL
     SELECT SYSDATE - 7 ,  23 FROM dual UNION ALL
     SELECT sysdate - 8 ,  67 from dual union all
     SELECT sysdate - 2 ,  7 from dual union all
     SELECT SYSDATE - 1 ,  8 FROM dual UNION ALL
     SELECT sysdate - 4 ,  541 from dual
  )
  t1
ORDER BY 1  

输出

|                             DAY |              A1 |   G |
|---------------------------------|-----------------|-----|
| December, 29 2013 00:00:00+0000 |          (null) |   1 |
| December, 31 2013 00:00:00+0000 |               1 |  67 |
|  January, 01 2014 00:00:00+0000 |              34 |  23 |
|  January, 02 2014 00:00:00+0000 | 30.333333333333 |  56 |
|  January, 03 2014 00:00:00+0000 |           36.75 |   3 |
|  January, 04 2014 00:00:00+0000 |              30 | 541 |
|  January, 05 2014 00:00:00+0000 |             138 |  45 |
|  January, 06 2014 00:00:00+0000 |           122.5 |   7 |
|  January, 07 2014 00:00:00+0000 |           112.5 |   8 |

Answer 3

试试这个(当然，testtable只用于测试)：

with testtable as (
  select date '2013-01-08' a,1 g from dual union all
  select date '2013-01-02' a,2 g from dual union all
  select date '2013-01-05' a,3 g from dual union all
  select date '2013-01-07' a,4 g from dual
)
select distinct trunc(a,'dd') day,
                avg(g) over(order by a rows between (select count(*)
                                                     from testtable tin
                                                     where trunc(a,'dd') between trunc(t.a,'dd') - 5 and trunc(t.a,'dd') - 1) preceding and current row) a1
from testtable t
order by 1

我使用sum()而不是average来检查，因为average很难快速测试，但确实有效。

我不确定你的这一要求trunc(t.a,'dd') - 1，也许你想删除-1？

有关它的更多信息，请参见Oracle窗口函数描述。