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>

Histograms on data (not the optimizer)

I’ve been doing a series on Analytics here but if you need to group data into ranges, you don’t necessarily need a fancy analytic. The function WIDTH_BUCKET can easily determine histogram groupings within a table.


SQL> SELECT CUST_ID, CUST_LAST_NAME, CUST_CREDIT_LIMIT,
  2     WIDTH_BUCKET(CUST_CREDIT_LIMIT, 100, 5000, 10) credit_bucket
  3     FROM sh.customers
  4     WHERE  country_id = 52787
  5     ORDER BY CUST_CREDIT_LIMIT;

   CUST_ID CUST_LAST_NAME       CUST_CREDIT_LIMIT CREDIT_BUCKET
---------- -------------------- ----------------- -------------
     44282 Justice                           1500             3
     50671 Sandoval                          1500             3
     26284 Lotto                             1500             3
      4827 Kessel                            1500             3
     27671 Lin                               1500             3
     17284 Fellows                           1500             3
      9671 Rittenour                         1500             3
     35283 Gatewood                          1500             3
      9670 Oppy                              1500             3
     35284 Thomas                            1500             3
     18670 Callihan                          1500             3
     18671 Sager                             1500             3
    102218 Hornick                           1500             3
     26283 Geiss                             1500             3
     45827 Rowe                              1500             3
     42898 Haske                             3000             6
      5519 Ballenger                         3000             6
     33898 Batterton                         3000             6
     46518 Orm                               3000             6
     24899 Lightfoot                         3000             6
     47211 Cartwright                        3000             6
     20744 Gravel                            3000             6
    102723 Myczkowski                        3000             6
    101478 Bishop                            3000             6
      1050 Overton                           3000             6
       700 Burnns                            3000             6
      1023 Newcomer                          3000             6
     38744 Tazelar                           3000             6
     29744 Durby                             3000             6
     41514 Burgess                           5000            11
     45828 Wood                              5000            11
    102129 Cay                               5000            11
     40128 Cain                              5000            11
     23515 Figgens                           5000            11
     32514 Lengel                            5000            11
    101613 Spivak                            7000            11
       470 Sandstrum                         7000            11
       763 Dutton                            7000            11
     36667 Capps                             7000            11
       446 Jeffreys                          7000            11
     42302 Everrett                          7000            11
     46519 East                              7000            11
       708 Door                              7000            11
    102178 Bloom                             7000            11
       123 Rockwell                          7000            11
    104403 Fenton                            7000            11
     13133 Colven                            7000            11
     27666 Remler                            7000            11
      4133 Carr                              9000            11
     10362 Tate                              9000            11
     19362 Rosenblum                         9000            11
     28362 Titus                             9000            11
     28458 Kohler                            9000            11
      6208 Vail                              9000            11
      2749 Kimball                           9000            11
     15208 Trimmer                           9000            11
     11748 Stokley                           9000            11
     24208 Baley                             9000            11
     26975 Baer                              9000            11
     35975 Grubb                             9000            11
     44974 Grier                             9000            11
       529 Barone                            9000            11
      2750 Jansen                           10000            11
    103571 Kennedy                          10000            11
       346 Elliott                          10000            11
     47209 Lieberman                        10000            11
     34590 Barden                           10000            11
     28469 Adams                            10000            11
      6209 Crocker                          10000            11
     43589 Eppling                          10000            11
    100761 Zwolinsky                        10000            11
     15209 Fernandez                        10000            11
    100824 Roy                              11000            11
    103845 Moy                              15000            11
     35402 Elkin                            15000            11

75 rows selected.

where the 4 arguments are:

  • Expression to be evaluated
  • Minimum (start) value
  • Maximum (end) value
  • Number of buckets in the distribution

Notice that if you extend outside the min and max value, the width bucket returns a value one less or one more then the bucket count.

KISS video series for Analytic functions

Analytic functions still bamboozle many SQL practitioners out there.  So I’m building a suite of videos to walk people through some of the common questions we get asked that we need to solve with SQL, and look at how we can often solve them most simply using Analytic functions.

It will all be based on the KISS principle – Keep It Simply SQL, as it pertains to understanding the Analytic syntax.

You can find my introductory video here, with more to come as we solve problems simply with (analytic) SQL.  There is also an “Analytics” playlist on my channel, which will grow each time I add a new video.

I hope you enjoy it – and feel free to comment or offer feedback.

 

Analytics Demo Scripts

