Ⅰ oracle sql developer 中的 CUBE 啥意思
使用rollup或cube通过交叉列可产生高级汇总结果集；
rollup分组产生的结果集包含常规分组和分类汇总；
cube分组结果包括rollup结果和交叉汇总
Ⅱ oracle 分析函数rank()over()使用，与group by的区别
你这里的rank 就是参数吧。
oracle里面rank over 和 group by 是完全不一样的东西。
怎么还功能相似呢。理解不能。
rank over 的用法。
http://oracle.se-free.com/dml/06_rank.html
group by 只是在分组的时候使用。
Ⅲ 求 Oracle 分析函数
假设截图中的表为t_num_pid (你要操作的表应该没有pid字段吧，我导进去了，供参照），可通过如下语句实现：
select a.*
,case when length(a||b||c||d||e||f)-2 = 0 then null
else sid - row_number()over( partition by substr(a||b||c||d||e||f ,1,length(a||b||c||d||e||f)-2) order by sid )
end pid
from t_num_pid a
order by sid
;
思路：
1.你的每一条记录对应一个sid，相当于一条条的文件路径，
那么最后一个文件节点都有一个父路径，
先用row_number()over( partition by substr(a||b||c||d||e||f ,1,length(a||b||c||d||e||f)-2)
对父路径分组排序，所得的序号即为每个路径最后一个节点sid与其父节点pid的差值，即：pid = sid - 序号 的关系存在。
2.
sid - 序号 = 0 时，用 case when .. end 置为null;
我这边跑的结果：
select a.a,a.b,a.c,a.d,a.e,a.f,a.sid
--,sid - row_number()over( partition by substr(a||b||c||d||e||f ,1,length(a||b||c||d||e||f)-2) order by sid ) pid
,case when length(a||b||c||d||e||f)-2 = 0 then null
else sid - row_number()over( partition by substr(a||b||c||d||e||f ,1,length(a||b||c||d||e||f)-2) order by sid )
end pid
from t_num_pid a
order by sid
;
A B C D E F SID PID
---- ---- ---- ---- ---- ---- ---------- ----------
Q1 1
Q1 W1 2 1
Q1 W1 E1 3 2
Q1 W1 E1 R1 4 3
Q1 W1 E1 R2 5 3
Q1 W1 E1 R3 6 3
S1 7
S1 D1 8 7
S1 D1 F1 9 8
S1 D1 F1 G1 10 9
S1 D1 F1 G2 11 9
S1 D1 F1 G3 12 9
H1 13
H1 J1 14 13
H1 J1 K1 15 14
H1 J1 K1 L1 16 15
H1 J1 K1 L2 17 15
H1 J1 K1 L3 18 15
Ⅳ 能说一下oracle中的开窗函数 聚合函数 分析函数都是什么吗
我也是用oracle数据库的，在实际开发中用到开窗函数和分析函数的机会还是很少的，用聚合函数的时候非常多，请LZ多关注聚合函数，下面是我上各大网站收集的，希望对楼主有所帮助。
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。
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)
other（
数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
一、 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))
在上面的解决方式中，最大的问题在于开窗函数过大。导致每次计算涉及到的行数过多，影响到执行的速度和效率。并且需要额外的计算处理清除多余叠加进去的数值 。
）
Ⅳ plsql中分析函数和存储过程有什么区别
1. 返回值的区别,函数有1个返回值,而存储过程是通过参数返回的,可以有多个或者没有
2.调用的区别,函数可以在查询语句中直接调用,而存储过程必须单独调用.
3.函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）
4.参数的返回情况来看：
如果返回多个参数值最好使用存储过程，如果只有一个返回值的话可以使用函数；
5.从调用情况来看：
如果在SQL语句（DML或SELECT）中调用的话一定是存储函数或存储的封装函数不可以是存储过程，但调用存储函数的时候还有好多限制以及函数的纯度等级的问题.
6.oracle函数和存储过程最大的区别就在于，函数必须带上一个return返回值，后面跟的是返回值的类型，而存储过程可以不带任何返回值。
7.另外，存储过程和函数同样支持in，out,in out这三种类型的参数，对应的是分别指明输入性的参数、输出型的参数、输入输出型的参数。也就是说如果我们需要返回多个值，除了可以使用存储过程来实现之外，也可以用函数来实现，方法是你将其中一个用return来返回，其他就指明该数据为out参数就可以了。
8.Oracle中存储过程和函数都可以返回值，但是函数必须要返回值，并一般只返回一个值，而存储过程则没有这个限制。从一般应用上来看，如果不需要返回值或者需要多个返回值，使用存储过程，如果只用一个返回值，就使用函数，从一般程序员的习惯上来看，这样更符合思维。
Ⅵ plsql Oracle分析函数取得以下结果，以分数第二（77分）的为参照对象，比如
不用那么麻烦，直接用77参考基数就可以了。
例：select 学生编号,学科,分数,77-分数 分数差 from 表;
Ⅶ oracle中的分组函数有哪些，具体怎么用
常用的函数有：
1、COUNT ()返回查寻的行数
例如：select count(*) from table;
2、MAX() 返回表达式的最大值
例如：select a, max(b) from table group by a;
3、MIN() 返回表达式的最小值
例如：select a, min(b) from table group by a;
4、SUM() 返回表达式的总合
例如：select a, sum(b) from table group by a;
5、AVG() 返回表达式的平均值
例如：select a, avg(b) from table group by a。
Ⅷ oracle中的 分析函数与sql的分析函数是一样的吗
不一样。oracle的分析函数更多，更复杂。
Ⅸ oracle分析函数作用
简化sql语句，实现较为复杂的功能，比如lag
Ⅹ sql中有没有类似于oracle中rownum的函数
SQL Server 2000中没有SQL Server 2005中有一个ROW_NUMBER()函数实现了相同的功能 ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> ) 参数 <partition_by_clause> 将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。有关 PARTITION BY 的语法，请参阅 OVER 子句 (Transact-SQL)。 <order_by_clause>确定将 ROW_NUMBER 值分配给分区中的行的顺序。有关详细信息，请参阅 ORDER BY 子句 (Transact-SQL)。当在排名函数中使用 <order_by_clause> 时，不能用整数表示列。 返回类型 bigint 备注 ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。 参考： http://msdn.microsoft.com/zh-cn/library/ms186734(SQL.90).aspx