Скачать или смотреть Mastering the Cumulative Sum with Group By in PostgreSQL

Discover how to efficiently implement a `cumulative sum` grouped by date in PostgreSQL, streamlining your revenue data analysis.
---
This video is based on the question https://stackoverflow.com/q/73696572/ asked by the user 'Michael Anckaert' ( https://stackoverflow.com/u/192023/ ) and on the answer https://stackoverflow.com/a/73696856/ provided by the user 'jjanes' ( https://stackoverflow.com/u/1721239/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Cumulative sum which is grouped by

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering the Cumulative Sum with Group By in PostgreSQL

When it comes to analyzing revenue data over time, one common requirement is to retrieve the cumulative sum grouped by a specific timeframe, such as daily totals. This becomes especially important when you’re managing datasets from various locations or offices. In this guide, we will break down a solution to achieve a cumulative sum that is grouped by a truncated time in PostgreSQL, helping you enhance your reporting and data analysis skills.

Understanding the Problem

Let’s say you have a table that records revenue for various offices over time. The goal is to obtain the cumulative revenue grouped by date, aggregating the revenue for each office accurately.

Here’s a glimpse of your sample data:

office_idtimerevenue12022-01-01 12:00:0010012022-01-02 13:10:005012022-01-02 17:00:0040Using the provided query, you can get a cumulative sum for each entry but not grouped by the date in a way you need:

[[See Video to Reveal this Text or Code Snippet]]

This outputs the cumulative revenue like this:

office_idtscum_rev12022-01-01 00:00:0010012022-01-02 00:00:0015012022-01-02 00:00:00190However, you want the output to reflect total cumulative revenue per date instead. The expected outcome is:

office_idtscum_rev12022-01-01 00:00:0010012022-01-02 00:00:00190The Solution

To achieve the desired result, you need to implement a two-step nesting approach in your SQL query. The inner query will first summarize the revenues based on the truncated date and then apply the cumulative sum in the outer query. Here’s how you can do it:

Step-by-Step Query Breakdown

Inner Query: This aggregates the revenue on a daily basis.

[[See Video to Reveal this Text or Code Snippet]]

Outer Query: This calculates the cumulative sum based on the results of the inner query.

[[See Video to Reveal this Text or Code Snippet]]

Final Query

Combining both parts, the complete query looks like this:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By utilizing this two-level query approach, you can efficiently derive the cumulative sum of revenues grouped by day in PostgreSQL. This method not only streamlines data analysis but also enhances clarity in reporting, especially when handling large datasets from various sources.

Using these SQL techniques, you can take your data analysis and reporting to the next level, ensuring that your insights are accurate and actionable. Happy querying!