OVER(PARTITION BY)函数介绍

11人阅读 评论(0) 收藏 举报
分类:
  • 问题场景  

  最近在项目中遇到了对每一个类型进行求和并且求该类型所占的比例,当时考虑求出每种类型的和,并在java中分别对每一种类型的和与总和相除求出所占比例。后来,想到这样有点麻烦,并且项目中持久层使用的是iBatis框架,所有考虑从SQL方面进行入手来简化这个问题。

  后来SQL的解决方法就为:

复制代码
1 SELECT T.CHANNEL AS PATTERN,
2        COUNT(T.TRANSACTIONKEY) AS T_COUNT,
3        SUM(T.AMT) AS T_AMT,
4        ROUND(100 * SUM(T.AMT) / SUM(SUM(T.AMT)) OVER(PARTITION BY 1), 2) AS AMT_PERCENT,
5        ROUND(100 * COUNT(T.TRANSACTIONKEY) / SUM(COUNT(T.TRANSACTIONKEY)) OVER(PARTITION BY 1),2) AS COUNT_PERCENT
6   FROM XX(表名) T
7  WHERE T.PARTY_ID = '100579050'
8  GROUP BY T.CHANNEL
复制代码

  看到这里自己很佩服SQL的强大,于是刨根问底,深入研究了一番Oracel的OVER(PARTITION BY)函数。

  • 简介

  开窗函数,Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。

  开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。

  下面的测试用例数据语句如下: 

复制代码
 1 create table T2_TEMP(
 2     NAME varchar2(10) primary key,
 3     CLASS varchar2(10),
 4     SROCE NUMBER 
 5 )
 6 
 7 insert into T2_TEMP (NAME, CLASS, SROCE)
 8 values ('cfe', '2', 74);
 9 
