hive 常用函数 grouping sets last_value first_value

数据准备 

desc temp_shop_info;
select * from temp_shop_info;

shop_id	commodity_id	sale
110	1	10
110	2	20
110	3	30
110	4	50
110	5	60
110	6	20
110	7	80
111	1	90
111	2	80
111	3	50
111	4	70
111	5	20
111	6	10

1、grouping sets

grouping sets是一种将多个 group by 逻辑写在一个sql语句中的便利写法。等价于将不同维度的 group by 结果集进行 union all。

select shop_id, commodity_id, sum(sale)
from temp_shop_info
group by shop_id, commodity_id grouping sets (shop_id, commodity_id);

shop_id	commodity_id	sale
	7	80
	6	30
	1	100
	5	80
110		270
111		320
	3	80
	2	100
	4	120

select shop_id, null commodity_id, sum(sale)
from temp_shop_info
group by shop_id
union all
select null shop_id, commodity_id, sum(sale)
from temp_shop_info
group by commodity_id;

shop_id	commodity_id	sale
	7	80
	6	30
	1	100
	5	80
110		270
111		320
	3	80
	2	100
	4	120

2、last_value first_value

select *,
       row_number() over (partition by shop_id order by sale)           rn,
       first_value(sale) over (partition by shop_id order by sale)      first_value,
       first_value(sale) over (partition by shop_id order by sale desc) first_value_
from temp_shop_info;

shop_id	commodity_id	sale	rn	first_value	first_value_
110	7	80	7	10	80
110	5	60	6	10	80
110	4	50	5	10	80
110	3	30	4	10	80
110	2	20	2	10	80
110	6	20	3	10	80
110	1	10	1	10	80
111	1	90	6	10	90
111	2	80	5	10	90
111	4	70	4	10	90
111	3	50	3	10	90
111	5	20	2	10	90
111	6	10	1	10	90

select *,
       row_number() over (partition by shop_id order by sale)          rn,
       last_value(sale) over (partition by shop_id order by sale )     last_value,
       last_value(sale) over (partition by shop_id order by sale desc) last_value_
from temp_shop_info;

shop_id	commodity_id	sale	rn	first_value	first_value_
110	7	80	7	80	80
110	5	60	6	60	60
110	4	50	5	50	50
110	3	30	4	30	30
110	2	20	2	20	20
110	6	20	3	20	20
110	1	10	1	10	10
111	1	90	6	90	90
111	2	80	5	80	80
111	4	70	4	70	70
111	3	50	3	50	50
111	5	20	2	20	20
111	6	10	1	10	10