HIVE中的分组聚合语句

server/2025/3/4 23:29:37/

GROUPING SETS使用

grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

--GROUP BY a, b GROUPING SETS ((a,b))

hive (default)>

1.

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id GROUPING SETS ((order_date,user_id))

等价于

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id

2.

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id GROUPING SETS ((order_date,user_id), user_id)

等于

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id

UNION ALL

SELECT null,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY user_id

3.

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id GROUPING SETS (order_date,user_id)

等于

SELECT order_date, null,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date

UNION ALL

SELECT null, user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY user_id

4.

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id GROUPING SETS ((order_date,user_id), order_date,user_id, ())

等于

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id

UNION ALL

SELECT order_date, null,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date

UNION ALL

SELECT null, user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY user_id

UNION ALL

SELECT null, null,count(*) FROM ds_hive.ch6_t_order

;

with cube使用

group by a,b,c with cube group by中所有key的组合

等效

group by a,b,c grouping sets((a,b,c), (a,b), (a,c), (b,c), a, b, c, ())

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id

GROUPING SETS ((order_date,user_id), order_date,user_id, ())

;

-- 等效

SELECT order_date,user_id,count(*) FROM ds_hive.ch6_t_order GROUP BY order_date,user_id

with cube

;

with rollup的使用

-- GROUP BY age, sex with rollup 等效于 GROUP BY age, sex GROUPING SETS ( (age,sex),age,() )
-- 相当于按右侧递减的顺序group by

group by a,b,c grouping sets((a,b,c), (a,b), (a), ())

SELECT user_id

,product_id

,order_date

,count(id) FROM

ds_hive.ch6_t_order

GROUP BY user_id

,product_id

,order_date

with rollup;

--等于

SELECT user_id

,product_id

,order_date

,count(id) FROM

ds_hive.ch6_t_order

GROUP BY user_id

,product_id

,order_date

GROUPING SETS ( (user_id,product_id,order_date),(user_id,product_id),user_id,())

;