BUG #57414 订单利润按天统计-消耗大量资源执行时间就导致读库延迟 - 技术中心-需求池

重现步骤

[步骤]

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempQ') and type='U')

BEGIN

DROP TABLE #tempQ

END



IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempAll') and type='U')

BEGIN

DROP TABLE #tempAll

END

select OrderSourceID, StatisticDate into #tempQ from RB_Statistic_Queue(NOLOCK)q where q.CustomerID=1 and StatisticType='OrderProfitStaDimDay' and IsProcess = 0 and ProcessMessage='1' ;CREATE INDEX IX_TempTable_ID ON #tempQ (OrderSourceID,StatisticDate);select g1.* into #tempAll From(

select 1 CustomerID, '' HashKey,Getdate()AddTime,0 AllAmountRMB,

tt1.是否区分发票 PromotionType,convert(date,tt1.DataDate)DataDate,1 DataDim,tt1.BusinessAdminID,tt1.BusinessTeamID,tt1.SalesAdminID,tt1.SalesTeamID,tt1.SettlementDate,tt1.OrderSourceType,tt1.OrderSourceID,tt1.AddOrderReasonType,tt1.WareHouseID,tt1.DeliveryTypeDsec,isnull(tt1.AliExpressTrusteeType,8)AliExpressTrusteeType,

tt1.OrderSourceContry,

sum(isnull(二毛USD,0))'ProfitsUSD', --二毛USD

sum(isnull(二毛RMB,0))'ProfitsRMB',-- 二毛RMB

sum(isnull(二点五毛USD,0))'ProfitsTwoFiveUSD',-- 二点五毛USD

sum(isnull(二点五毛RMB,0))'ProfitsTwoFiveRMB',-- 二点五毛RMB

sum(isnull(销售额USD,0))'SalesPriceUSD',-- 销售额USD

sum(isnull(销售额RMB,0))'SalesPriceRMB',-- 销售额RMB

sum(isnull(销售收入USD,0))'SalesIncomeUSD',-- 销售收入USD

sum(isnull(运费收入USD,0))'TransportPayUSD',-- 运费收入USD

sum(isnull(退款金额USD,0))'SalesRefundsUSD',-- 退款金额USD

sum(isnull(折扣USD,0))'PromotionRebatesUSD',-- 折扣USD

sum(isnull(其他收入USD,0))'OtherCreditsUSD',-- 其他收入USD

sum(isnull(税费收入USD,0))'TaxUSD',-- 税费收入USD

sum(isnull(销售收入RMB,0))'SalesIncomeRMB',-- 销售收入RMB

sum(isnull(运费收入RMB,0))'TransportPayRMB',-- 运费收入RMB

sum(isnull(退款金额RMB,0))'SalesRefundsRMB',-- 退款金额RMB

sum(isnull(折扣RMB,0))'PromotionRebatesRMB',-- 折扣RMB

sum(isnull(其他收入RMB,0))'OtherCreditsRMB',-- 其他收入RMB

sum(isnull(税费收入RMB,0))'TaxRMB',-- 税费收入RMB

sum(isnull(佣金USD,0))'CommisionFeeUSD',-- 佣金USD

sum(isnull(FBA操作费USD,0))'FBATransactionFeesUSD',-- FBA操作费USD

sum(isnull(平台其他支出USD,0))'OrderSourceOtherDebitsUSD',-- 平台其他支出USD

sum(isnull(广告费USD,0))'CostOfAdvertisingUSD',-- 广告费USD

sum(isnull(采购成本USD,0))'BuyPriceUSD',-- 采购成本USD

sum(isnull(退还税费USD,0))'TaxRefundFeeUSD',-- 退还税费USD

sum(isnull(包装成本USD,0))'PackagedCostUSD',-- 包装成本USD

sum(isnull(补贴调整USD,0))'SubsidyPriceUSD',-- 补贴调整USD

