PostgreSQL – Calculate Running Sum with Filtering

postgresqlwindow functions

I have this simplified table entries:

----------------------------
| id | date       | amount |
----------------------------
| 1  | 2017-12-01 | 100    |
----------------------------
| 2  | 2017-12-03 | 100    |
----------------------------
| 3  | 2017-12-05 | 100    |
----------------------------

I need to query the running sum (balance) of the amount column alongside other columns.

I came up with a simple query:

SELECT id, date, amount, SUM(amount) OVER (ORDER BY date) AS balance
FROM entries
GROUP BY id, date, amount
ORDER BY date

and it displays the following:

--------------------------------------
| id | date       | amount | balance |
--------------------------------------
| 1  | 2017-12-01 | 100    | 100     |
-------------------------------------
| 2  | 2017-12-03 | 100    | 200     |
-------------------------------------
| 3  | 2017-12-05 | 100    | 300     |
-------------------------------------

Now, what I need to do is filter the results based on date, but the balance column still needs to be calculated from the start date i.e. 2017-12-01 (or from the first record in the table).

i.e. if I were to add a WHERE date >= '2017-12-03', I'd still want the balance of each row to be same as it was without the date range:

--------------------------------------
| id | date       | amount | balance |
--------------------------------------
| 2  | 2017-12-03 | 100    | 200     |
-------------------------------------
| 3  | 2017-12-05 | 100    | 300     |
-------------------------------------

How can I accomplish this?

Best Answer

Just wrap it in a subselect.

SELECT *
FROM (
  SELECT id, date, amount, SUM(amount) OVER (ORDER BY date, id) AS balance
  FROM entries
  GROUP BY id, date, amount
)
WHERE date >= '2017-12-03'
ORDER BY date, id;

I'm also confused about a few things if you have three rows you don't have to group by it, see if this gives you the same result, it should be a lot faster.

SELECT *
FROM (
  SELECT id, date, amount, SUM(amount) OVER (ORDER BY date, id) AS balance
  FROM entries
)
WHERE date >= '2017-12-03'
ORDER BY date, id;

Related Solutions

Postgresql – Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

A few thoughts. Consider range partitioning on "timestamp", it will reduce the amount of work your query have to do. Further optimization might be to calculate and store the agg for "closed" partitions. You will of course have to recalculate this when you modify historical information.

As a bonus it will be much easier to roll-out historical data that is no longer needed.

As mentioned this is just some thoughts and reflections, it may or it may not fit your situation. One pitfall may be the number of different intervals that you use for reporting. If larger intervals is not multiples of the partitioning interval this wont work well.

Edit: How is the plan affected by creating an temp table and an index on "timestamp"?

create index X on market_trades ( timestamp );

create temp table T ( time_range tsrange );

insert into T ( time_range )
WITH vals AS (
    SELECT '2013-08-19 0:00'::timestamp  AS frame_start,
       '2013-08-26 0:00'::timestamp  AS frame_end,
       '17h'::interval               AS interval_length
),   intervals AS (
    SELECT tsrange(start_time,
               lead(start_time, 1, frame_end) OVER (ORDER BY start_time   NULLS FIRST)) AS time_range
    FROM (
        SELECT generate_series(frame_start, frame_end, interval_length) AS  
               start_time, frame_end
        FROM vals
    ) _
    WHERE start_time < frame_end
)
SELECT time_range
FROM intervals i;

create index x on T ( time_range );

analyze t;

SELECT time_range, count(td.id) AS agg
FROM T
LEFT JOIN market_trades td
    ON td.timestamp <@ T.time_range
GROUP  BY T.time_range
ORDER  BY T.time_range;

PostgreSQL – Calculating Column Running Total

Well, apparently this is it:

SELECT 
    *, 
    SUM(amount) OVER (
        ORDER BY tx_date DESC 
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

Output:

 id |      memo       | amount |  tx_date   | running_balance 
----+-----------------+--------+------------+-----------------
  4 | Wages           |  75000 | 2015-08-31 |           77000
  2 | Eating out      |  -1000 | 2015-08-19 |            2000
  3 | Gas             |  -7000 | 2015-08-19 |            3000
  1 | Initial balance |  10000 | 2015-08-17 |           10000

Related Question