开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）： Hive 高阶--分组窗口函数--OLAP 相关分组函数（GROUPING SETS,CUBE,ROLLUP）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/696/detail/12236
Hive 高阶--分组窗口函数--OLAP 相关分组函数（GROUPING SETS,CUBE,ROLLUP）
内容介绍：
一、数据准备
二、GROUPING SETS
三、CUBE
四、ROLLUP
一、数据准备
1、了解在 olap 面向分析中常见的窗口函数，主要跟分组维度多维分析相关，数据准备，数据是非常标准的的结构化数据，有 month 哪一月，哪一天，比如3月10号，cookie id。
2018-03,2018-03-10, cookie1
2018-03,2018-03-10, cookie5
2018-03,2018-03-12, cookie7
2018-04,2018-04-12, cookie3
2018-04,2018-04-13, cookie2
2018-04,2018-04-13, cookie4
2018-04,2018-04-16, cookie4
2018-03,2018-03-10, cookie2
2018-03,2018-03-10, cookie3
2018-04,2018-04-12, cookie5
2018-04,2018-04-13, cookie6
2018-04,2018-04-15, cookie3
2018-04,2018-04-15, cookie2
2018-04,2018-04-16, cookie1
2、创建表把结构化数据映射出，建表之后分隔符为逗号，文件格式为普通文本文件，这些都是非常基本操作，把数据的文件创建，叫 itcast_t5.dat，打开复制，在服务器中vim创建文件，将讲义上的数据完整的进行复制，复制好之后再就是常见的两步操作，建表 create table 把数据加载到表中，这步操作能保证后面操作的执行，把建表语句复制到 hive 的终端中进行 create table。
CREATE TABLE itcast t5 (
month STRING ,
day STRING,
cookieid STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' , '
stored as textfile;
3、使用loaddata 命令把数据从本地进行加载。
加载数据 :
load data local inpath ' /root/hivedata/itcast_t5.dat' into table itcast_t5;
二、GROUPING SETS
1、grouping 是分组，sets 是集合，grouping sets 是一种将多个 group by 逻辑写在一个 sq| 语句中的便利写法。比如要进行分组，根据人分组，再根据性别分组，这时如果两个都要写，大概需要写两个 sql，这个语法能通过一种手段可以将多个分组逻辑写在 sql 中，等价于将不同维度的 GROUP BY 结果集进行 UNION ALL。 GROUPING_ID ,表示结果属于哪一个分组集合。
2、分组的维度有两个，month，day。复制 sql 在 hive 中做执行。首先根据 month 分组，再根据 day 进行分组，因为有两个维度，所以结果，有两部分，第一部分用 GROUPING_ID 表示结果属于哪一个分组集合，GROUP BY month 或者 GROUP BY day，GROUPING_ID 是1表示第一个分组，GROUPING_ID 是2表示另外一个维度分组，其中哪个是 month，哪个是day要结合前面数据，通过对结果的剖析，发现 day 为空，month 数据一个是4月一个是3月，根据 month 分组，数据中只有3月和4月，数据将会分为两组，分成两组之后，每个分组内进行去除统计操作，不重复的人 uv 统计，因此在4月中，有六个人，3月有五个人。
SELECT
month ,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING_ID
FROM itcast_ t5
GROUP BY month, day
GROUPING SETS(month,day)
ORDER BY GROUPING_ID;
grouping _id 表示这一组结果属于哪个分组集合，
根据 grouping sets 中分组条件 month , day , 1是代表 month , 2是代表 day
等价于
SELECT month , NULL , COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING_ ID FROM itcast_ t5根据 month 进行分组，每个组内做统计。
GROUP BY month UNION ALL
SELECT NULL as month , day, COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING_ ID FROM
itcast_ t5 GROUP BY day;
其他的字段都是强制自己返回的，比如 month 查询出来，还有 null 字段，1作为GROUPING_ ID，GROUP BY day，把 sql 做 UNION ALL，整个语句就相当于 grouping sets 语句，执行后发现结果一样，但是顺序不一样，上面是先 month 再day，下面是先 day 后 month，再进行 GROUP BY 即可。根据 month 分为两个月，3月4月，这就 grouping sets 的使用相当于在一个 sql 中可以进行多个维度的分组，把结果做聚集。
三、CUBE
1、CUBE (立方体 数据立方体 多维数据分析)
举个栗子：某个事情有A、B、C三个维度，根据这三个维度进行组合分析，共有多少种情况?
这些情况加起来就是所谓多维分析中数据立方体。
（1）没有维度：[]
不根据abc分，这种情况只有一种就是abc都不参与分组。
（2）一个维度：[A] [B] [C]
三种情况
（3）两个维度：[AB] [AC] [BC]
多维分析中维度对结果没有影响，顺序对结构没有影响，影响中间执行的过程。
（4）三个维度：[ABC]
共有8个结果，八个结果把它构成图形就可以构成CUBE，叫做立方体的概念。
2、画图板，比如画一个一维的，中间是两维的三个，三维一个，把它构成的三维图形，想象相当于变成立方体，3d的概念。
规律：假如有n个维度所有的维度组合情况是2的n次方。比如三个维度，二三次方是八，所以有八个结果。
3、根据 GROUP BY 的维度的所有组合进行聚合。
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING_ID
FROM itcast_ t5
GROUP BY month , day 总共两个维度，所有的排列组合加一起有四种
WITH CUBE
ORDER BY GROUPING_ ID;
零维的一个，一维的 month，day各一个，两维的 month，day 总共四种情况，复制，执行，不根据 month，day 分有七种，GROUPING_ ID 有0，1，2，3，0不根据 month 分，零维的，一维是根据 month 分的，3月4月，2是根据 day 维度分的，3是根据 month，day 分的，整个 month，day 两个维度构成的 CUBE 有四种情况，sql 背后相当于进行四次 GROUP BY。
4、等价于
SELECT NULL , NULL , COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING_ ID FROM itcast _t5
UNION ALL
不进行 GROUP BY 分组，直接进行 count 统计。执行验证，发现没有根据维度。
SELECT month ,NULL , COUNT (DISTINCT cookieid) AS uv,1 AS GROUPING ID FROM itcast, t5 GROUP BY month
UNION ALL
根据 month 进行分组，进行执行3月4月。
SELECT NULL , day , COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING ID FROM itcast _t5 GROUP BY day
UNION ALL
根据 day 进行分组。
SELECT month , day, COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING ID FROM itcast _t5
GROUP BY month , day;
根据 month，day 进行分组。通过 UNION ALL 语法，把这四个查询语句的结果做合并做最终的结果。WITH CUBE 可以简单的实现多维数据分析，相当于避免写太多的 sql 语句，零维，一维，两维总共四种情况。GROUPING_ ID 表示当中的分组情况。
四、ROLLUP
是 CUBE 的子集,以最左侧的维度为主,从该维度进行层级聚合。
比如，以 month 维度进行层级聚合:
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uV,
GROUPING__ ID
FROM itcast_ t5
GROUP BY month，day
WITH ROLLUP
ORDER BY GROUPING_ ID;
复制执行，以左侧为准，左侧维度会存在三种情况，没有 month，只有 month，有 month，day，总共两个维度，以左侧为准，month 分为有无，day 有没有，不会存在有 day，没有 month，以 month 为标准，最终结果分为三种情况，0分组中没有 month，不根据它进行分组，根据 month 分，只有 month，得到一个结果，根据 month 和 day 分，只根据 day 分，没有 month，以右边为准，所以 rollup 是 CUBE 的子集，是它的一部分。
把 month 和 day 做顺序颠倒，还有三种情况，没有 day，month 一种情况，只有 day 的一种情况，有 day 又有 month 的一种情况，三种情况，绝不可能没有 day，有 month，要以左边为准，这就是 rollup 函数。
--把 month 和 day 调换顺序，则以 day 维度进行层级聚合:
SELECT
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING_ ID
FROM itcast_ t5
GROUP BY day , month
WITH ROLLUP
ORDER BY GROUPING ID;
(这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样)
在面向分析，尤其是多位分析中非常常见，结合所补充的内容认真梳理，方便做数据分析的时候写上大量的 sql，一个 sql 可以搞定多个 sql 所执行的结果。
