mysql8.0窗口函数

1.求平均值

select name, avg(balance) over(partition by name) as avg from channel;

根据name分组求balance的平均值

 

2.求总和

select name, sum(balance) over(partition by name) as balanveAvg from channel;

根据name求balance的总和

 

3.根据balance排序，选出拍第一的金额

select name, channel, balance, first_value(balance) over(partition by name) as balanveAvg from channel;

 

4.求总和

select name, channel, balance, sum(balance) over() as balance from channel;

 

5.序号窗口函数

根据balance排序，生成对应的序号

select name, channel, balance, row_number() over(order by balance) as sort from channel;

row_number

 

rank

相同的值序号一样，不会跳过自增

 

dense_rank

相同的值序号一样，会跳过自增