Oracle GROUP BY Rollup,decode,grouping,nvl,nvl2,nullif,grouping_id,group_id,grouping Sets,ratio_to

Dry Oracle 047 Article 12 when the issue, experience GROUP BY statement. So encounter GROUP by Rollup,decode,grouping,nvl,nvl2,nullif,ratio_to_report wait a minute.

1. decode and If...then,case...when ... This kind of flow data statement is nearly identical in function

Decode (condition, value 1, return value 1, value 2, return value 2,... Value N, return value N, default value)

The meaning of the function is as follows:
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 value)
END IF

Decode (field or Field operation, value 1.) Value 2, value 3)

The result of this function execution is. When the value of the field or Field Operation equals the value 1 o'clock, the function returns the value 2, otherwise the return value 3
Of course the value is 1. A value of 2, a value of 3 can also be an expression

----size

Select decode(sign (variable 1-variable 2), 1, variable 1, variable 2) from dual; --Take a smaller value
The sign () function returns 0, 1, 1, depending on whether a value is 0, positive, or negative.

---Sort on a fixed value order by

SELECT * from Table_subject ORDER by decode (subject_name, ' language ', 1, ' Mathematics ', 2, ' foreign Languages ', 3);

2. GROUP by rollup, grouping,grouping_id,group_id,grouping Set,group by CUBE aggregation function, also refer to over (PARTITION by), This is an aggregate function that can return multiple rows, a bit darker, link: http://blog.csdn.net/fengeh/article/details/24934437

Rollup: returns a subtotal for each grouping, returning the totals for all groups at the same time;
For example: GROUP by rollup (A, B)

grouping receives the column as a parameter, assuming that it is empty, grouping returns 1, otherwise 0 is returned. This function is used in conjunction with the 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), ' Total employee year ') ND,
SUM (AMOUNT) from All_sales GROUP by ROLLUP (emp_id, MONTH);

The grouping_id () function can accept one or more columns, returning the decimal value of the GROUPING bit vector. The grouping-bit vectors are calculated by combining the results of invoking the grouping function in each column in order, gourping bit vectors, which are nearly identical in function to grouping , in the form grouping_id (field A, Field B, field C ...)

For example, as seen in the following example

Grade ID bit vector grouping_id () return value

Non-empty non-empty 00 0

Non-empty 01 1

Empty non-empty 10 2

Empty 11 3

group_id () uniquely identifies the repeating group, the function and grouping almost the same, but this field is based on whether there is repeated data to infer, repeated data is sentenced to 1, no repetition is 0.

grouping sets is similar to union all, and does not go heavy. For group BY, for example:
Group BY grouping sets ((stock_id), (stock_id, TYPE_CD, DISCOUNT));

Integrate all possibilities, such as:

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvzmvuz2vo/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/southeast ">

3.NVL nvl2 Nullif is a method of handling null fields

NULL refers to a null value. or illegal value.
NVL (EXPR1, EXPR2)->EXPR1 is null. returns EXPR2; not null, returns EXPR1. Note that the two types should be consistent
NVL2 (Expr1, EXPR2, EXPR3)->EXPR1 is not NULL, returns EXPR2, or NULL, which returns EXPR3. EXPR3 will be converted to EXPR2 type if the EXPR2 and EXPR3 types are different
Nullif (Expr1, expr2), equals return null, unequal return EXPR1

4.ratio_to_ report analytic function, often used in percent evaluation

Ratio_to_report (field) over (PARTITION by customer_id)------PARTITION by equals GROUP by

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

Oracle GROUP BY Rollup,decode,grouping,nvl,nvl2,nullif,grouping_id,group_id,grouping Sets,ratio_to