sql之窗口函数看这篇就够了！

一、三个排序窗口函数:row_number,rank(), dense_rank()的区别

结论：

• row_number() 排序相同时不会重复，会根据顺序排序
• rank() 排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果（跳过排序）
• dense_rank()排序相同时会重复，总数会减少，即会出现1、1、2这样的排序结果

具体实例：

1、数据准备：

create table rownumber(
       id varchar(10) not null,
       name varchar(10) null,
       age varchar(10) null,
       salary int null
);

insert into rownumber(id,name,age,salary) values(1,'a',10,8000);
insert into rownumber(id,name,age,salary) values(1,'a2',11,7500);
insert into rownumber(id,name,age,salary) values(2,'b',12,7500);
insert into rownumber(id,name,age,salary) values(2,'b2',13,4500);
insert into rownumber(id,name,age,salary) values(3,'c',14,8000);
insert into rownumber(id,name,age,salary) values(3,'c2',15,20000);
insert into rownumber(id,name,age,salary) values(4,'d',16,30000);
insert into rownumber(id,name,age,salary) values(5,'d2',17,8000);

2、查看数据：
select * from rownumber;

3、实例：

1）row_number()

    select *,row_number() over (order by salary) as 'rank' from rownumber;

 当排序的字段salary出现相同值时,会根据顺序排序

2）rank()

   select *,rank() over (order by salary) as 'rank' from rownumber;

  当排序的字段salary出现相同值时,rank会一样，且直接跳过排序的值，如上

3）rank()

   select *,rank() over (order by salary) as 'rank' from rownumber;

 当排序的字段salary出现相同值时,rank会一样，但是排序值不会跳过去，如上

4、三个函数的解决实战需求：

求每组(id)内第二名的信息（排序时相同值不跳过）,

分析：这里是不能跳过，相同的排序字段相同时给的排序值是一样，且后面的排序顺序不能跳过：select * from (select *,dense_rank() over (partition by id order by salary) as 'rank' from rownumber)t where t.`rank`=2;

二、 NTILE() 函数

语法：

NITLE(buckets) over ([PARTITION BY partition_column_list]  [order by order_column_list])

参数:

buckets：必需的。桶的数量。桶的数量最大为此分区内的行的数量。

partition_column_list：参与分区的列的列表。

order_column_list ：参与排序的列的列表。

返回值

MySQL NTILE() 函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的排名桶，并返回当前行所在的桶的排名。

 NTILE() 函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的区间，并返回当前行所在的区间编号。每个区间， MySQL 称之为一个排名桶。 NTILE() 根据指定排序为每个桶指设定排名。

实例：求rownumber 表中，salary 前25%的信息。

分析：注意！这里不是TOP25,而是前25%，表里数据总共8条数据，前25%，就是1/4,也就是把数据切分成4个桶，排名第一个的就是25%的，sql 如下：

select *,ntile(4) over (order by salary desc) as 'rank' from rownumber

 select * from (select *,ntile(4) over (order by salary desc) as 'rank' from rownumber)t where t.`rank`=1;