http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html
http://blog.csdn.net/fu0208/article/details/7179001
OVER(PARTITION BY)函数介绍
Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是:对于每个组返回多行,而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
1:over后的写法:
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后的范围内的。
举例:
--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在这个范围内只有45
asdf 3 55 55
cfe 2 74 74
3dd 3 78 158 --78在76到80范围内有78,80,求和得158
fda 1 80 158
gds 2 92 92
ffd 1 95 190
dss 1 95 190
ddd 3 99 198
gf 3 99 198
举例:
select name,class,s, sum(s)over(order by s rows between 2 preceding and 2 following) mm from t2
adf 3 45 174 (45+55+74=174)
asdf 3 55 252 (45+55+74+78=252)
cfe 2 74 332 (74+55+45+78+80=332)
3dd 3 78 379 (78+74+55+80+92=379)
fda 1 80 419
gds 2 92 440
ffd 1 95 461
dss 1 95 480
ddd 3 99 388
gf 3 99 293
3、与over函数结合的几个函数介绍
下面以班级成绩表t2来说明其应用
t2表信息如下:
cfe 2 74
dss 1 95
ffd 1 95
fda 1 80
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;
得到的结果是:
dss 1 95 1
ffd 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1
注意:
1.在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果;
select * from
(
select name,class,s,row_number()over(partition by class order by s desc) mm from t2
)
where mm=1;
1 95 1 --95有两名但是只显示一个
2 92 1
3 99 1 --99有两名但也只显示一个
2.rank()和dense_rank()可以将所有的都查找出来:
如上可以看到采用rank可以将并列第一名的都查找出来;
rank()和dense_rank()区别:
--rank()是跳跃排序,有两个第二名时接下来就是第四名;
select name,class,s,rank()over(partition by class order by s desc) mm from t2
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 name,class,s,dense_rank()over(partition by class order by s desc) mm from t2
dss 1 95 1
ffd 1 95 1
fda 1 80 2 --连续排序(仍为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
--sum()over()的使用
select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --根据班级进行分数求和
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
first_value() over()和last_value() over()的使用
--找出这三条电路每条电路的第一条记录类型和最后一条记录类型
first_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) low,
last_value(res_type) over(PARTITION BY opr_id ORDER BY res_type rows BETWEEN unbounded preceding AND unbounded following) high
FROM rm_circuit_route
WHERE opr_id IN ('000100190000000000021311','000100190000000000021355','000100190000000000021339')
ORDER BY opr_id;
注:rows BETWEEN unbounded preceding AND unbounded following 的使用
--取last_value时不使用rows BETWEEN unbounded preceding AND unbounded following的结果
first_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) low,
last_value(res_type) over(PARTITION BY opr_id ORDER BY res_type) high
FROM rm_circuit_route
WHERE opr_id IN ('000100190000000000021311','000100190000000000021355','000100190000000000021339')
ORDER BY opr_id;
如下图可以看到,如果不使用
数据如下:
取出该电路的第一条记录,加上ignore nulls后,如果第一条是判断的那个字段是空的,则默认取下一条,结果如下所示:
lag(expresstion,<offset>,<default>)
with a as
(select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual
)
select id,name,lag(id,1,'')over(order by name) from a;
--lead() over()函数用法(取出后N行数据)
lead(expresstion,<offset>,<default>)
with a as
(select 1 id,'a' name from dual
union
select 2 id,'b' name from dual
union
select 3 id,'c' name from dual
union
select 4 id,'d' name from dual
union
select 5 id,'e' name from dual
)
select id,name,lead(id,1,'')over(order by name) from a;
--ratio_to_report(a)函数用法 Ratio_to_report() 括号中就是分子,over() 括号中就是分母
with a as (select 1 a from dual
union all
select 1 a from dual
union all
select 1 a from dual
union all
select 2 a from dual
union all
select 3 a from dual
union all
select 4 a from dual
union all
select 4 a from dual
union all
select 5 a from dual
)
select a, ratio_to_report(a)over(partition by a) b from a
order by a;
with a as (select 1 a from dual
union all
select 1 a from dual
union all
select 1 a from dual
union all
select 2 a from dual
union all
select 3 a from dual
union all
select 4 a from dual
union all
select 4 a from dual
union all
select 5 a from dual
)
select a, ratio_to_report(a)over() b from a --分母缺省就是整个占比
order by a;
with a as (select 1 a from dual
union all
select 1 a from dual
union all
select 1 a from dual
union all
select 2 a from dual
union all
select 3 a from dual
union all
select 4 a from dual
union all
select 4 a from dual
union all
select 5 a from dual
)
select a, ratio_to_report(a)over() b from a
group by a order by a;--分组后的占比
SELECT a.deptno,
a.ename,
a.sal,
a.r,
b.n,
(a.r-1)/(n-1) pr1,
percent_rank() over(PARTITION BY a.deptno ORDER BY a.sal) pr2
FROM (SELECT deptno,
ename,
sal,
rank() over(PARTITION BY deptno ORDER BY sal) r --计算出在组中的排名序号
FROM emp
ORDER BY deptno, sal) a,
(SELECT deptno, COUNT(1) n FROM emp GROUP BY deptno) b --按部门计算每个部门的所有成员数
WHERE a.deptno = b.deptno;
如下所示自己计算的pr1与通过percent_rank函数得到的值是一样的:
SELECT a.deptno,
a.ename,
a.sal,
a.r,
b.n,
c.rn,
(a.r + c.rn - 1) / n pr1,
cume_dist() over(PARTITION BY a.deptno ORDER BY a.sal) pr2
FROM (SELECT deptno,
ename,
sal,
rank() over(PARTITION BY deptno ORDER BY sal) r
FROM emp
ORDER BY deptno, sal) a,
(SELECT deptno, COUNT(1) n FROM emp GROUP BY deptno) b,
(SELECT deptno, r, COUNT(1) rn,sal
FROM (SELECT deptno,sal,
rank() over(PARTITION BY deptno ORDER BY sal) r
FROM emp)
GROUP BY deptno, r,sal
ORDER BY deptno) c --c表就是为了得到每个部门员工工资的一样的个数
WHERE a.deptno = b.deptno
AND a.deptno = c.deptno(+)
AND a.sal = c.sal;
如下,输入百分比为0.7,因为0.7介于0.6和0.8之间,因此返回的结果就是0.6对应的sal的1500加上0.8对应的sal的1600平均
SELECT ename,
sal,
deptno,
percentile_cont(0.7) within GROUP(ORDER BY sal) over(PARTITION BY deptno) "Percentile_Cont",
percent_rank() over(PARTITION BY deptno ORDER BY sal) "Percent_Rank"
FROM emp
WHERE deptno IN (30, 60);
SELECT ename,
sal,
deptno,
percentile_cont(0.6) within GROUP(ORDER BY sal) over(PARTITION BY deptno) "Percentile_Cont",
percent_rank() over(PARTITION BY deptno ORDER BY sal) "Percent_Rank"
FROM emp
WHERE deptno IN (30, 60);
注意:本函数与PERCENTILE_CONT的区别在找不到对应的分布值时返回的替代值的计算方法不同
SAMPLE:下例中0.7的分布值在部门30中没有对应的Cume_Dist值,所以就取下一个分布值0.83333333所对应的SALARY来替代
SELECT ename,
sal,
deptno,
percentile_disc(0.7) within GROUP(ORDER BY sal) over(PARTITION BY deptno) "Percentile_Disc",
cume_dist() over(PARTITION BY deptno ORDER BY sal) "Cume_Dist"
FROM emp
WHERE deptno IN (30, 60);
相关推荐
Oracle查询中的`OVER (PARTITION BY ..)`是一个窗口函数,它允许我们在数据集上执行计算,但不是在整个结果集上,而是针对每个分区。这部分功能非常强大,可以用于复杂的分析和排序任务,尤其是在处理分组数据时。在...
### Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数详解 #### 一、OVER (PARTITION BY ..) 概述 在Oracle数据库中,`OVER (PARTITION BY ...)` 是一种非常强大的功能,它允许用户在数据集上进行窗口操作。这在...
在SQL Server中,`PARTITION BY` 和 `ROW_NUMBER()` 是两种非常重要的分析函数,它们在处理大数据集时尤其有用。本文将详细讲解这两个函数的使用方法及其在实际场景中的应用。 `PARTITION BY` 关键字是分析函数的一...
在本文中,我们将详细介绍 Partition By 函数的使用方法和示例。 Partition By 函数的基本语法 --------------------------- Partition By 函数的基本语法如下所示: ``` SELECT *, ROW_NUMBER() OVER ...
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY name || idNumber || TO_CHAR(date, 'YYYYMMDD') ORDER BY id) AS rn FROM teacher t ) subquery WHERE rn = 1; ``` - **PARTITION BY** 子句中的`name || idNumber...
### `over(partition by)` 函数详解 `over(partition by)`用于将数据集划分为不同的分区或子集,并允许在每个分区内部执行特定的操作。这种方式非常适用于需要对不同类别或组的数据进行独立分析的情况。例如,在一...
在Oracle数据库中,`RANK()`, `OVER()`, `PARTITION BY` 是窗口函数的重要组成部分,它们在数据处理和分析中发挥着至关重要的作用。本文将深入探讨这些函数的用法,通过实例来帮助理解它们的功能和应用场景。 首先...
- **部门连续求和**:`SUM(sal) OVER (PARTITION BY deptno ORDER BY ename)`表示按照部门进行分组后,再按`ename`排序对薪资进行连续累加。 - **部门总和**:`SUM(sal) OVER (PARTITION BY deptno)`表示计算每个...
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees; ``` 这个查询将为每个部门的员工按工资降序排列,并为每个员工分配一个行号。 ##### 示例2:使用SUM()计算...
SUM(SAL) OVER (PARTITION BY DEPT_NO) AS DEPT_TOTAL_SALARY FROM EMPLOYEE; ``` 在这个示例中,我们使用了SUM函数结合OVER子句来计算每个部门的总薪水。可以看到,通过PARTITION BY DEPT_NO,我们可以为每个部门...
而在分析函数中,AVG会根据PARTITION BY和ORDER BY子句对每个分组或排序后的子集计算平均值。 2. SUM ( [ DISTINCT | ALL ] expr ) OVER ( analytic_clause ) SUM函数用于求和,同样有聚合和分析两种形式。聚合...
在使用 `PARTITION BY` 进行窗口函数计算时,特别需要注意的是,聚合函数如 `SUM` 在 `PARTITION BY` 后的结果集上运行时,不是一次性计算整个分组的值,而是对每一行数据逐一累加。例如,SQL 示例中的 `MIN(a.num) ...
- **`OVER`子句**:`OVER`子句用于指定分析函数的作用范围,它可以包括`PARTITION BY`子句和`ORDER BY`子句。 - `PARTITION BY`:用于将数据分为不同的分区,每个分区独立进行计算。 - `ORDER BY`:用于对数据进行...
分析函数的语法结构比较复杂,但多数函数都具有相同的语法结构,所以先在之前进行统一介绍,后续单个函数介绍时就不过多说明函数语法结构了。 基本上所有的分析函数均是这种格式: 函数名称 ([参数]) OVER ...
下面将详细介绍 `OVER` 函数以及如何利用它来执行递归汇总计算。 #### 1. OVER 函数详解 `OVER` 函数允许指定分析函数的工作窗口,即在哪些数据范围内进行计算。这些窗口可以是静态的也可以是动态变化的,具体取决...
ROW_NUMBER() OVER (PARTITION BY COLUMN ORDER BY COLUMN) 其中,PARTITION BY COLUMN 是用于分组的列名,ORDER BY COLUMN 是用于排序的列名。ROW_NUMBER() 函数从 1 开始,为每一条分组记录返回一个数字。 示例...
ROW_NUMBER() OVER (PARTITION BY f_deptid ORDER BY f_salary DESC) AS f_order, f_deptname, f_employeename, f_salary FROM (SELECT a.f_employeeid, a.f_deptid, b.f_deptname, a.f_...
这里,`SUM(sal) OVER (PARTITION BY deptno)`计算每个部门的总薪水,而`SUM(sal) OVER (PARTITION BY deptno ORDER BY ename)`则在每个部门内按照员工姓名排序并累加薪水。 此外,我们还可以通过改变`ORDER BY`...