时间戳

首页 > 解决方案 > 如何使用运行总计加快选择查询？

sql - 如何使用运行总计加快选择查询？

问题描述

我有一个大约 45,000 行的源表。它有一些详细信息，然后是周数，以及该周的计划数量和发货数量以及详细信息行。我现在正在尝试为计划和发货添加运行数量

我编写了一个查询，如果我添加了很多 WHERE 子句，它就可以工作，但是如果我删除它们，查询会运行很长时间。

SELECT

 s.Season
,s.CustomerCode
,s.Customer
,s.VarietyCode
,s.Variety
,s.GrowingMethodCode
,s.ProductSizeCode
,s.PackType
,s.Market
,s.Brand
,s.WeekNum
,s.Qty_Planned
,(SELECT

  SUM(Qty_Planned)

  FROM  [DataMiningImports].[Apples_Planned_vs_Shipped]
  WHERE WeekNum <= s.WeekNum
  AND   Season = s.Season
  AND   CustomerCode = s.CustomerCode
  AND   VarietyCode = s.VarietyCode
  AND   GrowingMethodCode = s.GrowingMethodCode
  AND   ProductSizeCode = s.ProductSizeCode
  AND   PackType = s.PackType
  AND   Market = S.Market
  AND   Brand = s.Brand
  ) AS Qty_Planned_Running

,s.Qty_Shipped

,(SELECT

  SUM(Qty_Shipped)

  FROM  [DataMiningImports].[Apples_Planned_vs_Shipped]
  WHERE WeekNum <= s.WeekNum
  AND   Season = s.Season
  AND   CustomerCode = s.CustomerCode
  AND   VarietyCode = s.VarietyCode
  AND   GrowingMethodCode = s.GrowingMethodCode
  AND   ProductSizeCode = s.ProductSizeCode
  AND   PackType = s.PackType
  AND   Market = S.Market
  AND   Brand = s.Brand
  ) AS Qty_Shipped_Running

FROM [DataMiningImports].[Apples_Planned_vs_Shipped] AS s

ORDER BY S.WeekNum

标签： sqlsql-server

解决方案

您正在子查询中进行求和，这很可能对外部表中的每一行运行一次。

相反，请尝试使用窗口函数：

SELECT Season, 
       CustomerCode, Customer,
       VarietyCode, Variety,
       GrowingMethodCode, ProductSizeCode, PackType, Market, Brand, 
       WeekNum, Qty_Planned, 
       SUM(Qty_Planned) OVER(PARTITION BY Season, CustomerCode, VarietyCode, GrowingMethodCode, ProductSizeCode, PackType, Market, Brand ORDER BY WeekNum) AS Qty_Planned_Running, 
       SUM(Qty_Shipped) OVER(PARTITION BY Season, CustomerCode, VarietyCode, GrowingMethodCode, ProductSizeCode, PackType, Market, Brand ORDER BY WeekNum) AS Qty_Shipped_Running
FROM [DataMiningImports].[Apples_Planned_vs_Shipped]
ORDER BY WeekNum

推荐阅读