Oracle数据库中SQL开窗函数的使用(2)-木庄网络博客

区别：

这个SQL语句与例1中的SQL语句唯一不同的就是“ROWS”被替换成了“RANGE”。“ROWS”
是按照行数进行范围定位的，而“RANGE”则是按照值范围进行定位的，这两个不同的定位方式
主要用来处理并列排序的情况。比如 Lily、Swing、Bill这三个人的工资都是2000元，如果按照
“ROWS”进行范围定位，则计算从第一条到当前行的累积和，而如果 如果按照 “RANGE”进行
范围定位，则仍然计算从第一条到当前行的累积和，不过由于等于2000元的工资有三个人，所
以计算的累积和为从第一条到2000元工资的人员结，所以对 Lily、Swing、Bill这三个人进行开
窗函数聚合计算的时候得到的都是7000（ “ 1000+2000+2000+2000 ”）。

下边这的估计不常用：

例子程序三：

SELECT FName,
  FSalary,
  SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) 前二后二和
 FROM T_Person;

这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2
PRECEDING AND 2 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行前两行（2
PRECEDING）到当前行后两行（2 FOLLOWING）的工资和，注意对于第一条和第二条而言它们
的“前两行”是不存在或者不完整的，因此计算的时候也是要按照前两行是不存在或者不完整进
行计算，同样对于最后两行数据而言它们的“后两行”也不存在或者不完整的，同样要进行类似
的处理。

例子程序四：

SELECT FName, FSalary,
SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) 后面一到三之和
FROM T_Person;

这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1
FOLLOWING AND 3 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行后一行（1
FOLLOWING）到后三行（3 FOLLOWING）的工资和。注意最后一行没有后续行，其计算结果为
空值NULL而非0。

例子程序五：算工资排名

SELECT FName, FSalary,
COUNT(*) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW)
FROM T_Person;

这里的开窗函数“COUNT(*) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序，然后计算从第一行
（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）的人员的个数，这个可以看作是计算
人员的工资水平排名。

不再用ROWNUM 了省事了。这个over简写就会出错。

例子程序6：结合max求到目前行的最大值

SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(ORDER BY FAge) 此行之前最大值
FROM T_Person;

这里的开窗函数“MAX(FSalary) OVER(ORDER BY FAge)”是“MAX(FSalary)
OVER(ORDER BY FAge RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”
的简化写法，它表示按照FSalary进行排序，然后计算从第一行（UNBOUNDED PRECEDING）
到当前行（CURRENT ROW）的人员的最大工资值。

例子程序6：over(partition by XX order by XX) partition by和order by 结合

员工信息+同龄人最高工资，按工资排序

SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(PARTITION BY FAge order by fsalary) 同龄人最高工资
FROM T_Person;

PARTITION BY子句和ORDER BY 可以共同使用，从而可以实现更加复杂的功能

==================================================================================

高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()

除了可以在开窗函数中使用COUNT()、SUM()、MIN()、MAX()、AVG()等这些聚合函数，
还可以在开窗函数中使用一些高级的函数，有些函数同时被DB2和Oracle同时支持，比如
RANK()、DENSE_RANK()、ROW_NUMBER()，而有些函数只被Oracle支持，比如
RATIO_TO_REPORT()、NTILE()、LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。
下面对这几个函数进行详细介绍。

RANK()和DENSE_RANK()函数都可以用于计算一行的排名，不过对于并列排名的处理方式
不同；ROW_NUMBER()函数计算一行在结果集中的行号，同样可以将其当成排名函数。这三个
函数的功能存在一定的差异，举例如下：工资从高到低排名：

SELECT FName, FSalary,FAge,
RANK() OVER(ORDER BY fsalary desc) f_RANK,
DENSE_RANK() OVER(ORDER BY fsalary desc) f_DENSE_RANK,
ROW_NUMBER() OVER(ORDER BY fsalary desc) f_ROW_NUMBER
FROM T_Person;

rank(),dense_rank()语法：

RANK()
dense_rank()
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
 dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当，
【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） 
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。

row_number() 函数语法：

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 
row_number() 返回的主要是“行”的信息，并没有排名
【参数】
【说明】Oracle分析函数

主要功能：用于取前几名，或者最后几名等

===================================================================

排序函数实际场景使用：计算排行榜，排名

微信活动，每天参与，有得分，活动结束后选出排名靠前的发奖。

每参与一次，就是一个订单，表结构：

比如要查询期号issue为20170410期的排行榜，按得分倒叙排序，得分一样按订单创建先后，算排行，sql需要这么写：

select ROWNUM rank, t.*
 from (select *
   from t_zhcw_order
   where issue = '20170410'
   order by integral desc, create_date asc) t

使用了开窗函数后就可以简化：

select t.*,
    row_number() over(order by t.integral desc, t.create_date asc) 排名
   from t_zhcw_order t
   where issue = '20170410'

如果想只要排名范围，可以在外边再包一层，这也是高效分页的一种方式：

select tt.* from (
select t.id,
  t.integral,
  t.cell,
  t.create_date,
  row_number() over(order by t.integral desc, t.create_date asc) rankNum
 from t_zhcw_order t
 where t.issue = 20170331
)tt where tt.rankNum<=50

到此这篇关于Oracle数据库中SQL开窗函数的使用的文章就介绍到这了,更多相关Oracle SQL开窗函数内容请搜索

标签：Oracle

返回前面的内容