CODEWITHSUNDEEP
sqloracle-database
typeof programmer
typeof programmer

Reputation: 1609

How to finish this LAG calculation in Oracle

I have month and value columns in a table,like

Month              Value        Market
2010/01            100            1  
2010/02            200            1
2010/03            300            1
2010/04            400            1
2010/05            500            1
2010/01            100            2  
2010/02            200            2
2010/03            300            2
2010/04            400            2
2010/05            500            2

What I want to do is get new Month and Value combinations using (value in month(n-1)+value in month(n))/2=value in month n, also this calculation is based on market column, it group by market number. So, for the above example, the new month and value combination should be 

Month                Value         Market
2010/01            null               1
2010/02            (100+200)/2        1
2010/03            (200+300)/2        1
2010/04            (300+400)/2        1
2010/05            (400+500)/2        1
2010/01            null               2
2010/02            (100+200)/2        2
2010/03            (200+300)/2        2
2010/04            (300+400)/2        2
2010/05            (400+500)/2        2

Do you know how to achieve it in Oracle? Thank you!

Upvotes: 1

Views: 89

Answers (2)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

If there is no gap in your data, you can use LAG:

SQL> WITH DATA AS (
  2     SELECT DATE '2010-01-01' mon, 100 val FROM dual UNION ALL
  3     SELECT DATE '2010-02-01' mon, 200 val FROM dual UNION ALL
  4     SELECT DATE '2010-03-01' mon, 300 val FROM dual UNION ALL
  5     SELECT DATE '2010-04-01' mon, 400 val FROM dual UNION ALL
  6     SELECT DATE '2010-05-01' mon, 500 val FROM dual
  7  )
  8  SELECT mon, (LAG(val) OVER (ORDER BY mon) + val) / 2 avg_val FROM DATA;

MON            AVG_VAL
----------- ----------
01/01/2010  
01/02/2010         150
01/03/2010         250
01/04/2010         350
01/05/2010         450

However, if there is a gap the result might not be what you expect. In that case, you can either use a self-join or narrow the windowing clause:

SQL> WITH DATA AS (
  2     SELECT DATE '2010-01-01' mon, 100 val FROM dual UNION ALL
  3     SELECT DATE '2010-02-01' mon, 200 val FROM dual UNION ALL
  4     SELECT DATE '2010-03-01' mon, 300 val FROM dual UNION ALL
  5     /* gap ! */
  6     SELECT DATE '2010-05-01' mon, 400 val FROM dual UNION ALL
  7     SELECT DATE '2010-06-01' mon, 500 val FROM dual
  8  )
  9  SELECT mon, (first_value(val)
 10                 OVER (ORDER BY mon
 11                       RANGE BETWEEN INTERVAL '1' MONTH PRECEDING
 12                                 AND INTERVAL '1' MONTH PRECEDING)
 13               + val) / 2 avg_val
 14    FROM DATA;

MON            AVG_VAL
----------- ----------
01/01/2010  
01/02/2010         150
01/03/2010         250
01/05/2010  
01/06/2010         450

Upvotes: 5

Tony Andrews
Tony Andrews

Reputation: 132570

This does it:

SQL> select month,
  2         (value+lag(value) over (order by month))/2 as value
  3* from t1

MONTH           VALUE
---------- ----------
2010/01
2010/02           150
2010/03           250
2010/04           350
2010/05           450

5 rows selected.

Upvotes: 3

Related Questions