Snowflake Cumulative SUM and AVERAGE

Most of the analytical databases such as Netezza, Teradata, Oracle, Vertica allow you to use windows function to calculate running total or average. In this article, we will check how to use analytic functions with windows specification to calculate Snowflake Cumulative Sum (running total) or cumulative average with some examples.

The cumulative sum or running total is one of the interesting problems in the databases where you have to calculate the sum or average using current result and previous (or next) row value.

Snowflake Cumulative SUM

A cumulative sum or a running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, by adding the value of the new number to the previous running total.

Many day to day applications uses cumulative sum. For example, in the transaction system, the machine may keep a running total of all transactions made. At any given point of the time you will get the total transaction made.

You can use the Snowflake window function such as SUM analytical function to calculate the running total.

Snowflake Cumulative SUM Example

In this example, we will use window function such as SUM analytic function to calculate running total.

Following SQL statement uses window function with specification to calculate the cumulative sum. To calculate the cumulative sum, use “unbounded preceding” in your windows specification.

SELECT   p_partkey, 
         p_retailprice, 
         Sum(p_retailprice) OVER( partition BY NULL ORDER BY p_partkey ASC rows UNBOUNDED PRECEDING ) "Cumulative Sum"
FROM     snowflake_sample_data.tpch_sf10.part LIMIT 10;

+-----------+---------------+----------------+
| P_PARTKEY | P_RETAILPRICE | Cumulative Sum |
|-----------+---------------+----------------|
|         1 |        901.00 |         901.00 |
|         2 |        902.00 |        1803.00 |
|         3 |        903.00 |        2706.00 |
|         4 |        904.00 |        3610.00 |
|         5 |        905.00 |        4515.00 |
|         6 |        906.00 |        5421.00 |
|         7 |        907.00 |        6328.00 |
|         8 |        908.00 |        7236.00 |
|         9 |        909.00 |        8145.00 |
|        10 |        910.01 |        9055.01 |
+-----------+---------------+----------------+

Snowflake Cumulative Average

Similar to cumulative sum, cumulative average or a running average is the average of a sequence of number or column values.

Many day to day applications uses cumulative average. For example, calculate the average grade of all the students in class. So, the use if cumulative average will help to identify the average grade of students.

Snowflake Cumulative Average Examples

In this example, we will use window function such as AVG analytic function to calculate cumulative or running average.

Following SQL statement uses window function with specification to calculate the cumulative average. To calculate the cumulative average, use “unbounded preceding” in your windows specification.

SELECT   p_partkey, 
         p_retailprice, 
         Avg(p_retailprice) OVER( partition BY NULL ORDER BY p_partkey ASC rows UNBOUNDED PRECEDING ) "Cumulative Average"
FROM     snowflake_sample_data.tpch_sf10.part LIMIT 10;

+-----------+---------------+--------------------+
| P_PARTKEY | P_RETAILPRICE | Cumulative Average |
|-----------+---------------+--------------------|
|         1 |        901.00 |          901.00000 |
|         2 |        902.00 |          901.50000 |
|         3 |        903.00 |          902.00000 |
|         4 |        904.00 |          902.50000 |
|         5 |        905.00 |          903.00000 |
|         6 |        906.00 |          903.50000 |
|         7 |        907.00 |          904.00000 |
|         8 |        908.00 |          904.50000 |
|         9 |        909.00 |          905.00000 |
|        10 |        910.01 |          905.50100 |
+-----------+---------------+--------------------+