2

Sample data is as follows: 

date       value  
2015-09-09      1  
2015-09-08      2  
2015-09-07      3  
2015-09-04      4  
2015-09-03      5

I need to calculate sum value for each date and two days before, so the result should appear:

2015-09-09      6 --sum value of 2015-09-09, 2015-09-08, 2015-09-07  
2015-09-08      5 --sum value of 2015-09-08, 2015-09-07  
2015-09-07      3 --sum value of 2015-09-07  
2015-09-04      9 --sum value of 2015-09-04, 2015-09-03  
2015-09-03      5 --sum value of 2015-09-03

How could I achieve it with window functions in Oracle?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Amis
  • 21
  • 2
  • 1
    Can you show us what you've done so far? – Roman Marusyk Sep 16 '15 at 14:14
  • Nothing... Many attempts with no satisfied result. :( – Amis Sep 16 '15 at 14:17
  • Use an analytic function, with a window clause that defines a logical range based on an interval: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#i97640 – David Aldridge Sep 16 '15 at 14:29
  • very similar question [here](http://stackoverflow.com/q/32566575/319875) – Florin Ghita Sep 16 '15 at 15:04
  • 1
    You said you've done `Nothing...` and then go on to say `Many attempts`. That means you ***do*** have code. We don't need to see code that works, if it already worked you wouldn't have a question. We benefit from seeing what you're *tried*. You should include in your post the attempts you have made, including error messages and/or in what way the results were not what you desired. – MatBailie Sep 16 '15 at 15:19

2 Answers2

3

Since your data is sparse you should use the RANGE clause:

SELECT OrderDay, 
SUM(quantity) OVER 
(ORDER BY OrderDay RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW) AS totals,
Quantity
FROM Order

or even shorter using the default window:

SELECT OrderDay, 
SUM(quantity) OVER 
(ORDER BY OrderDay RANGE INTERVAL '2' DAY PRECEDING) AS totals,
Quantity
FROM Order
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

Suppose you have an Order table with OrderDay as a Date field and Quantity as number of orders in that day. Now have to order by day and use analytic function to sum only current date and two days before.

The key is to use RANGE BETWEEN clause that operates over a window that includes the current and two prior days. Here is a solution:

SELECT 
SUM(Quantity) OVER 
(ORDER BY OrderDay RANGE BETWEEN
            INTERVAL '2' DAY PRECEDING
            AND
            CURRENT ROW ) totals,
Quantity
FROM Order
istovatis
  • 1,408
  • 14
  • 27