Group by clause with rollup appended

Today, I came into contact with the group by clause with the rollup clause (it's really ugly. I 've been in contact with ORACLE for a year or two before I know this usage ), the functions produced by the group by clause with the rollup clause are too advanced. But after learning, I feel nothing. It's very simple. The following is a brief summary based on my understanding: 1. How to understand the effect of a group by clause with a rollup clause can be understood: multiple groups are generated based on certain rules, and statistical data is collected based on various groups. (whether the calculated data includes sum, maximum, or average depends on the aggregate function after SELECT ). Therefore, www.2cto.com needs to understand the use of the rollup clause after group by. It mainly involves how to generate multiple groups according to certain rules. In addition, the result set returned by the group by clause with the rollup clause can be understood as the Union of result sets generated by each group without removing duplicate data. Example 1: Compare goup by without rollup. For example, Group by A and B: one type; group by A and B: return result set: that is, the result set of this group. 2. There is no content between group by and rollup with rollup. Example 1: Group by rollup (A, B) number of groups: 3; Type 1: group by, B 2: group by A www.2cto.com 3: group by NULL (Note: No group by NULL is written here, which is used for convenience. Meaning: No group, that is, all data is counted. For example, if the aggregate function is SUM, it is To SUM all data that meets the conditions. Return result set: the Union of the preceding three grouping statistical result sets without removing duplicate data. Example 2: Group by rollup (A, B, C) number of groups: 4; group by A, B, C; group by A, B, C, b. 3: group by A. 4: group by NULL. Returned result set: the Union of the preceding four grouping statistical result sets without deduplication. 3. groups generated by group by A and rollup (A, B) with rollup but between Group by and rollup also contain column information. Example 1: group by A, rollup (A, B): 3; Type 1: group by A, A, B is equivalent to group by A, B. The second is group by A, A is equivalent to group by A. The third is group by, NULL is equivalent to the return result set returned by group by A. It is the union of the preceding three grouping statistical result sets and does not remove duplicate data. Example 2: Group by C, rollup (A, B) number of groups: 3; group by C, A, B www.2cto.com; Type 2: group by C, B www.2cto.com, a's third type: group by C, NULL is equivalent to the return result set of group by C: it is the Union set of the preceding three grouping statistical result sets without removing duplicate data. 4. Groups produced by Group by rollup (A, B) are combined with rollup and rollup clause parentheses. Example 1: Group by rollup (A, B); Type 1: group by A, B Type 2: group by NULL return result set: it is the union of the preceding two grouping statistical result sets without removing duplicate data. Example 2: Group by rollup (A, (B, C) number of groups: 3; group by A, B, C; 2: group by A www.2cto.com 3: group by NULL return result set: the Union of the preceding three group statistical result sets and no duplicated data are removed. Note: In this case, when several columns are enclosed in parentheses, they can only be regarded as a whole. Grouping does not need to be refined. Therefore, it can be inferred that the rollup brackets are also added at most to the heavy brackets. Adding multiple brackets makes no sense (I did not validate this inference ). Ii. Other auxiliary functions used in combination with rollup 1. The grouping () function must accept one column and only one column can be used as its parameter. If the parameter column value is null, 1 is returned. If the parameter column value is not null, 0 is returned. 2. The grouping_id () function must take one or more columns as its parameters. The return values are sorted by parameters. The grouping () function is used for each parameter in turn, and the result values are serialized into a string of binary numbers and then converted to the decimal value. For example: grouping (A) = 0; grouping (B) = 1; then: grouping_id (A, B) = (01) 2 = 1; grouping_id (B,) = (10) 2 = 2; 3. when calling the group_id () function www.2cto.com, no parameters are required and cannot be input. The returned value is the number of repetitions of a specific group (repeated groups are usually generated in 3rd cases in the first point ). The number of repetitions starts from 0. For example, if a group appears for the first time, the returned value is 0. If the group appears for the second time, the returned value is 1 ,......, The return value for the nth occurrence is n-1. Note: The above three functions are often used to filter out a portion of the statistical data and beautify the statistical results. 3. The group by clause with rollup and the group by clause are different from those with cube clauses. The only difference between a group by clause with rollup and a group by clause with cube clauses is: group by with cube clause generates more grouping statistics. The number of groups in the column after the cube (note that the combination is irrelevant to the order. For example, the number of groups generated by Group by cube (A, B, C) is 8. The first is group by A, B, C, and the second is group by, b: group by A, C; 4: group by B, C; 5: group by C; 6: group by B; 7: group by A www.2cto.com; 8: group by NULL return result set: it is the union of the preceding eight grouping statistical result sets and no duplicated data is removed. 4. If the group by clause is followed by the grouping sets clause and the grouping sets clause is followed, only the note record is returned, that is, only the statistical data grouped by a single column is returned, no statistical data is returned for grouping multiple columns. Example 1: Group by grouping sets (A) indicates the number of groups: 1. group by A indicates the returned result set. Group by A indicates the statistical result set of the preceding groups. Example 2: Group by grouping sets (A, B): Two grouping types; one is group by A, and the other is group by B. Return result set: the Union of the preceding two grouping statistical result sets without removing duplicate data. Example 3: Group by grouping sets (A, B, C): 3 grouping types; 1: group by A www.2cto.com; 2: group by B; 3: group by C Return result set: This is the union of the preceding three group statistical result sets without removing duplicate data. Select t. city_name, decode (t. directline_type, null, 'Total', directline_type, directline_type), sum (t. amount) from tmp_dl_info t group by rollup (t. city_name, t. directline_type) by love398146779