hive窗口函数/分析函数详细剖析（三）

SELECT 
  dept,
  userid,
  sal,
  PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分组内
  RANK() OVER(ORDER BY sal) AS rn11,          --分组内RANK值
  SUM(1) OVER(PARTITION BY NULL) AS rn12,     --分组内总行数
  PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
  FROM bigdata_t3;
  rn1: rn1 = (rn11-1) / (rn12-1) 
       第一行,(1-1)/(5-1)=0/4=0
       第二行,(2-1)/(5-1)=1/4=0.25
       第四行,(4-1)/(5-1)=3/4=0.75
  rn2: 按照dept分组，
       dept=d1的总行数为3
       第一行，(1-1)/(3-1)=0
       第三行，(3-1)/(3-1)=1

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
  CREATE TABLE bigdata_t5 (
  month STRING,
  day STRING, 
  cookieid STRING 
  ) ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
  stored as textfile;
  加载数据：
  load data local inpath '/root/hivedata/bigdata_t5.dat' into table bigdata_t5;

SELECT 
  month,
  day,
  COUNT(DISTINCT cookieid) AS uv,
  GROUPING__ID 
  FROM bigdata_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 bigdata_t5 GROUP BY month UNION ALL 
  SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day;

SELECT 
  month,
  day,
  COUNT(DISTINCT cookieid) AS uv,
  GROUPING__ID 
  FROM bigdata_t5 
  GROUP BY month,day 
  GROUPING SETS (month,day,(month,day)) 
  ORDER BY GROUPING__ID;
  等价于
  SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month 
  UNION ALL 
  SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
  UNION ALL 
  SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;

SELECT 
  month,
  day,
  COUNT(DISTINCT cookieid) AS uv,
  GROUPING__ID 
  FROM bigdata_t5 
  GROUP BY month,day 
  WITH CUBE 
  ORDER BY GROUPING__ID;
  等价于
  SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM bigdata_t5
  UNION ALL 
  SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM bigdata_t5 GROUP BY month 
  UNION ALL 
  SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM bigdata_t5 GROUP BY day
  UNION ALL 
  SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM bigdata_t5 GROUP BY month,day;

比如，以month维度进行层级聚合：
  SELECT 
  month,
  day,
  COUNT(DISTINCT cookieid) AS uv,
  GROUPING__ID  
  FROM bigdata_t5 
  GROUP BY month,day
  WITH ROLLUP 
  ORDER BY GROUPING__ID;
  --把month和day调换顺序，则以day维度进行层级聚合：
  SELECT 
  day,
  month,
  COUNT(DISTINCT cookieid) AS uv,
  GROUPING__ID  
  FROM bigdata_t5 
  GROUP BY day,month 
  WITH ROLLUP 
  ORDER BY GROUPING__ID;
  （这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）