Windowed aggregates with date constraints

Database
N/A

Windowed aggregates with date constraints

Hi,

I'm looking for an efficient way to calculate some windowed aggregates over a table with date-stamped records. A simple table would be organized as:

Acct_Nbr Date Purchase
-----------------------------------------------------
101 2010-01-05 $32.95
101 2010-01-11 $55.00
102 2010-01-03 $12.50
101 2010-02-10 $10.00
101 2010-02-10 $15.00
101 2010-02-10 $85.33

What I would like to query is, for example: For each of these rows, how many purchases were made by the same account in the previous 10 days, and what is the sum of purchases for that account over the previous 10 days.

IF there were exactly one row for each account for each day on the calendar, it would be easy to use SUM(purchase) over (PARTITION BY Acct_Nbr ORDER BY Date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING). However, most days there are no purchases for any given account. Some days there are more than one purchases. A one-record-per-day table would be vastly larger than the sample here.

Thanks for any help.
Tags (2)
1 REPLY
Enthusiast

Re: Windowed aggregates with date constraints

if the requirement is simply calculate sum of purchase during the past ten days, then a simple
select acct_nbr,
sum(purchase) as pur
from table
where purchase_date between date and date -10
group by 1

It almost sounds like that you are looking for a moving 10 day sum for each date and each account. to do that..

SELECT ACCT , SUM() OVER (PARTITION BY ...)

(SELECT CDATE,ACCT_NBR
FROM SYS_CALENDAR.CALDATE
A
JOIN
(SELECT DISTINCT ACCT FROM BASETABLE) B
ON 1=1
AND CALDATE.CDATE BETWEEN 2009-01-01 AND DATE
) PRD_JOIN1
LEFT JOIN TO

(SELECT ACCT,DATE,SUM(PURCHASE)
FROM BASETABLE
GROUP BY 1,2) SUM1
ON
PRD_JOIN1.CDATE=SUM1.DATE AND PRD_JOIN1PRD_JOIN1.ACCT = SUM1.ACCT