sum(isnull(本地仓运费USD,0))'PackageFeesUSD',-- 本地仓运费USD

sum(isnull(海外仓运费USD,0))'OverSeasShipFeesUSD',-- 海外仓运费USD

sum(isnull(头程运费USD,0))'FirstLegFeesUSD',-- 头程运费USD

sum(isnull(仓库处理成本USD,0))'WareHouseManagementPriceUSD',-- 仓库处理成本USD

sum(isnull(税费USD,0))'TariffFeesUSD',-- 税费USD

sum(isnull(汇率折损USD,0))'ExchangeLostUSD',-- 汇率折损USD

sum(isnull(海外仓仓租USD,0))'CustomStorageFeeUSD',-- 海外仓仓租USD

sum(isnull(人工导入费USD,0))'ImportFeeUSD',-- 人工导入费USD

sum(isnull(报损费USD,0))'BreakageFeeUSD',--报损费USD

sum(isnull(佣金RMB,0))'CommisionFeeRMB',-- 佣金RMB

sum(isnull(FBA操作费RMB,0))'FBATransactionFeesRMB',-- FBA操作费RMB

sum(isnull(平台其他支出RMB,0))'OrderSourceOtherDebitsRMB',-- 平台其他支出RMB

sum(isnull(广告费RMB,0))'CostOfAdvertisingRMB',-- 广告费RMB

sum(isnull(采购成本RMB,0))'BuyPriceRMB',-- 采购成本RMB

sum(isnull(退还税费RMB,0))'TaxRefundFeeRMB',-- 退还税费RMB

sum(isnull(包装成本RMB,0))'PackagedCostRMB',-- 包装成本RMB

sum(isnull(补贴调整RMB,0))'SubsidyPriceRMB',-- 补贴调整RMB

sum(isnull(本地仓运费RMB,0))'PackageFeesRMB',-- 本地仓运费RMB

sum(isnull(海外仓运费RMB,0))'OverSeasShipFeesRMB',-- 海外仓运费RMB

sum(isnull(头程运费RMB,0))'FirstLegFeesRMB',-- 头程运费RMB

sum(isnull(仓库处理成本RMB,0))'WareHouseManagementPriceRMB',-- 仓库处理成本RMB

sum(isnull(税费RMB,0))'TariffFeesRMB',-- 税费RMB

sum(isnull(汇率折损RMB,0))'ExchangeLostRMB',-- 汇率折损RMB

sum(isnull(海外仓仓租RMB,0))'CustomStorageFeeRMB',-- 海外仓仓租RMB

sum(isnull(人工导入费RMB,0))'ImportFeeRMB',-- 人工导入费RMB

sum(isnull(报损费RMB,0))'BreakageFeeRMB',-- 报损费RMB,

sum(isnull(AffiliatePartnerCommissionFeeRMB,0))'AffiliatePartnerCommissionFeeRMB',

sum(isnull(AffiliatePartnerCommissionFeeUSD,0))'AffiliatePartnerCommissionFeeUSD',

sum(isnull(SalesRefundsV2RMB,0))'SalesRefundsV2RMB',

sum(isnull(SalesRefundsV2USD,0))'SalesRefundsV2USD',

sum(isnull(SalesVatFeeRMB,0))'SalesVatFeeRMB',

sum(isnull(SalesVatFeeUSD,0))'SalesVatFeeUSD',

sum(isnull(CustomFeeOtherCostPriceRMB,0))'CustomFeeOtherCostPriceRMB',

sum(isnull(CustomFeeOtherCostPriceUSD,0))'CustomFeeOtherCostPriceUSD',

sum(FirstLegBreakageFeeRMB)FirstLegBreakageFeeRMB,

sum(FirstLegBreakageFeeUSD)FirstLegBreakageFeeUSD,

sum(OrderNum)OrderNum,

sum(ProdutNum)ProdutNum ,

tt1.Idx

