TASK#25378 【数据导出】智能刊登，手动刊登出单情况数据导出 / 【智汇】产品部 3月非正常更新版本（2022年）

任务描述

需求描述

一、背景

背景：统计分析刊登出单情况，

价值：用于分析刊登出单情况，从数据分析挖掘出单可提升点。

说明：刊登时间，人工在线SKU总数，系统在线SKU总数，人工在线Listing总数，系统在线Listing总数，人工刊登SKU数，系统刊登SKU数，人工刊登Listing数，系统刊登listing数，人工出单SKU数，系统刊登出单SKU数，人工出单listing数，系统刊登出单Listing数，人工销售额（人民币），系统销售额（人民币），人工利润额（人民币），系统利润额（人民币），人工利润率，系统利润率，人工刊登14天出单率，系统刊登14天出单率，人工刊登30天出单率，系统刊登30天出单率，

--1）按当日维度统计智能刊登数据

--2）按当日维度统计手工刊登数据

--3）出单率按公式另算，统计出14天出单率，30天出单率。

二、出单率释义：

计算2022.1.18号上架的SKU30天出单率

分母上架时间为2022.1.18的SKU去重后的数量

分子上架时间为2022.1.18的listing对应订单中，订单时间在2022.1.18-2022.2.17的SKU数量去重后所得的数量

14天出单率类推。

三、要求：可以查看刊登时间范围内，每日刊登的出单情况。

3.1、导出刊登时间为1.10~1.23 的数据

3.2、SQL可以根据刊登时间，多少天出单率查出结果。

注：参考ebaySQL见附件。

举例展示如下(附件见【人工系统出单情况模板】)：

验收标准

暂无

历史记录

2022-02-25 13:01:36, 由 罗友军 创建。
2022-02-25 14:32:57, 由 魏鹏军 完成。
修改了 总计消耗，旧值为 "0"，新值为 "8"。
修改了 指派给 ，旧值为 "weipengjun"，新值为 "luoyoujun"。
修改了 实际完成，旧值为 ""，新值为 "2022-02-25 14:28:52"。
修改了 任务状态，旧值为 "wait"，新值为 "done"。
修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-02-25 14:29:59"。
修改了 预计剩余，旧值为 "8"，新值为 "0"。
修改了 由谁完成，旧值为 ""，新值为 "weipengjun"。

针对需求提到的，导出包含：智能刊登、一键上架的在线数据
2022-02-25 19:14:43, 由 魏鹏军 添加备注。

导出脚本如下：

DROP TABLE IF EXISTS #alltday;
CREATE TABLE #alltday (start_time DATE NOT NULL);

DECLARE @Num INT;
DECLARE @StartDate DATE = CONVERT(VARCHAR(10), DATEADD(DAY, -1, getdate()), 121);

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

SET @Num = @Num + 1;
END;

--select * from #alltday

--drop table #alltday;

--一键上架在线SKU总数、智能刊登在线SKU总数（时间小于等于当天）
--人工在线Listing总数、系统在线Listing总数（时间小于等于当天）

select start_time,
sum(case when OnShelfMode=1 then sumSkuCount else 0 end) as OneclickLaunchSumSKUCount, --一键上架在线sku数
sum(case when OnShelfMode=8 then sumSkuCount else 0 end ) as SmartPublicationSumSKUCount,--智能刊登在线sku数
sum(case when OnShelfMode=1 then sumLIstingCount else 0 end) as OneclickLaunchOffShelfSumListingCount,--一键上架在线listing数
sum(case when OnShelfMode=8 then sumLIstingCount else 0 end ) as SmartPublicationOffShelfSumListingCount--智能刊登在线listing数
into #temp_allOffShelfListingLessThanSkuCount
from
(
select a.start_time,
b.OnShelfMode,
count(distinct b.SKU) as sumSkuCount,
COUNT(0) AS sumLIstingCount
from
#alltday a
left join
RB_Amazon_Product_Base b(nolock)
on b.CustomerID=1
where OrderSourceSKUID>0 and isDelete=0
and b.Addtime<CONVERT(VARCHAR(10), DATEADD(DAY, 1, a.start_time), 121)
and b.OnShelfMode in(1,8)
and b.IsOffShelf=0
group by a.start_time,b.OnShelfMode
) aaaa group by start_time

