Oracle Group Statement Exploration (note)

1. The GROUP BY statement does not have a sort function in Oracle and must rely on order by to achieve the ordered results

2, GROUP by cube extension

1  withTest as2 (3     Select 1Id2Name fromDual4 )5 SelectId,name fromTestGroup  byCube (id,name);6 7 The output result is8 ID Name9 NULL    NULLTen 1        NULL One NULL    2 A 1        2

It is not difficult to see that the role of group by Cube is to introduce null into a Cartesian product, which is finally shown, in some cases, very convenient to use, in some cases can replace union all, very high efficiency. Where the amount of data is more than the case, the full empty column appears only once

3. Grouping () function

Grouping () is used with cube to determine whether this value is a null value generated by the aggregation, and if it returns 1, not zero

1  withTest as2 (3     Select 1Id2Name fromDual4 )5 SelectId,name fromTest6 Group  byCube (id,name)7  having Grouping(ID)=1;8 9 The output result isTen ID Name One NULL    NULL A NULL    2 -  -  the  withTest as - ( -     Select 1Id2Name fromDual - ) + SelectId,name fromTest - Group  byCube (id,name) +  having Grouping(ID)=0; A  at The output result is - ID Name - 1        NULL - 1        2

4. grouping_id () function

GROUPING_ID () is somewhat similar to grouping (), except that grouping () evaluates an expression that returns 0 or 1, whereas group_id () evaluates an expression that determines which row in its argument is used to generate a hyper-aggregate row, and then a vector is common. and returns the value as an integer value

1  withTest as2 (3     Select 1Id2Name fromDual4 ),5cuded as(6     Select 7 grouping_id (id,name) GID,8To_char (Grouping(ID)) Id_1,9To_char (Grouping(name)) Name_1,TenDecodeGrouping(ID),1,'ID 1') Id_2, OneDecodeGrouping(name),1,'Name 2') name_2 A        fromTest -       Group  bycude (id,name) - ) the Select -Gid,id_1||name_1 dn,id_2,name_2 -  from - cuded; +  - The result is: + GID DN id_2 name_2 A 0          xx    at 1           onName2 - 2          TenId1 - 3           OneId1Name2 -         

　　

Oracle Group Statement Exploration (note)