CODEWITHSUNDEEP
sqloracle-database
seal031
seal031

Reputation: 7

Get the average of nearly three days sql in ORACLE

The table like this:

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

I want to get a result like this：

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

DICT_MOV is nearly three days according to the average of the ACTYPE grouping. E.g: DICT_MOV 3 = 5,1,3 average; DICT_MOV 6 = 3,6,9 average

Upvotes: 0

Views: 82

Answers (2)

Pham X. Bach
Pham X. Bach

Reputation: 5442

This query assume that (date, actype) is your table's PRIMARY KEY, and you want to get result for 3 nearest day - not consecutive. For consecutive date, you could use MTO's answer:

SELECT "date", actype, dict, 
    AVG(dict) OVER (
        PARTITION BY actype 
        ORDER BY "date"  
        rows BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS dict_mov
FROM table_name
ORDER BY "date", actype;

In case (date, actype) is not your table's PRIMARY KEY, then insted of table_name, you must use a subquery to calculate SUM(dict) GROUP BY "date", actype.

Upvotes: 0

MT0
MT0

Reputation: 167982

You can use AVG as an analytic function with a range window:

SELECT t.*,
       AVG( dict ) OVER (
         PARTITION BY actype
         ORDER BY "DATE"
         RANGE BETWEEN INTERVAL '2' DAY PRECEDING
               AND     INTERVAL '0' DAY FOLLOWING
       ) AS dict_mov
FROM   your_table t
ORDER BY "DATE", actype

Upvotes: 2

Related Questions