--人工刊登SKU数系统刊登SKU数(当天)
--人工刊登Listing数系统刊登listing数（当天）
select
start_time,
sum(case when OnShelfMode=1 then sumSkuCount else 0 end) as OneclickLaunchOffShelfCurrentDaySumSKUCount,--人工刊登SKU数
sum(case when OnShelfMode=8 then sumSkuCount else 0 end ) as SmartPublicationOffShelfCurrentDaySumSKUCount,--系统刊登SKU数
sum(case when OnShelfMode=1 then sumListingCount else 0 end) as OneclickLaunchOffShelfCurrentDaySumListingCount,--人工刊登Listing数
sum(case when OnShelfMode=8 then sumListingCount else 0 end ) as SmartPublicationOffShelfCurrentDaySumListingCount--系统刊登listing数
into #temp_allOffShelfListingEqualListingSkuCount
from (
select a.start_time,
b.OnShelfMode,
count(distinct b.SKU) as sumSkuCount,
COUNT(0) AS sumListingCount
from
#alltday a
left join
RB_Amazon_Product_Base b(nolock)
on b.CustomerID=1
where OrderSourceSKUID>0 and 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)
--and b.IsOffShelf=0
group by a.start_time,b.OnShelfMode) aaaa group by start_time

--人工出单SKU数、系统刊登出单SKU数、人工销售额（人民币）、系统销售额（人民币）、人工利润额（人民币）、系统利润额（人民币）、人工利润率、系统利润率

select start_time,
sum(case when OnShelfMode=1 then sumOrderSkuCount else 0 end) as OneclickLaunchOffShelfSumOrderSKUCount,
sum(case when OnShelfMode=8 then sumOrderSkuCount else 0 end ) as SmartPublicationOffShelfSumOrderSKUCount,
cast (sum(case when OnShelfMode=1 then DailyIncomes else 0 end) as decimal(18,2)) as OneclickLaunchOffShelfSumOrderDailyIncomes,--一键上架销售额
cast(sum(case when OnShelfMode=8 then DailyIncomes else 0 end) as decimal(18,2)) as SmartPublicationOffShelfSumOrderDailyIncomes,--智能刊登销售额
cast (sum(case when OnShelfMode=1 then DailyProfits else 0 end) as decimal(18,2)) as OneclickLaunchOffShelfSumOrderDailyProfits,--一键上架利润额
cast(sum(case when OnShelfMode=8 then DailyProfits else 0 end) as decimal(18,2)) as SmartPublicationOffShelfSumOrderDailyProfits,--智能刊登利润额
case when sum(case when OnShelfMode=1 then DailyIncomes else 0 end)=0 then '0.00%' else
cast(100 *(sum(case when OnShelfMode=1 then DailyProfits else 0 end)
/NULLIF(sum(case when OnShelfMode=1 then DailyIncomes else 0 end),0)) as nvarchar(50)) +'%' end as OneclickLaunchOffShelfSumOrderDailyIncomesPercentage,--一键上架利润率
case when sum(case when OnShelfMode=8 then DailyIncomes else 0 end)=0 then '0.00%' else
cast(100 *(sum(case when OnShelfMode=8 then DailyProfits else 0 end)
/NULLIF(sum(case when OnShelfMode=8 then DailyIncomes else 0 end),0)) as nvarchar(50)) +'%' end as SmartPublicationOffShelfSumOrderDailyIncomesPercentage--智能刊登利润率
into #temp_allOffShelfListingDailyIncomesProfitsStatistics
from (
select a.start_time,
b.OnShelfMode,
count(distinct c.SKU) as sumOrderSkuCount,
sum(c.DailyIncomes) as DailyIncomes,
sum(c.DailyProfits) as DailyProfits
from
#alltday a
left join
RB_Amazon_Product_Base b(nolock)
on b.CustomerID=1
inner join
RB_Report_ProductProfit c(nolock)
on b.CustomerID=c.CustomerID
and b.OrderSourceID=c.OrderSourceID
and b.OrderSourceSKU=c.SellerSKU
where b.OrderSourceSKUID>0 and b.isDelete=0
and b.Addtime<CONVERT(VARCHAR(10), DATEADD(DAY, 1, a.start_time), 121)
and b.OnShelfMode in(1,8)
--and b.IsOffShelf=0
and c.PayDate<CONVERT(VARCHAR(10), DATEADD(DAY, 1, a.start_time), 121)
and c.PayDate>=a.start_time
group by a.start_time,b.OnShelfMode) aaaa group by start_time

