hive grouping set

reference

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           |

Original: https://www.cnblogs.com/suanec/p/16040353.html
Author: 苏轶然
Title: hive grouping set

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/6827/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

免费咨询
免费咨询
扫码关注
扫码关注
联系站长

站长Johngo!

大数据和算法重度研究者!

持续产出大数据、算法、LeetCode干货,以及业界好资源!

2022012703491714

微信来撩,免费咨询:xiaozhu_tec

分享本页
返回顶部