From (

select 1'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType,

alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,

3 AliExpressTrusteeType,

alias.OrderSourceContry,

sum(Profits/alias.USD_ExchangeRMB)'二毛USD',

sum(Profits)'二毛RMB',

sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD',

sum(Profits+isnull(BreakageFee,0))'二点五毛RMB',

sum(0)'订单数',

sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD',

sum(SalesPrice)'销售额RMB',

sum(0)'销售数量',

sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD',

sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD',

sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD',

sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD',

sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD',

sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD',

sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB',

sum(alias.TransportPay)'运费收入RMB',

sum(alias.SalesRefunds)'退款金额RMB',

sum(alias.PromotionRebates)'折扣RMB',

sum(alias.OtherCredits)'其他收入RMB',

sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB',

sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD',

sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD',

sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD',

sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD',

sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD',

sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD',

sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD',

sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD',

sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD',

sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD',

sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD',

sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD',

sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD',

sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD',

sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD',

sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD',

sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD',

sum(alias.CommisionFee)'佣金RMB',

sum(alias.FBATransactionFees)'FBA操作费RMB',

sum(alias.OrderSourceOtherDebits)'平台其他支出RMB',

sum(alias.CostOfAdvertising)'广告费RMB',

sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB',

sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB',

sum(alias.PackagedCost)'包装成本RMB',

sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB',

sum(alias.PackageFees)'本地仓运费RMB',

sum(alias.OverSeasShipFees)'海外仓运费RMB',

sum(alias.FirstLegFees)'头程运费RMB',

sum(alias.WareHouseManagementPrice)'仓库处理成本RMB',

sum(alias.TariffFees)'税费RMB',

sum(alias.ExchangeLost)'汇率折损RMB',

sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB',

sum(ISNULL(alias.ImportFee,0))'人工导入费RMB',

sum(ISNULL(alias.BreakageFee,0))'报损费RMB',



sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB',

sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD',

ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB',

ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD',

sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB',

sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD',

sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB',

sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD',

sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB',

sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD',

COUNT(distinct alias.OrderCode)OrderNum,

SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum ,

alias.Idx

From(

select alias.*,3 AliExpressTrusteeType,

case (alias.OrderCode) when '' then 0

else

case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC)

else 0

end

end Idx

from RB_Report_OrderSettlement alias(nolock)

join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.ShipDate





where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ)



and alias.OrderSourceType in(1)

AND ISNULL(alias.PromotionType,0)=0

AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate<'2024-05-01' ) ) or alias.CostOfAdvertising=0 OR alias.OrderDescription LIKE '%发票广告费%' )

)alias

group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID,

ISNULL(alias.DeliveryTypeDsec, ''),



