hive grouping set

reference

• https://www.cnblogs.com/erlou96/p/13564191.html

data-demo

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 query

select 
    month,day,count(cookieid) 
from cookie5 
    group by month,day 
grouping sets (month,day);

same as group query

select month,NULL as day,count(cookieid) as nums from cookie5 group by month
union all
select NULL as month,day,count(cookieid) as nums from cookie5 group by day;

result

| month    | day         | c2 |
| -        | -           | -  |
| NULL     | 2015-03-10  | 4  |
| NULL     | 2015-03-12  | 1  |
| NULL     | 2015-04-12  | 2  |
| NULL     | 2015-04-13  | 3  |
| NULL     | 2015-04-15  | 2  |
| NULL     | 2015-04-16  | 2  |
| 2015-03  | NULL        | 5  | 
| 2015-04  | NULL        | 19 | 

GROUPING__ID query

select 
  month,
  day,
  count(distinct cookieid) as uv,
  GROUPING__ID
from cookie5 
group by month,day 
grouping sets (month,day) 
order by GROUPING__ID;

same as group query

SELECT month,NULL as day,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month
UNION ALL 
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5 GROUP BY day;

result

| _u1.month | _u1.day     | _u1.uv  | _u1.grouping_id |
| 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               |
| NULL      | 2015-04-16  | 2       | 2               |
| 2015-03   | NULL        | 5       | 1               |
| 2015-04   | NULL        | 6       | 1               |

all demo query

SELECT  month, day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID 
FROM cookie5 
GROUP BY month,day 
GROUPING SETS (month,day,(month,day)) 
ORDER BY GROUPING__ID;

same as group query

SELECT month,NULL as day,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5 GROUP BY month 
UNION ALL 
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5 GROUP BY day
UNION ALL 
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM cookie5 GROUP BY month,day;

result

| month   | day        | uv | grouping_id |
| 2015-04 | NULL       | 6  | 1           |
| 2015-03 | NULL       | 5  | 1           |
| NULL    | 2015-03-10 | 4  | 2           |
| NULL    | 2015-04-16 | 2  | 2           |
| NULL    | 2015-04-15 | 2  | 2           |
| NULL    | 2015-04-13 | 3  | 2           |
| NULL    | 2015-04-12 | 2  | 2           |
| NULL    | 2015-03-12 | 1  | 2           |
| 2015-04 | 2015-04-16 | 2  | 3           |
| 2015-04 | 2015-04-12 | 2  | 3           |
| 2015-04 | 2015-04-13 | 3  | 3           |
| 2015-03 | 2015-03-12 | 1  | 3           |
| 2015-03 | 2015-03-10 | 4  | 3           |
| 2015-04 | 2015-04-15 | 2  | 3           |