Oracle 進階排序函數 和 進階分組函數

標籤：color   set   等級   _id   方式   order   學生   nec   group   

進階排序函數: 
[ ROW_NUMBER()| RANK() | DENSE_RANK ] OVER (partition by xx order by xx)


1.row_number() 連續且遞增的數字 1 2 3 4 
  row_number() over (partition by xx order by xx )  
  
--學生表中按照所在專業分組，同專業內按成績倒序排序，成績相同則按學號正序排序，並給予組內等級
select row_number() over(partition by class_id order by score desc)rn,t.* from student2016 t
  
2.rank() 跳躍排序 若有相同資料則排名相同 然後跳躍排序 1 2 2 2 5
  rank() over (partition by xx order by xx ) 


select rank() over(partition by class_id order by score desc)rn,t.* from student2016 t
  
3.dense_rank 若有相同資料則排名相同 然後遞增排序
dense_rank  over (partition by xx order by xx ) 1 2 2 2 3


select dense_rank() over(partition by class_id order by score desc)rn,t.* from student2016 t

----------------------------------------------------------------------------------------------------------------------------

進階分組函數

group by rollup(a,b,c)

select a，b,c,sum(d) from test group by rollup(a,b,c)

對rollup後面的列 按從右至左以少一列的方式進行分組直到所有列都去掉後的分組（也就是全表分組）
對於n個參數的 rollup，有n+1次分組

即按a，b，c，分組，union all a，b分組 union all a分組 union from test

----------------------------------------------------------------------------------
group by cube(a,b,c)

對n個參數，有2^n次分組

即按 ab，ac，a，bc，b，c最後對 全部分組

----------------------------------------------------------------------------------
group by grouping sets(a,b)

即只列出 對 a分組後，和對 b分組的結果集

 

-- 建立銷售表create table sales_tab(year_id number not null,month_id number not null,day_id number not null,sales_value number(10,2) not null);-- 插入資料insert into sales_tabselect trunc(dbms_random.value(low=>2010,high=>2012)) as year_id,trunc(dbms_random.value(low=>1,high=>13)) as month_id,trunc(dbms_random.value(low=>1,high=>32)) as day_id,round(dbms_random.value(low=>1,high=>100)) as sales_valuefrom dual connect by level <=1000;-- 查詢 group by 後的資料 select sum(t.sales_value) from SALES_TAB t -- 1行select t.year_id,t.month_id,t.day_id,sum(t.sales_value) sales from SALES_TAB t group by t.year_id,t.month_id,t.day_id order by t.year_id,t.month_id,t.day_id desc; -- 540行select t.year_id,t.month_id,sum(t.sales_value) sales from SALES_TAB t group by t.year_id,t.month_idorder by t.year_id,t.month_id desc; -- 24 行select t.year_id,sum(t.sales_value) sales from SALES_TAB t group by t.year_idorder by t.year_id desc; -- 2 行-- 使用進階分組函數-- group by rollup（a,b,c）select t.year_id,t.month_id,t.day_id,sum(t.sales_value) sales from SALES_TAB t group by rollup(t.year_id,t.month_id,t.day_id)order by t.year_id,t.month_id,t.day_id; -- 567 行 = 同上面 1+540+24+2-- group by cube(a,b,c)select t.year_id,t.month_id,t.day_id,sum(t.sales_value) sales from SALES_TAB t group by cube(t.year_id,t.month_id,t.day_id)order by t.year_id,t.month_id,t.day_id;--group by grouping sets(a,b,c) select t.year_id,t.month_id,sum(t.sales_value) sales from SALES_TAB t group by cube(t.year_id,t.month_id)order by 1,2; -- 39 行select t.year_id,t.month_id,sum(t.sales_value) sales from SALES_TAB t group by grouping sets(t.year_id,t.month_id)order by 1,2; -- 14 行

　　

 

Oracle 進階排序函數 和 進階分組函數