alias.OrderSourceContry,convert(varchar(10), alias.ShipDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx

UNION ALL

select 1'是否区分发票',convert(varchar(10), alias.SettlementDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType,

alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,

3 AliExpressTrusteeType,

alias.OrderSourceContry,

sum(Profits/alias.USD_ExchangeRMB)'二毛USD',

sum(Profits)'二毛RMB',

sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD',

sum(Profits+isnull(BreakageFee,0))'二点五毛RMB',

sum(0)'订单数',

sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD',

sum(SalesPrice)'销售额RMB',

sum(0)'销售数量',

sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD',

sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD',

sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD',

sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD',

sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD',

sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD',

sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB',

sum(alias.TransportPay)'运费收入RMB',

sum(alias.SalesRefunds)'退款金额RMB',

sum(alias.PromotionRebates)'折扣RMB',

sum(alias.OtherCredits)'其他收入RMB',

sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB',

sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD',

sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD',

sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD',

sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD',

sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD',

sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD',

sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD',

sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD',

sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD',

sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD',

sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD',

sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD',

sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD',

sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD',

sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD',

sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD',

sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD',

sum(alias.CommisionFee)'佣金RMB',

sum(alias.FBATransactionFees)'FBA操作费RMB',

sum(alias.OrderSourceOtherDebits)'平台其他支出RMB',

sum(alias.CostOfAdvertising)'广告费RMB',

sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB',

sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB',

sum(alias.PackagedCost)'包装成本RMB',

sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB',

sum(alias.PackageFees)'本地仓运费RMB',

sum(alias.OverSeasShipFees)'海外仓运费RMB',

sum(alias.FirstLegFees)'头程运费RMB',

sum(alias.WareHouseManagementPrice)'仓库处理成本RMB',

sum(alias.TariffFees)'税费RMB',

sum(alias.ExchangeLost)'汇率折损RMB',

sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB',

sum(ISNULL(alias.ImportFee,0))'人工导入费RMB',

sum(ISNULL(alias.BreakageFee,0))'报损费RMB',



sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB',

sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD',

ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB',

ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD',

sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB',

sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD',

sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB',

sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD',

sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB',

sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD',

COUNT(distinct alias.OrderCode)OrderNum,

SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum ,

alias.Idx

From(

select alias.*,3 AliExpressTrusteeType,

case (alias.OrderCode) when '' then 0

else

case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC)

else 0

end

end Idx

from RB_Report_OrderSettlement alias(nolock)

join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=convert(varchar(10), alias.SettlementDate,120)





where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ)



and alias.OrderSourceType in(1)

AND ISNULL(alias.PromotionType,0)=1

AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate<'2024-05-01' ) ) or alias.CostOfAdvertising=0 OR alias.OrderDescription LIKE '%发票广告费%' )

)alias

group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID,

ISNULL(alias.DeliveryTypeDsec, ''),



alias.OrderSourceContry,convert(varchar(10), alias.SettlementDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx

UNION ALL

select 2 '是否区分发票',convert(varchar(10), alias.SettlementDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120) SettlementDate,alias.OrderSourceType,alias.OrderSourceID,'否'AddOrderReasonType,

alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,

3 AliExpressTrusteeType,

alias.OrderSourceContry,

sum(alias.AdCost*alias.ExchangeRMB/alias.USD_ExchangeRMB)'二毛USD',

sum(alias.AdCost*alias.ExchangeRMB)'二毛RMB',

sum((alias.AdCost*alias.ExchangeRMB-0)/alias.USD_ExchangeRMB)'二点五毛USD',

sum(alias.AdCost*alias.ExchangeRMB-0)'二点五毛RMB',

sum(0)'订单数',

0'销售额USD',

0'销售额RMB',

sum(0)'销售数量',

0'销售收入USD',

0'运费收入USD',

0'退款金额USD',

0'折扣USD',

0'其他收入USD',

0'税费收入USD',

0'销售收入RMB',

0'运费收入RMB',

0'退款金额RMB',

0'折扣RMB',

0'其他收入RMB',

0'税费收入RMB',

0'佣金USD',

0'FBA操作费USD',

0'平台其他支出USD',

sum(alias.AdCost*alias.ExchangeRMB/alias.USD_ExchangeRMB)'广告费USD',

0'采购成本USD',

0'退还税费USD',

0'包装成本USD',

0'补贴调整USD',

0'本地仓运费USD',

0'海外仓运费USD',

0'头程运费USD',

0'仓库处理成本USD',

0'税费USD',

0'汇率折损USD',

0'海外仓仓租USD',

0'人工导入费USD',

0'报损费USD',

0'佣金RMB',

0'FBA操作费RMB',

0'平台其他支出RMB',

sum(alias.AdCost*alias.ExchangeRMB)'广告费RMB',

0'采购成本RMB',

0 '退还税费RMB',

0'包装成本RMB',

0'补贴调整RMB',

0'本地仓运费RMB',

0'海外仓运费RMB',

0'头程运费RMB',

0'仓库处理成本RMB',

0'税费RMB',

0'汇率折损RMB',

0'海外仓仓租RMB',

0'人工导入费RMB',

0'报损费RMB',0 'AffiliatePartnerCommissionFeeRMB',0 'AffiliatePartnerCommissionFeeUSD',0 'SalesRefundsV2RMB',0 'SalesRefundsV2USD',0'SalesVatFeeRMB',0'SalesVatFeeUSD',0'CustomFeeOtherCostPriceRMB',0'CustomFeeOtherCostPriceUSD',0 'FirstLegBreakageFeeRMB',0 'FirstLegBreakageFeeUSD',0 'ProdutNum' ,0 'OrderNum',1 'Idx'

from RB_Report_OrderSettlement_AdCost_Share alias(nolock)

join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.SettlementDate

where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ)

