今天就跟大家聊聊有关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窗口函数怎么运行有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注亿速云行业资讯频道,感谢大家的支持。
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。
原文链接:http://blog.itpub.net/26250550/viewspace-2689311/