Cumulative Sum

[JimKnicely]

We can use the analytic function SUM to create a column in a query that keeps a cumulative sum for an important measure in our fact tables...

Example:

Code: Select all

dbadmin=> select * from sum_test;
pk | sales
----+-------
  1 |   100
  2 |    50
  3 |   250
  4 |   100
  5 |   500
(5 rows)

Code: Select all

dbadmin=> select pk,
dbadmin->        sales,
dbadmin->        sum(sales) over (order by pk) cumulative_sum
dbadmin->   from sum_test
dbadmin->  order
dbadmin->     by pk;
pk | sales | cumulative_sum
----+-------+----------------
  1 |   100 |            100
  2 |    50 |            150
  3 |   250 |            400
  4 |   100 |            500
  5 |   500 |           1000
(5 rows)

Have fun!