Lag: Analytic function to retrieve a value from the previous row.
Usage:
Lag([column]) OVER (PARTITION BY [partition_column] ORDER BY [ordering_column])
column - column to be pulled in from the previous row
ordering_column - column by which a partition will be ordered
partition_column - column to be used to partition data. On first thought it might seem that this column can as well be included in the ordering_column list. On careful inspection one may notice that doing so can result in incorrect data. Suppose the requirement is not just to pull data from any previous row; rather only rows belonging to the same class as the current row; in such a case including the partition_column under ordering_column will produce an incorrect result.
Eg: See below example: Fetching transaction amounts for customers over a two months: Here the data needs to be partitioned by the customer and then the previous row may be pulled in.
CREATE TABLE tmp828cust(month date, custno number, cust_name varchar2(50))
CREATE TABLE tmp828transact(month date, transactno number, custno number, amount number)
Required output:
CUSTNO CUST_NAME TOTAL-MONTH1 TOTAL-MONTH2
1 A 5000 5500
2 B 1000 500
3 C 2000 1800
Inefficient Solution
SELECT t1.custno,
t1.cust_name,
t1.tot,
t2.tot
FROM
(
SELECT c.custno,
c.cust_name,
Sum(t.amount) tot
FROM tmp828cust c,
tmp828transact t
WHERE c.month = t.month
AND c.custno = t.custno
AND t.MONTH = '01-JAN-2008'
GROUP BY c.custno, c.cust_name
)t1,
(
SELECT c.custno,
c.cust_name,
Sum(t.amount) tot
FROM tmp828cust c,
tmp828transact t
WHERE c.month = t.month
AND c.custno = t.custno
AND t.MONTH = '01-FEB-2008'
GROUP BY c.custno, c.cust_name
)t2
WHERE t1.custno = t2.custno
Better Solution
SELECT *
FROM (SELECT custno,
cust_name,
month,
amt,
Lag(amt) OVER (PARTITION BY custno ORDER BY month) last_amt
FROM (SELECT t.month,
c.custno,
c.cust_name,
Sum(t.amount) amt
FROM tmp828cust c,
tmp828transact t
WHERE c.month = t.month
AND c.custno = t.custno
GROUP BY t.month,
c.custno,
c.cust_name
)
)
WHERE last_amt IS NOT NULL
Notice the usage of Lag function here
Lag(amt) OVER (PARTITION BY custno ORDER BY month) : Get the amt column from the previous row, rows should be Partitioned by custno and Ordered by month
References:
1.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13964776003469479042.
http://www.adp-gmbh.ch/ora/sql/analytical/lag.html