I'd like to ask about how to calculate forecast data in parallel in a relational DB like PostgreSQL, which seems like a very typical problem when doing forecasts. Let's say we're trying to get the average sales rate for the past X hours for every item we have, and our data looks like this
item_id | avg | ----------+-----| 86401 | tbd | 1234 | tbd | 22779195 | tbd | . . .
item_id | qty_sold | time --------+----------+-------------------- 86401 | 5 | 2020-01-01T00:00:00 1234 | 5 | 2020-01-01T00:00:00 86401 | 2 | 2020-01-01T21:04:04 . . .
A query to get the average sales rate for one item would be simple (forgive syntax), like
SELECT item_id, avg(qty_sold) FROM sales WHERE item_id=86401 BETWEEN '2020-01-01T00:00:00' AND '2020-01-01T23:59:59';
But, how would you do that for every item efficiently and quickly and then save that data in the
items table in the
avg column for quick reference later? And furthermore, let's say items could be in different locations, and you had to get the sales rate for each item for each location, what would the query be like?
I guess I'm not understanding how to think in SQL because programming wise, it's like you'd just iterate all the items in the
items table, and perform that query, but that's not quite the right way to think when doing SQL. With locations in the mix, you'd iterate through the items and do it for each location the item is in, like a nested for loop, which is terrible performance. Would like some help understanding because this seems like something a pattern that would come up often when working with forecasting data.