0

Let's say I have a continuous aggregate view that tracks the warehouse inventory change daily. The example below is not real, but I tried to simplify it for the purpose of the question.

CREATE MATERIALIZED VIEW inventory_daily
WITH (timescaledb.continuous) AS
SELECT item,
       time_bucket(INTERVAL '1 day', 'time') AS bucket,
       SUM(item_delta) as daily_change
FROM conditions
GROUP BY item, bucket;

This gives you intraday inventory changes nicely. But what would be the best, or the most CPU efficient way to the get cumulative sum of all inventory changes over the whole lifespan of items? If you sum all changes together, you should have the count how many items there are left in the inventory, for double accounting, for each day.

Can the cumulative sum done in continous aggregated view or is there a better way to do a breakdown of inventory totals, assuming you have just the change as an input? What I hope to accomplish is:

-- Don't know how to do CUMULATIVE SUM
CREATE MATERIALIZED VIEW inventory_daily
WITH (timescaledb.continuous) AS
SELECT item,
       time_bucket(INTERVAL '1 day', time) AS bucket,
       SUM(item_delta) as daily_change,
       CUMULATIVE_SUM(item_total) as total_at_the_end_day,
FROM conditions
GROUP BY item, bucket;

If this cannot be done in an aggregated view (as it looks like based on the comment) what would be the next best option? Manually calculate values for each day?

Mikko Ohtamaa
  • 82,057
  • 50
  • 264
  • 435
  • 1
    Do you mean by cumulative sum the sum of all previous time buckets to the current time bucket? (it would be nice to have table with data and a result of a query) If so, you can achieve this with window function, but it cannot be done in continuous aggregates. See this answer which discusses the limitations and possible approaches: https://stackoverflow.com/a/66796113/840340 – k_rus Apr 06 '21 at 20:27
  • `Do you mean by cumulative sum the sum of all previous time buckets to the current time bucket?` exactly this, thank you. Will check the link. – Mikko Ohtamaa Apr 07 '21 at 08:05
  • I was hoping that I could somehow calculate this as (previous bucket total + delta) but this would mean the buckets are created in time order and I could just refer to the value of the previous bucket. – Mikko Ohtamaa Apr 07 '21 at 08:09
  • One option could be save resume information in a table, and update it daily. – nachospiu Apr 13 '21 at 13:39

0 Answers0