1

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

Table: items

 item_id  | avg |
----------+-----|
 86401    | tbd |
 1234     | tbd |
 22779195 | tbd |
 .
 .
 .

Table: sales

 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.

1
  • 1
    Maybe I am missing something in your question, but if you remove item_id = .. from the WHERE clause and use group by item_id then you should get what you want (I don't understand the part about locations though) – a_horse_with_no_name Apr 30 '20 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.