Analytics: Lagging entire rows

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

A simple example

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:

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

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:

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*

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:

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

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.

Moving beyond a simple field

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:

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)

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:

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

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;

---------+------+------------+---------+------+------------

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.

Analytics: Lagging entire rows

A simple example

Moving beyond a simple field

Leave a Reply Cancel reply

Hans-Jürgen Schönig

Blog Tags

NEWSLETTER

Articles by our PostgreSQL Experts