oracle中的over函数怎么用的，什么意思

如题所述

推荐答案 2019-11-13

over函数是oracle中的分析函数，分析函数是对行集组进行聚合计算，但是不像普通聚合仗函数那样每组只返回一个值，分析函数可以为每组返回多个值。

使用方法为：over(partition by排列名1 order by 列名2 )，括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。

扩展资料

在SQL语句中，很多查询语句需要进行GROUP BY分组汇总，但是一旦经过分组，SELECT返回的记录孢数就会减少。为了保留所有原始行记录，并且仍可以进行分组数据分析，分析函数应运而生。

oracle数据库函数，分析函数用于为行定义一个窗口，对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

RANK（）也为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如两个相同的行生成序号3，那么接下来会生成序号。

DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。也就是说如果两个相同的行生成序号，那么接下来生成的序号还是。

参考资料来源：百度百科-分析函数

温馨提示：答案为网友推荐，仅供参考

当前网址：http://88.wendadaohang.com/zd/gMtgSaS1g.html

其他回答

第1个回答推荐于2017-11-23

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
一、 over函数
over函数指定了分析函数工作的数据窗口的大小，这个数据窗口大小可能会随着行的变化而变化，例如：
over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数
over(partition by deptno) 按照部门分区
over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150的数据记录
over(order by salary rows between 50 perceding and 150 following)前50行，后150行
over（order by salary rows between unbounded preceding and unbounded following）所有行
over（order by salary range between unbounded preceding and unbounded following）所有行
二、 sum函数
功能描述：该函数计算组中表达式的累积和。
SAMPLE：下例计算同一经理下员工的薪水累积值
SELECT manager_id, last_name, salary,
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in (101,103,108);
三、应用实例
1，测试环境设置
设有销售表t_sales (subcompany,branch,region,customer,sale_qty); 存储客户的销售明细，记录如下所示。
Subcompany Branch Region Customer Sale_qty
北京分公司北京经营部片区1 客户1 1
北京分公司北京经营部片区1 客户1 1
北京分公司北京经营部片区1 客户2 1
北京分公司北京经营部片区1 客户2 1
北京分公司北京经营部片区2 客户1 1
北京分公司北京经营部片区2 客户1 1
北京分公司北京经营部片区2 客户2 1
北京分公司北京经营部片区2 客户2 1
北京分公司其他经营部片区1 客户1 1
北京分公司其他经营部片区1 客户1 1
北京分公司其他经营部片区1 客户2 1
北京分公司其他经营部片区1 客户2 1
北京分公司其他经营部片区2 客户1 1
北京分公司其他经营部片区2 客户1 1
北京分公司其他经营部片区2 客户2 1
北京分公司其他经营部片区2 客户2 1
create table t_sales(
subcompany varchar2(40),
branch varchar2(40),
region varchar2(40),
customer varchar2(40),
sale_qty numeric(18,4)
);

comment on table t_sales is '销售表，分析函数测试';
comment on column t_sales.subcompany is '分公司';
comment on column t_sales.branch is '经营部';
comment on column t_sales.region is '片区';
comment on column t_sales.customer is '客户';
comment on column t_sales.sale_qty is '销售数量';

