Обсуждение: Forcasting in Postgresql

On Wed, 2023-03-01 at 13:18 +0530, Govardhan .G wrote:
> Is it possible to write a query for forecasting in postgresql.
> I have been trying to search but have no clue. please advise

test=> SELECT 'I don''t think that you will get a good answer to such a generic question';
                                 ?column?                                 
══════════════════════════════════════════════════════════════════════════
 I don't think that you will get a good answer to such a generic question
(1 row)

Sorry, I couldn't resist.

Yours,
Laurenz Albe

The short, unhelpful, answer is Yes.

 

Time to take a step back from the SQL and ask yourself:

Can I write an algorithm to forecast some value dependant on values I already have.

 

Very simple, somewhat vague, example.

I have been selling Snow Tyres for some time so I have values for sales along with dates.

What are the average sales for each month?

If I have more than one year’s data, what are the averages per month over the last X years?

Is a running average over the last few weeks/months/year any use?

Some made up figures.

Given that my average sales (in the Northern Hemisphere), were 20 in November, 18 in December, 2 in January, 3 in February, I might expect significant sales in November and December, but a drop in sales in January and February.

If last year the actual figures for November to February were 2,30,0,3, and the previous year were 38,6,4,3 then there is still a drop in sales in January and February, and still high sales in November and December, it’s just a little more complicated.

(If I were selling Snow Tyres, I would possibly look at these figures and get some stock in for November so that I would have stock on hand for the rest of the year, but now we are getting on to questions relating to order lead times).

 

I noticed that what I had intended to be a simple example quickly started to become more complicated.

I needed a clear definition of what I had, and what I wanted, so I tried to draw on my experience using a Stock Control System in the ‘80s and make up some easy data. I know that real systems that attempt to predict ordering quantities can soon get deep into Statistics, which is not one of my strengths. For this particular example I would be looking at search results for ‘stock management algorithm’ as a starting point.

 

Hope this helps a little.

Regards

Dave Bolt

 

From: Govardhan .G [mailto:govardhansrr@gmail.com]
Sent: 01 March 2023 07:48
To: pgsql-admin@lists.postgresql.org
Subject: Forcasting in Postgresql

 

Hi Folks,

Is it possible to write a query for forecasting in postgresql. I have been trying to search but have no clue. please advise

Thanks