HiveSQL实战积累_groupingsets加lvlcode实现快速多维度预聚合与关联

HiveSQL实战积累_groupingsets加lvlcode实现快速多维度预聚合与关联

1.SQL实例

   select 
         bu_id                
        ,max(bu_name)    as bu_name          
        ,dept_id_1            
        ,max(dept_name_1 ) as dept_name_1
        ,dept_id_2            
        ,max(dept_name_2 ) as dept_name_2
        ,dept_id_3           
        ,max(dept_name_3 ) as dept_name_3
        ,item_id              
        ,max(item_name )  as  item_name           
        ,max(item_type )  as  item_type    
        ,corp_type_cd
        ,shop_type_cd
        ,sum(valid_vend_qty     ) as  valid_vend_qty 
        ,sum(charge_vend_qty    ) as  charge_vend_qty 
        ,sum(mau                ) as  mau
        ,sum(new_vend_qty       ) as  new_vend_qty 
        ,sum(first_ord_vend_qty ) as  first_ord_vend_qty 
        ,sum(expire_vend_qty    ) as  expire_vend_qty 
        ,sum(renew_vend_qty     ) as  renew_vend_qty 
        ,sum(upgrade_vend_qty   ) as  upgrade_vend_qty
        ,sum(invalid_vend_qty   ) as  invalid_vend_qty
        ,sum(lost_vend_qty      ) as  lost_vend_qty 
        ,sum(recall_vend_qty    ) as  recall_vend_qty
        ,sum(valid_sale_amt     ) as  valid_sale_amt 
        ,sum(new_sale_amt       ) as  new_sale_amt 
        ,sum(renew_amt          ) as  renew_amt 
        ,sum(recall_amt         ) as  recall_amt
        ,sum(m_tat_rev_amt      ) as  m_tat_rev_amt 
        ,sum(m_act_rev_amt      ) as  m_act_rev_amt 
        ,sum(y_tat_rev_amt      ) as  y_tat_rev_amt 
        ,sum(y_act_rev_amt      ) as  y_act_rev_amt 
        ,sum(y_est_act_rev_amt  ) as  y_est_act_rev_amt
        ,lvl
        ,shop_level
   from
    (
         select --1.商家指标
            (case when grouping__id in(9,11,15,31,41,43,47,63,73,75,79,95,105,107,111,127,137,139,143,159,169,171,175,191,201,203,207,223,233,235,239,255) then  bu_id  else '999999' end) as bu_id                
           ,(case when grouping__id in(9,11,15,31,41,43,47,63,73,75,79,95,105,107,111,127,137,139,143,159,169,171,175,191,201,203,207,223,233,235,239,255) then  max(bu_name)  else '所有' end)as  bu_name           
           ,(case when grouping__id in(11,15,31,43,47,63,75,79,95,107,111,127,139,143,159,171,175,191,203,207,223,235,239,255) then  dept_id_1  else '999999' end) as dept_id_1            
           ,(case when grouping__id in(11,15,31,43,47,63,75,79,95,107,111,127,139,143,159,171,175,191,203,207,223,235,239,255) then  max(dept_name_1 )   else '所有' end) as dept_name_1
           ,(case when grouping__id in(15,31,47,63,79,95,111,127,143,159,175,191,207,223,239,255) then  dept_id_2  else '999999' end) as dept_id_2            
           ,(case when grouping__id in(15,31,47,63,79,95,111,127,143,159,175,191,207,223,239,255) then  max(dept_name_2 )   else '所有' end) as dept_name_2
           ,(case when grouping__id in(31,63,95,127,159,191,223,255) then  dept_id_3  else '999999' end) as dept_id_3           
           ,(case when grouping__id in(31,63,95,127,159,191,223,255) then  max(dept_name_3 )   else '所有' end) as dept_name_3
           ,(case when grouping__id in(40,41,43,47,63,104,105,107,111,127,168,169,171,175,191,232,233,235,239,255) then shop_level else '999999' end) as  shop_level
           ,item_id              
           ,max(item_name) as  item_name
           ,max(item_type) as  item_type
           ,(case when grouping__id in(72,73,75,79,95,104,105,107,111,127,200,201,203,207,223,232,233,235,239,255) then corp_type_cd else '999999' end) as corp_type_cd
           ,(case when grouping__id in(136,137,139,143,159,168,169,171,175,191,200,201,203,207,223,232,233,235,239,255) then shop_type_cd else '999999' end) as shop_type_cd
           ,count(distinct case when is_ord_valid=1 then a.vender_id end) as valid_vend_qty       
           ,count(distinct case when is_charge=1 then a.vender_id end) as charge_vend_qty      
           ,count(distinct case when is_visit=1 and is_ord_valid=1 then a.vender_id end) as mau                  
           ,count(distinct case when is_new=1 then a.vender_id end) as new_vend_qty         
           ,count(distinct case when is_first_ord=1 then a.vender_id end) as first_ord_vend_qty   
           ,count(distinct case when is_expired=1 then a.vender_id end) as expire_vend_qty      
           ,count(distinct case when is_renew=1 then a.vender_id end) as renew_vend_qty       
           ,count(distinct case when is_upgrade=1 then a.vender_id end) as upgrade_vend_qty     
           ,count(distinct case when is_invalid=1 then a.vender_id end) as invalid_vend_qty     
           ,count(distinct case when is_lost=1 then a.vender_id end) as lost_vend_qty        
           ,count(distinct case when is_recall=1 then a.vender_id end) as recall_vend_qty      
           ,sum(valid_sale_amt) as valid_sale_amt
           ,sum(new_sale_amt) as new_sale_amt         
           ,sum(renew_amt)  as renew_amt            
           ,sum(recall_amt) as recall_amt           
           ,0 as m_tat_rev_amt        
           ,0 as m_act_rev_amt        
           ,0 as y_tat_rev_amt        
           ,0 as y_act_rev_amt        
           ,0 as y_est_act_rev_amt
           ,(case 
              when grouping__id=8 and item_id='999999' then '1' 
              when grouping__id=9 and item_id='999999' then '3' 
              when grouping__id=11 and item_id='999999' then '5' 
              when grouping__id=15 and item_id='999999' then '7'
              when grouping__id=31 and item_id='999999' then '17'
              when grouping__id=40 and item_id='999999' then '21'
              when grouping__id=41 and item_id='999999' then '23'
              when grouping__id=43 and item_id='999999' then '25'
              when grouping__id=47 and item_id='999999' then '27'
              when grouping__id=63 and item_id='999999' then '29'
             
              when grouping__id=72 and item_id='999999' then '41' 
              when grouping__id=73 and item_id='999999' then '43' 
              when grouping__id=75 and item_id='999999' then '45' 
              when grouping__id=79 and item_id='999999' then '47'
              when grouping__id=95 and item_id='999999' then '49'
              when grouping__id=104 and item_id='999999' then '51'
              when grouping__id=105 and item_id='999999' then '53'
              when grouping__id=107 and item_id='999999' then '55'
              when grouping__id=111 and item_id='999999' then '57'
              when grouping__id=127 and item_id='999999' then '59'
             
              when grouping__id=136 and item_id='999999' then '61' 
              when grouping__id=137 and item_id='999999' then '63' 
              when grouping__id=139 and item_id='999999' then '65' 
              when grouping__id=143 and item_id='999999' then '67'
              when grouping__id=159 and item_id='999999' then '69'
              when grouping__id=168 and item_id='999999' then '71'
              when grouping__id=169 and item_id='999999' then '73'
              when grouping__id=171 and item_id='999999' then '75'
              when grouping__id=175 and item_id='999999' then '77'
              when grouping__id=191 and item_id='999999' then '79'
             
              when grouping__id=200 and item_id='999999' then '81' 
              when grouping__id=201 and item_id='999999' then '83' 
              when grouping__id=203 and item_id='999999' then '85' 
              when grouping__id=207 and item_id='999999' then '87'
              when grouping__id=223 and item_id='999999' then '89'
              when grouping__id=232 and item_id='999999' then '91'
              when grouping__id=233 and item_id='999999' then '93'
              when grouping__id=235 and item_id='999999' then '95'
              when grouping__id=239 and item_id='999999' then '97'
              when grouping__id=255 and item_id='999999' then '99'
              
              when grouping__id=8 and item_id<>'999999' then '9'
              when grouping__id=9 and item_id<>'999999' then '11' 
              when grouping__id=11 and item_id<>'999999' then '13'
              when grouping__id=15 and item_id<>'999999' then '15'
              when grouping__id=31 and item_id<>'999999' then '19'
              when grouping__id=40 and item_id<>'999999' then '31'
              when grouping__id=41 and item_id<>'999999' then '33'
              when grouping__id=43 and item_id<>'999999' then '35'
              when grouping__id=47 and item_id<>'999999' then '37'
              when grouping__id=63 and item_id<>'999999' then '39'
             
              when grouping__id=72 and item_id<>'999999' then '101' 
              when grouping__id=73 and item_id<>'999999' then '103' 
              when grouping__id=75 and item_id<>'999999' then '105' 
              when grouping__id=79 and item_id<>'999999' then '107'
              when grouping__id=95 and item_id<>'999999' then '109'
              when grouping__id=104 and item_id<>'999999' then '111'
              when grouping__id=105 and item_id<>'999999' then '113'
              when grouping__id=107 and item_id<>'999999' then '115'
              when grouping__id=111 and item_id<>'999999' then '117'
              when grouping__id=127 and item_id<>'999999' then '119'
             
              when grouping__id=136 and item_id<>'999999' then '121' 
              when grouping__id=137 and item_id<>'999999' then '123' 
              when grouping__id=139 and item_id<>'999999' then '125' 
              when grouping__id=143 and item_id<>'999999' then '127'
              when grouping__id=159 and item_id<>'999999' then '129'
              when grouping__id=168 and item_id<>'999999' then '131'
              when grouping__id=169 and item_id<>'999999' then '133'
              when grouping__id=171 and item_id<>'999999' then '135'
              when grouping__id=175 and item_id<>'999999' then '137'
              when grouping__id=191 and item_id<>'999999' then '139'
             
              when grouping__id=200 and item_id<>'999999' then '141' 
              when grouping__id=201 and item_id<>'999999' then '143' 
              when grouping__id=203 and item_id<>'999999' then '145' 
              when grouping__id=207 and item_id<>'999999' then '147'
              when grouping__id=223 and item_id<>'999999' then '149'
              when grouping__id=232 and item_id<>'999999' then '151'
              when grouping__id=233 and item_id<>'999999' then '153'
              when grouping__id=235 and item_id<>'999999' then '155'
              when grouping__id=239 and item_id<>'999999' then '157'
              when grouping__id=255 and item_id<>'999999' then '159'
              
           end) as lvl
        from 
(
select 
	*
from
	app.app_zs_z9004_srvc_vender_tag_new --商家版商家打标
where dt='""" + data_day_str + """'      
        and stat_ct_cd='""" + stat_ct_cd + """'
) a
left join
(
select
  vender_id,
  shop_id,
  case when corp_type_cd='0' then '0'
  	when corp_type_cd in ('1','2') then '1'
  else '-99'
  end as corp_type_cd,
  case when shop_type_cd in ('9','10','11','16','24','28','32','33','34','35','36','40','48','56') then shop_type_cd
  else '-99' end as shop_type_cd
from
  gdm.gdm_m01_vender_da
where dt = sysdate(-1)
) b
on a.vender_id = b.vender_id and a.shop_id = b.shop_id
group by 
               bu_id
              ,dept_id_1
              ,dept_id_2
              ,item_id
              ,dept_id_3
              ,shop_level
              ,corp_type_cd
              ,shop_type_cd
grouping sets(
                  (item_id),--00001000(8)
                  (bu_id,item_id),--00001001(9)
                  (bu_id,dept_id_1,item_id),--00001011(11)
                  (bu_id,dept_id_1,dept_id_2,item_id),--00001111(15)
                  (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id),--00011111(31)
                  (item_id,shop_level),--00101000(40)
                  (bu_id,item_id,shop_level),--00101001(41)
                  (bu_id,dept_id_1,item_id,shop_level),--00101011(43)
                  (bu_id,dept_id_1,dept_id_2,item_id,shop_level),--00101111(47)
                  (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_level),--00111111(63)
  								(item_id,corp_type_cd),--01001000(72)
                  (bu_id,item_id,corp_type_cd),--01001001(73)
                  (bu_id,dept_id_1,item_id,corp_type_cd),--01001011(75)
                  (bu_id,dept_id_1,dept_id_2,item_id,corp_type_cd),--01001111(79)
                  (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,corp_type_cd),--01011111(95)
                  (item_id,shop_level,corp_type_cd),--01101000(104)
                  (bu_id,item_id,shop_level,corp_type_cd),--01101001(105)
                  (bu_id,dept_id_1,item_id,shop_level,corp_type_cd),--01101011(107)
                  (bu_id,dept_id_1,dept_id_2,item_id,shop_level,corp_type_cd),--01101111(111)
                  (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_level,corp_type_cd),--01111111(127)
                  (item_id,shop_type_cd),--10001000(136)
                  (bu_id,item_id,shop_type_cd),--10001001(137)
                  (bu_id,dept_id_1,item_id,shop_type_cd),--10001011(139)
                  (bu_id,dept_id_1,dept_id_2,item_id,shop_type_cd),--10001111(143)
                  (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_type_cd),--10011111(159)
                  (item_id,shop_level,shop_type_cd),--10101000(168)
                  (bu_id,item_id,shop_level,shop_type_cd),--10101001(169)
                  (bu_id,dept_id_1,item_id,shop_level,shop_type_cd),--10101011(171)
                  (bu_id,dept_id_1,dept_id_2,item_id,shop_level,shop_type_cd),--10101111(175)
                  (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_level,shop_type_cd),--10111111(191)
  								(item_id,corp_type_cd,shop_type_cd),--11001000(200)
                  (bu_id,item_id,corp_type_cd,shop_type_cd),--11001001(201)
                  (bu_id,dept_id_1,item_id,corp_type_cd,corp_type_cd,shop_type_cd),--11001011(203)
                  (bu_id,dept_id_1,dept_id_2,item_id,corp_type_cd,shop_type_cd),--11001111(207)
                  (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,corp_type_cd,shop_type_cd),--11011111(223)
                  (item_id,shop_level,corp_type_cd,shop_type_cd),--11101000(232)
                  (bu_id,item_id,shop_level,corp_type_cd,shop_type_cd),--11101001(233)
                  (bu_id,dept_id_1,item_id,shop_level,corp_type_cd,shop_type_cd),--11101011(235)
                  (bu_id,dept_id_1,dept_id_2,item_id,shop_level,corp_type_cd,shop_type_cd),--11101111(239)
                  (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_level,corp_type_cd,shop_type_cd)--11111111(255)
             )
        
        union all
        
        select --2.服务月度收入实际
             (case when grouping__id in(1,3,7,9,11,15,23,31,33,35,39,55,41,43,47,63,65,67,71,87,129,131,135,151,73,75,79,95,137,139,143,159,97,99,103,119,161,163,167,183,193,195,199,215,105,107,111,127,169,171,175,191,201,203,207,223,225,227,231,247,233,235,239,255) then  bu_id  else '999999' end) as bu_id                
            ,(case when grouping__id in(1,3,7,9,11,15,23,31,33,35,39,55,41,43,47,63,65,67,71,87,129,131,135,151,73,75,79,95,137,139,143,159,97,99,103,119,161,163,167,183,193,195,199,215,105,107,111,127,169,171,175,191,201,203,207,223,225,227,231,247,233,235,239,255) then  max(bu_name)  else '所有' end)as  bu_name           
            ,(case when grouping__id in(3,7,11,15,23,31,35,39,55,43,47,63,67,71,87,131,135,151,75,79,95,139,143,159,99,103,119,163,167,183,195,199,215,107,111,127,171,175,191,203,207,223,227,231,247,235,239,255) then  dept_id_1  else '999999' end) as dept_id_1            
            ,(case when grouping__id in(3,7,11,15,23,31,35,39,55,43,47,63,67,71,87,131,135,151,75,79,95,139,143,159,99,103,119,163,167,183,195,199,215,107,111,127,171,175,191,203,207,223,227,231,247,235,239,255) then  max(dept_name_1 )   else '所有' end) as dept_name_1
            ,(case when grouping__id in(7,15,23,31,39,55,47,63,71,87,135,151,79,95,143,159,103,119,167,183,199,215,111,127,175,191,207,223,231,247,239,255) then  dept_id_2  else '999999' end) as dept_id_2            
            ,(case when grouping__id in(7,15,23,31,39,55,47,63,71,87,135,151,79,95,143,159,103,119,167,183,199,215,111,127,175,191,207,223,231,247,239,255) then  max(dept_name_2 )   else '所有' end) as dept_name_2 
            ,(case when grouping__id in(23,31,55,63,87,151,95,159,119,183,215,127,191,223,247,255) then  dept_id_3  else '999999' end) as dept_id_3           
            ,(case when grouping__id in(23,31,55,63,87,151,95,159,119,183,215,127,191,223,247,255) then  max(dept_name_3 )   else '所有' end) as dept_name_3
            ,(case when grouping__id in(32,33,35,39,55,40,41,43,47,63,96,97,99,103,119,160,161,163,167,183,104,105,107,111,127,168,169,171,175,191,224,225,227,231,247,232,233,235,239,255) then shop_level else '999999' end) as  shop_level           
            ,(case when grouping__id in(8,9,11,15,31,40,41,43,47,63,72,73,75,79,95,136,137,139,143,159,104,105,107,111,127,168,169,171,175,191,200,201,203,207,223,232,233,235,239,255) then  item_id  else '999999' end) as item_id              
            ,(case when grouping__id in(8,9,11,15,31,40,41,43,47,63,72,73,75,79,95,136,137,139,143,159,104,105,107,111,127,168,169,171,175,191,200,201,203,207,223,232,233,235,239,255) then  max(item_name )  else '所有' end) as item_name            
            ,(case when grouping__id in(8,9,11,15,31,40,41,43,47,63,72,73,75,79,95,136,137,139,143,159,104,105,107,111,127,168,169,171,175,191,200,201,203,207,223,232,233,235,239,255) then  max(item_type )  else 9 end) as item_type  --收费项目类型(1:版本,2:模块,3:套餐,9:所有)  
            ,(case when grouping__id in(64,65,67,71,87,72,73,75,79,95,96,97,99,103,119,192,193,195,199,215,104,105,107,111,127,200,201,203,207,223,224,225,227,231,247,232,233,235,239,255) then corp_type_cd else '999999' end) as corp_type_cd
            ,(case when grouping__id in(128,129,131,135,151,136,137,139,143,159,160,161,163,167,183,192,193,195,199,215,168,169,171,175,191,200,201,203,207,223,224,225,227,231,247,232,233,235,239,255) then shop_type_cd else '999999' end) as shop_type_cd
            ,0 as valid_vend_qty       
            ,0 as charge_vend_qty      
            ,0 as mau                  
            ,0 as new_vend_qty         
            ,0 as first_ord_vend_qty   
            ,0 as expire_vend_qty      
            ,0 as renew_vend_qty       
            ,0 as upgrade_vend_qty     
            ,0 as invalid_vend_qty     
            ,0 as lost_vend_qty        
            ,0 as recall_vend_qty      
            ,0 as valid_sale_amt       
            ,0 as new_sale_amt         
            ,0 as renew_amt            
            ,0 as recall_amt           
            ,0 as m_tat_rev_amt        
            ,sum(m_act_rev_amt) as m_act_rev_amt   --有效商家收入金额   
            ,0 as y_tat_rev_amt        
            ,sum(y_act_rev_amt) as y_act_rev_amt   --年度实际累积收入 
            ,sum(y_est_act_rev_amt) as y_est_act_rev_amt   --年度预估累积收入
            ,(case 
                  when grouping__id=0 then '1' 
                  when grouping__id=1 then '3' 
                  when grouping__id=3 then '5' 
                  when grouping__id=7 then '7'
                  when grouping__id=8 then '9'
              
                  when grouping__id=9 then '11'
                  when grouping__id=11 then '13'
                  when grouping__id=15 then '15'
                  when grouping__id=23 then '17'
                  when grouping__id=31 then '19'
              
                  when grouping__id=32 then '21'
                  when grouping__id=33 then '23'
                  when grouping__id=35 then '25'
                  when grouping__id=39 then '27'
                  when grouping__id=55 then '29'
              
                  when grouping__id=40 then '31'
                  when grouping__id=41 then '33'
                  when grouping__id=43 then '35'
                  when grouping__id=47 then '37'
                  when grouping__id=63 then '39'
              
                  when grouping__id=64 then '41' 
                  when grouping__id=65 then '43' 
                  when grouping__id=67 then '45' 
                  when grouping__id=71 then '47'
                  when grouping__id=87 then '49'
              
                  when grouping__id=128 then '61'
                  when grouping__id=129 then '63'
                  when grouping__id=131 then '65'
                  when grouping__id=135 then '67'
                  when grouping__id=151 then '69'
              
                  when grouping__id=72 then '101'
                  when grouping__id=73 then '103'
                  when grouping__id=75 then '105'
                  when grouping__id=79 then '107'
                  when grouping__id=95 then '109'
              
                  when grouping__id=136 then '121'
                  when grouping__id=137 then '123'
                  when grouping__id=139 then '125'
                  when grouping__id=143 then '127'
                  when grouping__id=159 then '129'
              
                  when grouping__id=96 then '51'
                  when grouping__id=97 then '53'
                  when grouping__id=99 then '55'
                  when grouping__id=103 then '57'
                  when grouping__id=119 then '59'
              
              		when grouping__id=160 then '71' 
                  when grouping__id=161 then '73' 
                  when grouping__id=163 then '75' 
                  when grouping__id=167 then '77'
                  when grouping__id=183 then '79'
              
                  when grouping__id=192 then '81'
                  when grouping__id=193 then '83'
                  when grouping__id=195 then '85'
                  when grouping__id=199 then '87'
                  when grouping__id=215 then '89'
              
                  when grouping__id=104 then '111'
                  when grouping__id=105 then '113'
                  when grouping__id=107 then '115'
                  when grouping__id=111 then '117'
                  when grouping__id=127 then '119'
              
                  when grouping__id=168 then '131'
                  when grouping__id=169 then '133'
                  when grouping__id=171 then '135'
                  when grouping__id=175 then '137'
                  when grouping__id=191 then '139'
              
              		when grouping__id=200 then '141'
                  when grouping__id=201 then '143'
                  when grouping__id=203 then '145'
                  when grouping__id=207 then '147'
                  when grouping__id=223 then '149'
              
              		when grouping__id=224 then '91'
                  when grouping__id=225 then '93'
                  when grouping__id=227 then '95'
                  when grouping__id=231 then '97'
                  when grouping__id=247 then '99'
              
              		when grouping__id=232 then '151'
                  when grouping__id=233 then '153'
                  when grouping__id=235 then '155'
                  when grouping__id=239 then '157'
                  when grouping__id=255 then '159'
             end) as lvl
        from 
(
select 
	*
from
	app.app_zs_z9002_srvc_rev_actual_new --服务月度收入实际
where dt='""" + data_day_str + """'      
        and stat_ct_cd='""" + stat_ct_cd + """'
) a
left join
(
select
  vender_id,
  shop_id,
  case when corp_type_cd='0' then '0'
  	when corp_type_cd in ('1','2') then '1'
  	else '-99'
  end as corp_type_cd,
  case when shop_type_cd in ('9','10','11','16','24','28','32','33','34','35','36','40','48','56') then shop_type_cd
  else '-99' end as shop_type_cd
from
  gdm.gdm_m01_vender_da
where dt = sysdate(-1)
) b
on a.vender_id = b.vender_id and a.shop_id = b.shop_id
       group by 
               bu_id
              ,dept_id_1
              ,dept_id_2
              ,item_id
              ,dept_id_3
              ,shop_level
              ,corp_type_cd
              ,shop_type_cd
        grouping sets(
                    (),--0
                    (bu_id),--00000001(1)
                    (bu_id,dept_id_1),--00000011(3)
                    (bu_id,dept_id_1,dept_id_2), --00000111(7)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3), --00010111(23)
                    
                    (item_id),--00001000(8)
                    (bu_id,item_id),--00001001(9)
                    (bu_id,dept_id_1,item_id),--00001011(11)
                    (bu_id,dept_id_1,dept_id_2,item_id),--00001111(15)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id),--00011111(31)
                    
          					(shop_level),--00100000(32)
                    (bu_id,shop_level),--00100001(33)
                    (bu_id,dept_id_1,shop_level),--00100011(35)
                    (bu_id,dept_id_1,dept_id_2,shop_level), --00100111(39)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,shop_level), --00110111(55)
                    
                    (item_id,shop_level),--00101000(40)
                    (bu_id,item_id,shop_level),--00101001(41)
                    (bu_id,dept_id_1,item_id,shop_level),--00101011(43)
                    (bu_id,dept_id_1,dept_id_2,item_id,shop_level),--00101111(47)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_level),--00111111(63)
          					
          					(corp_type_cd),--01000000(64)
                    (bu_id,corp_type_cd),--01000001(65)
                    (bu_id,dept_id_1,corp_type_cd),--01000011(67)
                    (bu_id,dept_id_1,dept_id_2,corp_type_cd), --01000111(71)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,corp_type_cd), --01010111(87)
          					
          					(shop_type_cd),--10000000(128)
                    (bu_id,shop_type_cd),--10000001(129)
                    (bu_id,dept_id_1,shop_type_cd),--10000011(131)
                    (bu_id,dept_id_1,dept_id_2,shop_type_cd), --10000111(135)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,shop_type_cd), --10010111(151)
          					
          					(item_id,corp_type_cd),--01001000(72)
                    (bu_id,item_id,corp_type_cd),--01001001(73)
                    (bu_id,dept_id_1,item_id,corp_type_cd),--01001011(75)
                    (bu_id,dept_id_1,dept_id_2,item_id,corp_type_cd),--01001111(79)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,corp_type_cd),--01011111(95)
          
          					(item_id,shop_type_cd),--10001000(136)
                    (bu_id,item_id,shop_type_cd),--10001001(137)
                    (bu_id,dept_id_1,item_id,shop_type_cd),--10001011(139)
                    (bu_id,dept_id_1,dept_id_2,item_id,shop_type_cd),--10001111(143)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_type_cd),--10011111(159)
          
          					(shop_level,corp_type_cd),--01100000(96)
                    (bu_id,shop_level,corp_type_cd),--01100001(97)
                    (bu_id,dept_id_1,shop_level,corp_type_cd),--01100011(99)
                    (bu_id,dept_id_1,dept_id_2,shop_level,corp_type_cd), --01100111(103)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,shop_level,corp_type_cd), --01110111(119)
          
          					(shop_level,shop_type_cd),--10100000(160)
                    (bu_id,shop_level,shop_type_cd),--10100001(161)
                    (bu_id,dept_id_1,shop_level,shop_type_cd),--10100011(163)
                    (bu_id,dept_id_1,dept_id_2,shop_level,shop_type_cd), --10100111(167)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,shop_level,shop_type_cd), --10110111(183)
          
          					(corp_type_cd,shop_type_cd),--11000000(192)
                    (bu_id,corp_type_cd,shop_type_cd),--11000001(193)
                    (bu_id,dept_id_1,corp_type_cd,shop_type_cd),--11000011(195)
                    (bu_id,dept_id_1,dept_id_2,corp_type_cd,shop_type_cd), --11000111(199)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,corp_type_cd,shop_type_cd), --11010111(215)
          
          					(item_id,shop_level,corp_type_cd),--01101000(104)
                    (bu_id,item_id,shop_level,corp_type_cd),--01101001(105)
                    (bu_id,dept_id_1,item_id,shop_level,corp_type_cd),--01101011(107)
                    (bu_id,dept_id_1,dept_id_2,item_id,shop_level,corp_type_cd),--01101111(111)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_level,corp_type_cd),--01111111(127)
          
          					(item_id,shop_level,shop_type_cd),--10101000(168)
                    (bu_id,item_id,shop_level,shop_type_cd),--10101001(169)
                    (bu_id,dept_id_1,item_id,shop_level,shop_type_cd),--10101011(171)
                    (bu_id,dept_id_1,dept_id_2,item_id,shop_level,shop_type_cd),--10101111(175)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_level,shop_type_cd),--10111111(191)
          
          					(item_id,corp_type_cd,shop_type_cd),--11001000(200)
                    (bu_id,item_id,corp_type_cd,shop_type_cd),--11001001(201)
                    (bu_id,dept_id_1,item_id,corp_type_cd,shop_type_cd),--11001011(203)
                    (bu_id,dept_id_1,dept_id_2,item_id,corp_type_cd,shop_type_cd),--11001111(207)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,corp_type_cd,shop_type_cd),--11011111(223)
          
          					(shop_level,corp_type_cd,shop_type_cd),--11100000(224)
                    (bu_id,shop_level,corp_type_cd,shop_type_cd),--11100001(225)
                    (bu_id,dept_id_1,shop_level,corp_type_cd,shop_type_cd),--11100011(227)
                    (bu_id,dept_id_1,dept_id_2,shop_level,corp_type_cd,shop_type_cd), --11100111(231)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,shop_level,corp_type_cd,shop_type_cd), --11110111(247)
          
                    (item_id,shop_level,corp_type_cd,shop_type_cd),--11101000(232)
                    (bu_id,item_id,shop_level,corp_type_cd,shop_type_cd),--11101001(233)
                    (bu_id,dept_id_1,item_id,shop_level,corp_type_cd,shop_type_cd),--11101011(235)
                    (bu_id,dept_id_1,dept_id_2,item_id,shop_level,corp_type_cd,shop_type_cd),--11101111(239)
                    (bu_id,dept_id_1,dept_id_2,dept_id_3,item_id,shop_level,corp_type_cd,shop_type_cd)--11111111(255)
                  )
        
        union all
        
        select --3.服务月度收入kpi目标,只到部门维度，不到收费项目维度，不到
             (case when grouping__id in(1,3) then  bu_id  else '999999' end) as bu_id                
            ,(case when grouping__id in(1,3) then  max(bu_name)  else '所有' end)as  bu_name           
            ,(case when grouping__id in(3) then  dept_id_1  else '999999' end) as dept_id_1            
            ,(case when grouping__id in(3) then  max(dept_name_1 )   else '所有' end) as dept_name_1     
            ,'999999' as dept_id_2 
            ,'所有' as dept_name_2
            ,'999999' as dept_id_3
            ,'所有' as dept_name_3
            ,'999999' as shop_level
            ,'999999' as item_id              
            ,'所有' as item_name            
            ,9      as item_type  --收费项目类型(1:版本,2:模块,3:套餐,9:所有)
            ,'999999' as corp_type_cd
            ,'999999' as shop_type_cd
            ,0 as valid_vend_qty       
            ,0 as charge_vend_qty      
            ,0 as mau                  
            ,0 as new_vend_qty         
            ,0 as first_ord_vend_qty   
            ,0 as expire_vend_qty      
            ,0 as renew_vend_qty       
            ,0 as upgrade_vend_qty     
            ,0 as invalid_vend_qty     
            ,0 as lost_vend_qty        
            ,0 as recall_vend_qty      
            ,0 as valid_sale_amt       
            ,0 as new_sale_amt         
            ,0 as renew_amt            
            ,0 as recall_amt           
            ,sum(m_tat_rev_amt) as m_tat_rev_amt  --月度收入目标       
            ,0 as m_act_rev_amt        
            ,sum(y_tat_rev_amt) as y_tat_rev_amt  --年度收入目标       
            ,0 as y_act_rev_amt        
            ,0 as y_est_act_rev_amt 
            ,(case 
                  when grouping__id=0 then '1' 
                  when grouping__id=1 then '3' 
                  when grouping__id=3 then '5' 
             end) as lvl
        from app.app_zs_z9002_srvc_rev_target  --服务月度收入kpi目标(目前只到一级部门粒度)
        where dt='""" + data_day_str + """'
        group by 
               bu_id
              ,dept_id_1
        grouping sets(
                   (bu_id,dept_id_1) --11(3)
                  ,(bu_id) --01(1)
                  ,()--00(0)
                  ) 
    )t_sum
    group by  bu_id
             ,dept_id_1
             ,dept_id_2
             ,dept_id_3
             ,lvl
             ,item_id
             ,shop_level
             ,corp_type_cd
             ,shop_type_cd
) ta

知识要点1：使用grouping sets实现在同一个sql中同时对多个维度组合进行聚合计算，并使用grouping__id对相应的被聚合维度列赋上默认值。其实我觉得这个可以用coalesce()来实现同样的效果。

知识要点2：使用grouping__id为每一个维度组合赋上独有的lvlcode，使得不同的select语句中的相同维度组合的结果可以使用lvlcode关联到最终结果表的同一行中。

知识要点3：一定要注意不同计算引擎的grouping__id计算方式是不同的，上述sql中使用的是hive的生产规则，按照group by字段的倒叙，出现的置1，未出现的置0。而spark引擎则正好是相反的规则，所以上述sql只能用hive来执行，如果要用spark来执行必须重写所有grouping__id值才能正确对应上。