2，问题提出
现在要求给出销售汇总报表，报表中需要提供的数据包括客户汇总，和客户在其上级机构中的销售比例。
Subcompany Branch Region Customer Sale_qty Rate
北京分公司北京经营部片区1 客户1 2 50％
北京分公司北京经营部片区1 客户2 2 50％
北京分公司北京经营部片区1 小计 4 50％
北京分公司北京经营部片区2 客户1 2 50％
北京分公司北京经营部片区2 客户2 2 50％
北京分公司北京经营部片区2 小计 4 50％
北京分公司北京经营部小计小计 8 50％
北京分公司北京经营部片区1 客户1 2 50％
北京分公司北京经营部片区1 客户2 2 50％
北京分公司北京经营部片区1 小计 4 50％
北京分公司北京经营部片区2 客户1 2 50％
北京分公司北京经营部片区2 客户2 2 50％
北京分公司北京经营部片区2 小计 4 50％
北京分公司北京经营部小计小计 8 50％
北京分公司小计小计小计 16 100％
3，解决方案（方案1）
首先我们可以使用oracle对group by 的扩展功能rollup得到如下的聚合汇总结果。
select
subcompany,
branch,
region,
customer,
sum(sale_qty) sale_qty
from t_sales
group by rollup(subcompany,branch,region,customer);
Subcompany Branch Region Customer Sale_qty
北京分公司北京经营部片区1 客户1 2
北京分公司北京经营部片区1 客户2 2
北京分公司北京经营部片区1 4
北京分公司北京经营部片区2 客户1 2
北京分公司北京经营部片区2 客户2 2
北京分公司北京经营部片区2 4
北京分公司北京经营部 8
北京分公司其他经营部片区1 客户1 2
北京分公司其他经营部片区1 客户2 2
北京分公司其他经营部片区1 4
北京分公司其他经营部片区2 客户1 2
北京分公司其他经营部片区2 客户2 2
北京分公司其他经营部片区2 4
北京分公司其他经营部 8
北京分公司 16
16
分析上面的临时结果，我们看到：
明细到客户的汇总信息，其除数为当前的sum(sale_qty),被除数应该是到片区的小计信息。
明细到片区的汇总信息，其除数为片区的sum(sale_qty),被除数为聚合到经营部的汇总数据。
。。。
考虑到上述因素，我们可以使用oracle的开窗函数over，将数据定位到我们需要定位的记录。如下代码中，我们利用开窗函数over直接将数据定位到其上次的小计位置。
over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null)
经整理后的查询语句如下。
select subcompany,
decode(f_branch, 1,subcompany||'(Ð¡¼Æ)', branch),
decode(f_region,1,branch||'(Ð¡¼Æ)',region),
decode(f_customer,1,region||'(Ð¡¼Æ)', customer),
sale_qty,
trim(to_char(round(sale_qty/
sum(sale_qty) over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null),2) *100,99990.99))
from (select grouping(branch) f_branch,
grouping(region) f_region,
grouping(customer) f_customer,
subcompany,
branch,
region,
customer,
sum(sale_qty) sale_qty
from t_sales
group by subcompany, rollup(branch, region, customer))
Subcompany Branch Region Customer Sale_qty Rate
北京分公司北京经营部片区1 客户1 2 50.00
北京分公司北京经营部片区1 客户2 2 50.00
北京分公司北京经营部片区2 客户1 2 50.00
北京分公司北京经营部片区2 客户2 2 50.00
北京分公司北京经营部片区1 片区1(小计) 4 50.00
北京分公司北京经营部片区2 片区2(小计) 4 50.00
北京分公司其他经营部片区1 客户1 2 50.00
北京分公司其他经营部片区1 客户2 2 50.00
北京分公司其他经营部片区2 客户1 2 50.00
北京分公司其他经营部片区2 客户2 2 50.00
北京分公司其他经营部片区1 片区1(小计) 4 50.00
北京分公司其他经营部片区2 片区2(小计) 4 50.00
北京分公司北京经营部北京经营部(小计) (小计) 8 50.00
北京分公司其他经营部其他经营部(小计) (小计) 8 50.00
北京分公司北京分公司(小计) (小计) (小计) 16 100.00
北京分公司北京经营部片区1 客户1 2 50.00
4，可能的另外一种解决方式（方案2）
select subcompany,
decode(f_branch, 1,subcompany||'(Ð¡¼Æ)', branch),
decode(f_region,1,branch||'(Ð¡¼Æ)',region),
decode(f_customer,1,region||'(Ð¡¼Æ)', customer),
sale_qty,
/* trim(to_char(round(sale_qty/*/
decode(f_branch+f_region+f_customer,
0,
(sum(sale_qty) over(partition by subcompany,branch,region))/2,
1,
(sum(sale_qty) over(partition by subcompany,branch))/3,
2,
(sum(sale_qty) over(partition by subcompany))/4 ,
sum(sale_qty) over()/4
)/*
,2) *100,99990.99))*/
from (select grouping(branch) f_branch,
grouping(region) f_region,
grouping(customer) f_customer,
subcompany,
branch,
region,
customer,
sum(sale_qty) sale_qty
from t_sales
group by subcompany, rollup(branch, region, customer))
在上面的解决方式中，最大的问题在于开窗函数过大。导致每次计算涉及到的行数过多，影响到执行的速度和效率。并且需要额外的计算处理清除多余叠加进去的数值。本回答被提问者采纳

