CODEWITHSUNDEEP
sqloracle-databaseaverage
piguy
piguy

Reputation: 526

Oracle floating average

I got this little statement in my select which calculates the difference in minutes. Both are date.

 ((Cars.Closed - Costomer.BookedDate)*24* 60) differnece_in_minutes

This in my result:

  differnece_in_minutes
  340
  23
  10
  4500
  245

Using AVG(((Cars.Closed - Costomer.BookedDate)*24* 60)) differnece_in_minutes gives me the average value. So far no problem.

But I would like to calculate the floating average - not the basic average.

I found some stuff in the web but I don't know exactly which function to use and how.

I want it to ignore values that are extremely high or low compared to the "normal" average.

I found stuff like moving, rolling average.

Upvotes: 0

Views: 206

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

In case you are looking for "moving" or "rolling" average you can use the windowing clause, example:

WITH t AS 
    (SELECT 1 AS N, 10 AS val FROM dual
    UNION ALL SELECT 2, 20 FROM dual
    UNION ALL SELECT 3, 20 FROM dual
    UNION ALL SELECT 4, 30 FROM dual
    UNION ALL SELECT 5, 25 FROM dual
    UNION ALL SELECT 6, 35 FROM dual
    UNION ALL SELECT 7, 33 FROM dual
    UNION ALL SELECT 8, 18 FROM dual
    UNION ALL SELECT 9, 1500 FROM dual
    UNION ALL SELECT 10, 15 FROM dual)
SELECT N, Val,
    AVG(val) OVER (ORDER BY N ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS rolling_average
FROM t;


         N        VAL ROLLING_AVERAGE
---------- ---------- ---------------
         1         10      16.6666667
         2         20              20
         3         20              21
         4         30              26
         5         25            28.6
         6         35            28.2
         7         33           322.2
         8         18           320.2
         9       1500           391.5
        10         15             511

10 rows selected.

For example, row 6 is the average of rows 4 to 8, i.e. AVG(30,25,35,33,18)

Upvotes: 2

Related Questions