兩個(gè)實(shí)用的SQL高級(jí)函數(shù)
點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，
設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨
SQL Server從2012版本開始，引入了LEAD和LAG函數(shù)，這兩個(gè)函數(shù)可以把之前要關(guān)聯(lián)查詢的方法，改為可直接獲取當(dāng)前數(shù)據(jù)上下相鄰多少行數(shù)據(jù)，可以很方便的對(duì)上下相鄰兩行的數(shù)據(jù)進(jìn)行加減乘除。今天我們就給大家介紹一下這兩個(gè)函數(shù)的用法。
LAG函數(shù)
LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
WITH T AS
(
SELECT 1 ID,10 NUM
UNION ALL
SELECT 1,20
UNION ALL
SELECT 1,30
UNION ALL
SELECT 2,40
UNION ALL
SELECT 2,50
UNION ALL
SELECT 2,60
)
SELECT ID,NUM,
LAG(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,
LAG(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,
LAG(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs
FROM T(提示：可以左右移動(dòng)代碼)
結(jié)果如下：
從上面的示例中我們可以看到
1、針對(duì)列OneArgs，組內(nèi)的NUM列的值默認(rèn)向后偏移了一行，每組的第一行用默認(rèn)的NULL來代替
2、針對(duì)TowArgs，使用了2個(gè)參數(shù)顯示的偏移行，NUM的值也是向后偏移一行。
3、針對(duì)ThreeArgs，不僅使用了顯示的偏移2行，而且第三個(gè)參數(shù)將偏移后默認(rèn)值NULL改成了0
實(shí)戰(zhàn)例子：如何求解組內(nèi)上下兩行的和？
SELECT ID,NUM,
NUM+LAG(NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM) AS Result
FROM T結(jié)果如下：
注意：第一行因?yàn)槟J(rèn)是0，所以每組第一行的結(jié)果是NUM+0=NUM
LEAD函數(shù)
LEAD函數(shù)與LAG函數(shù)剛剛相反，它是向前偏移指定的行數(shù)，默認(rèn)是1行。
語法哈參數(shù)與LAG類似，這里就不重復(fù)介紹了。我們直接看示例：
SELECT ID,NUM,
LEAD(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,
LEAD(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,
LEAD(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs
FROM T結(jié)果：
使用情況與LAG函數(shù)類似，只是組內(nèi)數(shù)據(jù)分別向前偏移了指定行數(shù)。
實(shí)戰(zhàn)示例：求解同組內(nèi)上下兩行的差？
SELECT ID,NUM,
LEAD(NUM,1,0) OVER (PARTITION BY ID ORDER BY NUM)-NUM AS Result
FROM T結(jié)果：
每組最后一行默認(rèn)是0，所以0-NUM=-NUM
以上就是這兩個(gè)函數(shù)的相關(guān)用法。
最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識(shí)第二版》和《SQL高級(jí)知識(shí)第二版》的PDF電子版。里面有各個(gè)語法的解釋、大量的實(shí)例講解和批注等等，非常通俗易懂，方便大家跟著一起來實(shí)操。
有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號(hào)「數(shù)據(jù)前線」(非本號(hào))后臺(tái)回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群
記得幫忙點(diǎn)「贊」和「在看」↓
謝謝啦
