Oracle group statement Inquiry Learning notes

Oracle group statement Inquiry Learning notes

1. The group by statement does not have the sorting function in Oracle. order by must be used to sort the statements according to the predefined results.

2. cube extension of group

With test
(
Select 1 id, 2 name from dual
)
Select id, name from test group by cube (id, name );

The output result is
Id name
Null
1 null
Null 2
1 2

It is not hard to see that the role of group by cube is to introduce null into a Cartesian Product and finally display it. In some cases, it is very convenient to use and can replace union all in some cases, high efficiency. When the data volume is large, only one empty column appears.

3. grouping () function

Grouping () is used with cube to determine whether this value is a null value generated by aggregation. If it is 1, it is not zero.

With test
(
Select 1 id, 2 name from dual
)
Select id, name from test
Group by cube (id, name)
Having grouping (id) = 1;

The output result is
Id name
Null
Null 2

With test
(
Select 1 id, 2 name from dual
)
Select id, name from test
Group by cube (id, name)
Having grouping (id) = 0;

The output result is
Id name
1 null
1 2

4. grouping_id () function

Grouping_id () is similar to grouping () to some extent. The difference is that grouping () calculates an expression and returns 0 or 1, while group_id () calculates an expression, determine which row of the parameter is used to generate a superaggregate row. Then, a vector is common and the value is returned as an integer value.

With test
(
Select 1 id, 2 name from dual
),
Cuded (
Select
Grouping_id (id, name) gid,
To_char (grouping (id) id_1,
To_char (grouping (name) name_1,
Decode (grouping (id), 1, 'Id 1') id_2,
Decode (grouping (name), 1, 'name 2') name_2
From test
Group by cube (id, name)
)
Select
Gid, id_1 | name_1 dn, id_2, name_2
From
Cuded;

Result:
Gid dn id_2 name_2
0 00
1 01 name 2
2 10 id 1
3 11 id 1 name 2