and alias.OrderSourceType in(1) AND alias.SettlementDate>='2024-05-01' AND alias.IsBuildProfit=1

group by alias.OrderSourceType,alias.OrderSourceID,alias.WareHouseID,

ISNULL(alias.DeliveryTypeDsec, ''),

alias.OrderSourceContry,convert(varchar(10), alias.SettlementDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120)

UNION ALL

select 2'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType,

alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,

alias.AliExpressTrusteeType AliExpressTrusteeType,

alias.OrderSourceContry,

sum(Profits/alias.USD_ExchangeRMB)'二毛USD',

sum(Profits)'二毛RMB',

sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD',

sum(Profits+isnull(BreakageFee,0))'二点五毛RMB',

sum(0)'订单数',

sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD',

sum(SalesPrice)'销售额RMB',

sum(0)'销售数量',

sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD',

sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD',

sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD',

sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD',

sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD',

sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD',

sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB',

sum(alias.TransportPay)'运费收入RMB',

sum(alias.SalesRefunds)'退款金额RMB',

sum(alias.PromotionRebates)'折扣RMB',

sum(alias.OtherCredits)'其他收入RMB',

sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB',

sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD',

sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD',

sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD',

sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD',

sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD',

sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD',

sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD',

sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD',

sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD',

sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD',

sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD',

sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD',

sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD',

sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD',

sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD',

sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD',

sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD',

sum(alias.CommisionFee)'佣金RMB',

sum(alias.FBATransactionFees)'FBA操作费RMB',

sum(alias.OrderSourceOtherDebits)'平台其他支出RMB',

sum(alias.CostOfAdvertising)'广告费RMB',

sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB',

sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB',

sum(alias.PackagedCost)'包装成本RMB',

sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB',

sum(alias.PackageFees)'本地仓运费RMB',

sum(alias.OverSeasShipFees)'海外仓运费RMB',

sum(alias.FirstLegFees)'头程运费RMB',

sum(alias.WareHouseManagementPrice)'仓库处理成本RMB',

sum(alias.TariffFees)'税费RMB',

sum(alias.ExchangeLost)'汇率折损RMB',

sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB',

sum(ISNULL(alias.ImportFee,0))'人工导入费RMB',

sum(ISNULL(alias.BreakageFee,0))'报损费RMB',



sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB',

sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD',

ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB',

ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD',

sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB',

sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD',

sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB',

sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD',

sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB',

sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD',

COUNT(distinct alias.OrderCode)OrderNum,

SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum ,

alias.Idx

From(

select alias.*,oi.AliExpressTrusteeType AliExpressTrusteeType,

case (alias.OrderCode) when '' then 0

else

case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC)

else 0

end

end Idx

from RB_Report_OrderSettlement alias(nolock)

join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.ShipDate

left join RB_OrderInfo oi(NOLOCK) ON oi.CustomerID=alias.CustomerID AND oi.OrderSourceID=alias.OrderSourceID AND oi.OrderCode=alias.OrderCode



where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ)



and alias.OrderSourceType in(57)



AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate<'2024-05-01' ) ) or alias.CostOfAdvertising=0 OR alias.OrderDescription LIKE '%发票广告费%' )

)alias

