共计 1307 个字符,预计需要花费 4 分钟才能阅读完成。
今天就跟大家聊聊有关 MySQL 8.0 窗口函数怎么运行,可能很多人都不太了解,为了让大家更加了解,丸趣 TV 小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。
# ROW_NUMBER() 当前行在所有结果里的序号
# CUME_DIST() 累计分布百分比。即 =a 的行数在所有数据里占的比例
# PERCENT_RANK() 百分比排名。 (rank - 1) / (rows - 1) ,即累计 ( a) 行数 /(total-1)
SELECT
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
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 窗口函数怎么运行有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注丸趣 TV 行业资讯频道,感谢大家的支持。
正文完