oracle分析函数

原创

wx6323d657ef2b3 2022-09-16 10:00:41 博主文章分类：Oracle日常管理 ©著作权

©著作权归作者所有：来自51CTO博客作者wx6323d657ef2b3的原创作品，请联系作者获取转载授权，否则将追究法律责任

oracle分析函数--SQL*PLUS环境
--1、GROUP BY子句
--创建测试表并插入测试数据.
创建表学生
（ID号（15，0），
面积瓦查尔2（10），
stu_type瓦查尔2（2），
分数号（20，2））;

插入到学生值（1， '111'， 'g'， 80 ）;
插入到学生值（1， '111'， 'j'， 80 ）;
插入到学生值（1， '222'， 'g'， 89 ）;
插入到学生值（1， '222'， 'g'， 68 ）;
插入到学生值（2， '111'， 'g'， 80 ）;
插入到学生值（2， '111'， 'j'， 70 ）;
插入到学生值（2， '222'， 'g'， 60 ）;
插入到学生值（2， '222'， 'j'， 65 ）;
插入到学生值（3， '111'， 'g'， 75 ）;
插入到学生值（3， '111'， 'j'， 58 ）;
插入到学生值（3， '222'， 'g'， 58 ）;
插入到学生值（3， '222'， 'j'， 90 ）;
插入到学生值（4， '111'， 'g'， 89 ）;
插入到学生值（4， '111'， 'j'， 90 ）;
插入到学生值（4， '222'， 'g'， 90 ）;
插入到学生值（4， '222'， 'j'， 89 ）;
提交;

col 分数格式 9999999999999.99
--A、分组集
按 id、面积、stu_type（id、面积、stu_type）、（id、面积、id）的顺序选择学生
组的 id、面积、stu_type、总分（分数
）
分数;
/*--------理解分组集
从 t
组中选择 a、 b， c，求和（ d ）按分组集（ a， b， c ）
等效于
从（
选择 a，空，空，求和（ d ）从 t 组通过并
集全部
选择空， b，空，求和（ d ）从 t 组通过 b
并集全部
选择空，空， c，求和（ d ）从 t 组通过 c
）
*/
--B、ROLLUP
选择id，面积，stu_type，总和（分数）分数
，按id，面积，stu_type的stu_type）
顺序从学生
组中收集;
/*--------理解卷轴
通过汇总（a，b，c）
从t
组中选择a，b，c，和（ d）;
等效于
从中选择 * 从（
选择 a， b， c，和（ d ）从 t 组由 a， b， c
并集全部
选择 a， b，空，和（ d ）从 t 组由 a， b
并集全部
选择 a，空，空，求和（ d ）从 t 组通过一个
并集全部
选择空，空，空，和（ d ）从 t
）
*/
--C、CUBE
选择 ID，面积，stu_type，按id，面积，stu_type）顺序按id，面积，stu_type从学生
组的总和（分数）

分数;
/*--------理解立方体
从 t 组选择 a， b， c， sum（ d ）从 t
组通过立方体（ a， b， c， c）
等效于
选择 a， b， c， sum（ d ）从 t
组通过分组集（
（ a， b， c ），
（ a， b ），（ a， b
），（ a， c ），（ c ），（ c ），
（））
*/

