Rollup and cube usage in oracle

1. Oracle ROLLUP and CUBE usage in addition to the most basic syntax, Oracle group by statements also support ROLLUP and CUBE statements. For Group by ROLLUP (A, B, C), group by is 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. If www.2cto.com is a 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. Grouping_id () can beautify the effect. In addition to the GROUPING function, you can also use GROUPING_ID to identify the result of group. You can also Group by Rollup (A, (B, C), Group by A Rollup (B, C ),...... In this way, it is very convenient to combine statistics in the form you want. 2. instance SQL> select grade, id, num from a; grade id num ---------- a 1 1 a 2 2 B 3 4 B 4 4 4 perform rollup on the grade field: SQL> select grade, sum (num) from a group by rollup (grade); www.2cto.com grade sum (NUM) ---------- -------- a 3 B 8 11 simultaneously perform rollup on the grade and id Fields
SQL> SELECT decode (grouping_id (grade, ID), 2, 'subtotal ', 3, 'Total', grade) grade, 2 decode (grouping_id (grade, ID), 1, 'subtotal ', 3, 'Total', ID) ID, 3 SUM (num) 4 FROM a group by rollup (grade, ID) 5/grade id sum (NUM) ---------- a 1 1 a 2 2 2 a subtotal 3 B 3 4 B 4 4 B subtotal 8 total 11 7 rows selected then look at the first grade group, then perform rollup on the id: SQL> SELECT grade, 2 decode (GROUPING (ID), 1, 'Total', ID) ID, 3 SUM (num) 4 FROM a group by grade, rollup (ID) 5/grade id sum (NUM) ---------- a 1 a 2 2 2 a total 3 B 3 4 B 4 4 B Total 8 6 rows selected here GROUP BY grade, rollup (ID) the result of group by grade and rollup (ID) is similar to that of ROLLUP (grade, ID), but the sum of the last line is missing, however, we can also see that when rollup has multiple fields, not only one field works. We can think that what you understand is only the accumulation of the first field, with group by grade, the result of rollup (ID) is very close. Let's look at the rollup3 fields again: www.2cto.com SQL> select part, grade, id, num from; part grade id num ---- ---------- p1 a 1 1 p1 a 2 2 p1 B 3 3 p1 B 4 4 p2 c 5 5 p2 d 6 6 6 rows selected SQL> SELECT decode (grouping_id (part, grade, ID), 7, 'Total', part) part, 2 decode (grouping_id (part, grade, ID), 3, 'subtotal ', 7, 'Total ', grade) grade, 3 decode (grouping_id (part, grade, ID), 1, 'subtotal ', 3, 'subtotal', 7, 'Total', ID) ID, 4 SUM (num) 5 FROM a group by rollup (part, grade, ID) 6/part grade id sum (NUM) ---- ---------- p1 a 1 1 p1 a 2 2 p1 a subtotal 3 p1 B 3 3 p1 B 4 4 p1 B subtotal 7 p1 Subtotal 10 p2 c 5 5 p2 c subtotal 5 p2 d 6 6 p2 d subtotal 6 p2 Subtotal 11 Total total 21 13 rows selected not only accumulate the first field here, group by (part, grade, ID), group by (part, grade), and then group by (part, finally, let's accumulate all www.2cto.com and check the difference between rollup and cube: For ROLLUP (part, grade, ID), grouping_id (part, grade, ID), the value range is) the sum is 1, 0, 0, 0, 1, 1, 1, 1, 1, 1, and for cube (part, grade, ID ), the value range of grouping_id (part, grade, ID) is between 0 and 7, that is, part, grade, ID (as 1 in total) 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 1, 0 author runming918