hive--GROUPING SETS方法产生的聚合问题

最新推荐文章于 2024-04-18 08:30:00 发布

原创最新推荐文章于 2024-04-18 08:30:00 发布 · 406 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive

hive 专栏收录该内容

1 篇文章

订阅专栏

本文提供了一个具体的PV（页面浏览量）和UV（独立访客数）计算案例，通过SQL查询来展示如何根据不同维度如活动ID、页面名称等进行汇总统计。通过对原始数据的分组和聚合操作，详细说明了计算过程及GROUPING SETS的使用。

--- 这是计算pv uv指标
  select
    COALESCE(activity_id,'ALL')             as activity_id          
    , COALESCE(page_name,'ALL')             as page_name

    , COALESCE(product,'ALL')               as product
    , COALESCE(product_browse_name,'ALL')   as product_browse_name

    , COALESCE(platform,'ALL')              as platform
    
    , COALESCE(entry_src,'ALL')             as entry_src
    , GROUPING__ID
    , SUM(show_cnt)                         as show_cnt
    , COUNT(distinct user_id)               as show_user_num
  FROM(
      select  
        activity_id
        , product
        , page_name
        , platform

        , product_browse_name
        , entry_source    AS entry_src
        , user_id
        , count(1)  as show_cnt
      from
        xxx.xxxxx
      WHERE
        p_date = '20201006'
        and show_page_action='mm'
      group by 
        activity_id
        , product
        , page_name
        , platform
        , product_browse_name
        , entry_source
        , user_id
      )t1
  group by
    activity_id
    , page_name

    , product
    , product_browse_name

    , platform
    , entry_src
  GROUPING SETS(
    (product_browse_name,platform,entry_src),
    (activity_id,page_name,product,product_browse_name),
    (activity_id,page_name,platform),
    (activity_id,page_name,entry_src),
    (activity_id,page_name,product,product_browse_name,platform),
    (activity_id,page_name,product,product_browse_name,entry_src),
    (activity_id,page_name,platform,entry_src),
    (activity_id,page_name,product,product_browse_name,platform,entry_src)
  )

select
    product_browse_name
    , platform
    , entry_src
    , SUM(show_cnt)                         as show_cnt
    , COUNT(distinct user_id)               as show_user_num
    , COUNT(distinct device_id)             as show_device_num
  FROM(
      select  
        activity_id
        , page_name
        , product
        , product_browse_name
        
        , platform
        , entry_source    AS entry_src
        , device_id
        , user_id
        , count(1)  as show_cnt
      from
        xxx.xxxx
      WHERE
        p_date = '20201006'
        and show_page_action='mm'
      group by 
        activity_id
        , product
        , page_name
        , platform
        , product_browse_name
        , entry_source
        , device_id
        , user_id
      )t1
  group by
    product_browse_name
    , platform
    , entry_src