Postgresql – How to predict the next value in a table?


I have a table full of values. I want to know what the next values may be.

PostgreSQL cannot look into the future, but it can guess based on existing data.

I will not install any software or extensions.

I have not hooked this script up to a syringe full of poison which sticks me if it predicts the wrong number, nor will this mechanism be auto-trading on the stock market. I will not sue anyone if it predicts the wrong number. It will be used "for novelty purposes only".

What would be the most likely "algorithm" to stand the best chance of guessing the next value?

If I just do:

SELECT avg(integer_column) FROM table;

I will get the average number. That seems like a reasonable guess, but it also seems a bit simplistic. It seems like it could do better than that, without getting into some kind of crazy, ultra-complex query.

Best Answer

The Average or Mean is probably not what you are looking for. If what you are looking for is the most likely value based on simply being single most popular value in the past, that would be the Mode.

Postgres does have a MODE() function which will give you that.

However, you might want to consider windowing that over time or some other criteria to weight the probability. And you might want to consider randomizing the choice. If heads and tails were 51(heads)-49(tails) in past occurrences, using MODE() would give heads every time. Yet, perhaps you want a non-deterministic function which would predict heads 51% of the time and tails 49% of the time.

What if the values correlate to a user or time - then you might give different users different predictions. So there are many ways to approach the concept of "prediction" here.