ORACLE group statistics

Welcome to the Oracle community forum and interact with 2 million technical staff to access ROLLUP and CUBE statements. In addition to the basic syntax, Oracle GROUPBY statements also support ROLLUP and CUBE statements. If it is ROLLUP (A, B, C), it first performs GROUPBY on (A, B, C), then performs GROUPBY on (A, B), and then ()

Welcome to the Oracle community forum and interact with 2 million technical staff> go to ROLLUP and CUBE statements. In addition to the most basic syntax, Oracle group by statements also support ROLLUP and CUBE statements. If ROLLUP (A, B, C) is used, group by is performed on (A, B, C) and group by is performed on (A, B, then ()

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

ROLLUP and CUBE statements.

Oracle GROUP

In addition to the most basic syntax, BY statements also support ROLLUP and CUBE statements. For ROLLUP (A, B, C ),

Then perform group by on (A, B), then perform GROUP BY on (A), and finally perform GROUP BY on the entire table. For GROUP

CUBE (A, B, C ),

BY, followed BY (A, B), (A, C), (A), (B, C), (B), (C ), finally, perform the group by operation on the entire table.

Grouping_id () can beautify the effect:

In addition to the most basic syntax, Oracle group by statements also support ROLLUP and CUBE statements.

In addition to the content of this article, you can also refer:

Analytics function reference manual:

Http://xsb.itpub.net/post/419/33028

Example of using analytic functions:

Http://xsb.itpub.net/post/419/44634

SQL> create table t as select * from dba_indexes;

The table has been created.

SQL> select index_type, status, count (*) from t group by index_type, status;

INDEX_TYPE status count (*)

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

Lob valid 51

Normal n/A 25

Normal valid 479

Cluster valid 11

The following describes the execution results of ROLLUP and CUBE statements.

SQL> select index_type, status, count (*) from t group by rollup (index_type, status );

INDEX_TYPE status count (*)

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

Lob valid 51

LOB 51

Normal n/A 25

Normal valid 479

NORMAL 504

Cluster valid 11

CLUSTER 11

566

Eight rows have been selected.

SQL> select index_type, status, count (*) from t group by cube (index_type, status );

INDEX_TYPE status count (*)

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

566

N/A 25

VALID 541

LOB 51

Lob valid 51

NORMAL 504

Normal n/A 25

Normal valid 479

CLUSTER 11

Cluster valid 11

10 rows have been selected.

The query results are not very clear at a glance. The following describes the query results through the GROUPING function provided by Oracle.

SQL> select grouping (index_type) g_ind, grouping (status) g_st, index_type, status, count (*)

2 from t group by rollup (index_type, status) order by 1, 2;

G_IND G_ST INDEX_TYPE status count (*)

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

0 0 lob valid 51

0 0 normal n/A 25

0 0 normal valid 479

0 0 cluster valid 11

0 1 LOB 51

1 0 NORMAL 504

0 1 CLUSTER 11

1 566

Eight rows have been selected.

The query results are much more intuitive. Compared with group by statements without ROLLUP, ROLLUP adds group by statistics for INDEX_TYPE and group by statistics for all records.

That is to say, if ROLLUP (A, B, C) is used, group by is first performed on (A, B, C), and then group by is performed on (A, B, then perform group by for (A) And then perform group by for the entire table.

Let's look at the CUBE statement.

SQL> select grouping (index_type) g_ind, grouping (status) g_st, index_type, status, count (*)

2 from t group by cube (index_type, status) order by 1, 2;

G_IND G_ST INDEX_TYPE status count (*)

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

0 0 lob valid 51

0 0 normal n/A 25

0 0 normal valid 479

0 0 cluster valid 11

0 1 LOB 51

1 0 NORMAL 504

0 1 CLUSTER 11

1 0 N/A 25

1 0 VALID 541

1 566

10 rows have been selected.

Compared with ROLLUP, the CUBE adds group by statistics for the STATUS column.

For group by cube (A, B, C), group by (A, B, C) is first performed, followed BY (A, B ), (A, C), (A), (B, C), (B), (C), and finally perform the GROUP BY operation on the entire table.

In addition to the GROUPING function, you can also use GROUPING_ID to identify the group by result.

SQL> select grouping_id (index_type, status) g_ind, index_type, status, count (*)

2 from t group by rollup (index_type, status) order by 1;

G_IND INDEX_TYPE status count (*)

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

0 lob valid 51

0 normal n/A 25

0 normally VALID 479

0 cluster valid 11

1 LOB 51

1 NORMAL 504

1 CLUSTER 11

3 566

Eight rows have been selected.

SQL> select grouping_id (index_type, status) g_ind, index_type, status, count (*)

2 from t group by cube (index_type, status) order by 1;

G_IND INDEX_TYPE status count (*)

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

0 lob valid 51

0 normal n/A 25

0 normally VALID 479

0 cluster valid 11

1 LOB 51

1 NORMAL 504

1 CLUSTER 11

2 N/A 25

2 VALID 541

3 566

10 rows have been selected.

Grouping_id () can beautify the effect:

Select DECODE (GROUPING_ID (C1), 1, 'Total', C1) D1,

DECODE (GROUPING_ID (C1, C2), 1, 'subtotal ', C2) D2,

DECODE (GROUPING_ID (C1, C2, C1 + C2), 1, 'subtotal ', C1 + C2) D3,

Count (*),

GROUPING_ID (C1, C2, C1 + C2, C1 + 1, C2 + 1 ),

GROUPING_ID (C1)

From T2

Group by rollup (C1, C2, C1 + C2, C1 + 1, C2 + 1 );

========================================================== ==============================

1.

Dedicated for total reports

Rollup

Function

Sales Report

Previous queries

SQL:

Select

Area, month, sum (money) from SaleOrder group by area, month

[1] [2] [3]