The LAG and LEAD Analytic Functions

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

The LAG and LEAD Analytic Functions

Post by JimKnicely » Fri Aug 10, 2012 12:39 pm

There are many built in analytic functions in Vertica. Two of my favorites are LAG and LEAD. The LAG function returns values from a row before the current row, while the LEAD function returns values from a row after the current row. Each let you access more than one row in a table at the same time!

Here is a simple example. The MY_LOCATION table stores my locations over time as I move from room to room in an old PC adventure game.

Code: Select all

dbadmin=> SELECT * FROM my_location;
col1 |      col2       |   col3
------+-----------------+----------
    1 | First Location  | Room 10
    2 | Second Location | Room 6
    3 | Third Location  | Room 55
    4 | Forth Location  | Room 55
    5 | Fifth Location  | Room 666
(5 rows)

Code: Select all

dbadmin=> SELECT col1, col2 || '(' || col3 || ')' where_am_i,
dbadmin->        LAG( col2 || '(' || col3 || ')', 1) OVER (ORDER BY col1) where_was_i,
dbadmin->        LEAD( col2 || '(' || col3 || ')', 1) OVER (ORDER BY col1) where_i_am_going
dbadmin->   FROM my_location;
col1 |        where_am_i        |       where_was_i       |     where_i_am_going
------+--------------------------+-------------------------+--------------------------
    1 | First Location(Room 10)  |                         | Second Location(Room 6)
    2 | Second Location(Room 6)  | First Location(Room 10) | Third Location(Room 55)
    3 | Third Location(Room 55)  | Second Location(Room 6) | Forth Location(Room 55)
    4 | Forth Location(Room 55)  | Third Location(Room 55) | Fifth Location(Room 666)
    5 | Fifth Location(Room 666) | Forth Location(Room 55) |
(5 rows)
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”