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

统计的数据量区间过大，销售分析项目还需要熟悉阶段暂无法做更进一步的优化，架构师确认一下可有优化的空间，

瑞丰，把查询的sql 和参数贴到这里，我优化一下

以后性能问题，需要优化的也都贴一下 sql

部分sql语句：

select 

isnull(tmpfee.Profits,0) as OperateFee--毛利润
,isnull(tmpfee.SalesRefunds,0) as RefundPrice--退款金额
,isnull(tmpfee.SalesPrice,0) as SalesPrice--销售收入
,isnull(tmpfee.TransportPay,0) as TransportPay--运费收入
,isnull(tmpfee.TaxIncome,0) AS TaxIncome--税费收入
,isnull(tmpfee.OtherCredits,0) as OtherIncomeAmount--其他收入
,isnull(tmpfee.PromotionRebates,0) as PromotionFee--折扣费
,0 as RefundOrderNum
,'' as ReportDay 
from 
(
select 
SUM(s.Profits/s.USD_ExchangeRMB) Profits,
SUM(s.SalesPrice/s.USD_ExchangeRMB) SalesPrice,
SUM(s.TransportPay/s.USD_ExchangeRMB) TransportPay,
SUM(s.SalesRefunds/s.USD_ExchangeRMB) SalesRefunds,
SUM(s.PromotionRebates/s.USD_ExchangeRMB) PromotionRebates,
SUM(s.OtherCredits/s.USD_ExchangeRMB) OtherCredits,
SUM((ISNULL(s.Tax,0)+ISNULL(s.MarketplaceFacilitatorTax,0))/s.USD_ExchangeRMB) as TaxIncome
FROM RB_Report_OrderSettlement s WITH (NOLOCK)
--inner join RB_Product p WITH(NOLOCK) on s.SKU=p.SKU and p.CustomerID=1
WHERE  
s.CustomerID =1
--and s.OrderSourceID IN (select item from CharSplitForIn('%s',','))
and s.PayDate BETWEEN '2021-04-01' AND '2022-04-01'
--and p.BrandID IN (select item from CharSplitForIn('%s',','))

) tmpfee

go接口 :代码路径:erpapi\pkg\platform\amazon\settlement.go\SalesAnalysisSettlementReportGroup

现在一个单表2个条件查询查询，不好优化，把sql  补充全 我在看看优化一下

 这个部分sql不好优化，帮把sql 补全，我在看看

select 

isnull(tmpfee.Profits,0) as OperateFee--毛利润
,isnull(tmpfee.SalesRefunds,0) as RefundPrice--退款金额
,isnull(tmpfee.SalesPrice,0) as SalesPrice--销售收入
,isnull(tmpfee.TransportPay,0) as TransportPay--运费收入
,isnull(tmpfee.TaxIncome,0) AS TaxIncome--税费收入
,isnull(tmpfee.OtherCredits,0) as OtherIncomeAmount--其他收入
,isnull(tmpfee.PromotionRebates,0) as PromotionFee--折扣费
,0 as RefundOrderNum
,tmpfee.ReportDay as ReportDay
from
(
select
CONVERT(varchar(10),s.PayDate,120) as ReportDay,
SUM(s.Profits) Profits,
SUM(s.SalesPrice) SalesPrice,
SUM(s.TransportPay) TransportPay,
SUM(s.SalesRefunds) SalesRefunds,
SUM(s.PromotionRebates) PromotionRebates,
SUM(s.OtherCredits) OtherCredits,SUM((ISNULL(s.Tax,0)+ISNULL(s.MarketplaceFacilitatorTax,0))) as TaxIncome
FROM RB_Report_OrderSettlement s WITH (NOLOCK)

WHERE   s.CustomerID =1 AND  s.PayDate BETWEEN '2020-01-01' AND '2022-01-01'
GROUP BY CONVERT(varchar(10),s.PayDate,120)
) tmpfee

SELECT  CONVERT(varchar(10),s.PayDate,120) as ReportDay, COUNT(DISTINCT s.OrderCode) AS RefundOrderNum
FROM RB_Report_OrderSettlement s with(nolock)
WHERE s.OrderCode<>'' and s.SalesRefunds<>0  and  s.CustomerID =1 AND  s.PayDate BETWEEN '2020-01-01' AND '2022-01-01' GROUP BY CONVERT(varchar(10),s.PayDate,120)

优化索引后18 秒执行完成，再验证一下，看看是否够用

优化索引后18 秒执行完成，再验证一下，看看是否够用

再验证下

线上观察，测试环境无数据，看不出效果