Mysql8.0支持窗口函数
习惯使用orale的程序猿们,mysql8.0支持窗口函数,使用mysql统计各种报表数据将不再麻烦。还未升级mysql的同学们,也可以升级尝试一下。
- number() , rank() , dense_rank()
CREATE TABLE numbers (
val INT
);
INSERT INTO numbers(val)
VALUES(1),(2),(2),(3),(4),(4),(5);
SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank'
FROM
numbers WINDOW w AS ( ORDER BY val );
- lag() , lead()
create table series(
date int,
val int
);
insert into series values
(202001,10000),(202002,12000),(202003,11000),
(202004,9800),(202005,15000),(202006,13000);
SELECT
date,
val,
LAG( val ) OVER w AS 'lag',
LEAD( val ) OVER w AS 'lead',
val - LAG( val ) OVER w AS 'lag diff',
val - LEAD( val ) OVER w AS 'lead diff'
FROM
series WINDOW w AS ( ORDER BY date );