时间戳

首页 > 解决方案 > 优化计算年初至今和上一年至今的查询

sql - 优化计算年初至今和上一年至今的查询

问题描述

我有以下查询来计算年初至今和今天的前一年。我该如何优化它？

;

WITH grouped_by_date AS
  (SELECT *
   FROM tmp.FACT_DC s),
     cumulative_sum_for_ytd AS
  (SELECT T.*,
          T1.QTY_UoM_YTD,t1.QTY_YTD,t1.AMOUNT_LCPNV
   FROM grouped_by_date T CROSS APPLY
     (SELECT SUM(QTY_UoM) AS QTY_UoM_YTD,SUM(QTY) AS QTY_YTD, SUM(PNV_LC)as AMOUNT_LCPNV
      FROM grouped_by_date
      WHERE [Delivery_Year]=T.[Delivery_Year]
        AND convert(date,Delivery_Year+'-'+ Delivery_month+'-'+ [Invoicing_Day])<=convert(date,T.Delivery_Year+'-'+ t.Delivery_month+'-'+ t.[Invoicing_Day])
        AND [Sales_Organization]=T.Sales_Organization
        AND[Sales_Product_Name_N3]=T.Sales_Product_Name_N3
        AND[Sales_Product_Name_N2]=T.[Sales_Product_Name_N2]
        AND[Sales_Product_Name_N1]=T.[Sales_Product_Name_N1]
        AND[Market_Segment_Name_N2]=T.[Market_Segment_Name_N2]
        AND[Doc_Currency]=T.Doc_Currency
        AND[User_Name_N1]=T.User_Name_N1
        AND[Engineer_Code]=T.Engineer_Code
        AND[User_Name_N2]=T.User_Name_N2
        AND[Bill_To_Customer_Code]=T.Bill_To_Customer_Code
        AND[User_Country_Code]=T.User_Country_Code
        AND[User_N3_Country_Name]=T.User_N3_Country_Name ) T1)
SELECT lastYear.*,
  (SELECT SUM(thisYear.QTY_UoM) AS QTY_UoM_YTD_prev
   FROM cumulative_sum_for_ytd thisYear
   WHERE convert(int,thisYear.Delivery_Year) = convert(int,lastYear.Delivery_Year - 1)
     AND (convert(int,thisYear.Delivery_Month) < convert(int,lastYear.Delivery_Month)
          OR convert(int,thisYear.Delivery_Month) = convert(int,lastYear.Delivery_Month)
          AND convert(int,thisYear.Invoicing_Day) <- convert(int,lastYear.Invoicing_Day))
     AND thisYear.[Sales_Organization]=lastYear.Sales_Organization
     AND thisYear.[Sales_Product_Name_N3]=lastYear.Sales_Product_Name_N3
     AND thisYear.[Sales_Product_Name_N2]=lastYear.[Sales_Product_Name_N2]
     AND thisYear.[Sales_Product_Name_N1]=lastYear.[Sales_Product_Name_N1]
     AND thisYear.[Market_Segment_Name_N2]=lastYear.[Market_Segment_Name_N2]
     AND thisYear.[Doc_Currency]=lastYear.Doc_Currency
     AND thisYear.[User_Name_N1]=lastYear.User_Name_N1
     AND thisYear.[Engineer_Code]=lastYear.Engineer_Code
     AND thisYear.[User_Name_N2]=lastYear.User_Name_N2
     AND thisYear.[Bill_To_Customer_Code]=lastYear.Bill_To_Customer_Code
     AND thisYear.[User_Country_Code]=lastYear.User_Country_Code
     AND thisYear.[User_N3_Country_Name]=lastYear.User_N3_Country_Name )AS QTY_UoM_YTD_prev,
     (SELECT SUM(thisYear.QTY) 
   FROM cumulative_sum_for_ytd thisYear
   WHERE convert(int,thisYear.Delivery_Year) = convert(int,lastYear.Delivery_Year - 1)
     AND (convert(int,thisYear.Delivery_Month) < convert(int,lastYear.Delivery_Month)
          OR convert(int,thisYear.Delivery_Month) = convert(int,lastYear.Delivery_Month)
          AND convert(int,thisYear.Invoicing_Day) <- convert(int,lastYear.Invoicing_Day))
     AND thisYear.[Sales_Organization]=lastYear.Sales_Organization

  AND thisYear.[Sales_Product_Name_N3]=lastYear.Sales_Product_Name_N3
     AND thisYear.[Sales_Product_Name_N2]=lastYear.[Sales_Product_Name_N2]
     AND thisYear.[Sales_Product_Name_N1]=lastYear.[Sales_Product_Name_N1]
     AND thisYear.[Market_Segment_Name_N2]=lastYear.[Market_Segment_Name_N2]
     AND thisYear.[Doc_Currency]=lastYear.Doc_Currency
     AND thisYear.[User_Name_N1]=lastYear.User_Name_N1
     AND thisYear.[Engineer_Code]=lastYear.Engineer_Code
     AND thisYear.[User_Name_N2]=lastYear.User_Name_N2
     AND thisYear.[Bill_To_Customer_Code]=lastYear.Bill_To_Customer_Code
     AND thisYear.[User_Country_Code]=lastYear.User_Country_Code
     AND thisYear.[User_N3_Country_Name]=lastYear.User_N3_Country_Name )AS

