PostgreSQL has offered support for powerful analytics and window functions for a couple of years now. Many people all around the globe use analytics to make their applications more powerful and even faster. However, there is a small little feature in the area of analytics which is not that widely known. The power to use composite data types along with analytics.
Table of Contents
AS always some sample data is needed. For analytics we at CYBERTEC rely on a basic data set containing some data from the oil industry. Here is how it can be loaded:
|
1 2 3 4 5 |
test=# CREATE TABLE t_oil (country text, year int, production int); CREATE TABLE test=# COPY t_oil FROM PROGRAM 'curl www.cybertec.at/secret/oil.txt'; COPY 92 |
All the data is drawn from the net (in case you got "curl" installed) and ends up in a table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
test=# SELECT * FROM t_oil WHERE country = 'USA' ORDER BY year; country | year | production ---------+------+------------ USA | 1965 | 9014 USA | 1966 | 9579 USA | 1967 | 10219 USA | 1968 | 10600 USA | 1969 | 10828 *snip* |
lag is a popular window function allowing us to move data around within the result set. lag(production, 1) means that the value in the production column should be pushed one row further down.
Here is how it works:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
test=# SELECT *, lag(production, 1) OVER (ORDER BY year) FROM t_oil WHERE country = 'USA' ORDER BY year; country | year | production | lag ---------+------+------------+------- USA | 1965 | 9014 | USA | 1966 | 9579 | 9014 USA | 1967 | 10219 | 9579 USA | 1968 | 10600 | 10219 USA | 1969 | 10828 | 10600 |
Mind that we need an ORDER BY inside the OVER clause to make sure that we know into which direction to move the data. Order is essential to this kind of operation.
So far so good. In the previous example one value was pushed one line down the resultset. However, it is also possible to move more complex structures around. It can be pretty useful to move an entire row:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
test=# SELECT *, lag(t_oil, 1) OVER (ORDER BY year) FROM t_oil WHERE country = 'USA' ORDER BY year; country | year | production | lag ---------+------+------------+------------------ USA | 1965 | 9014 | USA | 1966 | 9579 | (USA,1965,9014) USA | 1967 | 10219 | (USA,1966,9579) USA | 1968 | 10600 | (USA,1967,10219) USA | 1969 | 10828 | (USA,1968,10600) |
In PostgreSQL every table definition can be seen as a composite data type. Therefore it can actually be used as a field. In this example all it does is saving us from a typing exercise. However, it can be useful if more complicated values have to be passed around (many of them).
The main question arising now is: How can a composite type be broken up again? The trick can be achieved like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
test=# SELECT country, year, production, (lag).* FROM ( SELECT *, lag(t_oil, 1) OVER (ORDER BY year) FROM t_oil WHERE country = 'USA' ORDER BY year ) AS x; country | year | production | country | year | production ---------+------+------------+---------+------+------------ USA | 1965 | 9014 | | | USA | 1966 | 9579 | USA | 1965 | 9014 USA | 1967 | 10219 | USA | 1966 | 9579 USA | 1968 | 10600 | USA | 1967 | 10219 USA | 1969 | 10828 | USA | 1968 | 10600 |
(column).* helps us to extract all fields out of the composite type once again.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply