How-to: Lead, Lag and CASE Functions

LAG and LEAD provide a comparison between 2 rows in a table without requiring a self join.

LAG() provides access to a prior row
LEAD() provides access to a row after the current position

These functions are position, not value based

Syntax

LEAD | LAG
   (<exp1> [,<offset> [, <default>]]) OVER
    ([PARTITION BY <exp2> [,…]])
    ORDER BY <exp3> [ASC|DESC] 
    [NULLS FIRST | NULLS LAST] [,…])

<offset> is optional and defaults to 1
<default> is optional and is the value returned if the <offset> falls outside the bounds of the dataset.

Example:

SELECT customer_id, order_date, sum(Order_amt) As CurrentOrder,
  LAG(SUM(Order_amt),1) OVER
     (PARTITION BY customer_id
      ORDER BY payment_date ) AS LastOrder
 FROM OrderBook
WHERE order_date > '01-JAN-03'
GROUP BY customer_id, order_date
   

Case Function

A very flexible method of grouping data into even or unevenly sized buckets.
Very similar to DECODE

Syntax:

CASE WHEN <cond1> THEN <Value1>
     WHEN <cond2> THEN <Value2>
     [ELSE Value n ] END   

A single CASE statement can be selected (along with other columns), resulting in a vertical list of data buckets. Alternatively several case statements can be summed to display totals in a horizontal row:

 SUM(CASE WHEN SUM(amount) 
   BETWEEN 0 AND 49 
   THEN 1 ELSE 0 END) AS "0-49",
 SUM(CASE WHEN SUM(amount) 
   BETWEEN 50 AND 100
   THEN 1 ELSE 0 END) AS "50-100",

"We herd sheep, we drive cattle, we lead people. Lead me, follow me, or get out of my way" ~ George S. Patton

Related

Analytic features
Oracle SQL Functions