--人工刊登14天出单率系统刊登14天出单率
--人工刊登14天出单数量系统刊登14天出单数量

--计算2022.1.18号上架的SKU30天出单率(往前推30天)
--分母上架时间为2022.1.18的SKU去重后的数量
--分子上架时间为2022.1.18的listing对应订单中，订单时间在2022.1.12-2022.2.17的SKU数量去重后所得的数量

select start_time,
sum(case when OnShelfMode=1 then sumOrderSkuCount else 0 end) as OneclickLaunchOffShelfSumDenominator14SKUCount,
sum(case when OnShelfMode=8 then sumOrderSkuCount else 0 end) as SmartPublicationOffShelfSumOrderDenominator14SKUCount
into #temp_allOffShelfListingDenominator14OrderSumSKUCount
from
(
select a.start_time,
b.OnShelfMode,
count(distinct b.SKU) AS sumOrderSkuCount
from
#alltday a
left join
RB_Amazon_Product_Base b(nolock)
on b.CustomerID=1
left join
RB_Report_ProductProfit c(nolock)
on b.CustomerID=c.CustomerID
and b.OrderSourceID=c.OrderSourceID
and b.OrderSourceSKU=c.SellerSKU
where b.OrderSourceSKUID>0 and b.isDelete=0
and b.OnShelfMode in(1,8)
--and b.IsOffShelf=0
and b.Addtime<CONVERT(VARCHAR(10), DATEADD(DAY, 1, a.start_time), 121)
AND b.Addtime>=a.start_time
and c.PayDate>=a.start_time
and c.PayDate<CONVERT(VARCHAR(10), DATEADD(DAY, 15, a.start_time), 121)
group by a.start_time,b.OnShelfMode) aaaa
group by start_time

--人工刊登30天出单率系统刊登30天出单率
--人工刊登30天出单数量系统刊登30天出单数量
select start_time,
sum(case when OnShelfMode=1 then sumOrderSkuCount else 0 end) as OneclickLaunchOffShelfSumDenominator30SKUCount,
sum(case when OnShelfMode=8 then sumOrderSkuCount else 0 end) as SmartPublicationOffShelfSumOrderDenominator30SKUCount
into #temp_allOffShelfListingDenominator30OrderSumSKUCount
from
(
select a.start_time,
b.OnShelfMode,
count(distinct b.SKU) AS sumOrderSkuCount
from
#alltday a
left join
RB_Amazon_Product_Base b(nolock)
on b.CustomerID=1
left join
RB_Report_ProductProfit c(nolock)
on b.CustomerID=c.CustomerID
and b.OrderSourceID=c.OrderSourceID
and b.OrderSourceSKU=c.SellerSKU
where b.OrderSourceSKUID>0 and b.isDelete=0
and b.OnShelfMode in(1,8)
--and b.IsOffShelf=0
and b.Addtime<CONVERT(VARCHAR(10), DATEADD(DAY, 1, a.start_time), 121)
AND b.Addtime>=a.start_time
and c.PayDate>=a.start_time
and c.PayDate<CONVERT(VARCHAR(10), DATEADD(DAY, 31, a.start_time), 121)
group by a.start_time,b.OnShelfMode) aaaa
group by start_time

