Ⅰ 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