分析函数之分组排序
1.分析函数释义
row_number()OVER(PARTITIONBYCOL1ORDERBYCOL2)表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)。
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。
1> row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）;
2> rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）;
3> dense_rank()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的.
4> lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，是一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。
函数语法：
OPAP函数语法四部分：
1.function 本身用于对窗口中的数据进行操作；
2.partitioning clause 用于将结果集分区；
3.order by clause 用于对分区中的数据进行排序；
4.windowing clause 用于定义function在其上操作的行的集合，即function所影响的范围；
rank()/dense_rank()
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当，
【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。
【说明】Oracle分析函数
ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
row_number() 返回的主要是“行”的信息，并没有排名
【参数】
【说明】Oracle分析函数
主要功能：用于取前几名，或者最后几名等
sum(...) over ...
【功能】连续求和分析函数
【参数】具体参示例
【说明】Oracle分析函数
lag()和lead()
【语法】
lag(EXPR,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )
LEAD(EXPR,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
lead （） 下一个值 lag（） 上一个值
【参数】
EXPR是从其他行返回的表达式
OFFSET是缺省为1 的正数，表示相对行数。希望检索的当前行分区的偏移量
DEFAULT是在OFFSET表示的数目超出了分组的范围时返回的值。
【说明】Oracle分析函数
2.环境准备
CREATETABLETEST_Y(
IDVARCHAR2(32)PRIMARYKEY,
NAMEVARCHAR2(20),
AGENUMBER(3),
DETAILSVARCHAR2(1000)
);
INSERTINTOTEST_YVALUES(SYS_GUID(),'海子',20,'面朝大海，春暖花开');
INSERTINTOTEST_YVALUES(SYS_GUID(),'海子',30,'面朝大海，春暖花开');
INSERTINTOTEST_YVALUES(SYS_GUID(),'贝多芬',43,'致爱丽丝');
INSERTINTOTEST_YVALUES(SYS_GUID(),'普希金',34,'假如生活欺骗了你');
INSERTINTOTEST_YVALUES(SYS_GUID(),'杨过',23,'黯然销魂掌');
INSERTINTOTEST_YVALUES(SYS_GUID(),'小龙女',32,'神雕侠侣');
INSERTINTOTEST_YVALUES(SYS_GUID(),'李清照',21,'寻寻觅觅、冷冷清清');
INSERTINTOTEST_YVALUES(SYS_GUID(),'周芷若',18,'峨眉');
INSERTINTOTEST_YVALUES(SYS_GUID(),'赵敏',18,'自由');
INSERTINTOTEST_YVALUES(SYS_GUID(),'张无忌',20,'倚天屠龙记');
INSERTINTOTEST_YVALUES(SYS_GUID(),'张无忌',30,'倚天屠龙记');
commit;
SELECT * FROM TEST_Y;
3.row_number() over(partition by col1 order by col2)
---查询所有姓名，如果同名则按年龄降序
select name,age,details,row_number() over(partition by name order by age desc) from test_y;
通过上面的语句可知，row_number() over(partition by col1 order by col2)中是按照name字段分组，按age字段排序的。
如果是只需要查询出不重复的姓名即可，则可以使用如下语句：
select * from ( select name,age,details,(row_number() over(partition by name order by age desc)) as numid from test_y ) where numid=1;
由查询结果可知，姓名相同年龄小的数据备过略掉了。可以使用row_number() over(partition by col1 order by col2) 对部分子查询进行去重处理。
----上面查询还有种方法实现：select name,max(age),max (details) from test_y group by name; 这种方法有个问题，就是max的age和max的details列不一定能匹配，正确的写法是只保留1列，要么age列要么details列，如果你是想获取max的age和max的details这当然是没有错的。
4.rank() over(partition by col1 order by col2)
----跳跃查询
----先随便插入两条重复数据
INSERTINTOTEST_YVALUES(SYS_GUID(),'普希金',34,'假如生活欺骗了你');
INSERTINTOTEST_YVALUES(SYS_GUID(),'杨过',23,'黯然销魂掌');
INSERTINTOTEST_YVALUES(SYS_GUID(),'普希金',34,'假如生活欺骗了你');
INSERTINTOTEST_YVALUES(SYS_GUID(),'杨过',23,'黯然销魂掌');
INSERTINTOTEST_YVALUES(SYS_GUID(),'普希金',39,'假如生活欺骗了你');
INSERTINTOTEST_YVALUES(SYS_GUID(),'杨过',29,'黯然销魂掌');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '普希金',40 ,'假如生活欺骗了你');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '杨过',30 ,'黯然销魂掌');
commit;
select name,age,details,rank() over (partition by name order by age) from test_y;
由查询结果可知，相同的并列，下一个则跳跃到并列所在的序号后。如果两个并列1，那么下一个则直接排为3，跳过2。
5.dense_rank() over(partition by col1 order by col2)
----连续排序，当有多个并列排序是，下一个任然连续有序
select name,age,details,dense_rank() over (partition by name order by age ) from test_y;
由查询结果可知，当两个并列为1时，下一个任然连续有序为2，不跳跃到3。
6.lag(exp_str,offset,defval) over(partition by name order by age)
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率
Lag和Lead偏移量函数，其用途是：可以查出同一字段下一个值或上一个值，并作为新列存在表中
exp_str 返回显示的字段；
offset是exp_str字段的偏移量，默认是1，如offset=1 表示返回当前exp_str的下一个exp_str；
defval 当该函数无值可用的情况下返回改值。
(1)
select name,age,details,lag(name,2,'zhong') over (partition by name order by age desc) from test_y;
(2)
select name,age,details,lag(name,2,'zhong') over ( order by age desc) from test_y;
上面的这个查询没有用分组，直接进行排序，然后在最大的年纪出插入两行"zhong"，导致有错行。
7.lead(exp_str,offset,defval) over (partition by name order by age)
exp_str 返回显示的字段；
offset是exp_str字段的偏移量，默认是1，如offset=1 表示返回当前exp_str的下一个exp_str；
defval 当该函数无值可用的情况下返回改值。
(1)
select name,age,details,lead(name,2,'zhong') over (partition by name order by age desc) from test_y;
(2)
select name,age,details,lead(name,2,'zhong') over ( order by age desc) from test_y;
8.sum(col1) over([partition by col2] [order by col3])
(1)
select name,age,details,row_number() over(partition by name order by age desc),sum (age) over(partition by name order by age desc) from test_y;
(2)
select name,age,details,row_number() over(partition by name order by age desc),sum (age) over(partition by name) from test_y;
(3) nulls last 是把空值放在最后的意思
select name,age,details,row_number() over(partition by name order by age desc),sum (age) over(order by age desc nulls last) from test_y;
(4)
select name,age,details,sum(age) over() from test_y;
9.分析函数over与开窗函数
常见的分析函数如下：
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
开窗函数：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
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
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)
10.分析函数与开窗函数示列二：
----环境准备：
createtableearnings--打工赚钱表
(
earnmonthvarchar2(6),--打工月份
areavarchar2(20),--打工地区
snovarchar2(10),--打工者编号
snamevarchar2(20),--打工者姓名
timesint,--本月打工次数
singleincomenumber(10,2),--每次赚多少钱
personincomenumber(10,2)--当月总收入
)
insertintoearningsvalues('12','北平','511601','大魁',11,30,11*30);
insertintoearningsvalues('12','北平','511602','大凯',8,25,8*25);
insertintoearningsvalues('12','北平','511603','小东',30,6.25,30*6.25);
insertintoearningsvalues('12','北平','511604','大亮',16,8.25,16*8.25);
insertintoearningsvalues('12','北平','511605','贱敬',30,11,30*11);
insertintoearningsvalues('12','金陵','511301','小玉',15,12.25,15*12.25);
insertintoearningsvalues('12','金陵','511302','小凡',27,16.67,27*16.67);
insertintoearningsvalues('12','金陵','511303','小妮',7,33.33,7*33.33);
insertintoearningsvalues('12','金陵','511304','小俐',0,18,0);
insertintoearningsvalues('12','金陵','511305','雪儿',11,9.88,11*9.88);
insertintoearningsvalues('01','北平','511601','大魁',0,30,0);
insertintoearningsvalues('01','北平','511602','大凯',14,25,14*25);
insertintoearningsvalues('01','北平','511603','小东',19,6.25,19*6.25);
insertintoearningsvalues('01','北平','511604','大亮',7,8.25,7*8.25);
insertintoearningsvalues('01','北平','511605','贱敬',21,11,21*11);
insertintoearningsvalues('01','金陵','511301','小玉',6,12.25,6*12.25);
insertintoearningsvalues('01','金陵','511302','小凡',17,16.67,17*16.67);
insertintoearningsvalues('01','金陵','511303','小妮',27,33.33,27*33.33);
insertintoearningsvalues('01','金陵','511304','小俐',16,18,16*18);
insertintoearningsvalues('01','金陵','511305','雪儿',11,9.88,11*9.88);
commit;
----表内容如下：
SELECT * FROM earnings;
1> sum函数，统计总和
----按照月份，统计每个地区总收入
select earnmonth,area,sum(personincome) from earnings group by earnmonth,area;
2> rollup函数
----按照月份，地区统计收入
select earnmonth,area,sum(personincome) from earnings group by rollup(earnmonth,area);
3> cube函数
----按照月份，地区进行收入总汇总
select earnmonth,area,sum(personincome) from earnings group by cube(earnmonth,area) order by earnmonth,area nulls last;
----小结：
sum是统计求和的函数。
group by 是分组函数，按照earnmonth和area先后次序分组。
以上三例都是先按照earnmonth分组，在earnmonth内部再按area分组，并在area组内统计personincome总合。
group by 后面什么也不接就是直接分组。
group by 后面接 rollup 是在纯粹的 group by 分组上再加上对earnmonth的汇总统计。
group by 后面接 cube 是对earnmonth汇总统计基础上对area再统计。
另外那个 nulls last 是把空值放在最后。
rollup和cube区别：
如果是ROLLUP(A, B, C)的话，GROUP BY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUP BY操作。
如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序
(A、B、C)
(A、B)
(A、C)
(A)，
(B、C)
(B)
(C)，
最后对全表进行GROUP BY操作。
4> grouping函数
在以上例子中，是用rollup和cube函数都会对结果集产生null，这时候可用grouping函数来确认，该记录是由哪个字段得出来的
grouping函数用法，带一个参数，参数为字段名，结果是根据该字段得出来的就返回1，反之返回0
select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,
decode(grouping(area),1,'全部地区',area) 地区,
sum(personincome) 总金额
from earnings
group by cube(earnmonth,area)
order by earnmonth,area nulls last;
5> rank() over开窗函数
----按照月份、地区，求打工收入排序
select earnmonth 月份,area 地区,sname 打工者,personincome 收入,
rank() over(partition by earnmonth,area order by personincome desc) 排名
from earnings;
6> dense_rank() over开窗函数
----按照月份、地区，求打工收入排序2
select earnmonth 月份,area 地区,sname 打工者,personincome 收入,
dense_rank() over(partition by earnmonth,area order by personincome desc) 排名
from earnings;
7> row_number() over开窗函数
----按照月份、地区，求打工收入排序3
select earnmonth 月份,area 地区,sname 打工者,personincome 收入,
row_number() over(partition by earnmonth,area order by personincome desc) 排名
from earnings;
----rank,dense_rank,row_number的区别：
通过(5)(6)(7)发现，结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，比如两个第二，那么没有第三接下来就是第四；但是dense_rank不会跳跃式的排名，两个第二接下来还是第三；row_number最牛，即使两个数据相同，排名也不一样。
8> sum累计求和
----根据月份求出各个打工者收入总和，按照收入由少到多排序。
select earnmonth 月份,area 地区,sname 打工者,personincome 收入,
sum(personincome) over(partition by earnmonth,area order by personincome) 总收入
from earnings;
9> max,min.avg和sum函数综合运用
----按照月份和地区求打工收入最高值，最低值，平均值和总额
select distinct earnmonth 月份,area 地区,
max(personincome) over(partition by earnmonth,area) 最高值,
min(personincome) over(partition by earnmonth,area) 最低值,
avg(personincome) over(partition by earnmonth,area) 平均值,
sum(personincome) over(partition by earnmonth,area) 总额
from earnings;
10> lag和lead函数
----求出每个打工者上个月和下个月有没有挣钱(personincome大于零为挣钱)
select earnmonth 本月,sname 打工者,
lag(decode(nvl(personincome,0),0,'没挣','挣了'),1,0) over (partition by sname order by earnmonth) 上月,
lead(decode(nvl(personincome,0),0,'没挣','挣了'),1,0) over(partition by sname order by earnmonth) 下月
from earnings;
说明：Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙），原来没有分析函数的时候采用子查询方法，但是比较麻烦。
语法如下：
lag(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
lead(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
其中：
value_expression：可以是一个字段或一个内建函数。
offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，
default就是用于处理这样的信息，默认为空。
再讲讲所谓的开窗函数，开窗函数就是 over([query_partition_clase] order_by_clause)。比如说，我采用sum求和，rank排序等等，但是我根据什么来呢？over提供一个窗口，可以根据什么什么分组，就用partition by，然后在组内根据什么什么进行内部排序，就用 order by。
reference /yinshan33/article/details/18738229
/Ronger/archive//12/28/2304900.html
http://love-flying-/blog/573083
来自 “ ITPUB博客 ” ，链接：/30130773/viewspace-2122594/，如需转载，请注明出处，否则将追究法律责任。
