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 tbtWINDOW 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 tbtWINDOW 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 tbtWINDOW 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 tbtWINDOW w AS (PARTITION BY a ORDER BY create_time);

看完上述内容，你们对MySQL 8.0窗口函数怎么运行有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注行业资讯频道，感谢大家的支持。