一:分析函数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 14 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 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 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列值
=====
select * from test
数据:
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6
---将B栏位值相同的对应的C 栏位值加总
select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
from test
A B C C_SUM
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6
---如果不需要已某个栏位的值分割,那就要用 null
eg: 就是将C的栏位值summary 放在每行后面
select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum
from test
A B C C_SUM
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17
求个人工资占部门工资的百分比
SQL> select * from salary;
NAME DEPT SAL
---------- ---- -----
a 10 2000
b 10 3000
c 10 5000
d 20 4000
SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;
NAME DEPT SAL PERCENT
---------- ---- ----- ----------
a 10 2000 20
b 10 3000 30
c 10 5000 50
d 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
例如:对于以下列
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查询中的`OVER (PARTITION BY ..)`是一个窗口函数,它允许我们在数据集上执行计算,但不是在整个结果集上,而是针对每个分区。这部分功能非常强大,可以用于复杂的分析和排序任务,尤其是在处理分组数据时。在...
### Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数详解 #### 一、OVER (PARTITION BY ..) 概述 在Oracle数据库中,`OVER (PARTITION BY ...)` 是一种非常强大的功能,它允许用户在数据集上进行窗口操作。这在...
RANK() OVER (PARTITION BY s.CLASS ORDER BY SUM(s.DEGREE) DESC) AS RANK FROM SCOTT.SCORE s; ``` 6. **条件表达式(CASE Statement)**:允许根据不同的条件返回不同的值,常用于复杂的筛选和计算。例如,根据...
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...
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num FROM employees; ``` 这个查询将为每个部门的员工按工资降序排列,并为每个员工分配一个行号。 ##### 示例2:使用SUM()计算...
- `ROW_NUMBER() OVER (PARTITION BY superid ORDER BY id)` 为每个superid分组内部按id排序并分配唯一的行号。 - `RANK() OVER (ORDER BY superid)` 和 `ROW_NUMBER()` 类似,但并列排名时会跳过编号。 - `DENSE...
在Oracle数据库中,`RANK()`, `OVER()`, `PARTITION BY` 是窗口函数的重要组成部分,它们在数据处理和分析中发挥着至关重要的作用。本文将深入探讨这些函数的用法,通过实例来帮助理解它们的功能和应用场景。 首先...
row_number() over ([partition by col1] order by col2) as 别名 ``` 其中,`partition by col1` 用于指定分区的列,`order by col2` 用于指定排序的列。 在使用 row_number()over 函数时,需要注意以下几点: * ...
通过灵活运用`partition by`、`order by`和`windowing-clause`,可以创建出非常复杂的分析逻辑,满足各种业务需求。 总之,Oracle分析函数是数据库查询的重要工具,尤其在数据仓库和商业智能领域,它能够帮助用户...
聚合函数(over (partition by 字段1, 字段2, 字段3 order by 字段 desc/asc range between 数值/date preceding/flowing and 数值/date preceding/flowing)) ``` 这里,`聚合函数`可以是`sum`, `count`, `avg`, `...
SUM(SAL) OVER (PARTITION BY DEPT_NO) AS DEPT_TOTAL_SALARY FROM EMPLOYEE; ``` 在这个示例中,我们使用了SUM函数结合OVER子句来计算每个部门的总薪水。可以看到,通过PARTITION BY DEPT_NO,我们可以为每个部门...
SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) as cumulative_sales FROM sales_table; ``` 3. **分析函数OVER解析** `OVER`子句是分析函数的关键部分,它可以接受两个主要部分:`...
例如,在文档中的例子select deptno, row_number() over (partition by deptno order by sal desc) rw, ename, sal from emp中,就是按照部门编号partition by deptno进行分组,并在每个分组内按薪资降序order by ...
SUM(LOCAL_FARE) OVER (PARTITION BY NET_TYPE ORDER BY BILL_MONTH ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as MOVING_SUM, AVG(LOCAL_FARE) OVER (PARTITION BY NET_TYPE ORDER BY BILL_MONTH ROWS BETWEEN ...
这里,`SUM(sal) OVER (PARTITION BY deptno)`计算每个部门的总薪水,而`SUM(sal) OVER (PARTITION BY deptno ORDER BY ename)`则在每个部门内按照员工姓名排序并累加薪水。 此外,我们还可以通过改变`ORDER BY`...
- **`OVER`子句**:`OVER`子句用于指定分析函数的作用范围,它可以包括`PARTITION BY`子句和`ORDER BY`子句。 - `PARTITION BY`:用于将数据分为不同的分区,每个分区独立进行计算。 - `ORDER BY`:用于对数据进行...
分析函数如RANK() OVER(PARTITION BY ... ORDER BY ...)可以实现分组内的排名。此外,Oracle还支持复杂的查询逻辑和约束,如CHECK约束,可以用来确保数据的完整性。 总的来说,Oracle数据库提供了强大而灵活的...
- **部门连续求和**:`SUM(sal) OVER (PARTITION BY deptno ORDER BY ename)`表示按照部门进行分组后,再按`ename`排序对薪资进行连续累加。 - **部门总和**:`SUM(sal) OVER (PARTITION BY deptno)`表示计算每个...