1. Which of the following methods is equivalent to GROUPINGSETS? 2. Which of the following keywords is used for aggregation based on all the combinations of dimensions of GROUPBY? 3. What is the relationship between ROLLUP and ROLLUP? GROUPINGSETS, groupinggrouping ID, CUBE, and ROLLUP analysis functions are usually used in OLAP and cannot be accumulated. the drill-up and drill-down indicators must be consistent according to different dimensions.
1. Which of the following methods is equivalent to grouping sets? 2. which keyword is used for aggregation based on all the combinations of group by dimensions? 3. What is the relationship between ROLLUP and ROLLUP? Grouping sets, groupinggrouping ID, CUBE, and ROLLUP analysis functions are usually used in OLAP and cannot be accumulated. the drill-up and drill-down indicators must be consistent according to different dimensions.
1. Which of the following methods is equivalent to grouping sets?
2. which keyword is used for aggregation based on all the combinations of group by dimensions?
3. What is the relationship between ROLLUP and ROLLUP?
Grouping sets, groupinggrouping ID, CUBE, and ROLLUP are usually used in OLAP and cannot be Accumulated. statistics on drill-up and drill-down indicators in different dimensions are also required. for example, the number of UVS per hour, day, or month.
Hive version: apache-hive-0.13.1Data Preparation:
2015-03,2015-03-10,cookie1 2015-03,2015-03-10,cookie5 2015-03,2015-03-12,cookie7 2015-04,2015-04-12,cookie3 2015-04,2015-04-13,cookie2 2015-04,2015-04-13,cookie4 2015-04,2015-04-16,cookie4 2015-03,2015-03-10,cookie2 2015-03,2015-03-10,cookie3 2015-04,2015-04-12,cookie5 2015-04,2015-04-13,cookie6 2015-04,2015-04-15,cookie3 2015-04,2015-04-15,cookie2 2015-04,2015-04-16,cookie1 CREATE EXTERNAL TABLE lxw1234 ( month STRING, day STRING, cookieid STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile location '/tmp/lxw11/'; hive> select * from lxw1234; OK 2015-03 2015-03-10 cookie1 2015-03 2015-03-10 cookie5 2015-03 2015-03-12 cookie7 2015-04 2015-04-12 cookie3 2015-04 2015-04-13 cookie2 2015-04 2015-04-13 cookie4 2015-04 2015-04-16 cookie4 2015-03 2015-03-10 cookie2 2015-03 2015-03-10 cookie3 2015-04 2015-04-12 cookie5 2015-04 2015-04-13 cookie6 2015-04 2015-04-15 cookie3 2015-04 2015-04-15 cookie2 2015-04 2015-04-16 cookie1
GROUPING SETS
In a group by query, aggregation is performed based on the combination of different dimensions, which is equivalent to union all for the group by result set of different dimensions.
SELECT month, day, COUNT (DISTINCT cookieid) AS uv, groupinggrouping id from lxw1234 group by month, day grouping sets (month, day) order by groupinggrouping ID; month day uv grouping1_id limit 2015-03 NULL 5 1 2015-04 NULL 6 1 NULL 2015-03-10 4 2 NULL 2015-03-12 1 2 NULL 2015-04-12 2 2 NULL 2015-04-13 3 2 NULL 2015-04-15 2 2 2 NULL 2015-04-16 2 2 is equivalent to SELECT month, NULL, COUNT (DISTINCT cookieid) AS uv, 1 AS groupingrelative id from lxw1234 group by month union all select null, day, COUNT (DISTINCT cookieid) AS uv, 2 AS groupingtransferid FROM lxw1234 group by day
Another example is:
SELECT month, day, COUNT (DISTINCT cookieid) AS uv, groupinggrouping id from lxw1234 group by month, day grouping sets (month, day, (month, day) order by groupinggrouping ID; month day uv grouping1_id limit 2015-03 NULL 5 1 2015-04 NULL 6 1 NULL 2015-03-10 4 2 NULL 2015-03-12 1 2 NULL 2015-04-12 2 2 NULL 2015-04-13 3 2 NULL 2015-04-15 2 2 2 NULL 2015-04-16 2 2 2015-03 2015-03-10 4 3 2015-03 2015-03-12 1 3 2015-04 2015-04-12 2 3 2015-04 2015-04-13 3 3 3 2015-04 2015-04-15 2 3 2015-04 2015-04-16 2 3 is equivalent to SELECT month, NULL, COUNT (DISTINCT cookieid) AS uv, 1 AS groupingrelative id from lxw1234 group by month union all select null, day, COUNT (DISTINCT cookieid) AS uv, 2 AS groupingtransferid FROM lxw1234 group by day union all select month, day, COUNT (DISTINCT cookieid) AS uv, 3 AS groupingtransferid FROM lxw1234 group by month, day
Groupinggroups ID indicates which group the result belongs.
CUBE
All groups are aggregated based on the group by dimension.
SELECT month, day, COUNT (DISTINCT cookieid) AS uv, groupingrelative id from lxw1234 group by month, day with cube order by groupingrelative ID; month day uv grouping1_id every NULL 7 0 2015-03 NULL 5 1 2015-04 NULL 6 1 NULL 2015-04-12 2 2 NULL 2015-04-13 3 2 NULL 2015-04-15 2 2 NULL 2015-04-16 2 2 NULL 2015-03-10 4 2 NULL 2015-03-12 1 2 2015-03 2015-03-10 4 3 2015-03 2015-03-12 1 3 2015-04 2015-04-16 2 3 2015-04 2015-04-12 2 3 2015-04 2015-04-13 3 3 3 2015-04 2015-04-15 2 3 is equivalent to select null, NULL, COUNT (DISTINCT cookieid) AS uv, 0 AS groupingrelative id from lxw1234 union all select month, NULL, COUNT (DISTINCT cookieid) AS uv, 1 AS groupingtransferid FROM lxw1234 group by month union all select null, day, COUNT (DISTINCT cookieid) AS uv, 2 AS groupingtransferid FROM lxw1234 group by day union all select month, day, COUNT (DISTINCT cookieid) AS uv, 3 AS groupingcompletion id from lxw1234 group by month, day
ROLLUP
Is a subset of the CUBE. it is based on the leftmost dimension and hierarchical aggregation is performed from this dimension.
For example, hierarchical aggregation BY month: SELECT month, day, COUNT (DISTINCT cookieid) AS uv, grouping1_id FROM lxw1234 group by month, day with rollup order by grouping1_id; month day uv grouping1_id every NULL 7 0 2015-03 NULL 5 1 2015-04 NULL 6 1 2015-03 2015-03-10 4 3 2015-03 2015-03-12 1 3 2015-04 2015-04-12 2 3 2015 -04 2015-04-13 3 3 2015-04 2015-04-15 2 3 2015-04 2015-04-16 2 3 can realize the drilling process as follows: UV of the day of the month-> UV of the month-> total UV-change the order of month and day, then perform hierarchical aggregation in the day dimension: SELECT day, month, COUNT (DISTINCT cookieid) AS uv, groupingdirection id from lxw1234 group by day, month with rollup order by groupingdirection ID; day month uv grouping1_id limit NULL 7 0 2015-04-13 NULL 3 1 2015-03-12 NULL 1 1 2015-04-15 NULL 2 1 2015-03-10 NULL 4 1 2015-04-16 NULL 2 1 2015-04-12 NULL 2 1 2015-04-12 2015-04 2 3 2015-03-10 2015-03 4 3 2015-03-12 2015-03 1 3 2015-04-13 2015-04 3 3 2015-04-15 2015-04 2 3 2015-04-16 2015-04 2 3 can realize this drill-up process: UV of the day-month> UV of the day-> total UV (here, aggregation is based on the day and month, the same as the result of aggregation based on the day, because there is a parent-child relationship, if it is a combination of other dimensions, will be different)