hive 中over()和group by配合使用

建表：
create table temp.practice(
shop_id string,
user_id string,
amt int
)
stored as orc;
插入数据
insert into temp.practice
values
(‘01’,‘01’,40),
(‘01’,‘01’,20),
(‘01’,‘02’,30),
(‘02’,‘03’,100),
(‘02’,‘03’,0),
(‘02’,‘02’,20),
(‘02’,‘01’,10),
(‘03’,‘05’,50),
(‘03’,‘06’,60);

以shop_id和user_id两个字段为例进行测试：
1.对shop_id和user_id 进行分组，设置窗口为shop_id
select shop_id, user_id, count() over(partition by shop_id)
from temp.practice
group by shop_id,user_id;
结果如下：

2.对shop_id和user_id 进行分组，设置窗口为user_id
select shop_id, user_id, count() over(partition by user_id)
from temp.practice