QTY_YTD_prev, (SELECT SUM(thisYear.AMOUNT_LCPNV) FROMulative_sum_for_ytd thisYear

WHERE convert(int,thisYear.Delivery_Year) = convert(int,lastYear.Delivery_Year - 1)
     AND (convert(int,thisYear.Delivery_Month) < convert(int,lastYear.Delivery_Month)
          OR convert(int,thisYear.Delivery_Month) = convert(int,lastYear.Delivery_Month)
          AND convert(int,thisYear.Invoicing_Day) <- convert(int,lastYear.Invoicing_Day))
     AND thisYear.[Sales_Organization]=lastYear.Sales_Organization
     AND thisYear.[Sales_Product_Name_N3]=lastYear.Sales_Product_Name_N3
     AND thisYear.[Sales_Product_Name_N2]=lastYear.[Sales_Product_Name_N2]
     AND thisYear.[Sales_Product_Name_N1]=lastYear.[Sales_Product_Name_N1]
     AND thisYear.[Market_Segment_Name_N2]=lastYear.[Market_Segment_Name_N2]
     AND thisYear.[Doc_Currency]=lastYear.Doc_Currency
     AND thisYear.[User_Name_N1]=lastYear.User_Name_N1
     AND thisYear.[Engineer_Code]=lastYear.Engineer_Code
     AND thisYear.[User_Name_N2]=lastYear.User_Name_N2
     AND thisYear.[Bill_To_Customer_Code]=lastYear.Bill_To_Customer_Code
     AND thisYear.[User_Country_Code]=lastYear.User_Country_Code
     AND thisYear.[User_N3_Country_Name]=lastYear.User_N3_Country_Name )AS

AMOUNT_LCPNV_prev FROM 累积_

sum_for_ytd la

年

标签： sqltsqlsql-server-2008

解决方案

您可以使用临时表来重新组织代码，而不是使用 CTE。在 Temps 上放置正确的索引也会加快速度。

例如：

    drop table if exists #cumulative_sum_for_ytd;

    SELECT T.*,
       T1.QTY_UoM_YTD,t1.QTY_YTD,t1.AMOUNT_LCPNV
INTO #cumulative_sum_for_ytd
FROM tmp.FACT_DC  T
CROSS APPLY
(
    SELECT SUM(QTY_UoM) AS QTY_UoM_YTD,SUM(QTY) AS QTY_YTD, SUM(PNV_LC)as AMOUNT_LCPNV
    FROM tmp.FACT_DC 
    WHERE [Delivery_Year]=T.[Delivery_Year]
        AND convert(date,Delivery_Year+'-'+ Delivery_month+'-'+ [Invoicing_Day])<=convert(date,T.Delivery_Year+'-'+ t.Delivery_month+'-'+ t.[Invoicing_Day])
        AND [Sales_Organization]=T.Sales_Organization
        AND[Sales_Product_Name_N3]=T.Sales_Product_Name_N3
        AND[Sales_Product_Name_N2]=T.[Sales_Product_Name_N2]
        AND[Sales_Product_Name_N1]=T.[Sales_Product_Name_N1]
        AND[Market_Segment_Name_N2]=T.[Market_Segment_Name_N2]
        AND[Doc_Currency]=T.Doc_Currency
        AND[User_Name_N1]=T.User_Name_N1
        AND[Engineer_Code]=T.Engineer_Code
        AND[User_Name_N2]=T.User_Name_N2
        AND[Bill_To_Customer_Code]=T.Bill_To_Customer_Code
        AND[User_Country_Code]=T.User_Country_Code
        AND[User_N3_Country_Name]=T.User_N3_Country_Name
) T1

ETC ...

推荐阅读