mysql查询-窗口函数

1.当前表

create table order_tab(
	order_id int primary key,
	user_no char(3),
	amount float(5,2),
	create_date datetime
);

insert into order_tab values
(1,'001',100,'2018-01-01 00:00:00'),
(2,'001',300,'2018-01-02 00:00:00'),
(3,'001',500,'2018-01-02 00:00:00'),
(4,'001',800,'2018-01-03 00:00:00'),
(5,'001',900,'2018-01-04 00:00:00'),
(6,'002',500,'2018-01-03 00:00:00'),
(7,'002',600,'2018-01-04 00:00:00'),
(8,'002',300,'2018-01-10 00:00:00'),
(9,'002',800,'2018-01-16 00:00:00'),
(10,'002',800,'2018-01-22 00:00:00');

2.row_number()

查询每个用户最新的一笔订单

select * from(
	select ROW_NUMBER()over(partition by user_no order by create_date desc) as row_now,
	order_id,user_no,amount,create_date
	from order_tab
)t where row_now=1;

3.row_number()、rank()、dense_rank()区别

row_number()：分区中的当前行号
rank()：当前行在分区中的排名，相等时，有序号间隙
dense_rank()：当前行在分区中的排名，相等时，没有序号间隙

select row_number() over(partition by user_no order by amount desc) as row_num1,
	   rank() over(partition by user_no order by amount desc) as row_num2,
	   dense_rank() over(partition by user_no order by amount desc) as row_num3,
	   order_id, user_no, amount, create_date
from order_tab

4.percent_rank()、cume_dist()与rank()有关

percent_rank()：百分比等级值，(rank-1)/(rows-1)
cume_dist()：累计分配值，(分组内小于等于当前rank值得行数)/(分组内总行数)
注意别名不能为关键字

select rank() over w as row_rank,
	   percent_rank() over w as row_percent_rank,
	   cume_dist() over w as row_cume_dist,
	   order_id, user_no, amount, create_date
from order_tab
window w as (partition by user_no order by amount desc);

5.nfile(N)、nth_value(expr,N)

nfile(N)：分区中当前行的桶号；相当于对每个分区继续分成N组，每组的行数为：分区的总行数/N
nth_value(expr,N)：返回窗口中第N个expr值，对于前N-1列，返回NULL

select ntile(3) over w as row_ntile,
	   nth_value(order_id,3) over w as row_nth_value,
	   order_id, user_no, amount, create_date
from order_tab
window w as (partition by user_no order by amount desc);

6.lag(expr,N)/lead(expr,N)

返回指定字段(expr)的上N行(lag)或下N行(lead)的值

select order_id, user_no, amount, create_date,
	   lag(create_date,1) over w as row_lag,
	   lead(create_date,1) over w as row_lead
from order_tab
window w as (partition by user_no order by create_date);

7.first_value(expr)、last_value(expr)

获取滑动窗口范围内expr字段的第一个值和最后一个值

select order_id, user_no, amount, create_date,
	   first_value(amount) over w as row_first_value,
	   last_value(amount) over w as row_last_value
from order_tab
window w as (partition by user_no order by create_date);

8.聚合函数用作窗口函数

select order_id, user_no, amount, create_date,
	   sum(amount) over w as row_sum,
	   avg(amount) over w as row_avg,
	   max(amount) over w as row_max,
	   min(amount) over w as row_min,
	   count(amount) over w as row_count
from order_tab
window w as (partition by user_no order by order_id desc);