10 insert into T2_TEMP (NAME, CLASS, SROCE)
11 values ('dss', '1', 95);
12 
13 insert into T2_TEMP (NAME, CLASS, SROCE)
14 values ('ffd', '1', 95);
15 
16 insert into T2_TEMP (NAME, CLASS, SROCE)
17 values ('fda', '1', 80);
18 
19 insert into T2_TEMP (NAME, CLASS, SROCE)
20 values ('gds', '2', 92);
21 
22 insert into T2_TEMP (NAME, CLASS, SROCE)
23 values ('gf', '3', 99);
24 
25 insert into T2_TEMP (NAME, CLASS, SROCE)
26 values ('ddd', '3', 99);
27 
28 insert into T2_TEMP (NAME, CLASS, SROCE)
29 values ('adf', '3', 45);
30 
31 insert into T2_TEMP (NAME, CLASS, SROCE)
32 values ('asdf', '3', 55);
33 
34 insert into T2_TEMP (NAME, CLASS, SROCE)
35 values ('3dd', '3', 78);
复制代码

  1、over函数的写法:

  over(partition by class order by sroce) 按照sroce排序进行累计,order by是个默认的开窗函数,按照class分区。

  2、开窗的窗口范围:

  over(order by sroce range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的范围内的。

  over(order by sroce rows between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。

  3、与over()函数结合的函数的介绍

  (1)、查询每个班的第一名的成绩:如下 

1 SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

  结果为:

复制代码
1 得到的结果是:
2 dss        1        95        1
3 ffd        1        95        1
4 gds        2        92        1
5 gf         3        99        1
6 ddd        3        99        1
复制代码

  注意:在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果。

1 SELECT * FROM (select t.name,t.class,t.sroce,row_number() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

  结果为:

dss      1        95        1  
gfs      2        92        1
ddd      3        99        1 

  可以看出,本来第一名是两个人的并列,结果只显示了一个。

  (2)、rank()和dense_rank()可以将所有的都查找出来,rank可以将并列第一名的都查找出来;rank()和dense_rank()区别:rank()是跳跃排序,有两个第二名时接下来就是第四名。

  求班级成绩排名:

1 select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

  查询结果:

复制代码
dss        1        95        1
ffd        1        95        1
fda        1        80        3
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        3
asdf       3        55        4
adf        3        45        5
复制代码

  dense_rank()l是连续排序,有两个第二名时仍然跟着第三名

1 select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

  查询结果:

复制代码
dss        1        95        1
ffd        1        95        1
fda        1        80        2 
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        2
asdf       3        55        3
adf        3        45        4
复制代码

  3、sum()over()的使用

  根据班级进行分数求和

1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
复制代码
dss        1        95        190  --由于两个95都是第一名,所以累加时是两个第一名的相加
ffd        1        95        190 
fda        1        80        270  --第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376
复制代码

  4、first_value() over()和last_value() over()的使用 

1 select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
2 select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

  分别求出第一个和最后一个成绩。

  5、sum() over()的使用

1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

  求出班级的总分。

  下面还有很多用法,就不一一列举了,简单介绍一下,和上面用法类似:

 

  count() over(partition by ... order by ...):求分组后的总数。
  max() over(partition by ... order by ...):求分组后的最大值。
  min() over(partition by ... order by ...):求分组后的最小值。
  avg() over(partition by ... order by ...):求分组后的平均值。
  lag() over(partition by ... order by ...):取出前n行数据。  

  lead() over(partition by ... order by ...):取出后n行数据。

  ratio_to_report() over(partition by ... order by ...):Ratio_to_report() 括号中就是分子,over() 括号中就是分母。

  percent_rank() over(partition by ... order by ...):

  6、over partition by与group by的区别:

  group by是对检索结果的保留行进行单纯分组,一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition by虽然也具有分组功能,但同时也具有其他的高级功能。

查看评论

oracle的分析函数 over(Partition by...)

作为合格的dba,这一块还是需要看懂和熟练应用的,
  • fengeh
  • fengeh
  • 2014-05-03 21:10:16
  • 1496

over(Partition by…) 一个超级好用的特有(开窗)函数。

over(Partition by…) 一个超级牛皮的特有函数。分析函数over 及开窗函数一:分析函数over Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚...
  • zhushuhai331
  • zhushuhai331
  • 2016-01-06 10:32:08
  • 3594

PL/SQL函数 over(partition by col)

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编...
  • yhb315279058
  • yhb315279058
  • 2014-12-17 09:21:27
  • 2579

【SQL 编程你也行】SQL Server新功能之函数:count函数(2012、2014的over partition by order by)

上一篇我们讲了count聚合函数的基本功能,以及在2005、2008版本中新增的over partition by来实现分组计数的功能。 在SQL Server 2012、2014中进一步加强了c...
  • yupeigu
  • yupeigu
  • 2016-01-11 14:02:28
  • 2707

hive OVER(PARTITION BY)函数用法

OVER(PARTITION BY)函数用法  2010年10月26日 OVER(PARTITION BY)函数介绍 开窗函数                Oracle从8.1....
  • sherri_du
  • sherri_du
  • 2016-11-23 21:19:50
  • 16083

mysql 做出开窗函数的效果row number() OVER(PARTITION BY)

今天朋友发来一段Oracle的代码,要求改成mysql。代码里面有row number() OVER(PARTITION BY)和DECODE这些不通用的函数。 decode是翻译函数,这个用cas...
  • u013677636
  • u013677636
  • 2016-11-04 15:06:34
  • 3259

【Orcale】分析函数 OVER(PARTITION BY... ORDER BY...)的讲解

OVER(PARTITION BY... ORDER BY...)的使用说明和意义 这个函数长这个样子:OVER(PARTITION BY... ORDER BY...) 顾名思义,PARTITION...
  • East_MrChiu
  • East_MrChiu
  • 2017-04-15 15:00:46
  • 4923

sql实现oracle分析函数功能 over partition by

oracle、hive都比较好支持分析函数(如sum() over partition by),db2就一般,mysql、sqlite直接就不支持。 因此有必要学会用SQL实现分析函数。 sum()...
  • textboy
  • textboy
  • 2016-04-12 15:12:34
  • 1070

Oracle高级查询之over(partition by..)

Oracle高级查询之over(partition by..)
  • xiaokui_wingfly
  • xiaokui_wingfly
  • 2015-02-26 23:22:44
  • 1784

postgresql partition by over 窗口函数(分组求数量 并求某个条件之上的数量和)

-- 角色活越等级分布,并累加大于(含当前等级)的角色数量 select server_id,lv, os,rid,chn,num , sum(num) over(PARTITION BY serve...
  • liangrui1988
  • liangrui1988
  • 2018-03-29 17:20:32
  • 55
    个人资料
    持之以恒
    等级:
    访问量: 17万+
    积分: 4566
    排名: 8087
    最新评论