Oracle group summary statistics function grouping, oraclegrouping

Oracle group summary statistics function grouping, oraclegrouping

Two days ago, a colleague asked an oracle database to use grouping to complete a statistical report function. This function is pretty cool. The development grouping report can be done simply with an SQL statement.

The meaning of the grouping (columnA) function: if the current row is generated by rollup aggregation, the field value of columnA is 1 or 0.

Metadata:

Data queried through grouping:

SQL:

Select decode (grouping (f_line) + grouping (f_workarea), 1, 'subtotal ', 2, 'Total', f_workarea) f_workarea, decode (grouping (f_line), 1, count (*) | 'string', f_line) f_line, sum (f_pagesnumber) sum_pagesnumbers from t_testcount group by rollup (f_workarea, f_line );

Table creation data:

CREATE TABLE t_testcount   (     "F_ID" NUMBER(10,0) ,   "F_WORKAREA" NVARCHAR2(255) ,   "F_LINE" NVARCHAR2(255) ,   "F_REMARK" NVARCHAR2(255), "F_YEAR" VARCHAR2(20 BYTE),  "F_PAGESNUMBER" NVARCHAR2(255)  );insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (1, 'a', 'a1', null, '2014', '1');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (2, 'a', 'a2', null, '2013', '2');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (3, 'a', 'a3', null, '2014', '3');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (4, 'b', 'b1', null, '2014', '1');

Common oracle functions and grouping Functions

SELECT *

FROM dept_costs

WHERE dept_total> (SELECT dept_avg

FROM avg_cost)

Order by department_name;

Group by extension

Group by clause with ROLLUP and CUBE operations

-Use group by clauses with ROLLUP and CUBE operations to generate multiple grouping results

-ROLLUP generates n + 1 grouping result, which is an extension of the group by clause.

-CUBE generates the n-power grouping result of 2, which is an extension of the group by clause.

Note: n indicates the number of group_by_expression.

Rollup operator: ROLLUP generates n + 1 grouping results, in order from right to left

SELECT [column,] group_function (column )...

FROM table

[WHERE condition]

[Group by [ROLLUP] group_by_expression]

[HAVING having_expression]

[Order by column];

Cube operator: CUBE generates grouping results similar to Cartesian sets.

SELECT [column,] group_function (column )...

FROM table

[WHERE condition]

[Group by [CUBE] group_by_expression]

[HAVING having_expression]

[Order by column];

GROUPING function: enables more intuitive display of group results

SELECT [column,] group_function (column)., [GROUPING (group_by_expression)]...

FROM table

[WHERE condition]

[Group by [ROLLUP] [CUBE] group_by_expression]

[HAVING having_expression]

[Order by column];

-The GROUPING function can be used with CUBE or ROLLUP.

-Using the GROUPING function, you can find out which columns are grouped in this row.

-The GROUPING function can be used to identify the causes of null values.

-The GROUPING function returns 0 or 1.

Grouping sets:

-Grouping sets is a further expansion of the group by clause.

-You can use grouping sets to define multiple grouping sets in the same query.

-Oracle groups the GROUPING set specified by the grouping sets clause and then combines the GROUPING results with the union all operation.

-Advantages of Grouping set:

-Group only once

-Complex UNION statements are not required.

-The more group items contained in grouping sets, the better the performance.

In the following example, department_id and job_id groups and job_id and manager_id groups are implemented to form two groups:

SELECT department_id, job_id, manager_id, avg (salary ...... the remaining full text>

How to Use the grouping function in oracle?

GROUPING is used to distinguish between standard and null values returned by ROLLUP, CUBE, or grouping sets. A special application that returns NULL as a result of the ROLLUP, CUBE, or grouping sets operation. It acts as a placeholder for the column in The result set, indicating the whole.

When SQL is used, we often encounter such a problem that requires grading and calculating the sum. Do you often worry about how to classify statistics in a table? Here we can use the GROUPING () function to solve this problem.

The following example shows a data table of Administrative Region, unit, and sales.

Note: When running this example, note that the spaces in the full corner are deleted by Baidu, which may cause an error in the query analyzer.

-- Create a table and insert data

Create Table T_SendMoney (StateCode varchar (6), DepCode varchar (6), SendMoney Money)

Insert Into T_SendMoney

Select '20180101', '20180101', 100001

UNION ALL

Select '20180101', '20180101', 100001

UNION ALL

Select '20180101', '20180101', 100001

UNION ALL

Select '20180101', '20180101', 100002

UNION ALL

Select '20180101', '20180101', 100002

UNION ALL

Select '20180101', '20180101', 100003

UNION ALL

Select '20180101', '20180101', 100003

UNION ALL

Select '20180101', '20180101', 100004

-- Achieve hierarchical Summary of data by GROUPING

Select

Case when grouping (StateCode) = 1 THEN 'total: 'else StateCode END as StateCode

, Case when grouping (DepCode) = 1 THEN 'State Total: 'else DepCode END as DepCode

, Sum (SendMoney) AS SendMoney

From T_SendMoney

Group by rollup (StateCode, DepCode)

-- Query Result

StateCode DepCode SendMoney

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

100001 310001 3000.00

100001 310002 1500.00

100001 State Total: 4500.00

100002 320001 4200.00

100002 State Total: 4200.00

100003 330001 1800.00

100003 ...... remaining full text>