文档章节

OVER(PARTITION BY)函数大全

苗永超
 苗永超
发布于 2017/09/28 21:50
字数 1370
阅读 18
收藏 0

      问题场景:  

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

  后来SQL的解决方法就为:

SELECT T.CHANNEL AS PATTERN,
       COUNT(T.TRANSACTIONKEY) AS T_COUNT,
       SUM(T.AMT) AS T_AMT,
       ROUND(100 * SUM(T.AMT) / SUM(SUM(T.AMT)) OVER(PARTITION BY 1), 2) AS AMT_PERCENT,
       ROUND(100 * COUNT(T.TRANSACTIONKEY) / SUM(COUNT(T.TRANSACTIONKEY)) OVER(PARTITION BY 1),2) AS COUNT_PERCENT
  FROM XX(表名) T
 WHERE T.PARTY_ID = '100579050'
 GROUP BY T.CHANNEL

 

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

  • 简介

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

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

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

create table T2_TEMP(
    NAME varchar2(10) primary key,
    CLASS varchar2(10),
    SROCE NUMBER 
)

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('cfe', '2', 74);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('dss', '1', 95);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('ffd', '1', 95);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('fda', '1', 80);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('gds', '2', 92);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('gf', '3', 99);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('ddd', '3', 99);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('adf', '3', 45);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('asdf', '3', 55);

insert into T2_TEMP (NAME, CLASS, SROCE)
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)、查询每个班的第一名的成绩:如下 

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;

  结果为:

dss        1        95        1
ffd        1        95        1
gds        2        92        1
gf         3        99        1
ddd        3        99        1

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

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()是跳跃排序,有两个第二名时接下来就是第四名。

  求班级成绩排名:

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是连续排序,有两个第二名时仍然跟着第三名

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()的使用

  根据班级进行分数求和

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()的使用 

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;

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()的使用

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虽然也具有分组功能,但同时也具有其他的高级功能。

本文转载自:http://www.cnblogs.com/lcngu/p/5335170.html

共有 人打赏支持
苗永超
粉丝 15
博文 57
码字总数 14075
作品 0
海淀
后端工程师
私信 提问
MySQL错误代码大全

服务器错误信息来自下述源文件: · 错误消息信息列在share/errmsg.txt文件中。“%d”和“%s”分别代表编号和字符串,显示时,它们将被消息值取代。 · 错误值列在share/errmsg.txt文件中,用...

Alexdevlin
2016/05/26
71
0
oracle的分析函数‘over’

分析函数语法: 例: sum(sal) over (partition by deptno order by ename) new_alias sum就是函数名 (sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm) over...

0o清风徐来o0
2012/10/22
0
0
Oracle分析函数

文章转载自「开发者圆桌」一个关于开发者入门、进阶、踩坑的微信公众号 数据库中的函数封装了一些通用的功能,例如日期类型和字符串类型之间的转换,每个数据库系统都内置了一些函数,当然用...

开发者圆桌
2017/03/10
0
0
Hive SQL主要开窗函数用法介绍

在开窗函数出现之前,存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在2003年ISO SQL标准加入了开窗函数,开窗函数的使用使得这...

yntmdr
2018/08/01
0
0
GreenPlum常用SQL

下面整理记录一些时常会使用到的SQL,也方便以后查阅: 函数操作举例 例子一 删除函数: DROP FUNCTION function_name(varchar); 创建函数: CREATE OR REPLACE FUNCTION convert_grade(grade...

mybabe0312
2016/08/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

[walminer bug分析]checkpoint wal记录的lsn与checkpoint记录的redo点的关系

问题背景 walminer工具的用户反馈来一个问题,不管添加了多少wal日志,想要的wal文件的解析结果总是有未解析出的部分。 分析问题 分析问题发现,checkpoint wal记录之后对某个数据page进行修...

movead
27分钟前
2
0
OSChina 周二乱弹 —— 金 冈刂 犭良

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @蓝瞳 :分享骇物乐团的单曲《I'll be the one》: 《I'll be the one》- 骇物乐团 手机党少年们想听歌,请使劲儿戳(这里) @尾生 :工作使人...

小小编辑
33分钟前
133
8
python中类方法和静态方法区别

面相对象程序设计中,类方法和静态方法是经常用到的两个术语。 逻辑上讲:类方法是只能由类名调用;静态方法可以由类名或对象名进行调用。 在C++中,静态方法与类方法逻辑上是等价的,只有一...

xiangyunyan
今天
10
0
Hibernate SQLite方言

以下代码有参考过github上国外某位大佬的,在发文的最新稳定版Hibernate上是可用的,有时间再仔细分析一下 import org.hibernate.dialect.Dialect;import org.hibernate.dialect.function.S...

CHONGCHEN
今天
4
0
CentOS 7 MariaDB搭建主从服务器

本文编写环境为CentOS7。确保关闭SELinux,关闭防火墙或者防打开指定端口。具体信息如下 #master[root@promote ~]# cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) [r...

白豆腐徐长卿
今天
14
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部