Oracle advanced sorting functions and advanced grouping functions

Advanced sort function:  
[row_number () | RANK () | Dense_rank] Over (partition by XX order by xx)


1.row_number () continuous and incrementing number 1 2 3 4    Row_number () over (partition by XX order by xx)  
  
--In the student table in accordance with the professional group, in the same professional in the reverse order of grades, The same results are ordered by the number of numbers, and given the group hierarchy
Select Row_number () over (partition by class_id ORDER BY score Desc) rn,t.* from student2016 T
& NBSP;&NBSP
2.rank () jump sort if the same data is ranked the same then jump sort 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
 &nb Sp
3.dense_rank If you have the same data, rank the same and then increment the sort
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

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

Advanced grouping functions

GROUP BY rollup (A,B,C)

Select A,b,c,sum (d) from test Group by rollup (A,B,C)

The columns after rollup are grouped by one column from right to left until all columns are removed (that is, the whole table is grouped)
For rollup of n parameters, there are n+1 sub-groups

That is, by a,b,c, grouping, union all, and Grouping union all A to group union from Test

----------------------------------------------------------------------------------
Group by Cube (A,B,C)

For n parameters, there are 2^n sub-groups

That is, press Ab,ac,a,bc,b,c to group all last

----------------------------------------------------------------------------------
Group BY grouping sets (A, B)

That is, only the result set that is grouped by a and a group of B is listed

--Creating a Sales Table 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);--inserting data 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;--query Group by Data select SUM (t.sales_value) from Sales_tab T--1 rows Select T.year_id,t.month_id,t.day_id,sum (t.sales_value) sales from SAL Es_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 Lines 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 Lines Select T.year_id,sum (t.sales_value) sales from Sales_tab T Group by T.year_idorder by t.year_id Desc; --2 rows--using advanced grouping functions-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 rows = Same upper 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_t AB 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 Lines 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 rows

　　

Oracle advanced sorting functions and advanced grouping functions