oracle分析函数

说明
FIRST,LAST不应该算 做函数，它主要与聚合 函数联合使用 强
使用频率
聚合分析函数 行比较分析函数 统计分析函数 行连接分析函数 其他分析函数
强 解决当前行与前后行之 间的关系 解决报表占比问题 11g新特性 中 中 中 弱
• 分析子句
• PARTITION子句
– 按照表达式分区(就是分组)，将相同的行聚合 到一起成为一组。 – 如果省略了分区子句,则全部的结果集被看作是 一个单一的组
• COUNT(expr) 返回expr值非空的行的数.
SQL> SELECT 2 FROM 3 WHERE COUNT(comm) emp deptno = 30;
• 注意：
– 分组函数忽略了行中所有的空行 – NVL函数使得分组函数可以处理空值.
SQL> SELECT AVG(NVL(comm,0)) 2 FROM emp; AVG(NVL(COMM,0)) ---------------157.14286
• 分组函数的嵌套使用
SQL> SELECT max(avg(sal)) 2 FROM emp 3 GROUP BY deptno;
SELECT owner,object_type FROM demo2 WHERE brbh='xxx' AND created = (SELECT MAX(created) FROM demo2 WHERE brbh='xxx')
• grouping
– 作用：
• 用rollup和cube函数统计的结果中，被统计字段会 产生NULL，用grouping函数来确定，该记录是由 哪个字段得出来的 grouping(字段名)，如果是用该字段统计的返回1， 反之返回0 该列为空时是1，不为空时是0
– 语法：
• •
• 范例
select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份, decode(grouping(area),1,'所有地区',area) 地区, sum(personincome) 总金额 from earnings group by rollup(earnmonth,area)
SQL> 2 3 4 SQL> 2 3 4 SELECT FROM WHERE GROUP BY SELECT FROM GROUP BY HAVING deptno, AVG(sal) emp AVG(sal) > 2000 deptno; deptno, max(sal) emp deptno max(sal)>2900;
来自百度文库 WINDOWING子句
– 用于定义分析函数将在其上操作的行的集合 – 默认的窗口是一个固定的窗口,仅仅在一组的第 一行开始,一直继续到当前行 – 要使用窗口,必须使用ORDER BY子句 – window可以指定是逻辑窗口还是物理窗口
• 数据值的范围(range between) • 与当前行的行偏移量(rows between)
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) -------- --------- --------- --------1400 1600 1250 5600
• COUNT(*) 返回查询出的总行数.
SQL> SELECT 2 FROM 3 WHERE COUNT(*) emp deptno = 30;
• WINDOWING子句
– 有的分析函数不能有显式window,如 row_number,rank,dense_rank等
• range是逻辑窗口
– 数值范围
• 指定当前行对应值的范围取值 • 只要行值在范围内，对应列都包含在内
– 日期范围
• 指定时间范围进行统计 • interval '1' day/month/year
因为salary desc,所以100 preceding含义是比当前行的salary大100，以 employee_id=117为例，也就是起点是2800+100=2900,终点是比当前行小500， 2800-500=2300。那么employee_id=117行分析函数计算的窗口范围是 employee_id between 116 and 119。
• 列分组函数
– group by – rollup – cube – grouping sets – grouping
• group by
– 按月份，统计每个地区的总收入 – 范例 select earnmonth,area,sum(personincome) from earnings group by earnmonth,area
• grouping_id
• 为什么需要分析函数？
– 直接在SQL中做以下操作是比较难的：
• 计算运行行的总数：逐行的显示一个部门的累计工 资。每行包括前面各行工资的总和。 • 查找一组内的百分数：显示在某些部门中付给个人 的总工资的百分数。将他们的工资与该部门的工资 总和相除 • 查询前/最后N个
分析函数
• 语法结构：
– 分析函数(参数,参数...) OVER (<PARTITION BY子句> <ORDER BY子句> <WINDOWING 子句>)
• 分析函数 • 分析子句
– PARTITION子句 – ORDER BY子句 – WINDOWING子句
• 分析函数
– 按照各种分析函数使用频率以及业务特点：
• rollup
– rollup(a,b,c)的group by顺序
• [a,b,c],[a,b],[a] • 全表group by
– 等价于（）
• group by a,b,c • union all group by a,b • union all group by a
– 对于N个参数，有N+1次的分组
– 对于N个参数，有2^N次的分组
• 范例:按月份，地区统计收入
select earnmonth,area,sum(personincome) from earnings group by cube(earnmonth,area)
• grouping sets
– 对参数中的每个参数做分组
• group by grouping sets(a,b,c)，则对(a)(b)(c)进行 group by • group by grouping sets((a,b),c)，则对(a,b)(c)进行 group by
• 例如：order by times range between 1 preceing and 3 following
– 当times=8时，是sum为8-1<=times<=8+3 的和，即 sum=7+7+7+8+11+11+11=5(取times为7,8,11); – 当times=4时，是sum为4-1<=times<=4+3的和，即 sum=4+6+7+7+7（取times为4,6,7）； – 当times=21时，是sum为21-1<=times<=21+3 的和， 即sum=21（取times为21）;
• ORDER BY子句
– 没有ORDER BY时,默认的窗口是全部的分区 – 分析函数中ORDER BY的存在将添加一个默认 的开窗子句：RANGE BETWEEN unbounded preceding AND CURRENT ROW，即第1行到 当前行（根据order by顺序指定） – order by默认是range窗口，对应逻辑窗口 – 在Order by 子句后可以添加nulls last/first，指 定NULLS是出现在开始还是最后。
– Oracle 从 8.1.6 开始提供分析函数
• 按区域，每个员工工资总额占区域内工资 总额20%以上的员工，以及他们在各个区 域内的排名
– select ... group by area; – select ... group by area,sname; – 两表关联 – 排名？
• select area,sname,per / ptm ratio from • ( • select area,sname, sum(personincome) per,sum(sum(personincome)) over(partition by area) ptm from earnings group by area,sname • ) where per / ptm > 0.2;
• 使用 GROUP BY子句
– 在group by后面使用多个字段
SQL> SELECT deptno, job, sum(sal) 2 FROM emp 3 GROUP BY deptno, job;
• 注意：
– 不能在 WHERE子句中对列做出限定. – 使用 HAVING 子句来限定分组.
oralce分析函数
• 表名：earnings • 字段名：
– area：区域 – earnmonth：月份 – sname：员工姓名 – personincome：月收入 – times：演示其它功能
• 聚集（分组）函数
– 记住常用的分组函数 – 使用GROUP BY 子句对元组进行分组 – 使用HAVING 子句来筛选分组
• 范例：按月份，地区统计收入
select earnmonth,area,sum(personincome) from earnings group by rollup(earnmonth,area)
• cube
– cube(a,b,c)的group by顺序
• [a,b,c],[a,b],[a,c],[a],[b,c],[b],[c] • 全表group by
• 注意事项
– 不管排序键是否是唯一，都能保证诸如聚合分 析函数值的唯一 – 反应的是行与行之间的逻辑关系（当前行的排 序键的value加或减逻辑偏移量得到当前行对应 的逻辑窗口的范围） – 升序range窗口preceding含义是比当前行小xx 值，following含义是比当前行大xx值，降序 range窗口preceding含义是比当前行大xx值， following含义是比当前行小xx值。
• • • • • • 聚合分析函数 排名分析函数 行比较分析函数 统计分析函数 行连接分析函数 其他分析函数
分类
排名分析函数
内容
ROW_NUMBER、RANK、 DENSE_RANK、FIRST、 LAST、 LAST_VALUE,FIRST_VALUE AVG、COUNT、 MAX,MIN,SUM LEAD、LAG RATIO_TO_REPORT LISTAGG CORR、CORVAR_POP、 CORVAR_SAMP、 CUME_DIST、NTH_VALUE、 NTILE、PERCENT_RANK、 PERCENTILE_CONT、 PERCENTILE_DISC、REGR_ (Linear Regression) Functions、 STDDEV、STDDEV_POP、 STDDEV_SAMP 、VAR_POP,VAR_SAMP、 VARIANCE
select area,sname,ratio ( select area,sname, ratio_to_report(sum(personincome)) over(partition by area) ratio from earnings group by area,sname ) where ratio > 0.2;
• 常见分组函数
– AVG – COUNT – MAX – MIN – SUM – STDDEV – VARIANCE
• AVG、MAX、MIN、SUM • 一般我们用于数值型和日期类型取值
SQL> SELECT 2 3 FROM 4 WHERE AVG(sal), MAX(sal), MIN(sal), SUM(sal) emp job LIKE 'SALES%';
• 使用 GROUP BY子句
– 在SELECT语句中，没有使用分组函数的列必 须在GROUP By子句中.
SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno;
– GROUP BY后面的列可以不出现在 SELECT链 中.
SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno;