MySQL 8.0窗口函数怎么运行
今天就跟大家聊聊有关MySQL 8.0窗口函数怎么运行,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。
# ROW_NUMBER() 当前行在所有结果里的序号 # CUME_DIST() 累计分布百分比。即 <=a 的行数在所有数据里占的比例 # PERCENT_RANK() 百分比排名。 (rank - 1) / (rows - 1) ,即累计(< a)行数/(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 默认是1,default 默认是null # LEAD(expr [, N[, default]]) N 默认是1,default 默认是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(如果未定义,则相当于全表) 分为N组,返回当前行所在的组序号
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);
看完上述内容,你们对MySQL 8.0窗口函数怎么运行有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注行业资讯频道,感谢大家的支持。