--汇总
select
a.start_time as '时间',
b.OneclickLaunchSumSKUCount as '人工在线SKU总数',
b.SmartPublicationSumSKUCount as '系统在线SKU总',
b.OneclickLaunchOffShelfSumListingCount as '人工在线Listing总数',
b.SmartPublicationOffShelfSumListingCount as '系统在线Listing总数',
c.OneclickLaunchOffShelfCurrentDaySumSKUCount as '人工刊登SKU数',
c.SmartPublicationOffShelfCurrentDaySumSKUCount as '系统刊登SKU数',
c.OneclickLaunchOffShelfCurrentDaySumListingCount as '人工刊登Listing数',
c.SmartPublicationOffShelfCurrentDaySumListingCount as '系统刊登listing数',
d.OneclickLaunchOffShelfSumOrderSKUCount as '人工出单SKU数',
d.SmartPublicationOffShelfSumOrderSKUCount as '系统刊登出单SKU数',
d.OneclickLaunchOffShelfSumOrderDailyIncomes as '人工销售额（人民币）',
d.SmartPublicationOffShelfSumOrderDailyIncomes as '系统销售额（人民币）',
d.OneclickLaunchOffShelfSumOrderDailyProfits as '人工利润额（人民币）',
d.SmartPublicationOffShelfSumOrderDailyProfits as '系统利润额（人民币）',
d.OneclickLaunchOffShelfSumOrderDailyIncomesPercentage as '人工利润率',
d.SmartPublicationOffShelfSumOrderDailyIncomesPercentage as '系统利润率',
cast(cast(100*(e.OneclickLaunchOffShelfSumDenominator14SKUCount*1.0/c.OneclickLaunchOffShelfCurrentDaySumSKUCount)as decimal(18,2)) as nvarchar(50))+'%' as '人工刊登14天出单率',
cast(cast(100*(e.SmartPublicationOffShelfSumOrderDenominator14SKUCount*1.0/c.SmartPublicationOffShelfCurrentDaySumSKUCount)as decimal(18,2)) as nvarchar(50))+'%' as '系统刊登14天出单率',
cast(cast(100*(f.OneclickLaunchOffShelfSumDenominator30SKUCount*1.0/c.OneclickLaunchOffShelfCurrentDaySumSKUCount)as decimal(18,2)) as nvarchar(50))+'%' as '人工刊登30天出单率',
cast(cast(100*(f.SmartPublicationOffShelfSumOrderDenominator30SKUCount*1.0/c.SmartPublicationOffShelfCurrentDaySumSKUCount)as decimal(18,2)) as nvarchar(50))+'%' as '系统刊登30天出单率'
from
#alltday a
left join
#temp_allOffShelfListingLessThanSkuCount b
on a.start_time=b.start_time
left join
#temp_allOffShelfListingEqualListingSkuCount c
on a.start_time=c.start_time
left join
#temp_allOffShelfListingDailyIncomesProfitsStatistics d
on a.start_time=d.start_time
left join
#temp_allOffShelfListingDenominator14OrderSumSKUCount e
on a.start_time=e.start_time
left join
#temp_allOffShelfListingDenominator30OrderSumSKUCount f
on a.start_time=f.start_time

drop table #alltday;
drop table #temp_allOffShelfListingLessThanSkuCount;
drop table #temp_allOffShelfListingEqualListingSkuCount;
drop table #temp_allOffShelfListingDailyIncomesProfitsStatistics;
drop table #temp_allOffShelfListingDenominator14OrderSumSKUCount;
drop table #temp_allOffShelfListingDenominator30OrderSumSKUCount;
2022-03-10 14:50:11, 由 罗友军 关闭。
修改了 任务状态，旧值为 "done"，新值为 "closed"。
修改了 指派给 ，旧值为 "luoyoujun"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "luoyoujun"。
修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2022-03-10 14:50:11"。
修改了 关闭原因，旧值为 ""，新值为 "done"。

基本信息
任务的一生

所属迭代	【智汇】产品部 3月非正常更新版本（2022年）
所属模块	/
相关需求	【数据导出】智能刊登，手动刊登出单情况数据导出（无需发版）
指派给	Closed 于 2022-03-10 14:50:11
任务类型	后端
任务标签	初始
开发组
任务状态	已关闭
进度	100%
优先级	P1
关键词
抄送给

由谁创建	罗友军于 2022-02-25 13:01:36
由谁完成	魏鹏军于 2022-02-25 14:28:52
由谁取消	暂无
由谁关闭	罗友军于 2022-03-10 14:50:11
关闭原因	已完成
最后编辑	罗友军于 2022-03-10 14:50:11

团队	最初预计	总计消耗	预计剩余

工时信息

最初预计	8工时
总计消耗	8工时
预计剩余	0工时
预计开始	0000-00-00
实际开始	2022-02-25
截止日期	0000-00-00