oracle 分析函数和开窗函数

时间:2023-11-23 19:01:26

最近遇到一个需求,将查询出的数据按照地区分组,随机取出每个区域的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

什么是分析函数(partition by):

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

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

partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区。

关于开窗函数(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, --按月分组,并统计该月的总和,取第一个

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

得到结果如下:oracle  分析函数和开窗函数

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