group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID,

ISNULL(alias.DeliveryTypeDsec, ''),

alias.AliExpressTrusteeType,

alias.OrderSourceContry,convert(varchar(10), alias.ShipDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx

UNION ALL

select 2'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType,

alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec,

3 AliExpressTrusteeType,

alias.OrderSourceContry,

sum(Profits/alias.USD_ExchangeRMB)'二毛USD',

sum(Profits)'二毛RMB',

sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD',

sum(Profits+isnull(BreakageFee,0))'二点五毛RMB',

sum(0)'订单数',

sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD',

sum(SalesPrice)'销售额RMB',

sum(0)'销售数量',

sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD',

sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD',

sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD',

sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD',

sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD',

sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD',

sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB',

sum(alias.TransportPay)'运费收入RMB',

sum(alias.SalesRefunds)'退款金额RMB',

sum(alias.PromotionRebates)'折扣RMB',

sum(alias.OtherCredits)'其他收入RMB',

sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB',

sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD',

sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD',

sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD',

sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD',

sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD',

sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD',

sum(alias.PackagedCost/ali

历史记录

2025-05-29 09:42:25, 由 袁慧斌 创建。
2025-05-29 09:58:22, 由 李乐同 指派给 张明磊。
修改了 指派给，旧值为 "liletong"，新值为 "zhangminglei"。
2025-06-04 19:13:17, 由 张明磊 编辑。
修改了 解决时效，旧值为 "0"，新值为 "-1"。

都在30min内执行完毕
2025-06-04 19:13:28, 由 张明磊 解决，方案为 性能问题 。
修改了 解决方案，旧值为 ""，新值为 "performance"。
修改了 解决日期，旧值为 ""，新值为 "2025-06-04 19:13:19"。
修改了 指派给 ，旧值为 "zhangminglei"，新值为 "yuanhuibin"。
修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
修改了 是否确认，旧值为 "0"，新值为 "1"。
修改了 解决者 ，旧值为 ""，新值为 "zhangminglei"。
2025-06-06 09:18:04, 由 袁慧斌 指派给 张明磊。
修改了 指派给，旧值为 "yuanhuibin"，新值为 "zhangminglei"。

大批量数据操作影响读写同步延迟。建议分批次处理
2025-06-06 16:12:23, 由 张明磊 指派给 陈少军。
修改了 指派给，旧值为 "zhangminglei"，新值为 "chenshaojun"。

删除和插入分批处理

for
switchDELETE aliasFROM RB_Report_OrderSettlement_StatShiptimeDay alias (NOLOCK) JOIN #tempQ tmp ON alias.OrderSourceID = tmp.OrderSourceID AND alias.DataDate = tmp.StatisticDate AND alias.PromotionType IN( 1, 2 ); ;for INSERT INTO RB_Report_OrderSettlement_StatShiptimeDay
2025-06-06 16:12:39, 由 张明磊 编辑。
修改了 解决时效，旧值为 "0"，新值为 "81.52"。
修改了 指派给 ，旧值为 "chenshaojun"，新值为 "linhen"。
2025-06-06 16:25:26, 由林很激活。
修改了 Bug状态 ，旧值为 "resolved"，新值为 "active"。
修改了 激活日期，旧值为 ""，新值为 "2025-06-06 16:25:26"。
修改了 激活次数，旧值为 "0"，新值为 "1"。
修改了 解决方案，旧值为 "performance"，新值为 ""。
修改了 解决日期，旧值为 "2025-06-04 19:13:19"，新值为 "0000-00-00"。
修改了 解决者 ，旧值为 "zhangminglei"，新值为 ""。
修改了 关闭日期，旧值为 ""，新值为 "0000-00-00"。
2025-06-06 16:26:32, 由林很转为需求，编号为 #29069 订单利润按天统计-消耗大量资源执行时间就导致读库延迟 。
2025-06-06 16:26:32, 由林很关闭。
2025-06-18 09:15:10, 由 袁慧斌 激活。
修改了 指派给 ，旧值为 "closed"，新值为 "zhangminglei"。
修改了 Bug状态 ，旧值为 "closed"，新值为 "active"。
修改了 影响环境，旧值为 "proonline"，新值为 "pro627"。
修改了 激活日期，旧值为 "2025-06-06 16:25:26"，新值为 "2025-06-18 09:15:10"。
修改了 激活次数，旧值为 "1"，新值为 "2"。
修改了 解决方案，旧值为 "tostory"，新值为 ""。
修改了 解决日期，旧值为 "2025-06-06 16:26:32"，新值为 "0000-00-00"。
修改了 解决者 ，旧值为 "linhen"，新值为 ""。
修改了 由谁关闭，旧值为 "linhen"，新值为 ""。
修改了 关闭日期，旧值为 "2025-06-06 16:26:32"，新值为 "0000-00-00"。
修改了 转需求 ，旧值为 "29069"，新值为 "0"。

分批次，需要给临时表创建好批次的索引， #tempAllV2 “在创建表插入数据后” 创建一个以 NumIdx 的索引。
2025-06-18 11:08:32, 由 张明磊 解决，方案为 性能问题 。
修改了 解决方案，旧值为 ""，新值为 "performance"。
修改了 解决日期，旧值为 ""，新值为 "2025-06-18 11:08:26"。
修改了 指派给 ，旧值为 "zhangminglei"，新值为 "yuanhuibin"。
修改了 Bug状态 ，旧值为 "active"，新值为 "resolved"。
修改了 解决者 ，旧值为 ""，新值为 "zhangminglei"。
2025-06-19 20:56:54, 由 袁慧斌 编辑。
修改了 测试责任人，旧值为 ""，新值为 "admin"。
修改了 解决时效 ，旧值为 "81.52"，新值为 "313.43"。
修改了 上线时间 ，旧值为 "0000-00-00 00:00:00"，新值为 "2025-06-19 20:56"。
修改了 上线时效 ，旧值为 "-1"，新值为 "347.23"。
2025-06-19 20:56:57, 由 袁慧斌 关闭。
修改了 Bug状态 ，旧值为 "resolved"，新值为 "closed"。
修改了 指派给 ，旧值为 "yuanhuibin"，新值为 "closed"。
修改了 由谁关闭，旧值为 ""，新值为 "yuanhuibin"。
修改了 关闭日期，旧值为 ""，新值为 "2025-06-19 20:56:57"。
2025-06-27 09:06:42, 由 陈莉珍 编辑。
修改了 上线时间，旧值为 "2025-06-19 20:56:00"，新值为 "2025-06-06 16:25"。
修改了 上线时效，旧值为 "347.23"，新值为 "126.71"。

基本信息
迭代/需求/任务

所属项目	技术中心-需求池
所属模块	财务客户订单
所属计划
所属迭代
Bug类型
严重程度	一般
优先级	P2
Bug状态	已关闭
业务部门	技术研发中心
影响功能
开发责任人	张明磊
测试责任人	admin
解决时效	313.43
激活次数	2
激活日期	2025-06-18 09:15:10
是否确认	是
当前指派	Closed 于 2025-06-27 09:06:42
截止日期	0000-00-00
上线时间	2025-06-06 16:25:00
上线时效	126.71
操作系统
浏览器
关键词
抄送给

所属迭代
相关需求
相关任务

Bug的一生
其他相关

由谁创建	袁慧斌于 2025-05-29 09:42:24
影响环境	生产环境-业务反馈
由谁解决	张明磊于 2025-06-18 11:08:26
解决版本
解决方案	性能问题
由谁关闭	袁慧斌于 2025-06-19 20:56:57
最后修改	陈莉珍于 2025-06-27 09:06:42

相关Bug