hive通过grouping sets多维度组合去重统计避免使用distinct

系统管理员 2021-09-23 23:28 514阅读 1赞

在hive中，如果遇到多维度组合统计，并且要进行去重统计，例如统计不同维度组合的访问用户数，比如统计运营商、手机品牌、网络类型的用户数，怎样避免不用ditinct（因为distinct效率低），并且grouping\_\_id和以前维度组合保持一致呢？

select * from temp.temp_active_user_info t limit 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70]  
实现方法一：通过grouping sets和distinct进行统计

select grouping__id as group_id, 
           nvl(phone_brand, '剔重汇总') phone_brand, 
           nvl(network_type, '剔重汇总') network_type, 
           nvl(provider_name, '剔重汇总') provider_name, 
           count(distinct user_id) user_num
      from temp.temp_active_user_info t
     group by phone_brand,     --1
              network_type,    --2
              provider_name    --4
    grouping sets (
     (phone_brand),      --1
     (network_type, provider_name)   --6
    );

统计结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 1]  
实现方法二：通过grouping sets将user\_id加入维度组合再进行group by统计

select group_id, phone_brand, network_type, provider_name, count(1) user_num
      from 
      (
        select cast(grouping__id as bigint)&7 as group_id,--一定要先将grouping__id转换为数值类型 
               nvl(phone_brand, '剔重汇总') phone_brand, 
               nvl(network_type, '剔重汇总') network_type, 
               nvl(provider_name, '剔重汇总') provider_name, 
               user_id
          from temp.temp_active_user_info t
         group by phone_brand,     --1
                  network_type,    --2
                  provider_name,   --4
                  user_id          --8
        grouping sets (
         (phone_brand, user_id),      --9&7=1
         (network_type, provider_name, user_id)   --14&7=6
        )
      ) t
     group by group_id, phone_brand, network_type, provider_name
    ;

统计结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 2]

注意：

1.  先将grouping\_\_id转换为数值类型
2.  &前后不能有空格
3.  &后的数字为去重字段的位置数减去1，例如上面的SQL语句种user\_id的位置数为8，那&后紧跟7

通过实践证明，两种统计方法结果一样，并且grouping\_\_id也一样，但第二种方法避免了distinct的出现，当数据量特别大时会感觉到方法二执行效率和占用资源明显优于方法一

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70]: /images/20210923/6dce9ddd6b9a426090546ac01082f2de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 1]: /images/20210923/2bb187eabb9e455f9d93a36ce41d6455.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 2]: /images/20210923/2aaf055e9a934f5c82c48c49579be944.png