如何在不複製 SQL 的情況下更改聚合函式(使用 SQL)
在 SQL Server 2016 中,我有一個場景,其中數據將根據大型
GROUP BY ROLLUP
. 我想要一個儲存過程,它有一個參數,該參數指定使用哪個聚合函式來描述分組,這種方式不會冒 SQL 注入的風險並利用編譯(這是一個繁重的儲存過程)。我的想法是使用一組查詢來總結特定聚合函式上的數據分組。(例如 agg.DataMin、agg.DataMedian、agg.DataWeightedAverage 等)。然後將這些與 CTE 中的參數一起使用
WITH AggData AS ( SELECT * FROM agg.DataMin WHERE @AggFunction = 1 UNION ALL SELECT * FROM agg.DataMedian WHERE @AggFunction = 2 UNION ALL SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3 ) SELECT ...
我關心的是查詢性能和行業最佳實踐。數據表的大小合理(2+ Gig)。我將不得不添加許多聚合查詢,其中一些是內聯表值函式,用於一些遺漏聚合。
在上面,查詢/表值函式是僅在
@AggFunction
匹配WHERE
條件時執行,還是在返回結果後都執行和過濾?如果是後者,是否有一種方法可以在執行時縮短對不需要的查詢的評估?另外,是否有一些標準方法可以在我忽略的 SQL 中執行此操作?
矛盾檢測可以啟動以確保只執行其中一個語句,在我的簡單測試中,只要有語句級重新編譯提示,它就會執行,但為什麼要冒險呢?例如:
USE tempdb GO -- CREATE SCHEMA agg --DROP TABLE agg.DataMin --DROP TABLE agg.DataMedian --DROP TABLE agg.DataWeightedAverage --GO CREATE TABLE agg.DataMin ( x INT PRIMARY KEY ) CREATE TABLE agg.DataMedian ( x INT PRIMARY KEY ) CREATE TABLE agg.DataWeightedAverage ( x INT PRIMARY KEY ) GO INSERT INTO agg.DataMin ( x ) SELECT object_id FROM sys.all_objects INSERT INTO agg.DataMedian ( x ) SELECT object_id FROM sys.all_objects WHERE type = 'P' INSERT INTO agg.DataWeightedAverage ( x ) SELECT object_id FROM sys.all_objects WHERE type = 'X' GO -- Are there some situations when it wouldn't... DECLARE @AggFunction INT = 1 ;WITH AggData AS ( SELECT * FROM agg.DataMin WHERE @AggFunction = 1 UNION ALL SELECT * FROM agg.DataMedian WHERE @AggFunction = 2 UNION ALL SELECT * FROM agg.DataWeightedAverage WHERE @AggFunction = 3 ) SELECT * FROM AggData OPTION ( RECOMPILE )
在這個簡單的例子中,重新編譯時只掃描了左側的一張表,右側掃描了3張表,沒有重新編譯。重新編譯提示允許優化器“查看”參數值並採取相應措施。在將使用參數嗅探的儲存過程中,還需要重新編譯以獲得相同的行為,無論是在語句級別還是在儲存過程級別。
但是我不能說是否沒有不會發生矛盾檢測的情況;你不能證明是否定的。換句話說,我無法證明即使重新編譯也總是會發生矛盾檢測。可能有一些未知的情況,即使重新編譯也不會發生;過度的複雜性浮現在腦海中。
此外,在您的範例中使用 CTE 並沒有真正的優勢,那麼為什麼不保持簡單呢?您可以只編寫一些簡單的過程 SQL
IF...THEN...ELSE
來保證只有一個語句會觸發,例如DECLARE @AggFunction INT = 99 IF @AggFunction = 1 SELECT * FROM agg.DataMin ELSE IF @AggFunction = 2 SELECT * FROM agg.DataMedian ELSE IF @AggFunction = 3 SELECT * FROM agg.DataWeightedAverage ELSE RAISERROR( 'Unknown value for parameter @AggFunction (%i).', 16, 1, @AggFunction )
在您使用時添加一些參數檢查。希望這能滿足您的要求,即保證在需要時只編譯一個語句,安全且易於實現。
高溫高壓