Vertica Cumulative Sum, Average and Example

  • Post author:
  • Post last modified:May 3, 2019
  • Post category:Vertica
  • Reading time:4 mins read

The cumulative sum or running total is one of the interesting problem. Most of the modern analytical database line Netezza, Teradata, Oracle, Vertica provides supports to analytical functions. You can make use of those analytical functions along with window specification to calculate cumulative sum and average. In this article, we will check how to calculate Vertica Cumulative Sum (running total) or cumulative average.

Vertica Cumulative Sum

As explained earlier, 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.

Cumulative sum or running total is commonly used every day, for example, cash registers display running total of total purchases so far.

Vertica Cumulative Sum Syntax

Below is the syntax of sum analytical function along with window specifications that you can use to calculate cumulative sum or running total based on your requirements:

SUM(column | expression) 
OVER( PARTITION BY columns ORDER BY sort columns rows unbounded preceding | 
rows between <N> preceding and current row );

Vertica Cumulative Sum Example

Below are some of the examples on calculating Vertica Cumulative Sum. For cumulative SUM, you will either have to use “unbounded preceding” or “unbounded following”.

Below example demonstrates usage of “unbounded preceding” to consider preceding result for cumulative calculation.

SELECT s_qty, 
       Sum(s_price) 
         OVER( 
           partition BY NULL 
           ORDER BY s_qty ASC rows UNBOUNDED PRECEDING ) "Cumulative Sum" 
FROM   sample_sales;

Output:

 S_QTY | Cumulative Sum 
----------+----------------
1	| 1000
100	| 11000
150	| 26000
200	| 28000
250	| 53000
300	| 83000
2000 | 103000
(7 rows)

Vertica Cumulative Average

As explained earlier, cumulative average or a running average is the average of a sequence of number or column values.

Cumulative average or running average is commonly used every day, for example, calculate average grade of students in class.

Vertica Cumulative Average Syntax

Below is the syntax of AVG analytical function along with window specifications that you can use to calculate cumulative average or running average based on your requirements:

AVG(column | expression) 
OVER( PARTITION BY columns ORDER BY sort columns rows unbounded preceding | 
rows between <N> preceding and current row );

Vertica Cumulative Average Example

Below are some of the examples on calculating Vertica Cumulative average. For cumulative AVG, you will either have to use “unbounded preceding” or “unbounded following”.

Below example demonstrates usage of “unbounded preceding” to consider preceding result for cumulative calculation.

SELECT s_qty, 
       Avg(s_price) 
         OVER( 
           partition BY NULL 
           ORDER BY s_qty ASC rows UNBOUNDED PRECEDING ) "Cumulative Average" 
FROM   sample_sales;

Output:

 S_QTY | Cumulative Average 
----------+----------------
1	 | 1,000
100	 | 5,500
150	 | 8,666.66667
200	 | 7,000
250	 | 10,600
300	 | 13,833.33333
2000 | 14,714.28571
(7 rows)

Hope this helps 🙂

Tags: ,