一,Lead 語法及例子
Lead函數是十分的好用的一個函數.它的語法如下圖:
簡單地說，lead是個奇特函數，在允許不使用自連接的情況下，一次返回多行。
參數說明：
value_expr 值表達式，通常是字段，也可是是表達式。value_expr本身不支持分析函數，也就是lead不支持多層調用。
offset 偏移，應該是很熟悉的數學概念了，或者是相對偏移，表格來開當前行的第offset行，如果offset是整數就表示是順序下的前第n行，如果是負數就是往后第n行。 如果不提供這個參數，就是默認為1.
default 默認值，如果沒有找到，應該返回什么值的意思，有點類似nvl(col,value)。如果沒有設置，且找不到，那么就返回Null
over 可以簡單地翻譯為在什么。。。的基礎之上
query_partition_clause 分區語句，對結果集合分區的語句，是可選的，如果沒有就是所有的一個分區。
Order_by_clause 排序語句 必須需要 ,形如order by xxx desc/asc
舉例一：
有TABLE TJGMXLS(LSH INT UNIQUE,KHH VARCHAR2(20),RQ NUMBER(8),CJSL NUMBER(12));
現在要查詢連續三天CJSL都大于1000的記錄,則可以按照以下方式查詢.
已經假定了每天都有數據.
select * from (
select cjrq,khh,
lead(cjrq,1) over (order by cjrq) as next_day,
cjsl,lead(cjsl,1) over (order by cjrq) as next_cjsl,
lead(cjrq,2) over (order by cjrq) as next2_day,
lead(cjsl,2) over (order by cjrq) as next2_cjsl
from
(select khh,cjrq,sum(cjsl) cjsl from
tjgmxls group by khh,cjrq)
where khh='000100000012' ) a
where a.cjsl>1000 and a.next_cjsl>1000 and a.next2_cjsl>1000
結果如下:
CJRQ KHH CJSL NEXT_DAY NEXT_CJSL NEXT2_DAY NEXT2_CJSL
--------- ------------ ---------- ---------- ---------- ---------- ----------------------------------------------------
20070801 000100000012 11049 20070802 211185 20070803 282227
20070807 000100000012 75419 20070808 454943 20070809 109248
20070808 000100000012 454943 20070809 109248 20070810 240963
20070809 000100000012 109248 20070810 240963 20070813 24302
20070810 000100000012 240963 20070813 24302 20070814 38201
20070813 000100000012 24302 20070814 38201 20070816 7322
20070818 000100000012 4383 20070820 4434 20070821 5702
20070820 000100000012 4434 20070821 5702 20070822 69022
20070821 000100000012 5702 20070822 69022 20070823 52327
--------------------------------------------------------------------------------------------------------------------
舉例二：
通過這個例子應該能夠很清楚的了解lead是如何工作的了。
SQL> select * from test_value;
MONS JJR CJL CJJE
---------- ---------- ---------- ----------
200801 LZF 250 1999
200802 LZF 200 2000
200803 LZF 300 1000
200804 LZF 23 189
200805 LZF 356 456
200806 LZF 100 200
200807 LZF 600 700
200808 LZF 23 123
200809 LZF 400 500
9 rows selected
SQL> select rownum 序號,Mons,cjl cjl_01,
2 lead(cjl,1) over (order by mons desc) cjl_02,
3 lead(cjl,2) over (order by mons desc) cjl_03,
4 lead(cjl,3) over (order by mons desc) cjl_04,
5 lead(cjl,4) over (order by mons desc) cjl_05,
6 lead(cjl,5) over (order by mons desc) cjl_06,
7 lead(cjl,6) over (order by mons desc) cjl_07,
8 lead(cjl,7) over (order by mons desc) cjl_08,
9 lead(cjl,8) over (order by mons desc) cjl_09
10 from test_value
11 /
序號 MONS CJL_01 CJL_02 CJL_03 CJL_04 CJL_05 CJL_06 CJL_07 CJL_08 CJL_09
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
9 200809 400 23 600 100 356 23 300 200 250
8 200808 23 600 100 356 23 300 200 250
7 200807 600 100 356 23 300 200 250
6 200806 100 356 23 300 200 250
5 200805 356 23 300 200 250
4 200804 23 300 200 250
3 200803 300 200 250
2 200802 200 250
1 200801 250
9 rows selected
二）和LAG函數的區別以及轉換
LAG函數的格式和LEAD一樣，而且是容易和LEAD混淆的。不過看看它們翻譯過來的意思，應該就能大概了解：
LEAD ：前導，向前； LAG:落后 。
它們就是對反義詞。
先看看個查詢吧,并把lead的查詢結果放在后面比較。
SQL> select rownum 序號,Mons,cjl cjl_01,
2 LAG(cjl,1) over (order by mons desc) cjl_02,
3 LAG(cjl,2) over (order by mons desc) cjl_03,
4 LAG(cjl,3) over (order by mons desc) cjl_04,
5 LAG(cjl,4) over (order by mons desc) cjl_05,
6 LAG(cjl,5) over (order by mons desc) cjl_06,
7 LAG(cjl,6) over (order by mons desc) cjl_07,
8 LAG(cjl,7) over (order by mons desc) cjl_08,
9 LAG(cjl,8) over (order by mons desc) cjl_09
10 from test_value;
序號 MONS CJL_01 CJL_02 CJL_03 CJL_04 CJL_05 CJL_06 CJL_07 CJL_08 CJL_09
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
9 200809 400
8 200808 23 400
7 200807 600 23 400
6 200806 100 600 23 400
5 200805 356 100 600 23 400
4 200804 23 356 100 600 23 400
3 200803 300 23 356 100 600 23 400
2 200802 200 300 23 356 100 600 23 400
1 200801 250 200 300 23 356 100 600 23 400
-------------------------------------lead的數據在下面
9 200809 400 23 600 100 356 23 300 200 250
8 200808 23 600 100 356 23 300 200 250
7 200807 600 100 356 23 300 200 250
6 200806 100 356 23 300 200 250
5 200805 356 23 300 200 250
4 200804 23 300 200 250
3 200803 300 200 250
2 200802 200 250
1 200801 250
它們的區別最重要的在于：
1)LEAD 訪問的是結果集合位于當前記錄之后的數據。
2)LAG 范圍的是結果集合位于當前記錄之前的數據。
有點拗口！ 還是以上面的例子來說明下。
先定義兩個字：
前記錄：指的是后于當前記錄輸出的記錄，在屏幕上就是指在當前記錄下方列出的數據。
后記錄：指的是限于當前記錄輸出的記錄，在屏幕上就是位于當前記錄之上的數據。
例如LEAD(CJL,1) OVER (ORDER BY MONS DESC) ,它是這么訪問數據的：
1)首先，從數據搜索滿足條件（滿足where..)的數據，然后把這些數據按照MONS 倒序排序,這個時候的結果集合就稱為"初步結果"
2)如果當前記錄的Mons＝200809，那么系統就找到前面一條記錄，也就是mons=200808的記錄，并把cjl(23)放在當前行。
3)其它行，依此類推。
而LAG，和這個最主要的區別在于上面的第2步驟，這個時候系統往后找，對于MONS=200809而言，后面已經沒有數據了，所以返回NULL。
最后，如果適當修改下查詢，它們是可以達到同樣效果的，例如修改lad語句如下：
select rownum 序號,Mons,cjl cjl_01,
LAG(cjl,1) over (order by mons ASC) cjl_02,
LAG(cjl,2) over (order by mons ASC) cjl_03,
LAG(cjl,3) over (order by mons asc) cjl_04,
LAG(cjl,4) over (order by mons asc) cjl_05,
LAG(cjl,5) over (order by mons asc) cjl_06,
LAG(cjl,6) over (order by mons asc) cjl_07,
LAG(cjl,7) over (order by mons asc) cjl_08,
LAG(cjl,8) over (order by mons asc) cjl_09
from test_value;
這里僅僅是改變了下排序的順序，從降序變為升序。
結果如下：
序號 MONS CJL_01 CJL_02 CJL_03 CJL_04 CJL_05 CJL_06 CJL_07 CJL_08 CJL_09
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 200801 250
2 200802 200 250
3 200803 300 200 250
4 200804 23 300 200 250
5 200805 356 23 300 200 250
6 200806 100 356 23 300 200 250
7 200807 600 100 356 23 300 200 250
8 200808 23 600 100 356 23 300 200 250
9 200809 400 23 600 100 356 23 300 200 250
如果說，還有什么區別，那么就是月份大的結果在最后輸出而已.
來源：http://lzfhope.blog.163.com/blog/static/63639922007844142447/
浙公網安備 33010602011771號