MySQl Window Function Concepts

create table test(
	user_id int1,
	user_class int1,
	user_amount int1
);


insert into test values (1,1,10);
insert into test values (3,2,30);
insert into test values (2,1,20);
insert into test values (4,2,40);

-- 分组,然后组内排序
select row_number()over(partition by user_class order by user_amount desc) as row_num,
user_id,user_class,user_amount
from test

-- 获取分组后,排序最靠前的
select * from (
	select row_number()over(partition by user_class order by user_amount desc) as row_num,
	user_id,user_class,user_amount
	from test
) j where row_num =1;

-- 获得分组后,排序最考前的 n个值

-- rank 如果排序值相同,则排名相同,后一位跳号
-- dense_rank 如果排序值相同,则排名相同,后一位不跳号


-- avg,sum,是累和的形式来求

-- lag,lead,获得某条数据上一条数据/或下一条数据

select lag(user_amount,1) over (partition by user_class order by user_amount desc) as row_num,
user_id,user_class,user_amount
from test

参考:
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html