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

什么是分析函数（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

得到结果如下：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!