深圳智汇创想科技有限公司

导出sql，如下：

-- 刊登时间 上架方式 销售额 利润率 上架listing数量 出单listing数量 listing出单率 上架SKU数量  出单SKU数量  SKU出单率
DROP TABLE IF EXISTS #alltday;
CREATE TABLE #alltday (start_time DATE NOT NULL);
--刊登开始时间、结束时间
DECLARE @Num INT;
DECLARE @StartDate DATE='2022-02-01';
DECLARE @EndDate DATE = '2022-02-28';
DECLARE  @LastEndDate DATE=DATEADD(DAY,1,@EndDate);
DECLARE @Days int =DateDiff(dd,@StartDate, @LastEndDate);

SET @Num = 1;
WHILE @Num <=@Days
BEGIN
    SET @LastEndDate = DATEADD(DAY, -1, @LastEndDate);
    INSERT #alltday
    VALUES (@LastEndDate);

    SET @Num = @Num + 1;
END;

--刊登时间范围内listing
select  
b.CustomerID,
b.OrderSourceID,
b.OrderSourceSKU,
b.SKU,
b.OrderSourceSKUID,
b.OnShelfMode,
a.start_time as PushTime
into #temp_AllAmazonProductBase_20220326
from  
#alltday  a
left join 
RB_Amazon_Product_Base b(nolock)
on b.CustomerID=1
where  b.OrderSourceSKUID>0 and b.isDelete=0
and b.Addtime<CONVERT(VARCHAR(10), DATEADD(DAY, 1, a.start_time), 121)
and b.Addtime>=a.start_time
and b.OnShelfMode in(1,8)

--上架SKU数量
--上架listing数量
select  
PushTime,
OnShelfMode,
count(distinct SKU) as sumSkuCount, --上架sku数量
COUNT(distinct OrderSourceSKUID) AS sumListingCount --上架listing数量
into  #temp_ShelfListing_20220326

from 
#temp_AllAmazonProductBase_20220326 
group by PushTime,OnShelfMode 


--出单listing数量
--出单SKU数量
--销售额
--利润率
select 
a.start_time as PushTime,
b.OnShelfMode,
count(distinct b.SKU) as sumOrderSKUCount, --出单sku数量
count(distinct b.OrderSourceSKUID) as  sumOrderListingCount,--出单listing数量
sum(c.Incomes) as DailyIncomes, --销售额
sum(c.Profits) as DailyProfits  --利润
into #temp_OrderSummary_20220326
from
#alltday  a
left join  
#temp_AllAmazonProductBase_20220326 b
on a.start_time=b.PushTime
inner join
RB_Report_OrderSettlement c(nolock)
on b.CustomerID=c.CustomerID
and  b.OrderSourceID=c.OrderSourceID
and  b.OrderSourceSKU=c.SellerSKU
where  c.PayDate<CONVERT(VARCHAR(10), DATEADD(DAY, 30, a.start_time), 121)
and  c.PayDate>=a.start_time
group by a.start_time,b.OnShelfMode

--利润率
--listing出单率
--SKU出单率
select 
a.start_time as '刊登时间',
case when b.OnShelfMode=1  then '人工刊登' else '智能刊登' end  as '上架方式',
isNULL(cast(c.DailyIncomes as decimal(18,2)),'0.00') as '销售额',
isnull(cast(c.DailyProfits as decimal(18,2)),'0.00') as '利润额',
case when isnull(c.DailyIncomes,0)=0 or isnull(c.DailyProfits,0)=0 then '0.00%' else
cast(cast(100*(c.DailyProfits/nullif(c.DailyIncomes,0)) as decimal(18,2)) as nvarchar(50))+'%'  end as '利润率',
isnull(sumListingCount,0) as '上架listing数量', 
isnull(sumOrderListingCount,0) as '出单listing数量',
case when isnull(b.sumListingCount,0)=0 or isnull(sumOrderListingCount,0)=0  then '0.00%' else 
cast(cast(100*(c.sumOrderListingCount*1.0/nullif(b.sumListingCount,0)) as decimal(18,2)) as nvarchar(50))+'%' end as 'listing出单率',
isnull(sumSkuCount,0) as '上架sku数量',
isnull(sumOrderSKUCount,0) as '出单sku数量',
case when isnull(b.sumSkuCount,0)=0 or isnull(sumOrderSKUCount,0)=0  then '0.00%' else 
cast(cast(100* (c.sumOrderSKUCount*1.0/nullif(b.sumSkuCount,0)) as decimal(18,2)) as nvarchar(50))+'%' end as 'sku出单率'
from  
#alltday  a
left join 
#temp_ShelfListing_20220326 b
on a.start_time=b.PushTime
left join
#temp_OrderSummary_20220326 c
on 
a.start_time=c.PushTime
and b.OnShelfMode=c.OnShelfMode
order by b.OnShelfMode,a.start_time

drop table #temp_AllAmazonProductBase_20220326;
drop table #temp_ShelfListing_20220326;
drop table #temp_OrderSummary_20220326;