What are Cumulative and Moving Aggregates?

Cumulative Aggregate

SELECT c.cust_id, t.calendar_quarter_desc,
TO_CHAR (SUM(amount_sold), '9,999,999,999') AS Q_SALES,
TO_CHAR (SUM(SUM(amount_sold)) OVER
(ORDER BY c.cust_id, t.calendar_quarter_desc
ROWS UNBOUNDED PRECEDING),
'9,999,999,999') AS CUM_SALES
FROM sales s, times t, customers c 
WHERE s.time_id=t.time_id
AND s.cust_id=c.cust_id
AND t.calendar_year=1999 
AND c.cust_id = 6380 
GROUP BY c.cust_id, t.calendar_quarter_desc;

 

• The analytic function SUM defines, for each row, a window that starts at the beginning of the partition (UNBOUNDED PRECEDING) and ends, by default, at the current row.

• Nested SUMs are needed in this example since we are performing a SUM over a value that is itself a SUM.

• Nested aggregations are used very often in analytic aggregate functions.

Cumulative Aggregate (with Partition)

SELECT c.cust_id, t.calendar_quarter_desc,
TO_CHAR (SUM(amount_sold), '9,999,999,999') AS Q_SALES,
TO_CHAR (SUM(SUM(amount_sold)) OVER
(PARTITION BY c.cust_id
ORDER BY c.cust_id, t.calendar_quarter_desc
ROWS UNBOUNDED PRECEDING),
'9,999,999,999') AS CUM_SALES
FROM sales s, times t, customers c 
WHERE s.time_id=t.time_id
AND s.cust_id=c.cust_id
AND t.calendar_year=1999 
AND c.cust_id IN (6380, 6510) 
GROUP BY c.cust_id, t.calendar_quarter_desc;

Moving Aggregate

SELECT c.cust_id, t.calendar_quarter_desc,
TO_CHAR (SUM(amount_sold), '9,999,999,999') AS Q_SALES,
TO_CHAR (AVG(SUM(amount_sold)) OVER
(ORDER BY c.cust_id, t.calendar_month_desc
ROWS 2 PRECEDING),
'9,999,999,999') AS MOVING_3_MONTH_AVG
FROM sales s, times t, customers c 
WHERE s.time_id=t.time_id
AND s.cust_id=c.cust_id
AND t.calendar_year=1999 
AND c.cust_id IN (6380) 
GROUP BY c.cust_id, t.calendar_month_desc;