ROWNUMBER、RANK、DENSE_RANK、NTILE排名窗口函数示例
来源:互联网 发布:长得帅的女生知乎 编辑:程序博客网 时间:2024/06/03 21:13
/*1.准备数据(去掉注释再执行--!)
create table t1
(
id int identity(1,1) primary key,
name varchar(50),
qty int default(0)
)
go
insert into t1(name,qty) select 'a1',1
insert into t1(name,qty) select 'a3',3
insert into t1(name,qty) select 'a2',2
insert into t1(name,qty) select 'a5',5
insert into t1(name,qty) select 'a2',22
insert into t1(name,qty) select 'a2',21
insert into t1(name,qty) select 'a2',20
insert into t1(name,qty) select 'a1',11
insert into t1(name,qty) select 'a1',12
insert into t1(name,qty) select 'a5',50
*/
/*2.1示例 OVER+ODER BY*/
select t1.*
,ROW_NUMBER() OVER( ORDER BY name,id) as 'ROW_NUMBER'
,ROW_NUMBER() OVER( ORDER BY name desc,id desc) as 'ROW_NUMBER_DESC'
,RANK() OVER( ORDER BY name) as 'RANK'
,RANK() OVER( ORDER BY name,id) as 'RANK_name_id'
,DENSE_RANK() OVER( ORDER BY name) as 'DENSE_RANK'
,t1.name
,NTILE(1) OVER( ORDER BY name,id) as 'NTILE_1group'
,NTILE(2) OVER( ORDER BY name,id) as 'NTILE_2g'
,NTILE(3) OVER( ORDER BY name,id) as 'NTILE_3g'
,NTILE(4) OVER( ORDER BY name,id) as 'NTILE_4g'
--NTILE(分组数目)平均有余时,从最后的组倒分平均:avg=2 for 4,avg=2 for 3,avg=3 for 2 and 1
from t1
order by t1.name,id
/*2.2示例 OVER + PATITION BY + ODER BY*/
--加了PATITION,将会先PATITION成多个分区,然后在分区内部排名,各区的排名互不干扰,排名计数器各自独立从0开始
select t1.*
,ROW_NUMBER() OVER( PARTITION BY name ORDER BY name,id ) as 'ROW_NUMBER'
,ROW_NUMBER() OVER( PARTITION BY name ORDER BY name desc,id desc) as 'ROW_NUMBER_DESC'
,RANK() OVER( PARTITION BY name ORDER BY name) as 'RANK'
,RANK() OVER( PARTITION BY name ORDER BY name,id) as 'RANK_name_id'
,DENSE_RANK() OVER( PARTITION BY name ORDER BY name) as 'DENSE_RANK'
,t1.name
,NTILE(1) OVER( PARTITION BY name ORDER BY name,id) as 'NTILE_1group'
,NTILE(2) OVER( PARTITION BY name ORDER BY name,id) as 'NTILE_2g'
,NTILE(3) OVER( PARTITION BY name ORDER BY name,id) as 'NTILE_3g'
,NTILE(4) OVER( PARTITION BY name ORDER BY name,id) as 'NTILE_4g'
from t1
order by t1.name,id
阅读全文
0 0
- ROWNUMBER、RANK、DENSE_RANK、NTILE排名窗口函数示例
- SQLServer针对排名函数ROWNUMBER()、RANK()、DENSE_RANK()、NTILE的研究!~
- MSSQL示例(三)ROW_NUMBER、RANK、DENSE_RANK、NTILE排名窗口函数示例
- SQLSERVER排名函数RANK,DENSE_RANK,NTILE,ROW_NUMBER
- SQL Server2008 排序函数应用RowNumber ,Rank,Dense_Rank ,Ntile
- Sql Server2005 4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()
- 排名或顺序的函数 ROW_NUMBER/RANK/DENSE_RANK/NTILE
- SQL SERVER排名函数RANK,DENSE_RANK,NTILE,ROW_NUMBER
- sql server 排名函数 row_number,rank,dense_rank和ntile
- Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介
- Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介
- Sql 四大排名函数(ROW_NUMBER、RANK、DENSE_RANK、NTILE)简介
- HIVE分析窗口函数:NTILE,ROW_NUMBER,RANK,DENSE_RANK
- Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK
- Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK
- Hive分析窗口函数之NTILE,ROW_NUMBER,RANK和DENSE_RANK
- Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK
- Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK
- 运维小白的python之路——paramiko
- 访问数据库地址的授权
- Model的常用方法
- aidl通信进阶,使用binder连接池
- 输入一个链表,从尾到头打印链表每个节点的值。
- ROWNUMBER、RANK、DENSE_RANK、NTILE排名窗口函数示例
- sourceInsight添加黑色主题(添加配置文件)
- C# 调用SVN: 提交与更新
- python中reduce()函数
- jvm内存模型
- HTTP Status 500
- thinkPHP易错点集合
- nodejs-mysql异步(2)
- 妨碍开发人员获得高性能的三种行为!