相似回答

oracle 求和函数答：oracle中连续求和分析函数为sum(?)over(?)。用法：selectbdcode,sum(1)over(orderbybdcode)aafrombd_bdinfo 规则：1、sum(?)over()，对所有行求和 2、sum(?)over(orderby?)，连续求和 3、sum(?)over(partitionby?)，同组内所行求和 4、sum(?)over(partitionby?orderby?)，同第1点中的排...

oracle中函数rank中的over是什么意思?答：over()是分析函数，可以和rank()函数配合使用，也可以和其他函数配合使用。下面给出一些来自网上的示例：TABLE：S （subject，mark）数学，80 语文，70 数学，90 数学，60 数学，100 语文，88 语文，65 语文，77 现在我想要的结果是：每门科目的前3名的分数数学，100 数学，90 数学，80 语文，88...

oracle 求和函数答：sum(…) over( )，对所有行求和。sum(…) over( order by … )，连续求和。sum(…) over( partition by… )，同组内所行求和。sum(…) over( partition by… order by … )，同第1点中的排序求和原理，只是范围限制在组内。

oracle中如何求百分比?答：THEN '30岁以上'WHEN db_psndoc.age>30 THEN '30岁及以下' END)ranges, COUNT(*) rs ,100*round(COUNT(*)/SUM(COUNT(*))OVER(),4)||'%' percent FROM bd_psnd GROUP BY CASE WHEN bd_psndoc.age<=30 then '30岁及以下'WHEN db_psndoc.age<=30 THEN '30岁以上'END ...

oracle分析函数over partition by 和group by的区别答：普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。分析函数的形式：分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) 。使用形式如下：over(partition by xxx order by ...

sql里的ROW_NUMBER() OVER是啥意思?答：ROW_NUMBER() OVER是oracle数据库的分析函数，会在数据表生成一个排序列。语法：ROW_NUMBER ( ) OVER( [ PARTITION BY value_expression , ... [ n ] ]order_by_clause )参数：PARTITION BY value_expression 将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。 value_expression ...

oracle中的 keep 和 over 的区别答：ver指定分区，如果没有over则是对所有行应用一次分析函数，因此结果就一行，有over并且指定了分区，那么分析函数的计算范围就在此分区内，对此分区每一行都使用分析函数，分区内的每一行的分析函数值是一样的，而且最终返回的行和总行数相同。或有over里面没有分区则是对每一行应用分析函数，计算范围是所有...

ORACLE 查询去重保留第一条排序答：用分析函数row_number() over (partition by ... order by ...)给记录按组编号，然后只取编号值为1的记录，应该就是你要的结果集了。select s.*from ( select d.A_ID, d.A_DATE, d.A_NAME, row_number() over (partition by d.A_ID order by d.A_DATE desc) as rowidx from...

大家正在搜

sql中over函数是做什么用的 oracle中的over函数 rank over函数怎么用 oracle中instr函数用法 sqlserver中位数函数 oracle lag函数用法 oracle中位数函数 oracle的over substr函数oracle