PostgreSQL introduced window functions in the 8.4 version (which means 6 years ago!) but I still feel like this is very little known feature. Let’s see how they work with a real-life example!
We have to generate data for stock charting site. The charting library expects data in the OHLC format and we need to support multiple intervals (currently 1min, 5min, 15min, 1h) in the given time period.
We have a table containing minute OHLC bars for stocks 30-days back. The table schema looks like this:
and it currently contains 4169221 rows of data.
First we need to somehow establish the boundaries for interval periods in the given time range. We will use awesome generate_series function that is built in PostgreSQL.
will return exactly what we want:
Now that we have boundaries that establish our set of rows we can dive into window functions and aggregates.
The aggregate calculations are performed over window (or fragment) of data that is determined with partition by intervals.start part of the query.
In the end we can easily query for different intervals and time ranges and we can get results fast (~70ms on my laptop)!