--创建测试表
CREATE TABLE ORDER_TEST(ID NUMBER,EMPID NUMBER,SAL NUMBER(10,2));
--依然使用序列
CREATE SEQUENCE SEQ_ORDER_TEST
MINVALUE 1
MAXVALUE 99999
START WITH 1
INCREMENT BY 1;
--插入数据
INSERT INTO ORDER_TEST(ID,EMPID,SAL) VALUES(SEQ_ORDER_TEST.NEXTVAL,&EMPID,&SAL);
--查看一下数据【COMMIT;】
SELECT * FROM ORDER_TEST;
--ROW_NUMBER() OVER[排名:1,2,3,4,5,6,7,8……]
SELECT ID,EMPID,SAL,ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY SAL) OR_1,ROW_NUMBER() OVER(ORDER BY SAL) OR_2 FROM ORDER_TEST ORDER BY EMPID,4;
--RANK() OVER[排名:1,2,2,2,2,6,7,7,9……]
SELECT ID,EMPID,SAL,RANK() OVER(PARTITION BY EMPID ORDER BY SAL) OR_1,RANK() OVER(ORDER BY SAL) OR_2 FROM ORDER_TEST ORDER BY EMPID,4;
--DENSE_RANK() OVER[排名:1,2,2,2,2,3,4,4,5……]
SELECT ID,EMPID,SAL,DENSE_RANK() OVER(PARTITION BY EMPID ORDER BY SAL) OR_1,DENSE_RANK() OVER(ORDER BY SAL) OR_2 FROM ORDER_TEST ORDER BY EMPID,4;
--RATIO_TO_REPORT() OVER[用于对汇总的统计信息计算比率,即分项占汇总数的比列]
SELECT ID,EMPID,SAL,RATIO_TO_REPORT(SAL) OVER()RR FROM ORDER_TEST;
--RATIO_TO_REPORT() OVER [占分组内总数的比例]
SELECT ID,EMPID,SAL,RATIO_TO_REPORT(SAL) OVER(PARTITION BY EMPID ) RR FROM ORDER_TEST;
--工资前三名
WITH A AS
(SELECT ID,EMPID,SAL,ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY SAL) OR_1,ROW_NUMBER() OVER(ORDER BY SAL DESC) OR_2 FROM ORDER_TEST)
SELECT * FROM A WHERE A.OR_2<=3;
--FIRST,LAST(将空值排名置第一,置倒数第一)有时候排名 空值往往会排到第一位。这样结果不是我们想要的。所以ORACEL给出了NULLS LAST/NULLS FIRT解决办法
SELECT ID,EMPID,SUM(SAL),ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY SUM(SAL) DESC /*NULLS FIRST*/) OR_1 FROM ORDER_TEST GROUP BY ID,EMPID;
SELECT ID,EMPID,SUM(SAL),ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY SUM(SAL) DESC NULLS LAST) OR_1 FROM ORDER_TEST GROUP BY ID,EMPID;
--@@@@注:语句格式基本上是这样.书本格式不会。大部分我都是这么在用。MIN()/MAX() KEEP (DENSE_RANK FIRST/LAST ORDER BY [ASC/DESC]) [OVER([PARTIITON BY ])]
--查看序号[就相当于我们单位的工号]最小[大]工资最多[最少]的。BOSS想看看哪个新入职员工工资涨的最快,哪个最慢
SELECT MIN(ID) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC NULLS LAST) FIRST,
MAX(ID) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC NULLS LAST) MAX_FIRST,
MIN(ID) KEEP(DENSE_RANK LAST ORDER BY SAL DESC) LAST,
MAX(ID) KEEP(DENSE_RANK LAST ORDER BY SAL DESC) MAX_LAST
FROM ORDER_TEST;
--带上OVER(),对每一条记录计算。
SELECT ID,EMPID,SAL,
MIN(ID) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC NULLS LAST) OVER() FIRST,
MAX(ID) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC NULLS LAST) OVER() MAX_FIRST,
MIN(ID) KEEP(DENSE_RANK LAST ORDER BY SAL DESC) OVER() LAST,
MAX(ID) KEEP(DENSE_RANK LAST ORDER BY SAL DESC) OVER() MAX_LAST
FROM ORDER_TEST;
--求每个部门序号[就相当于我们单位的工号]最小[大]工资最多[最少]的。
SELECT ID,EMPID,SAL,
MIN(ID) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC NULLS LAST) OVER(PARTITION BY EMPID) FIRST,
MAX(ID) KEEP(DENSE_RANK FIRST ORDER BY SAL DESC NULLS LAST) OVER(PARTITION BY EMPID) MAX_FIRST,
MIN(ID) KEEP(DENSE_RANK LAST ORDER BY SAL DESC) OVER(PARTITION BY EMPID) LAST,
MAX(ID) KEEP(DENSE_RANK LAST ORDER BY SAL DESC) OVER(PARTITION BY EMPID) MAX_LAST
FROM ORDER_TEST;
--NTIL函数为各个记录在记录集中的排名计算比例[4就是4个分档,5就是5个分档]
SELECT ID,EMPID,SUM(SAL),NTILE(4)OVER(ORDER BY SUM(SAL) DESC NULLS LAST) OR_1 FROM ORDER_TEST GROUP BY ID,EMPID;
--查询收入是前25%的用户
WITH A AS
(SELECT ID,EMPID,SUM(SAL),NTILE(4)OVER(ORDER BY SUM(SAL) DESC NULLS LAST) OR_1 FROM ORDER_TEST GROUP BY ID,EMPID)
SELECT * FROM A WHERE OR_1=1;
分享到:
相关推荐
MS SQL 2005 四个排序函数ROW_NUMBER、RANK、DENSE_RANK 和 NTILE简介用法结果排名排序
分析函数ROW_NUMBER、RANK、DENSE_RANK的用法
排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数row_number、rank、dense_rank和ntile,需要的朋友可以参考下。
本文主要是对Oracle分析函数Rank, Dense_rank, row_number的使用法,通过这些函数,我们可以对数据进行排序和分组,需要的朋友可以参考下。
hive中分组取topN、row_number、rank和dense_rank使用介绍
ROW_NUMBER()说明:返回结果集...参数:<partition> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。 <order>:确定将 ROW_NUMBER 值分配给分区中的行的顺序。返回类型:bigint 。示例:/*以下示例将
主要介绍了sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述 ROW_NUMBER () OVER([partition_clause]...
“rank()、dense_rank()和row_number()的区别”文章的date
排序用Rank, Dense_rank, row_number 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 1.窗口函数简介 2.窗口函数示例-全统计 3.窗口函数进阶-滚动统计(累积/均值) 4.窗口函数进阶-根据时间...
row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据... rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内) dense_rank()也是连续排序,有两个第二名时仍然跟
2 Oracle开发专题之:分析函数 Rank Dense rank row number 3 Oracle开发专题之:分析函数3 Top Bottom N First Last NTile 4 Oracle开发专题之:窗口函数 5 Oracle开发专题之:报表函数 6 Oracle开发专题之:...
本技巧文章将讨论ROW_NUMBER(),RANK()和DENSE_RANK()之间的区别。
目录 Oracle开发专题之:分析函数(OVER) Oracle开发专题之:分析函数2(Rank, Dense_rank, row_number) Oracle开发专题之:分析函数3(Top/Bottom N、First/Last、NTile) Oracle开发专题之:窗口函数 ...
,row_number, ratio_to_report Over不能单独使用,用来制定数据窗口大小 Partition by表示分类数据集合,在此集合上的运算 Order by 跟排序字段,range时只能按一个字段排序,使用rows是可以跟多个字段排序 Range ...