CODEWITHSUNDEEP
sqloracle-database
Amis
Amis

Reputation: 21

Oracle window function with dates

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?

Upvotes: 2

Views: 2267

Answers (2)

istovatis
istovatis

Reputation: 1408

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

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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

Upvotes: 3

Related Questions