您好,登錄后才能下訂單哦!
今天就跟大家聊聊有關(guān)MySQL 8.0窗口函數(shù)怎么運(yùn)行,可能很多人都不太了解,為了讓大家更加了解,小編給大家總結(jié)了以下內(nèi)容,希望大家根據(jù)這篇文章可以有所收獲。
# ROW_NUMBER() 當(dāng)前行在所有結(jié)果里的序號 # CUME_DIST() 累計分布百分比。即 <=a 的行數(shù)在所有數(shù)據(jù)里占的比例 # PERCENT_RANK() 百分比排名。 (rank - 1) / (rows - 1) ,即累計(< a)行數(shù)/(total-1) SELECT a, ROW_NUMBER() OVER w AS 'row_number', CUME_DIST() OVER w AS 'cume_dist', PERCENT_RANK() OVER w AS 'percent_rank' FROM tbt WINDOW w AS (ORDER BY a);
# LAG(expr [, N[, default]]) N 默認(rèn)是1,default 默認(rèn)是null # LEAD(expr [, N[, default]]) N 默認(rèn)是1,default 默認(rèn)是null SELECT create_time, id, LAG(id) OVER w AS '上1行的id', LEAD(id) OVER w AS '下1行的id', LAG(id, 3, 0) OVER w AS '上3行的id', LEAD(id, 3, 0) OVER w AS '下3行的id' FROM tbt WINDOW w AS (ORDER BY create_time);
# FIRST_VALUE(val) 同一個分組,第一個值 # LAST_VALUE(val) 同一個分組,最后一個值 # NTH_VALUE(val, N) 同一個分組,第N值 SELECT a, create_time, id, FIRST_VALUE(id) OVER w AS 'first', LAST_VALUE(id) OVER w AS 'last', NTH_VALUE(id, 3) OVER w AS 'third' FROM tbt WINDOW w AS (PARTITION BY a ORDER BY create_time);
# NTILE(N) 將同一個partition(如果未定義,則相當(dāng)于全表) 分為N組,返回當(dāng)前行所在的組序號
SELECT
a,
ROW_NUMBER() OVER w AS 'row_number',
NTILE(2) OVER w AS 'ntile2',
NTILE(4) OVER w AS 'ntile4'
FROM tbt
WINDOW w AS (ORDER BY a);
SELECT a, create_time, ROW_NUMBER() OVER w AS 'row_number', NTILE(2) OVER w AS 'ntile2', NTILE(4) OVER w AS 'ntile4' FROM tbt WINDOW w AS (PARTITION BY a ORDER BY create_time);
看完上述內(nèi)容,你們對MySQL 8.0窗口函數(shù)怎么運(yùn)行有進(jìn)一步的了解嗎?如果還想了解更多知識或者相關(guān)內(nèi)容,請關(guān)注億速云行業(yè)資訊頻道,感謝大家的支持。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。