type
Post
Created date
Oct 3, 2022 08:25 AM
category
Data Science
tags
Data Warehousing
status
Published
Language
English
From
School
summary
slug
password
Author
Jason Ching Yuen Siu
Priority
Featured
Featured
Cover
Origin
Type
URL
Youtube
Youtube
icon

What are Cumulative and Moving Aggregates?

Cumulative Aggregate calculates cumulative values within each window partition. Whereas Moving Aggregate Calculates moving aggregate values within each window partition

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;
notion image
 
  • 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;
notion image
 

Moving Aggregate

This example of a time-based window shows, for one customer, the moving average of sales for the current month and preceding two months:
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;
notion image
Note that the first two rows for the three month moving average calculation in the output data are based on a smaller interval size than specified because the window calculation cannot reach past the data retrieved by the query.

FIT3158 - Lab 1 Case studies on linear programming problem (Graphical method)FIT3003 - W8 - SQL Notes about RANK() OVER (PARTITION BY x ORDER BY y)