Cube and a rollup

Grouping sets can be grouped according to the grouping sets parameter, such as (school,grade),school,grade,(). Grouping sets is defined by grouping sets. For example, if we define four, we define four, and if we define one, we define one

select
    grouping__id, nvl(school,'All grades'),nvl(grade,'All School'),count(1) as userCnt
from
    ods.ods_student
group by
    school,grade
grouping sets(
       (school,grade),school,grade,()
)
order by
    grouping__id ;
Copy the code

cube

For example, in grouping sets, we use the grouping field of group BY to determine the required dimension and send it to the grouping sets function. In the example above is (school,grade),school,grade,()

You can think of cube as a special case of grouping sets in a group by query where all the grouped fields are grouped as dimensions

(school,grade),(school,null),(null,grade),(null, grade), This is equivalent to (school,grade),school,grade,()

select grouping__id, NVL (school,' grade '),count(1) as userCnt from ods.ods_student group by school,grade with cube order by grouping__id ;Copy the code

Cube is a special case of grouping sets. Cube is a special case of grouping sets. Cube is a special case of grouping sets

Especially if we have a lot of grouping fields, because you might miss certain combinations if you try to combine them manually

(a,b) => (a,b),(a, NULL),(NULL,b),(null, NULL)

rollup

Is a subset of CUBE, dominated by the leftmost dimension, from which hierarchical aggregation is performed. The right side has to be empty when the left side is empty, For example, the rollup dimension of group by Month,day is (month,day), (month, NULL), (NULL,null) and the cube dimension of Group by Month,day is (month,day), (month,null), (null,day),(null,null)

select
    grouping__id, nvl(school,'All grades'),nvl(grade,'All School'),count(1) as userCnt
from
    ods.ods_student
group by
    school,grade
with rollup
order by
    grouping__id ;
Copy the code

Grouping__id =2; grouping__id=2; grouping__id=2

conclusion

  • Grouping sets can be grouped in any given dimension according to our grouping sets

  • A cube can be seen as a special case of grouping sets, where a dimension is all of the grouping by fields

  • Rollup can be thought of as a cube subset, removing the case where the left field of the dimension is null but the right field is not

  • The grouping sets, cube or rollup criteria is the number of dimensions that we regrouping fields into. If we want all of the dimensions, it’s Cube. If not all of the dimensions are represented, it’s rollup. The other is grouping sets