Thread: Forcasting in Postgresql
Is it possible to write a query for forecasting in postgresql. I have been trying to search but have no clue. please advise
Thanks
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
There are some iterative patterns in the data then we can forecast the data in SQL. In some cases it is possible for particular type of data
Thank you
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
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