How to Calculate Cumulative Sum in PostgreSQL Using Window Functions?

Calculate cumulative sum in PostgreSQL

In PostgreSQL database, you can use window functions to calculate the cumulative sum (or running sum) of fields from the temporary table and transfer the results to the target table. Window functions allow calculations to be performed based on a series of rows in a partition, in this case, a partition created based on the circle_id column.

Window function syntax

The window function syntax used here is as follows:

sum(amount) OVER (PARTITION BY circle_id ORDER BY ea_year, ea_month) AS cum_amt

This window function partitions by circle_id column, which means that the cumulative sum will be calculated separately for each circle_id. Within each partition, the rows are sorted by the ea_year and ea_month columns, which ensures that the cumulative sum is calculated for each circle month from earliest to latest.

Example query

Based on the provided table structure, the following query will generate the required results:

SELECT ea_month,
       id,
       amount,
       ea_year,
       circle_id,
       sum(amount) OVER (PARTITION BY circle_id ORDER BY ea_year, ea_month) AS cum_amt
FROM tbl
ORDER BY circle_id, ea_year, ea_month;

Description

This query extracts the relevant columns from the tbl table and calculates the cumulative sum for each circle_id. The OVER clause specifies the partitioning and ordering of the window functions, ensuring that the cumulative sum is calculated correctly for each circle.

Important Note

The order of the
• clauses within the OVERORDER BY clause is critical to obtaining the desired result.
• If the table stores date values, you can use them directly in the ORDER BY clause. However, if you are using a string representation of the month, you may need to use the to_date() function to convert it to a date value.
• The resulting cum_amt column in the target table will contain the cumulative sum of the amount column, partitioned by circle_id, and sorted by ea_year and ea_month.

The above is the detailed content of How to Calculate Cumulative Sum in PostgreSQL Using Window Functions?. For more information, please follow other related articles on the PHP Chinese website!