SqlServer 创建变量表

DECLARE @liu TABLE
            (
              seller_id BIGINT ,
              thedate BIGINT ,
              访客数 BIGINT ,
              新访客数 BIGINT ,
              成交回头客数 BIGINT

            )
 INSERT  INTO @liu
                ( seller_id ,
                  thedate ,
                  访客数 ,
                  新访客数 ,
                  成交回头客数  --@begintime bigint,--开始时间 @endtime bigint,--结束时间 @seller_id --买家id
 
                )   
select 
a.seller_id, 
a.thedate, 
sum( a.uv)as 访客数 ,
b.新访客数,
c.成交回头客数
 from ysf_dwb_shop_platform_trade_repeat_traffic_d   a ,
 (select thedate,seller_id, sum(uv) as 新访客数  
from ysf_dwb_shop_platform_trade_repeat_traffic_d 
where  seller_id=788306478 and thedate between 20180117  and  20180123  and  platform_id in (1,2) and is_trd_repeat=0     group by seller_id,thedate) b,
( select  thedate,seller_id,sum(uv) as 成交回头客数 
  from ysf_dwb_shop_platform_trade_repeat_traffic_d where 
  seller_id=788306478 and thedate between 20180117  and  20180123  and  platform_id in (1,2) and is_trd_repeat=1  group by seller_id,thedate ) c
where  a.seller_id=b.seller_id and a.thedate=b.thedate and a.seller_id=c.seller_id and a.thedate=c.thedate and
a.seller_id=788306478 and a.thedate between 20180117  and  20180123 and platform_id in (1,2) group by a.seller_id,a.thedate, b.新访客数, c.成交回头客数;


   select a.seller_id,a.thedate,a.访客数,a.新访客数,  
  case when a.访客数 =0 then 0
  else  CONVERT(DECIMAL(18, 4), CONVERT(DECIMAL(18, 4), a.新访客数)/ CONVERT(DECIMAL(18, 4), a.访客数)) end  AS 新访占比  
from @liu a