在oracle里这种求平均应该如何写？

[seal031]

表如下：
DATE ACTYPE DICT
2017-01-01 A319 2
2017-01-01 A320 2
2017-01-02 A319 5
2017-01-02 A320 3
2017-01-03 A319 1
2017-01-03 A320 6
2017-01-04 A319 3
2017-01-04 A320 9
想得到：
DATE ACTYPE DICT DICT_MOV
2017-01-03 A319 1 2.666666
2017-01-03 A320 6 3.666666
2017-01-04 A319 3 3
2017-01-04 A320 9 6
手敲的，所以只写了2天的结果。。
DICT_MOV的值是同一个ACTYPE下近3天的均值，如3.666666是ACTYP为A320、2017-01-01至2017-01-03的均值，（2+3+6）/3。
请问这个sql应该如何写呢，谢谢！

[sxq129601]

select dates, actype, dict, (dict + dict2 + dict3) / 3 DICT_MOV from (SELECT dates, actype, dict, lag(dict, 1) over(partition by actype order by dates, actype) dict2, lag(dict, 2) over(partition by actype order by dates, actype) dict3 FROM TEST) where dict2 is not null and dict3 is not null

[小当家e]

select DATE,ACTYPE,DICT,sum(DICT)/3 over(partition by grouptype) DICT_MOV from ( select DATE,ACTYPE,DICT,group by trunc((date-date'2017-01-01')/3) grouptype from 表 )

[小当家e]

select DATE,ACTYPE,DICT,(sum(DICT) over(partition by grouptype))/3 DICT_MOV from ( select DATE,ACTYPE,DICT,group by trunc((date-date'2017-01-01')/3) grouptype from 表 )

[小当家e]

select DATE,ACTYPE,DICT,(sum(DICT) over(partition by grouptype))/3 DICT_MOV from ( select DATE,ACTYPE,DICT, trunc((date-date'2017-01-01')/3) grouptype from 表 ) 老是敲多字。。

[卜塔]

多谢楼主。 另：为什么DATES字段是varchar2时：prededing是2，following是0； 而dates字段是date类型时：preceding是3，following是0呢？

[chengccy]

WITH T AS (SELECT DATE '2017-01-01' AS T_DATE, 'A319' ACTYPE, 2 AS DICT FROM DUAL UNION ALL SELECT DATE '2017-01-01', 'A320', 2 FROM DUAL UNION ALL SELECT DATE '2017-01-02', 'A319', 5 FROM DUAL UNION ALL SELECT DATE '2017-01-02', 'A320', 3 FROM DUAL UNION ALL SELECT DATE '2017-01-03', 'A319', 1 FROM DUAL UNION ALL SELECT DATE '2017-01-03', 'A320', 6 FROM DUAL UNION ALL SELECT DATE '2017-01-04', 'A319', 3 FROM DUAL UNION ALL SELECT DATE '2017-01-04', 'A320', 9 FROM DUAL) SELECT T.*, AVG(DICT) OVER(PARTITION BY ACTYPE ORDER BY T_DATE RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AVG_DICT FROM T

[卜塔]

SELECT ID,actype,dict1,ROUND((dict1+dict2+dict3)/3 , 6)DICT_MOV FROM (SELECT ID,actype, dict dict1, LAG(dict,1) OVER (PARTITION BY actype ORDER BY ID,actype) dict2, LAG(dict,2) OVER (PARTITION BY actype ORDER BY ID,actype) dict3 FROM TEST) WHERE dict2 IS NOT NULL AND dict3 IS NOT NULL ORDER BY ID,actype;

[seal031]

谢谢各位，最后找到的解决方案是这样： DATES字段是varchar2时：

SELECT DATES, actype, dict,
       AVG( dict ) OVER (
         PARTITION BY actype
         ORDER BY TO_DATE(DATES,'yyyy-mm-dd')
         RANGE BETWEEN INTERVAL '2' DAY PRECEDING
               AND     INTERVAL '0' DAY FOLLOWING
       ) AS dict_mov
FROM   MOV_TEST
ORDER BY DATES, actype;

DATES字段为date时：

SELECT DATES, actype, dict,
       AVG( dict ) OVER (
         PARTITION BY actype
         ORDER BY DATES
         RANGE BETWEEN INTERVAL '3' DAY PRECEDING
               AND     INTERVAL '0' DAY FOLLOWING
       ) AS dict_mov
FROM   MOV_TEST
ORDER BY DATES, actype;

稍后结贴散分