SqlServer 创建变量表

ゝ一纸荒年。 2022-06-01 08:00 233阅读 0赞

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