Cách sử dụng hàm LAG và LEAD

Xét ví dụ sau: Lấy danh sách lương nhân viên kèm theo cho biết mức lương cận kề trước và sau của nhân viên đó.
Ta nhìn vào bảng kết quả sau để hiểu rõ hơn về yêu cầu trên:

DEPTNO	ENAME	SAL	PRE_SAL	NEXT_SAL
20	SMITH	800	0	950
30	JAMES	950	800	1100
20	ADAMS	1100	950	1250
30	WARD	1250	1100	1250
30	MARTIN	1250	1250	1300
10	MILLER	1300	1250	1500
30	TURNER	1500	1300	1600
30	ALLEN	1600	1500	2450
10	CLARK	2450	1600	2850
30	BLAKE	2850	2450	2975
20	JONES	2975	2850	3000
20	SCOTT	3000	2975	3000
20	FORD	3000	3000	5000
10	KING	5000	3000	0

Ta thấy rằng mức lương của JAMES là 950 kèm theo đó cũng cho ta biết được mức lương kề trước đó là 800 (của SMITH) và 1100 (của ADAMS)

Và đây là kết quả câu SELECT:

1 2 3 4 5 6

SELECT deptno, ename, sal, LAG (sal, 1, 0) OVER (ORDER BY sal) pre_sal, LEAD (sal, 1, 0) OVER (ORDER BY sal) next_sal FROM emp;

Ở đây chúng ta sử dụng 2 hàm LAG và LEAD để có được kết quả.
Cú pháp 2 hàm này như sau:

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause) LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

 Trong đó:

• LAG cho phép ta lấy kết quả của dòng trước đó, LEAD cho phép ta lấy kết quả của dòng sau đó
• value_expression: cột cần lấy kết quả 
• offset: bước nhảy dòng hoặc số dòng trước đó hoặc sau đó
• default: giá trị default nếu như offset vượt quá ngưỡng. Như ví dụ trên ta thấy rằng dòng đầu tiên khi dùng hàm LAG thì trước đó không có dòng nào để lấy giá trị nên kết quả trả về sẽ là giá trị default mà ta nhập vào (ở đây là 0). Nếu như ta không nhập giá trị default này thì Oracle sẽ trả về cho chúng ta kết quả là NULL

Với 2 hàm này ta có thể sử dụng để thực hiện 1 số báo cáo như:

• Lấy doanh số ngày n kèm theo doanh số ngày n-1
  

  DATE	AMOUNT	AMOUNT2
  01/01/2014	50000	0
  02/01/2014	40000	50000
  03/01/2014	90000	40000

• Lấy lịch sử đơn đặt hàng của khách hàng cho biết ngày đặt hàng, số lượng hàng của ngày đặt hàng và ngày đặt hàng trước đó
  

  ORDER_DATE	AMOUNT	PRE_ORD_DATE	PRE_ORD_AMOUNT
  01/01/2014	30		0
  09/01/2014	80	01/01/2014	30
  16/01/2014	40	09/01/2014	80

• V.v...