--D、GROUPING
/*从上面的结果中我们很容易发现，每个统计数据所对应的行都会出现null，
如何来区分到底是根据那个字段做的汇总呢，分组函数判断是否合计列！*/
选择解码（分组（id），1，“所有 id”，id） id，
解码（分组（区域），1，“所有区域”，to_char（区域））区域，
解码（分组（stu_type），1，“all_stu_type”，stu_type） stu_type，
学生
组的和（分数）分数
按立方体（id，面积，stu_type）
按id，面积，stu_type排序;
--2、OVER（）函数的使用
--1、RANK（）、DENSE_RANK（）的、ROW_NUMBER的、CUME_DIST的、最大（）、平均（）
的中断在 id 上跳过 1
选择 id，面积，分数从学生中按 id 排序，面积，分数描述;
选择 id，排名（）超过（按 id 分区按分数顺序按分数描述） rk，分数来自学生;
--允许并列名次、名次不间断
选择 id，dense_rank（） over（分区按 id 排序按分数 desc） rk，分数来自学生;
--即使SCORE相同，ROW_NUMBER（）结果也是不同
选择 id，row_number（） over（按 ID 分区按分数顺序按分数描述） rn，分数来自学生;
选择cume_dist（）以上（按 id 排序） a， --该组最大row_number/所有记录row_number
row_number（）超过（按 id 排序） rn，id，面积，分数来自学生;
选择 id，最大（分数）超过（按 id 分区按分数顺序按分数 desc）作为 mx，分数来自学生;
选择id，面积，平均（分数）超过（按id按区域顺序划分）作为平均值，来自学生的分数;

--注意有无order by的区别
--按照ID求AVG
select id,avg(score) over(partition by id order by score desc rows between unbounded preceding
and unbounded following ) as ag,score from students;
--2、SUM()
select id,area,score from students order by id,area,score desc;
select id,area,score,
sum(score) over (order by id,area) 连续求和, --按照OVER后边内容汇总求和
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;
select id,area,score,
sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和
sum(score) over (partition by id) id总和, --各id的分数总和
100*round(score/sum(score) over (partition by id),4) "id份额(%)",
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/ sum(score) over (),4) "份额(%)"
from students;
--4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据
select id,lag(score,1,0) over(order by id) lg,score from students;
select id,lead(score,1,0) over(order by id) lg,score from students;
--5、FIRST_VALUE()、LAST_VALUE()
select id,first_value(score) over(order by id) fv,score from students;
select id,last_value(score) over(order by id) fv,score from students;
/*而对于last_value() over(order by id),结果是有问题的，因为我们没有按照id分区，所以应该出来的效果应该全部是90（最后一条）。
再看个例子就明白了：
select id,last_value(score) over(order by rownum),score from students;
ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE
---------------- ------------------------------ ----------------------
1 80 80.00
1 80 80.00
1 89 89.00
1 68 68.00
2 80 80.00
2 70 70.00
2 60 60.00
2 65 65.00
3 75 75.00
3 58 58.00
3 58 58.00
3 90 90.00
4 89 89.00
4 90 90.00
4 90 90.00
4 89 89.00
16 rows selected
当使用last_value分析函数的时候，缺省的WINDOWING范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，在进行比较的时候从当前行向前进行比较，所以会出现上边的结果。加上如下的参数，结果就正常了。呵呵。默认窗口范围为所有处理结果。 */
select id,last_value(score) over(order by rownum RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),score from students;
/*
ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE
---------------- ------------------------------ ----------------------
1 89 80.00
1 89 80.00
1 89 89.00
1 89 68.00
2 89 80.00
2 89 70.00
2 89 60.00
2 89 65.00
3 89 75.00
3 89 58.00
3 89 58.00
3 89 90.00
4 89 89.00
4 89 90.00
4 89 90.00
4 89 89.00
16 rows selected
*/
--给出一个例子再次理解分析函数
/*********************************************************************************************

http://www.itpub.net/620932.html问题提出：
一个高级SQL语句问题
假设有一张表，A和B字段都是NUMBER，
A B
1 2
2 3
3 4
4
有这样一些数据
现在想用一条SQL语句，查询出这样的数据
1－》2－》3—》4
就是说，A和B的数据表示一种连接的关系，现在想通过A的一个值，去查询A所对应的B值，直到B为NULL为止，
不知道这个SQL语句怎么写？请教高手！谢谢
*********************************************************************************************/

参考至：http://zhouwf0726.itpub.net/post/9689/158090

如有错误，欢迎指正