How Can I Calculate Cumulative Sums in PostgreSQL Using Window Functions?

Calculate cumulative sum using window function in PostgreSQL

Question:

You have a staging table that contains financial data and cumulative sums that need to be inserted into the target table. The staging table structure contains the following columns:

• ea_month: month
• id: unique identifier
• amount: transaction amount
• ea_year: Year
• circle_id: Transaction-related circles

The target table should contain an extra column cum_amt that reflects the cumulative sum of the amounts for each row.

Solution:

To do this, you can use PostgreSQL's window functions feature, which allows you to perform calculations on a range of rows. Specifically, you can use the OVER clause to apply an aggregate function within a defined window (SUM in this case):

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;

In this query:

• PARTITION BY circle_id: Divide the data into groups based on circle_id, making sure the cumulative sum is calculated separately for each circle.
• ORDER BY ea_year, ea_month: Specifies the sort order within each partition, ensuring that the cumulative sum is calculated in chronological order.
• cum_amt: The aggregationSUM expression calculates the cumulative sum of each row, considering all rows from the start of the partition to the current row.

NOTE: Make sure your table pairs (circle_id, ea_year, ea_month) have unique indexes to guarantee correct grouping of calculations. This ensures that rows with the same circle_id, ea_year and ea_month always appear together in sorted order.

By using window function technique, you can efficiently calculate the cumulative sum of each row and insert it into the target table.

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