2016-08-01 #活动商品加入购物车的UV.md

活动商品加入购物车的UV

ruohan(若涵.詹爱玉)

select
b.terminal_id,
a.goods_type_id 
,sum(b.pv)
,count(distinct b.gu_id)
from
(select gi.shop_id
,gi.goods_id
,gi.goods_type_id
,s.sku_id
from dw.dim_goods_info gi
left join dw.dim_sku s on s.goods_id = gi.goods_id
where substr(gi.start_time,1,10) BETWEEN '2015-11-03' and '2015-11-11'
) a
left join 
(select 
fe.terminal_id,
fe.event_value
,fe.gu_id
,fe.pv
from dw.fct_event fe
left join dw.dim_event e on e.event_id = fe.event_id
where e.event_type_id = 1 and fe.date='2015-11-11'
and fe.terminal_id in (2,3)) b on a.sku_id = b.event_value
GROUP BY b.terminal_id,
a.goods_type_id
;

若涵的最终代码

use temp;
create table act_gu1 as
select distinct gu_id,user_id,app_version
from dw.fct_event a 
join dw.dim_event b on a.event_id=b.event_id and event_type_id=1
join dw.dim_sku s on a.event_value=s.sku_id
join dw.dim_goods_info  gi on s.goods_id = gi.goods_id
join temp.act_shop0728 c on gi.shop_id=c.shop_id
where  a.date= '2016-07-28'
and a.terminal_id in (2,3) ;