SQL技巧

1 滚动获取增量数据

insert overwrite table aid_action_on_item_view partition(ds='${date}')
select aid, item_id, cate_id, brand_id, shop_id, act_time, counter, bc_type, is_tmcs
from 
(
	  select aid, item_id, cate_id, brand_id, shop_id, ds as act_time, sum(frequency) as counter, max(bc_type) as bc_type, max(is_tmcs) as is_tmcs
	  from alimama_ecpm_algo.taobao_aid_action_on_item_base
	  where ds='${date}' and action='view'
	  group by ds, aid, item_id, cate_id, brand_id, shop_id
	  
	  union all 
	  
	  select aid, item_id, cate_id, brand_id, shop_id, act_time, counter, bc_type, is_tmcs
	  from alimama_ecpm_algo.aid_action_on_item_view
	  where ds='${yyyymmdd-1}' and act_time > '${yyyymmdd-180}' --and act_time not in('20181111','20181212')
)x;

2 窗口特征抽取

insert overwrite table xl_dmp_shop_fea 
partition(ds='${bizdate}')
select  sf_shop_id
        ,sum(if(window_tag<=0,sf_view_cnt,0)) as sf_view_cnt_1
        ,sum(if(window_tag<=1,sf_view_cnt,0)) as sf_view_cnt_3
        ,sum(if(window_tag<=2,sf_view_cnt,0)) as sf_view_cnt_7
        ,sum(if(window_tag<=3,sf_view_cnt,0)) as sf_view_cnt_15
        ,sum(if(window_tag<=4,sf_view_cnt,0)) as sf_view_cnt_30
        ,sum(if(window_tag<=5,sf_view_cnt,0)) as sf_view_cnt_60
        ,sum(if(window_tag<=0,sf_collect_cnt,0)) as sf_collect_cnt_1
        ,sum(if(window_tag<=1,sf_collect_cnt,0)) as sf_collect_cnt_3
        ,sum(if(window_tag<=2,sf_collect_cnt,0)) as sf_collect_cnt_7
        ,sum(if(window_tag<=3,sf_collect_cnt,0)) as sf_collect_cnt_15
        ,sum(if(window_tag<=4,sf_collect_cnt,0)) as sf_collect_cnt_30
        ,sum(if(window_tag<=5,sf_collect_cnt,0)) as sf_collect_cnt_60
        ,sum(if(window_tag<=0,sf_cart_cnt,0)) as sf_cart_cnt_1
        ,sum(if(window_tag<=1,sf_cart_cnt,0)) as sf_cart_cnt_3
        ,sum(if(window_tag<=2,sf_cart_cnt,0)) as sf_cart_cnt_7
        ,sum(if(window_tag<=3,sf_cart_cnt,0)) as sf_cart_cnt_15
        ,sum(if(window_tag<=4,sf_cart_cnt,0)) as sf_cart_cnt_30
        ,sum(if(window_tag<=5,sf_cart_cnt,0)) as sf_cart_cnt_60
        ,sum(if(window_tag<=0,sf_trade_cnt,0)) as sf_trade_cnt_1
        ,sum(if(window_tag<=1,sf_trade_cnt,0)) as sf_trade_cnt_3
        ,sum(if(window_tag<=2,sf_trade_cnt,0)) as sf_trade_cnt_7
        ,sum(if(window_tag<=3,sf_trade_cnt,0)) as sf_trade_cnt_15
        ,sum(if(window_tag<=4,sf_trade_cnt,0)) as sf_trade_cnt_30
        ,sum(if(window_tag<=5,sf_trade_cnt,0)) as sf_trade_cnt_60
from    
(
    select  sf_shop_id
            ,sf_view_cnt
            ,sf_collect_cnt
            ,sf_cart_cnt
            ,sf_trade_cnt
            ,case   when datediff(to_date('${bizdate}','yyyymmdd'),to_date(ds,'yyyymmdd'), 'dd')<1 then 0
                    when datediff(to_date('${bizdate}','yyyymmdd'),to_date(ds,'yyyymmdd'), 'dd')<3 then 1
                    when datediff(to_date('${bizdate}','yyyymmdd'),to_date(ds,'yyyymmdd'), 'dd') < 7 then 2
                    when datediff(to_date('${bizdate}','yyyymmdd'),to_date(ds,'yyyymmdd'), 'dd') < 15 then 3
                    when datediff(to_date('${bizdate}','yyyymmdd'),to_date(ds,'yyyymmdd'), 'dd') < 30 then 4
                    else 5
            end as window_tag
    from    xl_dmp_shop_fea_base
    where   ds <= '${bizdate}'
    and     datediff(to_date('${bizdate}','yyyymmdd'),to_date(ds,'yyyymmdd'), 'dd') <= 60
) a
group by sf_shop_id
;