如何優化我的查詢速度（避免對每一行使用子選擇）？-有解無憂

我有一個名為 CisLinkLoadedData 的表。有Distributor, Network, Product, DocumentDate, Weight,AmountCP和Quantity列。它用于存盤一些產品的日常銷售。AmountCP / Quantity是產品在特定日期的價格。有促銷和定期銷售，但沒有標志。我們可以通過比較它的價格與一個月內的最高記錄價格來判斷某個記錄是常規的還是促銷的。我確實在這張照片上解釋過。如何優化我的查詢速度（避免對每一行使用子選擇）？

我需要進行查詢以顯示每月某些產品的匯總常規和促銷銷售。好吧，我做到了，但它非常慢（6 分鐘執行 160 萬條記錄）。我懷疑這是因為我使用子查詢來確定每條記錄的最高價格，但我不知道如何以另一種方式進行。

這是我做的：

SELECT
    Distributor,
    Network,
    Product,
    cast(month(DocumentDate) as VARCHAR)   '.'   cast(year(DocumentDate) as VARCHAR) AS MonthYear,
    SUM(Weight) AS MonthlyWeight,
    IsPromo
FROM (SELECT
        main_clld.Distributor,
        main_clld.Network,
        main_clld.Product,
        main_clld.DocumentDate,
        main_clld.Weight,
        main_clld.Quantity,
        main_clld.AmountCP,
        CASE WHEN (main_clld.AmountCP / main_clld.Quantity) < (SELECT MAX(sub_clld.AmountCP / NULLIF(sub_clld.Quantity, 0)) FROM CisLinkLoadedData AS sub_clld WHERE sub_clld.Distributor = main_clld.Distributor AND sub_clld.Network = main_clld.Network AND sub_clld.Product = main_clld.Product AND cast(month(sub_clld.DocumentDate) as VARCHAR)   '.'   cast(year(sub_clld.DocumentDate) as VARCHAR) = cast(month(main_clld.DocumentDate) as VARCHAR)   '.'   cast(year(main_clld.DocumentDate) as VARCHAR) AND sub_clld.Quantity > 0 AND sub_clld.GCRecord IS NULL) THEN 1 ELSE 0 END AS IsPromo
    FROM CisLinkLoadedData AS main_clld
    WHERE main_clld.Quantity > 0 AND main_clld.GCRecord IS NULL) AS bad_query
GROUP BY
    Distributor,
    Network,
    Product,
    cast(month(DocumentDate) as VARCHAR)   '.'   cast(year(DocumentDate) as VARCHAR),
    IsPromo;

在這種情況下可以做什么？順便說一句，如果您可以使用類似 ( Distributor, Network, Product, MonthYear, RegularWeight, PromoWeight) 的另一種結構來制作結果表 - 那就更好了。這是我最初嘗試過的，但失敗了。我使用 Microsoft SQL Server。

uj5u.com熱心網友回復：

您可以使用視窗函式而不是相關子查詢來檢索每組的最高價格（每個組由 partition by 子句定義）：

MAX(main_clld.AmountCP / NULLIF(main_clld.Quantity, 0)) 
 OVER(PARTITION BY main_clld.Distributor, main_clld.Network, 
                   main_clld.Product, EOMONTH(main_clld.DocumentDate))

我認為您的完整查詢最終會是：

SELECT
    Distributor,
    Network,
    Product,
    MonthYear,
    SUM(Weight) AS MonthlyWeight,
    IsPromo
FROM (SELECT
        main_clld.Distributor,
        main_clld.Network,
        main_clld.Product,
        main_clld.DocumentDate,
        main_clld.Weight,
        main_clld.Quantity,
        main_clld.AmountCP,
        CAST(MONTH(DocumentDate) AS VARCHAR(2))   '.'   cast(year(DocumentDate) as VARCHAR(2)) AS MonthYear,
        CASE WHEN (main_clld.AmountCP / main_clld.Quantity) < MAX(main_clld.AmountCP / NULLIF(main_clld.Quantity, 0)) 
                                                                OVER(PARTITION BY main_clld.Distributor, main_clld.Network,
                                                                                    main_clld.Product, EOMONTH(main_clld.DocumentDate)) 
                THEN 1 ELSE 0 END AS IsPromo
    FROM CisLinkLoadedData AS main_clld
    WHERE main_clld.Quantity > 0 
    AND main_clld.GCRecord IS NULL
    ) AS bad_query
GROUP BY
    Distributor,
    Network,
    Product,
    MonthYear,
    IsPromo;

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/486974.html

標籤：sql sql服务器查询优化

上一篇：如何在Discord.JSSelectMenus中使用SQL結果？

下一篇：使用大量連接優化此MSSQL查詢