多维度聚合

,

INSERT OVERWRITE TABLE tmsp.trdcatecq PARTITION(ds='${bizdate}')

select /*+mapjoin(dis)*/

trd.ds as stat_date,

buyer_id

,max(trd.bc_type) as bc_type

,if(grouping(dis.cate_id) = 0,dis.cate_id,'1') as cate_id

,max(coalesce(dis.cate_name,'总计')) as cate_name

,max(coalesce(dis.parent_cate_id,'-1')) as parent_cate_id

,max(coalesce(dis.parent_cate_name,'全部')) as parent_cate_name

,max(coalesce(dis.is_leaf_cate,'0')) as is_leaf_cate

,max(coalesce(dis.cate_flag,'1111')) as cate_flag

,concat(

IF(GROUPING(dis.cate_id)=0, '#cate_id', '')

,IF(GROUPING(sp)=0, '#sp', '')

,IF(GROUPING(spmc)=0, '#spmc', '')

,IF(GROUPING(sx)=0, '#sx', '')

,IF(GROUPING(sxmc)=0, '#sxmc', '')

,IF(GROUPING(mc)=0, '#mc', '')

,IF(GROUPING(sptmpt)=0, '#sptmpt', '')

,IF(GROUPING(sxtmpt)=0, '#sxtmpt', '')

,IF(GROUPING(tmpt)=0, '#tmpt', '')

,IF(GROUPING(tx)=0, '#tx', '')

,IF(GROUPING(sxtb)=0, '#sxtb', '')

,IF(GROUPING(sptb)=0, '#sptb', '')

) AS biz_type

,(trd.pay_ord_qty) as pay_ord_qty

,(trd.div_pay_fee) as div_pay_fee

,(pay_ord_cnt) as pay_ord_cnt

from trdcq as trd

left join dis

on trd.cate_id = dis.leaf_cate_id

and dis.ds = max_pt('txcs_cdm.dim_smt_360_cate_distribute')

where trd.ds='${bizdate}'

group by

trd.ds, buyer_id,pay_ord_qty,div_pay_fee,pay_ord_cnt

,grouping sets ((dis.cate_id,trd.sp ),(dis.cate_id,trd.sx),trd.sp

,tx

,sxtb

,sptb

)

;

 

insert overwrite table ads_smt_360_stss_trd_biz_cate_1d partition (ds = '${bizdate}')

SELECT '${bizdate}' AS stat_date

,max(bc_type)

,if(grouping(biz_type) = 0,biz_type,'1') as biz_type

,if(grouping(cate_id) = 0,cate_id,'1') as cate_id

,max(cate_name) as cate_name

,max(parent_cate_id) as parent_cate_id

,max(parent_cate_name) as parent_cate_name

,max(is_leaf_cate) as is_leaf_cate

,max(cate_flag) as cate_flag

,SUM(div_pay_fee) AS pay_ord_amt

,COUNT(DISTINCT (buyer_id)) AS pay_byr_cnt

,SUM(pay_ord_cnt) AS pay_ord_cnt

,SUM(pay_ord_qty) AS pay_ord_qty

from tmsp.trdcatecq trd

where ds='${bizdate}'

group by grouping sets (cate_id,trd.biz_type

)

;

,if(grouping(dis.cate_id) = 0,dis.cate_id,'1') as cate_id

,

,concat(

IF(GROUPING(dis.cate_id)=0, '#cate_id', '')

,IF(GROUPING(sp)=0, '#sp', '')

group by

trd.ds, buyer_id,pay_ord_qty,div_pay_fee,pay_ord_cnt

,grouping sets ((dis.cate_id,trd.sp ),(dis.cate_id,trd.sx),trd.sp--sp:食品合计

,trd.spmc --spmc:食品-猫超

,trd.sx --sx:生鲜合计

,