People have asked us how to calculate running totals a number of times; not a lot but enough that we feel we should document the general technique.
This approach is fairly ANSI-SQL standard and involves using SELF JOINS. In a later article we shall describe how to calculate moving averages which
follows a similar technique but with some extra twists.
Note that the below examples can also be done with a correlated sub-select in the SELECT clause and in some cases that sometimes works better. Perhaps
we shall show that approach in a later issue.
We tend to prefer the look of the SELF JOIN though and in practice it is generally more efficient since its easier for planners to optimize and doesn't always result in a nested loop strategy.
Just feels a little cleaner and if you are totaling a lot of columns (e.g number of items, products) etc,
much more efficient.
PostGIS in Action
About the Authors
Consulting
