1. 问题描述分析
bug_28258 【销售分析内测】查询维度按年查询，从2020年1月到2022年1月，查询时间共花费1.68分钟
2.解决方案
优化索引后18秒查询完成
3.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)