Video 2: Ranking rows

https://livesql.oracle.com/apex/livesql/s/ch1tefsz0mc75pk4ix2jbfja7

Video 3: More options for ranking rows

https://livesql.oracle.com/apex/livesql/s/clebs8f5n4kr2b3vjvnvvgxkn

Video 4: Using the Tabibitosan method for grouping sets of rows

https://livesql.oracle.com/apex/livesql/s/cljrgh80cczwjtptlpk2e5081

Video 5: CUME_DIST, PERCENT_RANK and NTILE

https://livesql.oracle.com/apex/livesql/s/clq6jmj1jzju4k23v3tnbzo8u

Video 6: Dealing with NULLS

https://livesql.oracle.com/apex/livesql/s/cokpdete3hiy3jo0yzoa4eerz

Video 7: Analytics as predicates

https://livesql.oracle.com/apex/livesql/s/cokv9220ffzt8iqs7udqsv5vm

Video 8: The partition clause

https://livesql.oracle.com/apex/livesql/file/content_ERYV6B909XK196IAKLBUQIJSN.html

Video 9: Aggregation with partitions

https://livesql.oracle.com/apex/livesql/file/content_CPWDPU6R5I13KQG8EFUO5OPPZ.html

Video 11: The window clause

https://livesql.oracle.com/apex/livesql/file/content_CQXSUXL3JBUOBC82NO9SKKGLE.html

Video 12: More on the window clause

https://livesql.oracle.com/apex/livesql/file/content_CQXSUXL3VBUVDY45G4ASDXXI6.html

Video 13: The RANGE option in the Window clause

https://livesql.oracle.com/apex/livesql/file/content_CR93UBGK2U7OAW6K1EHTBBYHD.html

Video 14: FIRST_VALUE / LAST_VALUE for Windows

https://livesql.oracle.com/apex/livesql/file/content_CTQ60ECMLQDBA256F79UY1YZ5.html

Video 15: NTH_VALUE for Windows

https://livesql.oracle.com/apex/livesql/file/content_CVLPCO2ZHLLF15JGHXMCJ0FIL.html

Video 16: Handling nulls in Windows

https://livesql.oracle.com/apex/livesql/file/content_CW352GZBQYPMETRF31HG2Z034.html

Video 17: Dynamic windows

https://livesql.oracle.com/apex/livesql/file/content_CYAE37SZBU2770BVYW2OT1Q4I.html

Video 18: Lag and Lead

https://livesql.oracle.com/apex/livesql/file/content_CZUCT0MCOQZMJM7TI553HC8S9.html

Video 19: Lag and Lead extensions

https://livesql.oracle.com/apex/livesql/file/content_C0SXW1PG37G8REE0VTQH5LXJ8.html

Video 20: Removing the duplicates

https://1drv.ms/u/s!Aifh7VuM9I2xgQDClpz16T2QvAFo

Video 21: TOP-N queries

https://1drv.ms/u/s!Aifh7VuM9I2xgQm8eqdtQ1ZykvVJ

Video 22: Grouping ranges re-visited

https://livesql.oracle.com/apex/livesql/file/content_C35KKE5TX8H1O3M2Z5TLAGAON.html

Video 23: In-list processing

https://livesql.oracle.com/apex/livesql/file/content_C40G9LF09VJ8W43VGG7ZXDAMO.html

Video 24: Concatenating strings

https://livesql.oracle.com/apex/livesql/file/content_C5NPSOT3UKM9D7BG5J75SPQ2D.html

Video 25: A closer look at LISTAGG

https://livesql.oracle.com/apex/livesql/file/content_C8VCZCTSTJH9A2U9ZMYE10K1Y.html

Video 26: The KEEP clause

https://livesql.oracle.com/apex/livesql/file/content_C85WW581KIH95H1QKH08H8H5G.html

Video 27: Hypothetical analytics and ratios

https://livesql.oracle.com/apex/livesql/file/content_DBCCAWYD3M9R9SVS21SUGK1Q5.html

Video 28: PIVOT and UNPIVOT

https://livesql.oracle.com/apex/livesql/file/content_DCC032IJONO8467JXDM2X9I7T.html

Video 29: Partitioned outer join

https://livesql.oracle.com/apex/livesql/file/content_DDWIGFEMAI346G037QIRDJS8D.html

Video 30: Frequent itemsets

https://1drv.ms/u/s!Aifh7VuM9I2xfJvMsuP5A6hCEa0