oracle 分析函数和开窗函数

有一个需求，将查询出的数据按照地区分组，随机取出每个区域的2条数据，这里用到了oracle的开窗函数：

最终写出的sql如下：

select * from (select region,row_number() over(partition by region order by DBMS_RANDOM.random) rn
from T_PROCURE_REVIEW_EXPERT) where rn < 3

下面说下over(),partition by这些函数的意思：

什么是分析函数（partition by）：

分析函数是Oracle专门用于解决复杂报表统计需求的函数，它可以在数据中进行分组，然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

普通的聚合函数用group by分组，每个分组返回一个统计值，只有一行，而分析函数采用partition by分组，每组中包含多个值。

关于开窗函数(over())：

开窗函数指定了分析函数中的分组的大小。

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，这些就是窗口的规则。他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

注意：窗口子句不能单独出现，必须有order by子句时才能出现。

 

 

聚合函数，分析函数和开窗函数结合使用的例子：

取出每月通话费最高和最低的两个地区：（例子原文：https://blog.csdn.net/haiross/article/details/15336313 来自：CSDN）

select bill_month, area_code,sum(local_fare) local_fare,

　　first_value(area_code) over(partition by bill_month  order by sum(local_fare) desc

　　　　rows between unbounded preceding and unbounded following) firstval, --按月分组，并统计该月的总和，取第一个 rows后面这一行代表查找的范围。

　　last_value(area_code) over(partition by bill_month  order by sum(local_fare) desc

　　　　rows between unbounded preceding and unbounded following) lastval

from t

group by bill_month, area_code

order by bill_month

 

注：first_value()和last_value()：在分析函数中使用，取首尾记录值

注：unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录

（unbounded：不受控制的，无限的 preceding：在...之前 following：在...之后）

 

这里自己做了些数据，在数据库中测试一下：

分析：先按照 bill_month和area_code分组：

select bill_month, 
　　area_code,
　　sum(local_fare) local_fare 
from t 
group by bill_month, area_code 
order by bill_month

得到结果如下：

然后用分析函数得到最大和最小值

 

转载于:https://www.cnblogs.com/xjx199403/p/10672646.html