Oracle select --分析函數over()

 --分析函數over()

--開窗函數

--開窗函數的的了解：

--建立表test

create table test(sales_id varchar2(2),sales varchar2(10),dest varchar2(10),dept varchar2(10),revenue number);

--建立資料

insert into test values('11','smith','hangzhou','市場',1000);

insert into test values('12','smith','wenzhou','市場',2000);

insert into test values('13','allen','wenzhou','管道',3000);

insert into test values('14','allen','wenzhou','管道',4000);

insert into test values('15','jekch','shanghai','管道',2500);

--計算資料視窗表達式的銷售總額

select sales_id,sales,dest,dept,revenue,sum(revenue) over(order by sales_id) as 總銷售額       

from test

select sales_id,sales,dest,dept,revenue,sum(revenue) over(order by sales) as 總銷售額       

from test

--計算資料視窗表達式累計銷售額

select sales_id,sales,dest,dept,revenue,sum(revenue) over(partition by sales) as 累計銷售額       

from test

select sales_id,sales,dest,dept,revenue,sum(revenue) over(partition by sales) as 累計銷售額      

from test

order by sales_id

--計算資料視窗表達式的統計值，cnt1統計所有行數，cnt2統計表達式坐在的最大行數，cnt3統計表達式出現的次數

select sales_id,sales,dest,count(*) over() as cnt1,

       count(*) over(order by dest) as cnt2,

       count(*) over(partition by dest) as cnt3

from test

--删除表

drop table test

---平台函數

isNull(obj):判斷給定對象是否為null,傳回true/false;如isNull(repField("org_code"))

switch(condition,case,value,case,value……)

condition:switch語句的條件

case:case值

value:上一個case對應的值

如:switch(repField("org_code"),90,"社保科",91,"行政政法科",92,"企業科",93,"農業科",94,"經濟建設科",95,"教科文科")