Hadoop Hive Cumulative Sum, Average and Example

Latest version of Hive HQL supports the window analytics functions. You can make use of the Hadoop Hive Analytic functions to calculate the cumulative sum or running sum and cumulative average. Sum and Average analytical functions are used along with window options to calculate the Hadoop Hive Cumulative Sum or running sum.

Hadoop Hive Cumulative Sum, Average Syntax:

Below are the Syntax for Apache Hive Cumulative SUM, AVG analytic functions. You can use these function within query you have requirement to calculate cumulative SUM or AVG.

SUM([DISTINCT
| ALL] expression)
[OVER (analytic_clause)];

AVG([DISTINCT
| ALL] expression)
[OVER (analytic_clause)];

Hadoop Hive Cumulative Sum, Average Examples

Hive Cumulative Sum and Average.

Query:

select
name,
amount,
 SUM(amount)
over
 (
 order by name
 rows between unbounded preceding and current row
 ) cumulative_Sum
from test;

Output:

Query:

select
name,
amount,
 AVG(amount)
over
 (
 order by name
 rows between unbounded preceding and current row
 ) cumulative_avg
from test;

Output:

Read:

• Cloudera Impala Generate Sequence Numbers without UDF
• Netezza ROWNUM Pseudo Column Alternative
• Run Impala SQL Script File Passing argument and Working Example 
• An Introduction to Hadoop Cloudera Impala Architecture
• Commonly used Hadoop Hive Commands
• Hadoop Hive Dynamic Partition and Examples
• Hive String Functions and Examples
• Hive Join Types and Examples