排序函数
PARTITION BY 分区
加PATITION,将会先PATITION成多个分区,然后在分区内部排名,
各区的排名互不干扰,排名计数器各自独立从0开始.
ROW_NUMBER()
ROW_NUMBER函数允许以上升,连续的顺序给每一行数据一个序号。
注意ROW_NUMBER()函数后面一定要跟着over子句。
RANK()
RANK 函数允许以上升,跳跃的顺序给每一行数据一个序号,相同的行序号相同。
注意RANK()函数后面也要跟着over子句。
DENSE_RANK()
DENSE_RANK 函数允许以上升,连续的顺序给每一行数据一个序号,相同的行序号相同。
注意DENSE_RANK()函数后面也跟着over子句。
NTILE()
NTILE() 函数对纪录进行分片处理,根据排序规则, NTILE() 函数为每片生成一个序号,
且序号小的纪录条数大于等于序号大的纪录条数。
NTILE的值与公式[(N * (ROW_NUMBER()-1)/COUNT(*))+1]计算得到的值基本一致。
eg:
--建表
CREATE TABLE [dbo].[test](
[id] [int] NULL,
[col01] [int] NULL,
[col02] [int] NULL,
) ON [PRIMARY]
GO
--测试数据
insert into test (id, col01, col02) values (1, 1, 2);
insert into test (id, col01, col02) values (2, 1, 2);
insert into test (id, col01, col02) values (3, 1, 4);
insert into test (id, col01, col02) values (4, 1, 4);
insert into test (id, col01, col02) values (5, 5, 6);
insert into test (id, col01, col02) values (6, 5, 6);
insert into test (id, col01, col02) values (7, 5, 8);
insert into test (id, col01, col02) values (8, 5, 8);
insert into test (id, col01, col02) values (9, 9, 10);
insert into test (id, col01, col02) values (10, 9,10);
insert into test (id, col01, col02) values (11, 9,12);
insert into test (id, col01, col02) values (12, 9,12);
select * from test
SELECT id, col01, col02,
--ID降序排序后,生成连续不重复自增序号
row_number() over(order by id desc) AS rum,
--根据col01分组,col02降序排序,生成组内连续不重复自增序号
row_number() over(partition by col01 order by col02 desc) AS rum_p,
--col02降序排序后,生成跳跃的可重复自增序号
rank() over(order by col02 desc) AS rnk,
--根据col01分组,col02降序排序,生成组内跳跃的可重复自增序号
rank() over(partition by col01 order by col02 desc) AS rnk_p,
--col02降序排序后,生成连续的可重复自增序号
dense_rank() over(order by col02 desc) AS drk,
--根据col01分组,col02降序排序,生成组内连续的可重复自增序号
dense_rank() over(partition by col01 order by col02 desc) AS drk_p,
--ID降序排序后,分成两片(也可看作是分成了两页, 编号为页序号)
NTILE(2) over(order by id desc) as nle_2,
--根据col01分组,col02降序排序,分区后,再对区内数据分片
NTILE(2) over( partition by col01 order by id desc) as nle_2_p
FROM test
ORDER BY id desc;
PARTITION BY 分区
加PATITION,将会先PATITION成多个分区,然后在分区内部排名,
各区的排名互不干扰,排名计数器各自独立从0开始.
ROW_NUMBER()
ROW_NUMBER函数允许以上升,连续的顺序给每一行数据一个序号。
注意ROW_NUMBER()函数后面一定要跟着over子句。
RANK()
RANK 函数允许以上升,跳跃的顺序给每一行数据一个序号,相同的行序号相同。
注意RANK()函数后面也要跟着over子句。
DENSE_RANK()
DENSE_RANK 函数允许以上升,连续的顺序给每一行数据一个序号,相同的行序号相同。
注意DENSE_RANK()函数后面也跟着over子句。
NTILE()
NTILE() 函数对纪录进行分片处理,根据排序规则, NTILE() 函数为每片生成一个序号,
且序号小的纪录条数大于等于序号大的纪录条数。
NTILE的值与公式[(N * (ROW_NUMBER()-1)/COUNT(*))+1]计算得到的值基本一致。
eg:
--建表
CREATE TABLE [dbo].[test](
[id] [int] NULL,
[col01] [int] NULL,
[col02] [int] NULL,
) ON [PRIMARY]
GO
--测试数据
insert into test (id, col01, col02) values (1, 1, 2);
insert into test (id, col01, col02) values (2, 1, 2);
insert into test (id, col01, col02) values (3, 1, 4);
insert into test (id, col01, col02) values (4, 1, 4);
insert into test (id, col01, col02) values (5, 5, 6);
insert into test (id, col01, col02) values (6, 5, 6);
insert into test (id, col01, col02) values (7, 5, 8);
insert into test (id, col01, col02) values (8, 5, 8);
insert into test (id, col01, col02) values (9, 9, 10);
insert into test (id, col01, col02) values (10, 9,10);
insert into test (id, col01, col02) values (11, 9,12);
insert into test (id, col01, col02) values (12, 9,12);
select * from test
SELECT id, col01, col02,
--ID降序排序后,生成连续不重复自增序号
row_number() over(order by id desc) AS rum,
--根据col01分组,col02降序排序,生成组内连续不重复自增序号
row_number() over(partition by col01 order by col02 desc) AS rum_p,
--col02降序排序后,生成跳跃的可重复自增序号
rank() over(order by col02 desc) AS rnk,
--根据col01分组,col02降序排序,生成组内跳跃的可重复自增序号
rank() over(partition by col01 order by col02 desc) AS rnk_p,
--col02降序排序后,生成连续的可重复自增序号
dense_rank() over(order by col02 desc) AS drk,
--根据col01分组,col02降序排序,生成组内连续的可重复自增序号
dense_rank() over(partition by col01 order by col02 desc) AS drk_p,
--ID降序排序后,分成两片(也可看作是分成了两页, 编号为页序号)
NTILE(2) over(order by id desc) as nle_2,
--根据col01分组,col02降序排序,分区后,再对区内数据分片
NTILE(2) over( partition by col01 order by id desc) as nle_2_p
FROM test
ORDER BY id desc;