Oracle开发专题之：分析函数(OVER) (1)
Oracle开发专题之：分析函数2(Rank, Dense_rank, row_number) (6)
Oracle开发专题之：分析函数3(Top/Bottom N、First/Last、NTile) (10)
Oracle开发专题之：窗口函数 (14)
Oracle开发专题之：报表函数 (20)
Oracle开发专题之：分析函数总结 (22)
Oracle开发专题之：26个分析函数 (24)
PLSQL开发笔记和小结 (28)
分析函数简述 (60)
说明: 1)Oracle开发专题99%收集自: https://www.doczj.com/doc/677784308.html,/pengpenglin/(偶补充了一点点1%);
2) PLSQL开发笔记和小结收集自https://www.doczj.com/doc/677784308.html,/cheneyfree/
3)分析函数简述收集自https://www.doczj.com/doc/677784308.html,/7607759/
昆明小虫https://www.doczj.com/doc/677784308.html,/ 收集,并补充了一点点1%
Oracle开发专题之：分析函数(OVER)
目录：
===============================================
1.Oracle分析函数简介
2. Oracle分析函数简单实例
3.分析函数OVER解析
一、Oracle分析函数简介：
在日常的生产环境中，我们接触得比较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者至少说对响应的时间多长有一定的要求；其次这些系统的业务逻辑一般比较复杂，可能需要经过多次的运算。比如我们经常接触到的电子商城。
在这些系统之外，还有一种称之为OLAP的系统(即Online Aanalyse Process)，这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者根本不关注这方面的要求，以查询、统计操作为主。
我们来看看下面的几个典型例子：
①查找上一年度各个销售区域排名前10的员工
②按区域查找上一年度订单总额占区域订单总额20%以上的客户
③查找上一年度销售最差的部门所在的区域
④查找上一年度销售最好和最差的产品
我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同，具体有：
①需要对同样的数据进行不同级别的聚合操作
②需要在表内将多条数据和同一条数据进行多次的比较
③需要在排序完的结果集上进行额外的过滤操作
分析函数语法:
FUNCTION_NAME(
OVER
(
例:
sum(sal) over (partition by deptno order by ename) new_alias
sum就是函数名
(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)
over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数
partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区
order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的.
1)FUNC TION子句
ORACLE提供了26个分析函数,按功能分5类
分析函数分类
等级(ranking)函数:用于寻找前N种查询
开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上
例:
sum(t.sal) over (order by t.deptno,t.enam e) running_total,
sum(t.sal) over (partition by t.deptno order by t.enam e) depart m ent_total
制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列
例:
sum(t.sal) over () running_total2,
sum(t.sal) over (partition by t.deptno ) depart m ent_total2
制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句!
LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的.
VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值
2)PARTITION子句
按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组
3)ORDER BY子句
分析函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDER BY时,默认的窗口是全部的分区在Order by 子句后可以添加nulls last,如:order by comm desc nulls last 表示排序时忽略comm列为空的行.
4)WINDOWING子句
用于定义分析函数将在其上操作的行的集合
Windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行操作
默认的窗口是一个固定的窗口,仅仅在一组的第一行开始,一直继续到当前行,要使用窗口,必须使用ORDER BY子句
根据2个标准可以建立窗口:数据值的范围(RANGES)或与当前行的行偏移量.
5)Rang窗口
Range 5 preceding:将产生一个滑动窗口,他在组中拥有当前行以前5行的集合
ANGE窗口仅对NUMBERS和DATES起作用,因为不可能从VARCHAR2中增加或减去N个单元
另外的限制是ORDER BY中只能有一列,因而范围实际上是一维的,不能在N维空间中
例:
avg(t.sal) over(order by t.hiredate asc range 100 preceding) 统计前100天平均工资
6)Row窗口
利用ROW分区,就没有RANGE分区那样的限制了,数据可以是任何类型,且ORDER BY 可以包括很多列
7)Specifying窗口
UNBOUNDED PRECEDING:这个窗口从当前分区的每一行开始,并结束于正在处理的当前行
CURRENT ROW:该窗口从当前行开始(并结束)
Numeric Expression PRECEDING:对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始,对RANGE来说,从从行序值小于数字表达式的当前行的值开始.
Numeric Expression FOLLOWING:该窗口在当前行Numeric Expression行之后的行终止(或开始),且从行序值大于当前行Numeric Expression行的范围开始(或终止)
range between 100 preceding and 100 following:当前行100前,当前后100后
注意:分析函数允许你对一个数据集进排序和筛选,这是SQL从来不能实现的.除了最后的Order by子句之外,分析函数是在查询中执行的最后的操作集,这样的话,就不能直接在谓词中使用分析函数,即不能在上面使用where或having子句!!!
二、Oracle分析函数简单实例：
下面我们通过一个实际的例子：按区域查找上一年度订单总额占区域订单总额20%以上的客户，来看看分析函数的应用。【1】测试环境：
SQL>desc orders_tmp;
Name Null? Type
----------------------- -------- ----------------
CUST_NBR NOT NULL NUMBER(5)
REGION_ID NOT NULL NUMBER(5)
SALESPERSON_ID NOT NULL NUMBER(5)
YEAR NOT NULL NUMBER(4)
MONTH NOT NULL NUMBER(2)
TOT_ORDERS NOT NULL NUMBER(7)
TOT_SALES NOT NULL NUMBER(11,2)
【2】测试数据：
SQL>select*from orders_tmp;
CUST_NBR REGION_ID SALES PERS ON_ID YEAR MONTH TOT_OR DERS TOT_SALES
---------- ---------- -------------- ---------- ---------- ---------- ----------
1171120017212204
454200110237802
7672001233750
106820011221691
106720012342624
1571220005624
127920006250658
15220003244494
15120009274864
25420003235060
2542000446454
251200010435580
454200012239190
13 row s selected.
【3】测试语句：
SQL>select o.cust_nbr customer,
2 o.region_id region,
3s um(o.tot_sales) cust_sales,
4s um(sum(o.tot_sales)) over(partition by o.region_id) region_sales
5from orders_tmp o
6where o.year=2001
7group by o.region_id, o.cust_nbr;
CUSTOMER REGION CUST_S ALES REGION_SALES
---------- ---------- ---------- ------------
453780237802
76375068065
1066431568065
1171220412204
三、分析函数OVER解析：
请注意上面的绿色高亮部分，group by的意图很明显：将数据按区域ID，客户进行分组，那么Over这一部分有什么用呢？假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要group by o.region_id,o.cust_nbr就够了。但我们还想在每一行显示该客户所在区域的订单总额，这一点和前面的不同：需要在前面分组的基础上按区域累加。很显然group by和sum是无法做到这一点的(因为聚集操作的级别不一样，前者是对一个客户，后者是对一批客户)。
这就是over函数的作用了！它的作用是告诉SQL引擎：按区域对数据进行分区，然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。
现在我们已经知道2001年度每个客户及其对应区域的订单总额，那么下面就是筛选那些个人订单总额占到区域订单总额20%以上的大客户了
SQL>select*
2from (select o.c ust_nbr customer,
3 o.region_id region,
4s um(o.tot_sales) cust_sales,
5s um(sum(o.tot_sales)) over(partition by o.region_id) region_sales
6from orders_tmp o
7where o.year=2001
8group by o.region_id, o.cust_nbr) all_sales
9where all_sales.cust_sales>all_sales.region_sales *0.2;
CUSTOMER REGION CUST_S ALES REGION_SALES
---------- ---------- ---------- ------------
453780237802
1066431568065
1171220412204
SQL>
现在我们已经知道这些大客户是谁了！哦，不过这还不够，如果我们想要知道每个大客户所占的订单比例呢？看看下面的SQL语句，只需要一个简单的Round函数就搞定了。
SQL>select all_sales.*,
2100*round(cust_sales / region_sales, 2) ||'%'Percent
3from (select o.c ust_nbr customer,
4 o.region_id region,
5s um(o.tot_sales) cust_sales,
6s um(sum(o.tot_sales)) over(partition by o.region_id) region_sales
7from orders_tmp o
8where o.year=2001
9group by o.region_id, o.cust_nbr) all_sales
10where all_sales.cust_sales>all_sales.region_sales *0.2;
CUSTOMER REGION CUST_S ALES REGION_SALES PER CENT
---------- ---------- ---------- ------------ ----------------------------------------
453780237802100%
106643156806594%
1171220412204100%
SQL>
总结：
①Over函数指明在那些字段上做分析，其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。
②Over函数可以和其它聚集函数、分析函数搭配，起到不同的作用。例如这里的SUM，还有诸如Rank，Dense_rank
等。
Oracle开发专题之：分析函数2(Rank, Dense_rank, row_number)
目录
===============================================
1.使用rownum为记录排名
2.使用分析函数来为记录排名
3.使用分析函数为记录进行分组排名
一、使用rownum为记录排名：
在前面一篇《Oracle开发专题之：分析函数》，我们认识了分析函数的基本应用，现在我们再来考虑下面几个问题：
①对所有客户按订单总额进行排名
②按区域和客户订单总额进行排名
③找出订单总额排名前13位的客户
④找出订单总额最高、最低的客户
⑤找出订单总额排名前25%的客户
按照前面第一篇文章的思路，我们只能做到对各个分组的数据进行统计，如果需要排名的话那么只需要简单地加上rownum 不就行了吗？事实情况是否如此想象般简单，我们来实践一下。
【1】测试环境：
SQL>desc user_order;
Name Null? Type
----------------------------------------- -------- ----------------------------
REGION_ID NUMBER(2)
CUSTOMER_ID NUMBER(2)
CUSTOMER_SALES NUMBER
【2】测试数据：
SQL>select*from user_order order by custom er_sales;
REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
51151162
1029903383
67971585
1028986964
9211020541
9221036146
8161068467
681141638
531161286
551169926
8191174421
7121182275
7111190421
6101196748
691208959
10301216858
5 2 1224992
9 24 1224992
9 23 1224992
8181253840
7151255591
7131310434
10271322747
8201413722
661788836
10261808949
541878275
7141929774
8171944281
9252232703
30 rows selected.
注意这里有3条记录的订单总额是一样的。假如我们现在需要筛选排名前12位的客户，如果使用rownu m会有什么样的后果呢？
SQL>select rownum, t.*
2from (select*
3from user_order
4order by custom er_sales desc) t
5where rownum <=12
6order by custom er_sales desc;
ROWNUM REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ---------- ----------- --------------
19252232703
28171944281
37141929774
4541878275
510261808949
6661788836
78201413722
810271322747
97131310434
107151255591
118181253840
12 5 2 1224992
12 rows selected.
很明显假如只是简单地按rownum进行排序的话，我们漏掉了另外两条记录(参考上面的结果)。
二、使用分析函数来为记录排名：
针对上面的情况，Oracle从8i开始就提供了3个分析函数：rand，dense_rank，row_number来解决诸如此类的问题，下面我们来看看这3个分析函数的作用以及彼此之间的区别：
Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①ROW_NUMBER：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
②DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
③RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
这样的介绍有点难懂，我们还是通过实例来说明吧，下面的例子演示了3个不同函数在遇到相同数据时不同排名策略：
SQL>select region_id, custom er_id, sum(custom er_sales) total,
2 rank() over(order by sum(custom er_sales) desc) rank,
3 dense_rank() over(order by sum(custom er_sales) desc) dense_rank,
4 row_number() over(order by sum(custom er_sales) desc) row_number
5from user_order
6group by region_id, custom er_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
8181253840111111
521224992121212
9231224992121213
10301216858151315
30 rows selected.
请注意上面的绿色高亮部分，这里生动的演示了3种不同的排名策略：
①对于第一条相同的记录，3种函数的排名都是一样的：12
②当出现第二条相同的记录时，Rank和Dense_rank依然给出同样的排名12；而row_number则顺延递增为13，依次类推至第三条相同的记录
③当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增
比较上面3种不同的策略，我们在选择的时候就要根据客户的需求来定夺了：
①假如客户就只需要指定数目的记录，那么采用row_number是最简单的，但有漏掉的记录的危险
②假如客户需要所有达到排名水平的记录，那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要，选择dense_rank或得到最大的记录
三、使用分析函数为记录进行分组排名：
上面的排名是按订单总额来进行排列的，现在跟进一步：假如是为各个地区的订单总额进行排名呢？这意味着又多了一次分组操作：对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持，我们所要做的仅仅是在over函数中order by 的前面增加一个分组子句：partition by region_id。
SQL>select region_id, custom er_id,
sum(custom er_sales) total,
2 rank() over(partition by region_id
order by sum(custom er_sales) desc) rank,
3 dense_rank() over(partition by region_id
order by sum(custom er_sales) desc) dense_rank,
4 row_number() over(partition by region_id
order by sum(custom er_sales) desc) row_number
5from user_order
6group by region_id, custom er_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
541878275111
521224992222
551169926333
691208959222
6101196748333
30 rows selected.
现在我们看到的排名将是基于各个地区的，而非所有区域的了！Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分，这样排列函数就能够应用于这各个子集。
前面我们提到的5个问题已经解决了2个了(第1,2)，剩下的3个问题(Top/Bottom N，First/Last, NTile)会在下一篇讲解。
Oracle开发专题之：分析函数3(T op/Bottom N、First/Last、NTile)
目录
===============================================
1.带空值的排列
2.Top/Bottom N查询
3.First/Last排名查询
4.按层次查询
一、带空值的排列：
在前面《Oracle开发专题之：分析函数2(Rank、Dense_rank、row_number)》一文中，我们已经知道了如何为一批记录进行全排列、分组排列。假如被排列的数据中含有空值呢？
SQL>select region_id, custom er_id,
2sum(custom er_sales) cust_sales,
3sum(sum(custom er_sales)) over(partition by region_id) ran_total,
4 rank() over(partition by region_id
5order by sum(custom er_sales) desc) rank
6from user_order
7group by region_id, custom er_id;
REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL RANK
---------- ----------- ---------- ---------- ----------
103162389011
1026180894962389012
1027132274762389013
1030121685862389014
102898696462389015
102990338362389016
我们看到这里有一条记录的CUST_TOTAL字段值为NULL，但居然排在第一名了！显然这不符合情理。所以我们重新调整完善一下我们的排名策略，看看下面的语句：
SQL>select region_id, custom er_id,
2sum(custom er_sales) cust_total,
3sum(sum(custom er_sales)) over(partition by region_id) reg_total,
4 rank() over(partition by region_id
order by sum(custom er_sales) desc NULLS LAST) rank
5from user_order
6group by region_id, custom er_id;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
---------- ----------- ---------- ---------- ----------
1026180894962389011
1027132274762389012
1030121685862389013
102898696462389014
102990338362389015
103162389016
绿色高亮处，NULLS LAST/FIRST告诉Oracle让空值排名最后后第一。
注意是NULLS，不是NULL。
二、Top/Bottom N查询：
在日常的工作生产中，我们经常碰到这样的查询：找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示：
【1】找出所有订单总额排名前3的大客户：
SQL>select*
SQL>from (select region_id,
SQL> custom er_id,
SQL>sum(custom er_sales) cust_total,
SQL> rank() over(order by sum(custom er_sales) desc NULLS LAST) rank
SQL>from user_order
SQL>group by region_id, custom er_id)
SQL>where rank <=3;
REGION_ID CUSTOMER_ID CUST_TOTAL RANK
---------- ----------- ---------- ----------
92522327031
81719442812
71419297743
SQL>
【2】找出每个区域订单总额排名前3的大客户：
SQL>select*
2from (select region_id,
3 custom er_id,
4sum(custom er_sales) cust_total,
5sum(sum(custom er_sales)) over(partition by region_id) reg_total,
6 rank() over(partition by region_id
order by sum(custom er_sales) desc NULLS LAST) rank
7from user_order
8group by region_id, custom er_id)
9where rank <=3;
REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK
---------- ----------- ---------- ---------- ----------
54187827555856411
52122499255856412
55116992655856413
66178883663077661
69120895963077662
610119674863077663
714192977468684951
713131043468684952
715125559168684953
817194428168547311
820141372268547312
818125384068547313
925223270367393741
923122499267393742
924122499267393742
1026180894962389011
1027132274762389012
1030121685862389013
18 rows selected.
三、First/Last排名查询：
想象一下下面的情形：找出订单总额最多、最少的客户。按照前面我们学到的知识，这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名，第二个查询按照订单总额升序排列以期拿到最后一名。是不是很烦？因为Rank函数只告诉我们排名的结果，却无法自动替我们从中筛选结果。
幸好Oracle为我们在排列函数之外提供了两个额外的函数：first、last函数，专门用来解决这种问题。还是用实例说话：
SQL>select min(custom er_id)
2 keep (dense_rank first order by sum(custom er_sales) desc) first,
3min(custom er_id)
4 keep (dense_rank last order by sum(custom er_sales) desc) last
5from user_order
6group by custom er_id;
FIRST LAST
---------- ----------
311
这里有几个看起来比较疑惑的地方：
①为什么这里要用min函数
②Keep这个东西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同，能换成rank吗？
首先解答一下第一个问题：min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢？
SQL>select keep (dense_rank first order by sum(custom er_sales) desc) first,
2keep (dense_rank last order by sum(custom er_sales) desc) last
3from user_order
4group by custom er_id;
select keep (dense_rank first order by sum(custom er_sales) desc) first,
*
ERROR at line 1:
ORA-00907: missing right parenthesis
接下来看看第2个问题：keep是干什么用的？从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据，这就是keep的作用。告诉Oracle只保留符合keep条件的记录。
那么什么才是符合条件的记录呢？这就是第3个问题了。dense_rank是告诉Oracle排列的策略，first/last则告诉最终筛选的条件。
第4个问题：如果我们把dense_rank换成rank呢？
SQL>select min(region_id)
2 keep(rank first order by sum(custom er_sales) desc) first,
3min(region_id)
4 keep(rank last order by sum(custom er_sales) desc) last
5from user_order
6group by region_id;
select min(region_id)
*
ERROR at line 1:
ORA-02000: missing DENSE_RANK
四、按层次查询：
现在我们已经见识了如何通过Oracle的分析函数来获取Top/Bottom N，第一个，最后一个记录。有时我们会收到类似下面这样的需求：找出订单总额排名前1/5的客户。
很熟悉是不？我们马上会想到第二点中提到的方法，可是rank函数只为我们做好了排名，并不知道每个排名在总排名中的相对位置，这时候就引入了另外一个分析函数NTile，下面我们就以上面的需求为例来讲解一下：
SQL>select region_id,
2 custom er_id,
3 ntile(5) over(order by sum(custom er_sales) desc) til
4from user_order
5group by region_id, custom er_id;
REGION_ID CUSTOMER_ID TILE
---------- ----------- ----------
10311
9251
10261
661
8182
522
9233
693
7113
534
684
8164
675
10295
515
Ntil函数为各个记录在记录集中的排名计算比例，我们看到所有的记录被分成5个等级，那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。
Oracle开发专题之：窗口函数
1.窗口函数简介
2.窗口函数示例-全统计
3.窗口函数进阶-滚动统计(累积/均值)
4.窗口函数进阶-根据时间范围统计
5.窗口函数进阶-first_value/last_value
6.窗口函数进阶-比较相邻记录
一、窗口函数简介：
到目前为止，我们所学习的分析函数在计算/统计一段时间内的数据时特别有用，但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢？举些例子来说：
①列出每月的订单总额以及全年的订单总额
②列出每月的订单总额以及截至到当前月的订单总额
③列出上个月、当月、下一月的订单总额以及全年的订单总额
④列出每天的营业额及一周来的总营业额
⑤列出每天的营业额及一周来每天的平均营业额
仔细回顾一下前面我们介绍到的分析函数，我们会发现这些需求和前面有一些不同：前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集，而这里有部分需求例如2，需要随着遍历记录集的每一条记录的同时进行统计。
也即是说：统计不止发生一次，而是发生多次。统计不至发生在记录集形成后，而是发生在记录集形成的过程中。
这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合：
①通过指定一批记录：例如从当前记录开始直至某个部分的最后一条记录结束
②通过指定一个时间间隔：例如在交易日之前的前30天
③通过指定一个范围值：例如所有占到当前交易量总额5%的记录
二、窗口函数示例－全统计：
下面我们以需求：列出每月的订单总额以及全年的订单总额为例，来看看窗口函数的应用。
【1】测试环境：
SQL>desc orders;
名称是否为空? 类型
----------------------- -------- ----------------
MONTH NUMBER(2)
TOT_SALES NUMBER
SQL>
【2】测试数据：
SQL>select*from orders;
MONTH TOT_SALES
---------- ----------
1610697
2428676
3637031
4541146
5592935
6501485
7606914
8460520
9392898
10510117
11532889
12492458
已选择12行。
【3】测试语句：
回忆一下前面《Oracle开发专题之：分析函数(OVER)》一文中，我们使用了sum(sum(tot_sales)) over (partition by region_id) 来统计每个分区的订单总额。现在我们要统计的不单是每个分区，而是所有分区，partition by region_id在这里不起作用了。
Oracle为这种情况提供了一个子句：rows between ... preceding and ... following。从字面上猜测它的意思是：在XXX 之前和XXX之后的所有记录，实际情况如何让我们通过示例来验证：
SQL>select m onth,
2sum(tot_sales) month_sales,
3sum(sum(tot_sales)) over (order by month
4 rows between unbounded preceding and unbounded following) total_sales
5from orders
6group by m onth;
MONTH MONTH_SALES TOTAL_SALES
---------- ----------- -----------
16106976307766
24286766307766
36370316307766
45411466307766
55929356307766
65014856307766
76069146307766
84605206307766
93928986307766
105101176307766
115328896307766
124924586307766
已选择12行。
绿色高亮处的代码在这里发挥了关键作用，它告诉oracle统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次，这时相当费时的！但至少我们解决了问题。
unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢？看看下面的结果：
SQL>select m onth,
2sum(tot_sales) month_sales,
3sum(sum(tot_sales)) over (order by month
4 rows between1 preceding and unbounded following) all_sales
5from orders
6group by m onth;
MONTH MONTH_SALES ALL_SALES
---------- ----------- ----------
16106976307766
24286766307766
36370315697069
45411465268393
55929354631362
65014854090216
76069143497281
84605202995796
93928982388882
105101171928362
115328891535464
124924*********
已选择12行。
很明显这个语句错了。实际1在这里不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。
三、窗口函数进阶－滚动统计(累积/均值)：
考虑前面提到的第2个需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。
很明显这个需求需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。想想上面的语句，假如我们能够把and unbounded following换成代表当前月份的逻辑多好啊！很幸运的是Oracle考虑到了我们这个需求，为此我们只需要将语句稍微改成: curreent row就可以了。
SQL>select m onth,
2sum(tot_sales) month_sales,
3sum(sum(tot_sales)) over(order by m onth
4 rows between unbounded preceding and current row) current_total_sales
5from orders
6group by m onth;
MONTH MONTH_SALES CURRENT_TOTAL_SALES
---------- ----------- -------------------
1610697610697
24286761039373
36370311676404
45411462217550
55929352810485
65014853311970
76069143918884
84605204379404
93928984772302
105101175282419
115328895815308
124924586307766
已选择12行。
现在我们能得到滚动的销售总额了！下面这个统计结果看起来更加完美，它展现了所有我们需要的数据：
SQL>select m onth,
2sum(tot_sales) month_sales,
3sum(sum(tot_sales)) over(order by m onth
4 rows between unbounded preceding and current row) current_total_sales,
5sum(sum(tot_sales)) over(order by m onth
6 rows between unbounded preceding and unbounded following) total_sales
7from orders
8group by m onth;
MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
---------- ----------- ------------------- -----------
16106976106976307766
242867610393736307766
363703116764046307766
454114622175506307766
559293528104856307766
650148533119706307766
760691439188846307766
846052043794046307766
939289847723026307766
1051011752824196307766
1153288958153086307766
1249245863077666307766
已选择12行。
在一些销售报表中我们会时常看到求平均值的需求，有时可能是针对全年的数据求平均值，有时会是针对截至到当前的所有数据求平均值。很简单，只需要将：
sum(sum(tot_sales))换成avg(sum(tot_sales))即可。
四、窗口函数进阶－根据时间范围统计：
前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个SQL 语句就统计了当天销售额和五天内的评价销售额：
select trunc(order_dt) day,
sum(sale_price) daily_sales,
avg(sum(sale_price)) over (order by trunc(order_dt)
range between interval '2' day preceding
and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null
and order_dt between to_date('01-jul-2001','dd-m on-yyyy')
and to_date('31-jul-2001','dd-m on-yyyy')
为了对指定范围进行统计，Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。
五、窗口函数进阶－first_value/last_value：
Oracle提供了2个额外的函数：first_value、last_value，用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况，以及每3个月的销售平均值，这两个函数就可以派上用场了。
select m onth,
first_value(sum(tot_sales)) over (order by m onth
rows between 1 preceding and 1 following) prev_month,
sum(tot_sales) monthly_sales,
last_value(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) next_month,
avg(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) rolling_avg
from orders
where year = 2001
and region_id = 6
group by month
order by month;
首先我们来看:rows between 1 preceding and 1 following告诉Oracle在当前记录的前一条、后一条范围内查找并统计，而first_value和last_value在这3条记录中至分别找出第一条、第三条记录，这样我们就轻松地得到相邻三个月的销售记录及平均值了！
六、窗口函数进阶－比较相邻记录：
通过第五部分的学习，我们知道了如何利用窗口函数来显示相邻的记录，现在假如我们想每次显示当月的销售额和上个月的销售额，应该怎么做呢？
从第五部分的介绍我们可以知道，利用first_value(sum(tot_sales) over (order by m onth rows between 1 preceding and 0 following))就可以做到了，其实Oracle还有一个更简单的方式让我们来比较2条记录，它就是lag函数。
leg函数类似于preceding和following子句，它能够通过和当前记录的相对位置而被应用，在比较同一个相邻的记录集内两条相邻记录的时候特别有用。
select m onth,
sum(tot_sales) monthly_sales,
lag(sum(tot_sales), 1) over (order by month) prev_month_sales
from orders
where year=2001
and region_id =6
group by month
order by month;
lag(sum(tot_sales),1)中的1表示以1月为基准。
Oracle开发专题之：报表函数
1.报表函数简介
2.RATIO_TO_REPORT函数
一、报表函数简介：
sum
来统计全年的订单总额，这个函数会在记录集形成的过程中，每检索一条记录就执行一次，它总共执行了12次。这是非常费时的。实际上我们还有更简便的方法：
SQL>select m onth,
2sum(tot_sales) month_sales,
3sum(sum(tot_sales)) over(order by m onth
4 rows between unbounded preceding and unbounded following) win_sales,
5sum(sum(tot_sales)) over() rpt_sales
6from orders
7group by m onth;
Oracle中分析函数用法小结 一.分析函数适用场景: ○1需要对同样的数据进行不同级别的聚合操作 ○2需要在表内将多条数据和同一条数据进行多次的比较 ○3需要在排序完的结果集上进行额外的过滤操作 二.分析函数语法: FUNCTION_NAME(
Oracle 分析函数(Analytic Functions)说明一. Analytic Functions 说明 分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式. 官方对分析函数的说明如下： Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The groupof rows is called a window and is defined bythe analytic_clause. For each row, a sliding window of rows is defined.The window determines the range of rows used to perform the calculations forthe current row. Window sizes can be based on either a physical number of rowsor a logical interval such as time. Analytic functions are the last set of operations performed in a query except for thefinal ORDER BY clause. All joins and all WHERE, GROUP BY,and HAVING clauses are completed before the analytic functions areprocessed. Therefore, analytic functions can appear only in the select listor ORDER BY clause. Analytic functions are commonly used to compute cumulative, moving, centered, andreporting aggregates. From：Analytic Functions https://www.doczj.com/doc/677784308.html,/cd/E11882_01/server.112/e26088/functions004.htm#S QLRF06174 分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数（Aggregate Function）。区别是在调用分析函数时，后面加上了开窗子句over()。 聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。分析函数也是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。产生这个不同的原因是分析函数中有一个窗口的概念，一个窗口对应于一个分组中的若干行，分析函数每次对一个窗口进行运算。运算时窗口在查询结果或分组中从顶到底移动，对每一行数据生成一个窗口。 Oracle 聚合函数（Aggregate Functions）说明 https://www.doczj.com/doc/677784308.html,/tianlesoftware/article/details/7057249
oracle比较decode/case when 、4种去重和去重中的统计函数 一：decode 和case when decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default) decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。 select username,decode(lock_date,null,'unlocked','locked') status from t; ----------------如果lock_date是null就返回unlocked 如果不是null就返回locked select username,decode(lock_date,null,'unlocked') status from t; ----------------如果lock_date是null就返回unlocked 否则是空(因为没有定义) 例如有个学生表...... 行转列------- create table score2( name varchar2(10), Language number(3), Math number(3), English number(3)); insert into score2 values('Zhang',80,67,95); insert into score2 values('Li',79,84,62); insert into score2(name,Language) values('Chen',88); commit; Select * from score2; 显示成报表的格式....... select name, sum(decode(subject,'Language', grade,0)) "Language", sum(decode(subject,'Math', grade,0)) "Math", sum(decode(subject,'English', grade,0)) "English" from score group by name; NAME Language Math English -------------------- ---------- ---------- ---------- Zhang 80 92 76 Wang 73 0 0 Li 81 95 50 第二列，如果subject='Language',那么就显示成绩,否则显示为0 第三列，如果subject='Math',那么就显示成绩,否则显示为0 第四列，如果subject='English',那么就显示成绩，否则显示为0 Case when Case [selector] ---selector可以不设置 When id=1 THEN ‘id是1’; ELSE ‘没有id值’; END CASE; ---->如果ID=1 就显示id是1 否则显示没有id值 ~~~~~上面用case when显示................. select https://www.doczj.com/doc/677784308.html,, sum( case when t.subject = 'Language' then t.grade else 0 end ) dd, sum( case when t.subject = 'Math' then t.grade else 0 end ) Math,
--已知：两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数：dense_rank,rank 语法： rank()over(order by排序字段顺序) rank()over(partition by分组字段order by排序字段顺序) 1.顺序：asc|desc名次与业务相关： 示例：找求优秀学员：成绩：降序迟到次数：升序 2.分区字段：根据什么字段进行分区。 问题：分区与分组有什么区别? ·分区只是将原始数据进行名次排列(记录数不变), ·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。rank()与dense_rank()：非连续排名与连续排名(都是简单排名) ·查询原始数据：学号,姓名,科目名,成绩 select*from t_score; S_ID S_NAME SUB_NAME SCORE 1张三语文80.00 2李四数学80.00 1张三数学0.00 2李四语文50.00 3张三丰语文10.00 3张三丰数学 3张三丰体育120.00 4杨过JAVA90.00 5mike c++80.00 3张三丰Oracle0.00 4杨过Oracle77.00 2李四Oracle77.00 ·查询各学生科目为Oracle排名(简单排名) select sc.s_id,sc.s_name,sub_name,sc.score,rank()over(order by score desc)名次from t_score sc where sub_name='Oracle'; S_ID S_NAME SUB_NAME SCORE名次 4杨过Oracle77.001 2李四Oracle77.001 3张三丰Oracle0.003 ·对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名) S_ID S_NAME SUB_NAME SCORE名次
分析函数sum over() 介绍 报送单位：审核人： 类型：业务应用 关键字：分析函数 1、引言 运维中，常常需要通过SQL语句对单行数据进行查询，同时又需要对结果集进行汇总，通常的方法是通过两个SQL语句分别进行查询汇总，这样效率低下。 2、现象描述 本节介绍一种ORACLE提供的全新的函数sum over()，该类函数称为分析函数，这类函数功能强大，可以通过一个SQL语句对数据进行遍历的同时又进行汇总，而且一张表只进行一次扫描，极大地提高SQL的执行效率。 3、处理过程 语法： FUNCTION_NAME(
执行计划： 下图说明分析函数只对表进行一次扫描 4、举例说明 下面分别举例来说明分析函数的使用，原始数据如下表
示例1：查询单行数据同时对所有数据工资进行汇总求和 示例2：查询所有数据，同时对第各部门工资进行汇总，汇总范围取值为第一行所在部门至当前部门所有数据。
示例3：查询所有数据，并且每一行汇总值按ENAME排序后取第一行至当前行ENAME所在行。 示例4：查询所有数据，并且每一行汇总值按EMPNO排序后取第一行至当前行EMPNO所在行
示例5：查询所有数据，同时每行对各部门分别进行按EMPNO排序后从各组第一行至当前行汇总求和。 示例6：查询所有数据，同时每行对从当前上两行数据范围的工资进行汇总求和。 示例7：查询所有数据，同时每行对从当前向前一行数据向后两行数据范围的工资进行汇总求和。
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 一、开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。 例如： 1)over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数 2)over（partition by department_id）按照部门分区 3)over（order by salary range between 50 preceding and 150 following） 每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150 4)over（order by salary rows between 50 preceding and 150 following） 每行对应的数据窗口是之前50行，之后150行 5)over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： 6) over（order by salary range between unbounded preceding and unbounded following） 其中： 第一行是unbounded preceding 当前行是current row 最后一行是unbounded following 二、分析函数的概念 分析函数是在整个SQL查询结束后(SQL语句中的ORDER BY的执行比较特殊)再进行的操作, 也就是说 SQL语句中的ORDER BY也会影响分析函数的执行结果。 分析函数中包含三个分析子句:分组(Partition By), 排序(Order By), 窗口(Window) 当省略窗口子句时: 1) 如果存在Order By则默认的窗口是unbounded preceding and current row 2) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following 如果省略分组,则把全部记录当成一个组 a) 如果SQL语句中的Order By满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析 函数分析时就不必再排序 b) 如果SQL语句中的Order By不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分 析函数分析结束后执行排序 三、分析函数 1）AVG
自定义聚合函数，分析函数 --from GTA Aaron 最近做一数据项目要用到连乘的功能，而Oracle数据库里没有这样的预定义聚合函数，当然利用数据库已有的函数进行数学运算也可以达到这个功能，如： selectexp(sum(ln(field_name))) from table_name; 不过今天的重点不是讲这个数学公式，而是如何自己创建聚合函数，实现自己想要的功能。很幸运Oracle 允许用户自定义聚合函数，提供了相关接口，LZ研究了下，留贴共享。 首先介绍聚合函数接口： 用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregate interface 来创建自定义聚合函数，而且自定义的聚合函数跟内建的聚合函数用法上没有差别。 通过实现ODCIAggregaterountines来创建自定义的聚合函数。可以通过定义一个对象类型（Object Type），然后在这个类型内部实现ODCIAggregate 接口函数(routines)，可以用任何一种Oracle支持的语言来实现这些接口函数，比如C/C++, JAVA, PL/SQL等。在这个Object Type定义之后，相应的接口函数也都在该Object Type Body内部实现之后，就可以通过CREATE FUNCTION语句来创建自定义的聚合函数了。 每个自定义的聚合函数需要实现4个ODCIAggregate 接口函数，这些函数定义了任何一个聚合函数内部需要实现的操作： 1. 自定义聚合函数初始化操作,从这儿开始一个聚合函数。初始化的聚合环境(aggregation context)会以对象实例(object type instance)传回给oracle. static function ODCIAggregateInitialize(varIN OUTagg_type ) return number 2. 自定义聚合函数,最主要的步骤,这个函数定义我们的聚合函数具体做什么操作，self 为 当前聚合函数的指针,用来与前面的计算结果进行关联。这个函数用来遍历需要处理的
Oracle常用函数及使用案例（珍藏版） 一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。 案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。 案例5.以首字母为大写的方式显示所有员工的姓名。 （1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路） （1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.
oracle高级分析函数使用实例 2014年11月26日10:26:55 ?标签： ?oracle ?1744 ORACLE的分析函数，发现大家写SQL的时候有些功能写的比较麻烦或者不知道复杂的功能怎么通过SQL实现，ORACLE自带的分析函数有很多相应的功能： 它是Oracle分析函数专门针对类似于"经营总额"、"找出一组中的百分之多少" 或"计算排名前几位"等问题设计的。 分析函数运行效率高，使用方便。 分析函数是基于一组行来计算的。这不同于聚集函数且广泛应用于OLAP环境中。 Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 语法：
组。这里的"分区partition"和"组group" 都是同义词。 5 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last] 其中： (1)asc|desc:指定了排列顺序。 (2)nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。 6窗口子句windowing-clause 给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中， 可用该子句让分析函数计算出它的值。 格式： {rows|range} {between {unbounded preceding|current row |
高级查询总结 A．层次查询：start with……connec by prior…….. select lpad(' ',3*level)||ename,job,level from emp start with mgr is null connect by prior empno=mgr; 从根结点向下查，lpad()为左添加，level为第几层，prior为查找的方向；此句若省掉start with….则表示要遍历整个树型结构；若出现level，则后面一定要跟connect by B.交叉报表(case when then end 与decode()) select ename,case when sal>=1500then sal*1.01 else sal*1.1 end工资 from emp; select姓名, max(case课程when'语文'then分数end) 语文, max(case课程when'数学'then分数end) 数学, max(case课程when'历史'then分数end) 历史 from学生group by姓名;------(交叉报表与函数一起使用) select ename,sum(decode(sal,'sal',comm,null)) 奖金from emp group by ename;--可实现分支 decode(条件,(值),(返回值),…………,(默认值)) 部门 select sal,decode(sign(sal-1500),-1,1.1*sal,0,1.1*sal,1,1.05*sal) from emp; C.连接查询 1．等值： select * from emp,dept where emp.deptno(+)=dept.deptno; ‘+’在左则以右表为主，反之亦然 2.非等值：where的运算符不是等号 3.自然连接: select * from emp natural join dept 4.99乘法: select * from emp e full join dept d using (deptno) where deptno=10; --where必须放在using(on)的后面 D集合查询： 1．A Union B:消除重复行，有all则可以重复,默认第一列升序select ename,sal from deptno=20 union select ename,sal from job=’CLERK’; 2．A intersect B:A与B的交集 select ename,sal from deptno=20 intersect select ename,sal from job=’CLERK’; 3.A minus B:在A中减去既属于A又属于B的一部分
--每一个值占总数的百分比 SELECT x, y, z,round(z/sum(z) over()*100,2)||'%' propn , sum(z) over() sum FROM t1; --每一个值占分组的百分比 SELECT x, y, z,round(z/sum(z) over(partition by x)*100,2)||'%' propn , sum(z) over(partition by x) sum FROM t1; --以x分区，按y排序累计取和 SELECT x, y, z, sum(z) over(partition by x order by y desc) sum FROM t1; --以x分区，按z降序，每个分区取前两个 select * from (select x, y, z, s, dense_rank() over(partition by x order by z desc) r1, rank() over(partition by x order by z desc) r2, count(*) over(partition by x order by z desc, y range unbounded preceding) r3 from (SELECT x, y, z, sum(z) over(partition by x) s FROM t1 order by 4 desc, z desc)) where r3 < 3 order by z desc, x /* 语法： function_name(
Oracle分析函数与分组关键字的用法 以下是我以前工作中做报表常用的几个函数，在此分享一下，希望对大家有帮助。 (一)分析函数 ●row_number Purpose ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1. You cannot use ROW_NUMBER or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions. 按部门分组后根据工资排序，序号rn特征：连续、无并列 select t.*, row_number() over(partition by t.deptno order by sal desc) rn from emp t; ●rank
Purpose RANK calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers. ? As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible. As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause. 按部门分组后根据工资排序，序号rn特征：不连续、有并列 select t.*, rank() over(partition by t.deptno order by sal desc) rn from emp t; dense_rank
oracle之分析函数over及开窗函数 一：分析函数over Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 统计各班成绩第一名的同学信息 NAME CLASS S ----- ----- ---------------------- fda 1 80 ffd 1 78 dss 1 95 cfe 2 74 gds 2 92 gf 3 99 ddd 3 99 adf 3 45 asdf 3 55 3dd 3 78 通过： -- select * from ( select name,class,s,rank()over(partition by class order by s desc) mm from t2 ) where mm=1 ---- 得到结果：
NAME CLASS S MM ----- ----- ---------------------- ---------------------- dss 1 95 1 gds 2 92 1 gf 3 99 1 ddd 3 99 1 注意： 1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一， row_number()只返回一个结果 2.rank()和dense_rank()的区别是： --rank()是跳跃排序，有两个第二名时接下来就是第四名 --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名 二：开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： 1： over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数over（partition by deptno）按照部门分区 2： over（order by salary range between 5 preceding and 5 following） 每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5 例如:对于以下列 aa 1 2 2
SQL Statistical Functions Make Big Data + Analytics Simple Charlie Berger, MS Engineering, MBA Sr. Director Product Management, Data Mining and Advanced Analytics charlie.berger@https://www.doczj.com/doc/677784308.html, https://www.doczj.com/doc/677784308.html,/CharlieDataMine
Data, data everywhere Data Analysis platforms requirements: ?Be extremely powerful and handle large data volumes ?Be easy to learn ?Be highly automated & enable deployment Growth of Data Exponentially Greater than Growth of Data Analysts! https://www.doczj.com/doc/677784308.html,/more-data-than-analysts-the-real-big-data-problem/
Analytics + Data Warehouse + Hadoop ?Platform Sprawl –More Duplicated Data –More Data Movement Latency –More Security challenges –More Duplicated Storage –More Duplicated Backups –More Duplicated Systems –More Space and Power
Oracle分析函数使用总结 1.使用评级函数 评级函数（ranking function）用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位 CUME_DIST()：返回特定值相对于一组值的位置：他是“cumulative distribution”(累积分布)的简写PERCENT_RANK()：返回某个值相对于一组值的百分比排名 NTILE():返回n分片后的值，比如三分片、四分片等等 ROW_NUMBER():为每一条分组纪录返回一个数字 下面我们分别举例来说明这些函数的使用 1）RANK（）与DENSE-RANK（） 首先显示下我们的源表数据的结构及部分数据： SQL> desc all_sales; 名称是否为空? 类型 ----------------------------------------- -------- ----------- YEAR NOT NULL NUMBER(38) MONTH NOT NULL NUMBER(38) PRD_TYPE_ID NOT NULL NUMBER(38) EMP_ID NOT NULL NUMBER(38) AMOUNT NUMBER(8,2) SQL> select * from all_sales where rownum<11; YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT ---------- ---------- ----------- ---------- ---------- 2003 1 1 21 10034.84 2003 2 1 21 15144.65 2003 3 1 21 20137.83 2003 4 1 21 25057.45 2003 5 1 21 17214.56 2003 6 1 21 15564.64 2003 7 1 21 12654.84 2003 8 1 21 17434.82 2003 9 1 21 19854.57 2003 10 1 21 21754.19 已选择10行。 好接下来我们将举例来说明上述函数的使用：首先是RANK（）与DENSE-RANK（）的使用：SQL> select 2 prd_type_id,sum(amount), 3 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank, 4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank