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
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.
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
A very flexible method of grouping data into even or unevenly sized buckets.
Very similar to DECODE
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
Oracle SQL Functions