Oraclegroupbyrollupdecodegroupingnvl and other judgments or subtotal experiences

In oracle 12th, the group by statement is used, and the group by rollup, decode, grouping, nvl, nvl2, nullif, RATIO_TO_REPORT statements are used.

1.DecodeSimilar to stream data statements such as if... then, case... when...

Decode(Condition, value 1, return value 1, value 2, return value 2,... value n, return value n, default value)

The function has the following meanings:
IF condition = value 1 THEN
RETURN (value 1)
ELSIF condition = value 2 THEN
RETURN (value 2)
......
ELSIF condition = value n THEN
RETURN (value n)
ELSE
RETURN (default)
END IF

Decode(Calculation of a field or field, value 1, value 2, value 3)

The result of this function is that when the calculated value of a field or field is equal to 1, the function returns 2; otherwise, the return value is 3.
Of course, the value 1, the value 2, and the value 3 can also be an expression.

---- Compare the size

SelectDecode(Sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; -- smaller value
The sign () function returns 0, 1, and-1 respectively based on a value of 0, positive, or negative.

--- Sort by fixed value order

Select * from table_subject order by decode (subject_name, '', 1, 'mat', 2, '', 3 );

2.Group by rollup, grouping, grouping_id, group_id, grouping setsAggregate Function, also can refer to OVER (partition by), this is the aggregate function that can return multiple rows, a little deeper, link: http://blog.csdn.net/fengeh/article/details/24934437

Rollup:Returns a subtotal for each group and a total for all groups;
Example: group by rollup (A, B)

GroupingThe receiving column is used as a parameter. If the column is empty, grouping returns 1; otherwise, 0. This function is used with rollup.
For example: SQL> SELECT DECODE (GROUPING (EMP_ID), 1, 'all employees', EMP_ID) EMP_ID,

Nvl2 (emp_id, DECODE (GROUPING (MONTH), 1, 'employee year', MONTH), 'all employees' year') ND,
SUM (AMOUNT) FROM ALL_SALES group by rollup (EMP_ID, MONTH );

GROUPING_ID ()The function can accept one or more columns and return the decimal value of the GROUPING bit vector. The calculation method of the GROUPING bit vector is to combine the results of calling the GROUPING function for each column in order, and calculate the GOURPING bit vectorGroupingSimilarGROUPING_ID (field A, field B, field C ...)

As shown in the following example:

Grade id bit vector GROUPING_ID () Return Value

Non-null 00 0

Non-empty 01 1

Null, not empty, 10 2

Null 11 3

GROUP_ID ()The unique identifier of the repeat group is similar to that of grouping. However, this field is determined based on whether duplicate data exists. Duplicate data is set to 1 and non-duplicate data is set to 0.

Grouping setsSimilar to union all, not heavy, used after group by, for example:
Group by grouping sets (STOCK_ID), (STOCK_ID, TYPE_CD, DISCOUNT )));

3.Nvl nvl2 nullifProcessing of all NULL Fields

NULL indicates a NULL value or an invalid value.
NVL (expr1, expr2)-> If expr1 is NULL, expr2 is returned. If not NULL, expr1 is returned. Note that the two types must be consistent.
NVL2 (expr1, expr2, expr3)-> expr1 is not NULL, returns expr2; is NULL, returns expr3. If the expr2 and expr3 types are different, expr3 will be converted to the expr2 type.
NULLIF (expr1, expr2)-> equal return NULL, not equal return expr1

4.RATIO_TO_REPORTResolution function, often used for percentage calculation

Ratio_to_report (field) OVER (partition by customer_id) ------ partition by is equivalent to group