LAG / LEAD quick tip

As most of us know, with LAG and LEAD or more generally, any analytic function that may extend “past” the boundary of window it is operating on, you can get null as a result.

Here’s a trivial example


SQL> create table t as
  2  select rownum x
  3  from dual
  4  connect by level <= 10;

Table created.

SQL>
SQL> select x, lag(x) over ( order by x ) as lag_Test
  2  from t;

         X   LAG_TEST
---------- ----------
         1           
         2          1
         3          2
         4          3
         5          4
         6          5
         7          6
         8          7
         9          8
        10          9

10 rows selected.

We get null for the first row, because we cannot lag “below” x=1 because there is no such value. That is of course trivially solved with an NVL, for example:



SQL> select x, nvl(lag(x) over ( order by x ),999) as lag_Test
  2  from t;

         X   LAG_TEST
---------- ----------
         1        999
         2          1
         3          2
         4          3
         5          4
         6          5
         7          6
         8          7
         9          8
        10          9

10 rows selected.

But a quick look at the documentation, shows that you don’t need the “clutter” of NVL, because lag, lead already provide for a default value.

image



SQL> select x, lag(x,1,999) over ( order by x ) as lag_Test
  2  from t;

         X   LAG_TEST
---------- ----------
         1        999
         2          1
         3          2
         4          3
         5          4
         6          5
         7          6
         8          7
         9          8
        10          9

10 rows selected.

SQL>

4 thoughts on “LAG / LEAD quick tip

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s