oracle的分析函数over 及开窗函数
一：分析函数over
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。
1：统计某商店的营业额。
date sale
1 20
2 15
3 14
4 18
5 30
规则：按天统计：每天都统计前面几天的总额
得到的结果：
DATE SALE SUM
----- -------- ------
1 20 20 --1天
2 15 35 --1天＋2天
3 1
4 49 --1天＋2天＋3天
4 18 67 .
5 30 97 .
2:统计各班成绩第一名的同学信息
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 nam e,class,s,rank()over(partition by class order by s desc) mm f rom 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是连续排序，有两个第二名时仍然跟着第三名
3.分类统计(并显示信息)
A B C
-- -- ----------------------
m a 2
n a 3
m a 2
n b 2
n b 1
x b 3
x b 2
x b 4
h b 3
select a,c,sum(c)over(partition by a) from t2
得到结果：
A B C SUM(C)OVER(PARTITIONBYA)
-- -- ------- ------------------------
h b 3 3
m a 2 4
m a 2 4
n a 3 6
n b 2 6
n b 1 6
x b 3 9
x b 2 9
x b 4 9
如果用sum，group by 则只能得到
A SUM(C)
-- ----------------------
h 3
m 4
n 6
x 9
无法得到B列值
＝＝＝＝＝
二：开窗函数
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
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
2
3
4
5
6
7
9
sum(aa)over（order by aa range between 2 preceding and 2 following）
得出的结果是
AA SUM
---------------------- -------------------------------------------------------
1 10
2 14
2 14
2 14
3 18
4 18
5 22
6 18
7 22
9 9
就是说，对于aa=5的一行，sum为5-1<=aa<=5+2 的和
对于aa=2来说，sum=1+2+2+2+3+4=14 ；
又如对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9 ；
3：其它：
over（order by salary rows between 2 preceding and 4 following）每行对应的数据窗口是之前2行，之后4行
4：下面三条语句等效：
over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
等效
over(partition by null)。
Oracle中分析函数用法小结
Oracle中分析函数用法小结一.分析函数适用场景:○1需要对同样的数据进行不同级别的聚合操作○2需要在表内将多条数据和同一条数据进行多次的比较○3需要在排序完的结果集上进行额外的过滤操作二.分析函数语法:FUNCTION_NAME(<argument>,<argument>...)OVER(<Partition-Clause><Order-by-Clause><Windowing Clause>)例:sum(sal) over (partition by deptno order by ename) new_aliassum就是函数名(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm) over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的.1)FUNCTION子句ORACLE提供了26个分析函数,按功能分5类分析函数分类等级(ranking)函数:用于寻找前N种查询开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上例:sum(t.sal) over (order by t.deptno,t.ename) running_total,sum(t.sal) over (partition by t.deptno order by t.ename) department_total制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列例:sum(t.sal) over () running_total2,sum(t.sal) over (partition by t.deptno) department_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子句!!!下面我们通过一个实际的例子：按区域查找上一年度订单总额占区域订单总额20%以上的客户，来看看分析函数的应用。
Oracle语法之OVER（PARTITIONBY）及开窗函数
Oracle语法之OVER（PARTITIONBY）及开窗函数oracle的分析函数over 及开窗函数⼀：分析函数overOracle从8.1.6开始提供分析函数，分析函数⽤于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多⾏，⽽聚合函数对于每个组只返回⼀⾏。
下⾯通过⼏个例⼦来说明其应⽤。
1：统计某商店的营业额。
date sale1 202 153 144 185 30规则：按天统计：每天都统计前⾯⼏天的总额得到的结果：DATE SALE SUM----- -------- ------1 20 20 --1天2 15 35 --1天＋2天3 14 49 --1天＋2天＋3天4 18 67 .5 30 97 .2:统计各班成绩第⼀名的同学信息NAME CLASS S----- ----- ----------------------fda 1 80ffd 1 78dss 1 95cfe 2 74gds 2 92gf 3 99ddd 3 99adf 3 45asdf 3 553dd 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 1gds 2 92 1gf 3 99 1ddd 3 99 1注意：1.在求第⼀名成绩的时候，不能⽤row_number()，因为如果同班有两个并列第⼀，row_number()只返回⼀个结果2.rank()和dense_rank()的区别是：--rank()是跳跃排序，有两个第⼆名时接下来就是第四名--dense_rank()l是连续排序，有两个第⼆名时仍然跟着第三名3.分类统计 (并显⽰信息)A B C-- -- ----------------------m a 2n a 3m a 2n b 2n b 1x b 3x b 2x b 4h b 3select a,c,sum(c)over(partition by a) from t2得到结果：A B C SUM(C)OVER(PARTITIONBYA)-- -- ------- ------------------------h b 3 3m a 2 4m a 2 4n a 3 6n b 2 6n b 1 6x b 3 9x b 2 9x b 4 9如果⽤sum，group by 则只能得到A SUM(C)-- ----------------------h 3m 4n 6x 9⽆法得到B列值＝＝＝＝＝select * from test数据:A B C1 1 11 2 21 3 32 2 53 4 6---将B栏位值相同的对应的C 栏位值加总select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum from testA B C C_SUM1 1 1 11 2 2 72 2 5 71 3 3 33 4 6 6---如果不需要已某个栏位的值分割,那就要⽤ nulleg: 就是将C的栏位值summary 放在每⾏后⾯select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum from testA B C C_SUM1 1 1 171 2 2 171 3 3 172 2 5 173 4 6 17求个⼈⼯资占部门⼯资的百分⽐SQL> select * from salary;NAME DEPT SAL---------- ---- -----a 10 2000b 10 3000c 10 5000d 20 4000SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;NAME DEPT SAL PERCENT---------- ---- ----- ----------a 10 2000 20b 10 3000 30c 10 5000 50d 20 4000 100⼆：开窗函数开窗函数指定了分析函数⼯作的数据窗⼝⼤⼩，这个数据窗⼝⼤⼩可能会随着⾏的变化⽽变化，举例如下：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例如:对于以下列aa1222345679sum(aa)over（order by aa range between 2 preceding and 2 following）得出的结果是AA SUM---------------------- -------------------------------------------------------1 102 142 142 143 184 185 226 187 229 9就是说，对于aa=5的⼀⾏，sum为 5-1<=aa<=5+2 的和对于aa=2来说，sum=1+2+2+2+3+4=14 ；⼜如对于aa=9 ，9-1<=aa<=9+2 只有9⼀个数，所以sum=9 ；3：其它：over（order by salary rows between 2 preceding and 4 following）每⾏对应的数据窗⼝是之前2⾏，之后4⾏4：下⾯三条语句等效：over（order by salary rows between unbounded preceding and unbounded following）每⾏对应的数据窗⼝是从第⼀⾏到最后⼀⾏，等效：over（order by salary range between unbounded preceding and unbounded following）等效over(partition by null)。
oracle的开窗函数
oracle的开窗函数开窗函数指的是OVER()，和分析函数配合使⽤。
语法：OVER(PARTITION BY分组字段ORDER BY排序字段 ROWS BETWEEN排序字段范围值1 AND排序字段范围值2)语法说明：开窗函数为分析函数带有的，包含三个分析⼦句：1. 分组（PARTITION BY）。
2. 排序（ORDER BY）。
3. 窗⼝（ROWS）-- 指定范围。
ROWS 有多个范围值：1. UNBOUNDED PRECEDING ⽆限/不限定先前⾏。
2. N PRECEDING N个先前⾏（N为1则是1个先前⾏，2则是2个先前⾏，以此类推）。
3. UNBOUNDED FOLLOWING ⽆限/不限定的跟随⾏。
4. N FOLLOWING N个跟随⾏（N为1则是1个跟随⾏，2则是2个跟随⾏，以此类推）。
5. CURRENT ROW 当前⾏。
⽰例1：SELECTCCTI.CTR_TYPE_ID,CCTI.ORDER_NUM,,WMSYS.WM_CONCAT() OVER(PARTITION BY CCTI.CTR_TYPE_ID) CTR_TYPE_ITEM_STRFROM T_CTRG_CTR_TYPE_ITEM CCTI;结果1：分析1：区别于GROUP BY⼦句的只返回分组⾏的结果，开窗函数每⼀⾏都会返回⼀个结果。
⽰例1的写法相当于指定了ROWS范围从不限定先前⾏到不限定跟随⾏（默认）：SELECTCCTI.CTR_TYPE_ID,CCTI.ORDER_NUM,,WMSYS.WM_CONCAT() OVER(PARTITION BY CCTI.CTR_TYPE_ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) CTR_TYPE_ITEM_STRFROM T_CTRG_CTR_TYPE_ITEM CCTI;⽰例2：SELECTCCTI.CTR_TYPE_ID,CCTI.ORDER_NUM,,WMSYS.WM_CONCAT() OVER(PARTITION BY CCTI.CTR_TYPE_ID ORDER BY CCTI.ORDER_NUM) CTR_TYPE_ITEM_STRFROM T_CTRG_CTR_TYPE_ITEM CCTI;结果2：分析2：加上了ORDER BY 之后，返回结果变成了逐级递增的效果。
开窗函数用法
开窗函数OVER(PARTITION BY)函数介绍开窗函数Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：1：over后的写法：over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数over（partition by deptno）按照部门分区over（partition by deptno order by salary）2：开窗的窗口范围：over（order by salary range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。
举例：--sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和select name,class,s, sum(s)over(order by s range between 2 preceding and 2 following) mm from t2adf 3 45 45 --45加2减2即43到47，但是s在这个范围内只有45asdf 3 55 55cfe 2 74 743dd 3 78 158 --78在76到80范围内有78，80，求和得158fda 1 80 158gds 2 92 92ffd 1 95 190dss 1 95 190ddd 3 99 198gf 3 99 198over（order by salaryrows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。
Oracle分析函数1
分析函数(OVER) (1)分析函数2(Rank, Dense_rank, row_number) .............................................................................. 错误！未定义书签。
分析函数3(Top/Bottom N、First/Last、NTile) .......................................................................... 错误！未定义书签。
窗口函数....................................................................................................................................... 错误！未定义书签。
报表函数....................................................................................................................................... 错误！未定义书签。
分析函数总结............................................................................................................................... 错误！未定义书签。
26个分析函数.............................................................................................................................. 错误！未定义书签。
Oracle之分析函数
Oracle之分析函数⼀、分析函数 1、分析函数 分析函数是Oracle专门⽤于解决复杂报表统计需求的功能强⼤的函数，它可以在数据中进⾏分组然后计算基于组的某种统计值，并且每⼀组的每⼀⾏都可以返回⼀个统计值。
2、分析函数和聚合函数的区别 普通的聚合函数⽤group by分组，每个分组返回⼀个统计值，⽽分析函数采⽤partition by分组，并且每组每⾏都可以返回⼀个统计值。
3、分析函数的形式 分析函数带有⼀个开窗函数over()，包含分析⼦句。
分析⼦句⼜由下⾯三部分组成： partition by ：分组⼦句，表⽰分析函数的计算范围，不同的组互不相⼲； ORDER BY：排序⼦句，表⽰分组后，组内的排序⽅式； ROWS/RANGE：窗⼝⼦句，是在分组(PARTITION BY)后，组内的⼦分组（也称窗⼝），此时分析函数的计算范围窗⼝，⽽不是PARTITON。
窗⼝有两种，ROWS和RANGE； 使⽤形式如下：OVER(PARTITION BY xxx PORDER BY yyy ROWS BETWEEN rowStart AND rowEnd) 注：窗⼝⼦句在这⾥我只说rows⽅式的窗⼝,range⽅式和滑动窗⼝也不提。
⼆、OVER() 函数 1、sql 查询语句的 order by 和 OVER() 函数中的 ORDER BY 的执⾏顺序 分析函数是在整个sql查询结束后(sql语句中的order by的执⾏⽐较特殊)再进⾏的操作, 也就是说sql语句中的order by也会影响分析函数的执⾏结果： [1] 两者⼀致：如果sql语句中的order by满⾜分析函数分析时要求的排序，那么sql语句中的排序将先执⾏,分析函数在分析时就不必再排序； [2] 两者不⼀致：如果sql语句中的order by不满⾜分析函数分析时要求的排序，那么sql语句中的排序将最后在分析函数分析结束后执⾏排序。
2、分析函数中的分组/排序/窗⼝分析函数包含三个分析⼦句：分组(partition by)，排序(order by)，窗⼝(rows/range)窗⼝就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗⼝中的记录⽽不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗⼝指定到该分组中的第⼀⾏数据到当前⾏, 如果你指定该窗⼝从该分组中的第⼀⾏到最后⼀⾏,那么该组中的每⼀个sum值都会⼀样，即整个组的总和。
Oracle开发之分析函数简介Over用法
Oracle开发之分析函数简介Over⽤法⼀、Oracle分析函数简介：在⽇常的⽣产环境中，我们接触得⽐较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者⾄少说对响应的时间多长有⼀定的要求；其次这些系统的业务逻辑⼀般⽐较复杂，可能需要经过多次的运算。
⽐如我们经常接触到的电⼦商城。
在这些系统之外，还有⼀种称之为OLAP的系统(即Online Aanalyse Process)，这些系统⼀般⽤于系统决策使⽤。
通常和数据仓库、数据分析、数据挖掘等概念联系在⼀起。
这些系统的特点是数据量⼤，对实时响应的要求不⾼或者根本不关注这⽅⾯的要求，以查询、统计操作为主。
我们来看看下⾯的⼏个典型例⼦：①查找上⼀年度各个销售区域排名前10的员⼯②按区域查找上⼀年度订单总额占区域订单总额20%以上的客户③查找上⼀年度销售最差的部门所在的区域④查找上⼀年度销售最好和最差的产品我们看看上⾯的⼏个例⼦就可以感觉到这⼏个查询和我们⽇常遇到的查询有些不同，具体有：①需要对同样的数据进⾏不同级别的聚合操作②需要在表内将多条数据和同⼀条数据进⾏多次的⽐较③需要在排序完的结果集上进⾏额外的过滤操作⼆、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 SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES---------- ---------- -------------- ---------- ---------- ---------- ----------11 7 11 2001 7 2 122044 5 4 2001 10 2 378027 6 7 2001 2 3 375010 6 8 2001 1 2 2169110 6 7 2001 2 3 4262415 7 12 2000 5 6 2412 7 9 2000 6 2 506581 52 20003 2 444941 5 1 2000 92 748642 5 4 20003 2 350602 5 4 2000 4 4 64542 5 1 2000 10 4 355804 5 4 2000 12 2 3919013 rows selected.【3】测试语句：复制代码代码如下:SQL> select o.cust_nbr customer,o.region_id region,sum(o.tot_sales) cust_sales,sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales from orders_tmp owhere o.year = 2001group by o.region_id, o.cust_nbr;CUSTOMER REGION CUST_SALES REGION_SALES---------- ---------- ---------- ------------4 5 37802 378027 6 3750 6806510 6 64315 6806511 7 12204 12204三、分析函数OVER解析：请注意上⾯的绿⾊⾼亮部分，group by的意图很明显：将数据按区域ID，客户进⾏分组，那么Over这⼀部分有什么⽤呢？假如我们只需要统计每个区域每个客户的订单总额，那么我们只需要group by o.region_id,o.cust_nbr就够了。
Oracle开窗函数
Oracle开窗函数大略总结一下Oracle的分析函数1. lag(字段1,n) over ([partition by 字段2] order by 字段3),向当前行的后n行提取字段1的值2. lead(字段1,n) over ([partition by 字段2] order by 字段3),向当前行的前n行提取字段1的值3. count(*或字段1) over ([partition by 字段2]),计算总共多少行,注意字段1若是为空,则不包括在行数里面.4.sum(字段1) over ([[partition by 字段2] order by 字段3]),计算和,若有order by,则表示按顺序累计当前行的前面所有行和当前行的字段1值的总和. 注意到,sum是计算所有相同的值后再累加,不管over 里面有没窗口语句.当前行若是最后一行,则后n行的和为null.avg(字段1) over ([[partition by 字段2] order by 字段3])用法相似.4.ntile(n) over (order by 字段1),把所有记录按顺序分成n组.5. row_number()/rank()/dense_rank() over ([partition by 字段2]order by 字段1) ,将返回的记录行按顺序编号.row_number()即使是相同的值,序号也不相同,序号是连续的;rank() 相同的值序号相同,序号是断的,而不是连续的;dense_rank() 相同的值序号相同,序号是连续的.6.非窗口函数,突然想起的. 1>group by rollup(字段1,字段2,字段3.....) :小计总和,select grouping(字段1) 返回0,则是按group by 字段1分组得到的值,返回1则是rollup创建的总和.grouping(字段2)返回0表示按group by 字段2分组的值,返回1表示rollup创建的总和,grouping(字段1)grouping(字段2)group(字段3)...都返回1,表示按字段1,字段2,字段3分组的值,都返回0,则表示rollup创建的总和...rollup会按字段1,(字段1,字段2),(字段1,字段2,字段3),()分组总计,即按N+1种方法分组;--若用group by rollup(a,b)则只会按a分组,ab分组,总计，而不会按b分组with tmp_a as(select 30 deptno,'zz' ename,'analyse' job,to_date('20010203','yyyymmdd') hiredate,500 sal from dual union allselect 10 deptno,'ee' ename,'clerk' job,to_date('20051213','yyyymmdd') hiredate,300 sal from dual union allselect 30 deptno,'cc' ename,'manager' job,to_date('20051213','yyyymmdd') hiredate,10000 sal from dual union allselect 20 deptno,'dd' ename,'analyse' job,to_date('20051213','yyyymmdd') hiredate,6000 sal from dual union allselect 30 deptno,'tt' ename,'clerk' job,to_date('20080212','yyyymmdd') hiredate,600 sal from dual union allselect 30 deptno,'gg' ename,'clerk' job,to_date('20080312','yyyymmdd') hiredate,800 sal from dual union allselect 10 deptno,'rr' ename,'analyse' job,to_date('20090212','yyyymmdd') hiredate,100 sal from dual )select deptno,job,sum(sal),'total by dept and job' categoryfrom tmp_agroup by rollup(deptno,job) --这里只按deptno,job分组和deptno分组和不分组，不会按 job 分组deptno,job,sal,category10 analyse 100 total by dept and job 0 030 analyse 500 total by dept and job 0 030 clerk 1400 total by dept and job 0 020 analyse 6000 total by dept and job 0 030 manager 10000 total by dept and job 0 010 clerk 300 total by dept and job 0 030 11900 total by dept 0 120 6000 total by dept 0 110 400 total by dept 0 1manager 10000 total by job 1 0analyse 6600 total by job 1 0clerk 1700 total by job 1 018300 total for table 1 12>group by cube(字段1,字段2,字段3.....) :小计总和,select grouping(字段1) 返回0,则是按group by 字段1分组得到的值,返回1则是cube创建的总和.grouping(字段2)返回0表示按group by 字段2分组的值,返回1表示cube创建的总和,grouping(字段1)grouping(字段2)group(字段3)...都返回1,表示按字段1,字段2,字段3分组的值,都返回0,则表示cube创建的总和...cube会按字段1,(字段1,字段2),(字段1,字段2,字段3),(),字段2,字段3,(字段1,字段3),(字段2,字段3)分组总计,即按2的N次方种方法分组;--grouping 返回1,则表示cube得到的；返回0，则表示按group得到的with tmp_a as(select 30 deptno,'zz' ename,'analyse' job,to_date('20010203','yyyymmdd') hiredate,500 sal from dual union allselect 10 deptno,'ee' ename,'clerk' job,to_date('20051213','yyyymmdd') hiredate,300 sal from dual union allselect 30 deptno,'cc' ename,'manager' job,to_date('20051213','yyyymmdd') hiredate,10000 sal from dual union allselect 20 deptno,'dd' ename,'analyse' job,to_date('20051213','yyyymmdd') hiredate,6000 sal from dual union allselect 30 deptno,'tt' ename,'clerk' job,to_date('20080212','yyyymmdd') hiredate,600 sal from dual union allselect 30 deptno,'gg' ename,'clerk' job,to_date('20080312','yyyymmdd') hiredate,800 sal from dual union allselect 10 deptno,'rr' ename,'analyse' job,to_date('20090212','yyyymmdd') hiredate,100 sal from dual )selectdeptno,job,sum(sal),decode(grouping(deptno)||grouping(job),'0 0','total by dept and job','01','total by dept','10','total by job','11','total for table') category,grouping(deptno) is_group_deptno,grouping(job)is_group_job from tmp_agroup by cube(deptno,job)order by grouping(deptno),grouping(job)deptno,job,sal,category,is_group_deptno,is_group_job10 analyse 100 total by dept and job 0 030 analyse 500 total by dept and job 0 030 clerk 1400 total by dept and job 0 020 analyse 6000 total by dept and job 0 030 manager 10000 total by dept and job 0 010 clerk 300 total by dept and job 0 030 11900 total by dept 0 120 6000 total by dept 0 110 400 total by dept 0 1manager 10000 total by job 1 0analyse 6600 total by job 1 0clerk 1700 total by job 1 018300 total for table 1 13>group by grouping sets((字段1),(字段2),(字段1,字段2,字段3),()),指定按哪几列分组,即允许定义组.这里定义的是按字段1分组,按字段2分组,按字段1,字段2,字段3一起分组,按()分组即总和.同样grouping(字段1)返回0表示按group by 普通分组所得,返回0则是grouping sets 创建的总和.--如 group by grouping sets(a,b,(a,b),())说明结果会按a分组，按b分组，按ab 分组，总计；也可以直接group by grouping set(())--grouping(a)返回0表示按a分组，返回1表示总计，而不是按a分组了with tmp_a as(select 30 deptno,'zz' ename,'analyse' job,to_date('20010203','yyyymmdd') hiredate,500 sal from dual union allselect 10 deptno,'ee' ename,'clerk' job,to_date('20051213','yyyymmdd') hiredate,300 sal from dual union allselect 30 deptno,'cc' ename,'manager' job,to_date('20051213','yyyymmdd') hiredate,10000 sal from dual union allselect 20 deptno,'dd' ename,'analyse' job,to_date('20051213','yyyymmdd') hiredate,6000 sal from dual union allselect 30 deptno,'tt' ename,'clerk' job,to_date('20080212','yyyymmdd') hiredate,600 sal from dual union allselect 30 deptno,'gg' ename,'clerk' job,to_date('20080312','yyyymmdd') hiredate,800 sal from dual union allselect 10 deptno,'rr' ename,'analyse' job,to_date('20090212','yyyymmdd') hiredate,100 sal from dual )selectdeptno,job,sum(sal),decode(grouping(deptno)||grouping(job),'0 0','total by dept and job','01','total by dept','10','total by job','11','total for table') category,grouping(deptno) is_group_deptno,grouping(job) is_group_job from tmp_agroup by grouping sets(deptno,job,(deptno,job),())order by grouping(deptno),grouping(job)deptno,job,sum(sal),category,is_group_deptno,is_group_job30 analyse 500 total by dept and job 0 010 clerk 300 total by dept and job 0 030 manager 10000 total by dept and job 0 010 analyse 100 total by dept and job 0 020 analyse 6000 total by dept and job 0 030 clerk 1400 total by dept and job 0 030 11900 total by dept 0 110 400 total by dept 0 120 6000 total by dept 0 1manager 10000 total by job 1 0analyse 6600 total by job 1 0clerk 1700 total by job 1 018300 total for table 1 17.oracle分析函数的语法：function_name(arg1,arg2,...)over( )说明：1. partition-clause 数据记录集分组2. order-by-clause 数据记录集排序3. windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合这是讲讲windowing clause部分a、窗口使用前提：分析函数必须有order-by-clause语句b、默认窗口范围：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWc、窗口有三种：range、row、specifyingrange窗口(值范围,此值对order by 后的字段的值来说)只对日期和数字类型数据生效,只能按照一个字段排序,在对应的字段数据范围内进行搜索.说明范围和排序的升降有关a、升序，查找[本行字段数据值-range值,本行数据值]数据集合b、降序, 查找[本行数据值,本行字段数据值+range值,]数据集合例：查询本人工资以及和本人工资差距在100内的员工个数,即和自己工资相等的员工个数(包括自己)with tmp_a as(select 30 deptno,7723 mgr,'zz' ename,'analyse' job,to_date('20010203','yyyymmdd') hiredate,500 sal from dual union allselect 10 deptno,7723 mgr,'ee' ename,'clerk' job,to_date('20051213','yyyymmdd') hiredate,300 sal from dual union allselect 30 deptno,7725 mgr,'cc' ename,'manager' job,to_date('20051213','yyyymmdd') hiredate,10000 sal fromdual union allselect 30 deptno,7725 mgr,'qq' ename,'manager' job,to_date('20051214','yyyymmdd') hiredate,50000 sal from dual union allselect 30 deptno,7725 mgr,'qq2' ename,'manager' job,to_date('20051215','yyyymmdd') hiredate,40000 sal from dual union allselect 30 deptno,7725 mgr,'qq3' ename,'manager' job,to_date('20051217','yyyymmdd') hiredate,30000 sal from dual union allselect 30 deptno,7725 mgr,'qq4' ename,'manager' job,to_date('20051216','yyyymmdd') hiredate,20000 sal from dual union allselect 30 deptno,7725 mgr,'qq5' ename,'manager' job,to_date('20051220','yyyymmdd') hiredate,20000 sal from dual union allselect 20 deptno,7733 mgr,'dd' ename,'analyse' job,to_date('20051213','yyyymmdd') hiredate,6000 sal from dual union allselect 30 deptno,7725 mgr,'tt' ename,'clerk' job,to_date('20080212','yyyymmdd') hiredate,600 sal from dual union allselect 30 deptno,7723 mgr,'gg' ename,'clerk' job,to_date('20080312','yyyymmdd') hiredate,800 sal from dual union allselect 30 deptno,7723 mgr,'gg2' ename,'clerk' job,to_date('20080315','yyyymmdd') hiredate,800 sal from dual union allselect 30 deptno,7723 mgr,'gg2' ename,'clerk' job,to_date('20080313','yyyymmdd') hiredate,800 sal from dualunion allselect 10 deptno,7724 mgr,'rr' ename,'analyse' job,to_date('20090212','yyyymmdd') hiredate,100 sal from dual )select ename,sal,sum(sal) over (order by sal range 200 preceding) 工资相差在200内的工资和,(count(ename) over (order by sal range 200 preceding))-1 工资相差在200内的员工数,(count(ename) over (order by sal range 0 preceding)) 工资相等员工数 from tmp_aename,sal,工资相差在200内的工资和,工资相差在200内的员工数,工资相等员工数rr 100 100 0 1ee 300 400 1 1zz 500 800 1 1tt 600 1100 1 1gg 800 3000 3 3gg2 800 3000 3 3gg2 800 3000 3 3dd 6000 6000 0 1cc 10000 10000 0 1qq5 20000 40000 1 2qq4 20000 40000 1 2qq3 30000 30000 0 1qq2 40000 40000 0 1qq 50000 50000 0 1--与自己入职日期相差在2天内的员工的工资和（包括自己）with tmp_a as(select 30 deptno,7723 mgr,'zz' ename,'analyse' job,to_date('20010203','yyyymmdd') hiredate,500 sal from dual union allselect 10 deptno,7723 mgr,'ee' ename,'clerk' job,to_date('20051213','yyyymmdd') hiredate,300 sal from dual union allselect 30 deptno,7725 mgr,'cc' ename,'manager' job,to_date('20051213','yyyymmdd') hiredate,10000 sal from dual union allselect 30 deptno,7725 mgr,'qq' ename,'manager' job,to_date('20051214','yyyymmdd') hiredate,50000 sal from dual union allselect 30 deptno,7725mgr,'qq2' ename,'manager' job,to_date('20051215','yyyymmdd') hiredate,40000 sal from dual union allselect 30 deptno,7725 mgr,'qq3' ename,'manager' job,to_date('20051217','yyyymmdd') hiredate,30000 sal from dual union allselect 30 deptno,7725 mgr,'qq4' ename,'manager' job,to_date('20051216','yyyymmdd') hiredate,20000 sal from dual union allselect 30 deptno,7725 mgr,'qq5' ename,'manager' job,to_date('20051220','yyyymmdd') hiredate,20000 sal from dual union allselect 20 deptno,7733 mgr,'dd' ename,'analyse' job,to_date('20051213','yyyymmdd') hiredate,6000 sal from dual union allselect 30 deptno,7725 mgr,'tt' ename,'clerk' job,to_date('20080212','yyyymmdd') hiredate,600 sal from dualunion allselect 30 deptno,7723 mgr,'gg' ename,'clerk' job,to_date('20080312','yyyymmdd') hiredate,800 sal from dual union allselect 30 deptno,7723 mgr,'gg2' ename,'clerk' job,to_date('20080315','yyyymmdd') hiredate,800 sal from dual union allselect 30 deptno,7723 mgr,'gg2' ename,'clerk' job,to_date('20080313','yyyymmdd') hiredate,800 sal from dual union allselect 10 deptno,7724 mgr,'rr' ename,'analyse' job,to_date('20090212','yyyymmdd') hiredate,100 sal from dual )select deptno,mgr,ename,job,hiredate,sal,sum(sal) over (order by hiredate range 2 preceding) rn from tmp_arow 窗口(行范围)row 窗口是设定分析函数的数据行数，使用该窗口基本没有限制rows n preceding即为该窗口数据包括本行前的 n 行以及本行共 (n+1) 行数据specifying 窗口实际上统计的函数都是由specifying 窗口设定，range 、row 窗口实际是指定了分析的对象（字段、数据行），而具体的行数由specifying 窗口设定，常用表达式如下：unbounded preceding 从当前分区第一行开始，结束于处理的当前行current row 从当前行开始 ( 并结束 )numberic expression preceding 从当前行的数字表达式之前的行开始numberic expression following 从当前行的数字表达式之后的行结束over (order by 字段) 默认为over (order by 字段range between unbounded preceding and current row)或over (order by 字段 rows between unbounded preceding and current row)。
Oracle开窗函数over（）（转）
Oracle开窗函数over（）（转）copy⽂链接：/yjjm1990/article/details/7524167#，/database/201402/281473.html格式： 可以开窗的函数(..) over(..) over中防⽌分组的条件和分组的排序，不过分组使⽤的不再是GROUP BY⽽是PARTITION BY，表⽰开窗-- 建表CREATE table tb_sc(uName varchar2(10),uCourse varchar2(10),Uscore varchar2(10));-- 插⼊数据INSERT INTO tb_sc VALUES('张三','语⽂','80');INSERT INTO tb_sc VALUES('张三','数学','95');INSERT INTO tb_sc VALUES('李四','语⽂','90');INSERT INTO tb_sc VALUES('李四','数学','70');INSERT INTO tb_sc VALUES('王五','语⽂','90');INSERT INTO tb_sc VALUES('王五','数学','90');-- 查询所有SELECT * FROM tb_sc;-- 查询每名学⽣的平均分（展⽰姓名、平均分）Select uName,AVG(uScore)FROM tb_scGROUP BY uName;-- 查询每名同学的平均分并降序排列（展⽰姓名、平均分）SELECT uName,AVG(uScore)FROM tb_scGROUP BY uNameORDER BY uName DESC;-- 查询平均分数⾼于85分的学⽣（展⽰姓名、平均分）SELECT uName,AVG(uScore)FROM tb_scGROUP BY uNameHAVING AVG(uScore)>85;-- 查询不为张三且平均分⾼于85的学⽣（展⽰姓名、平均分）SELECT uName,AVG(uScore)FROM tb_scGROUP BY uNameHAVING uName != '张三' AND AVG(uScore) >85;-- 查询所有学⽣的信息并将每个学⽣的各科成绩降序SELECT t.*,ROW_NUMBER() OVER(PARTITION BY t.uName ORDER BY core DESC) RMFROM tb_sc t;-- 查询每个学⽣考得最好的科⽬并展⽰该科⽬的成绩SELECT *FROM(SELECT t.*,row_number() OVER(PARTITION BY t.uName ORDER BY core DESC) rmFROM tb_sc t )WHERE rm=1;-- 注：row_number() over(oartition by 分组字段 order by 排序字段)常⽤于查询所有分组并将各个窗体进⾏排序-- 在开窗函数出现之前存在着很多⽤SQL语句很难解决的问题，很多都要通过复杂的相关⼦查询或者存储过程来完成。
Oracle分析函数Over（）
Oracle分析函数Over（）阅读⽬录⼀、Over()分析函数1、rank()/dense_rank over(partition by ... order by ...)2、min()/max() over(partition by ...)3、lead()/lag() over(partition by ... order by ...) 取前⾯/后⾯第n⾏记录4、FIRST_VALUE/LAST_VALUE() OVER(PARTITION BY ...) 取⾸尾记录5、ROW_NUMBER() OVER(PARTITION BY.. ORDER BY ..) 排序（应⽤：分页）6、sum/avg/count() over(partition by ..)7、 rows/range between … preceding and … following 上下范围内求值rows between … preceding and … following⼆、其他1、NULLS FIRST/LAST 将空值字段记录放到最前或最后显⽰2、NTILE(n)3、keep(dense_rank first/last)回到顶部⼀、Over()分析函数说明：聚合函数（如sum()、max()等）可以计算基于组的某种聚合值，但是聚合函数对于某个组只能返回⼀⾏记录。
若想对于某组返回多⾏记录，则需要使⽤分析函数。
1、rank()/dense_rank over(partition by ... order by ...)说明：over()在什么条件之上; partition by 按哪个字段划分组； order by 按哪个字段排序；注意： （1）使⽤rank()/dense_rank() 时，必须要带order by否则⾮法 （2）rank()/dense_rank()分级的区别： rank(): 跳跃排序，如果有两个第⼀级时，接下来就是第三级。