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](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F70132bb3-caff-4a46-a7c9-a11573f9710d%2FUntitled.png%3Ftable%3Dblock%26id%3D34b69ace-57e5-4c1d-8a24-4323b91a68b5%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725811200000%26signature%3Dizmg4ZBcRkoVbMJdKQ-gQdAam3YRBZvZmZMhsUjvLuM?table=block&id=34b69ace-57e5-4c1d-8a24-4323b91a68b5&cache=v2)
- 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](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F8e3d5bb4-4fb5-47f7-a2b7-ef817708b291%2FUntitled.png%3Ftable%3Dblock%26id%3D9a07f66a-2da3-423a-9a4e-62f8d3837540%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725811200000%26signature%3D0FKiS4LG-0oK8snw8DI1IcfZOrHYBkno5fwpK1XswyU?table=block&id=9a07f66a-2da3-423a-9a4e-62f8d3837540&cache=v2)
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](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F15c28ab1-ff03-4273-b403-d40b6185cccd%2FUntitled.png%3Ftable%3Dblock%26id%3Daa96f89e-b965-4be0-ab08-934b0c1a2605%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725811200000%26signature%3DiTodyO4fSw3346MgfBU3QZjzUBxHlhDBRBWgSyCCqag?table=block&id=aa96f89e-b965-4be0-ab08-934b0c1a2605&cache=v2)
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.
- Author:Jason Siu
- URL:https://jason-siu.com/article/30b42ad9-88b5-4026-887b-6e214fa7d240
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts
FIT3003 - W8 - CUBE & ROLLUP in SQL (With partial CUBE and partial ROLLUP) (W8)
FIT3003 - W7 - Determinant dimensions
FIT3003 - W8 - SQL Notes about RANK() OVER (PARTITION BY x ORDER BY y)
FIT3003 - Unique SQL functions in Oracle Database
FIT3003 - W11 - Active Data Warehousing
FIT3003 - W2 - Star Schema