Hive analysis window function (5) GROUPINGSETS, groupinggrouping ID, CUBE, ROLLUP

Source: Internet
Author: User
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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.