Sql-Server

從一組值中選擇最非預設值

  • September 14, 2017

給定下表：

CREATE TABLE FeeTestClient (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name VARCHAR(16))
INSERT INTO FeeTestClient (Name)
VALUES ('Test'), ('Test 2'), ('Test 3')

CREATE TABLE FeeTest (FeeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16))
INSERT INTO FeeTest (ClientId, Fee, Val, Val2)
VALUES (1, 15, 'Default', 'Default'),
      (1, 10, 'Default', 'asdf'),
      (2, 15, 'Default', 'Default'),
      (2, 20, 'Default', 'qwer'),
      (2, 10, 'zxcv', 'asdf'),
      (3, 20, 'Default', 'Default')

我的目標是選擇所有FeeTestClient元素，並選擇最不預設的費用。預設費用的規則非常簡單：如果Val2是'Default'，則Val不能是除 以外 'Default'的任何費用，並且對於每個費用，我們想要第一個Val不是'Default'的，或者第一個Val2不是的'Default'，否則我們保證匹配Val = 'Default' AND Val2 = 'Default'。

客戶將永遠只擁有一件匹配'Default'/'Default'的商品、一件匹配的商品'Default'/____和一件匹配的商品____/____。（儘管最後兩行可能不存在。）如果他們有一個，____/____那麼他們將永遠有一個'Default'/____，每個客戶都會有一個'Default'/'Default'。他們永遠不能有一個____/'Default'——這是應用程序上的一個無效狀態，他們永遠不能有多個相同的狀態x/y，這是由UNIQUE對錶的約束來強制執行的。

客戶可能（在數據庫中）擁有 a和a'Default'/a，'Default'/b但這在應用程序中被視為無效狀態，並且對此進行了測試。（使用者必須刪除兩者之一。）

這類似於我的上一個問題（選擇所有記錄，如果存在連接，則與表 A 連接，如果不存在則連接表 B），但不那麼令人愉快。因為它們INT（實際上FLOAT在數據庫中，但同樣的問題適用）它們被聚合在一起，就像我不想要的那樣。

我想得到以下結果：

Id  Name    (No column name)
1   Test    10
2   Test 2  10
3   Test 3  20

我試過了：

SELECT Id, Name, COALESCE(f1.Fee, f2.Fee, f3.Fee)
FROM FeeTestClient
LEFT OUTER JOIN FeeTest AS f1 ON f1.ClientId = Id AND f1.Val <> 'Default' AND f1.Val2 <> 'Default'
LEFT OUTER JOIN FeeTest AS f2 ON f2.ClientId = Id AND f2.Val = 'Default' AND f2.Val2 <> 'Default'
LEFT OUTER JOIN FeeTest AS f3 ON f3.ClientId = Id AND f3.Val = 'Default' AND f3.Val2 = 'Default'

這在實時數據集上非常慢，但返回了正確的結果（大約 15 秒，在數據上執行基本選擇，沒有這個連接選擇，是 7，計劃在這裡），我也試過（由 Joe Obbish 的建議）：

SELECT Id, Name, MAX(COALESCE(CASE WHEN Val <> 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 = 'Default' THEN Fee END))
FROM FeeTestClient
LEFT OUTER JOIN FeeTest ON FeeTest.ClientId = Id
GROUP BY Id, Name

這同樣慢（計劃在這裡），並產生錯誤的輸出（儘管如果你COALESCE是MAXs 那麼它可以正常工作）。（也許更糟。）

我不知所措，編寫這些查詢非常痛苦，因此對建構所需輸出的任何建議表示讚賞。

我已經提供了實際的計劃，但它們與測試計劃的差異很大（這個 MCVE 是為了展示我想要的結果，答案不是強制性的，甚至沒有期望做出任何性能聲明），看起來。請忽略上面計劃中顯示的額外連接 - 它們對範例不重要。

就分佈而言，在實時數據集中，1.5%的人只有一個'Default'/'Default'，44.9% 的人有'Default'/___和'Default'/'Default'，53.6% 的人三者都有。

我不確定您的 MCVE 是否完全代表您遇到的問題，但我會按給定的方式回答問題。這個問題是關於性能的，所以表中只有幾行不會削減它。我將您的樣本數據複製了一百萬次，總共 600 萬行FeeTest和 300 萬行FeeTestClient。在下面執行此操作的程式碼：

DROP TABLE IF EXISTS FeeTestClient;

CREATE TABLE FeeTestClient (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [Name] VARCHAR(16));

INSERT INTO FeeTestClient WITH (TABLOCK)
([Name])
SELECT 'ZZZZZZZ' + CAST(RN AS VARCHAR(7))
FROM
(
   SELECT TOP (3000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
   FROM master..spt_values t1
   CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS FeeTest_source;

CREATE TABLE FeeTest_source (ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16));
INSERT INTO FeeTest_source (ClientId, Fee, Val, Val2)
VALUES (1, 15, 'Default', 'Default'),
      (1, 10, 'Default', 'asdf'),
      (2, 15, 'Default', 'Default'),
      (2, 20, 'Default', 'qwer'),
      (2, 10, 'zxcv', 'asdf'),
      (3, 20, 'Default', 'Default');


SELECT 3 * client.Id - 2 + client.Id % 3 AS ClientId
, src.Fee
, src.Val
, src.Val2 into #t
FROM FeeTest_source src
INNER JOIN FeeTestClient client ON src.ClientId = 1 + client.Id % 3;    


DROP TABLE IF EXISTS FeeTest;

CREATE TABLE FeeTest (FeeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16));

INSERT INTO FeeTest WITH (TABLOCK)
(ClientId, Fee, Val, Val2)
SELECT * FROM #t;

DROP TABLE #t;

GROUP BY根據表中數​​據的性質和針對錶定義的索引，使用並僅保留相關聚合可能是一種好方法。下面的查詢在我的機器上在 2 秒內完成：

SELECT Id, Name, MAX(COALESCE(CASE WHEN Val <> 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 = 'Default' THEN Fee END))
FROM FeeTestClient
LEFT OUTER JOIN FeeTest ON FeeTest.ClientId = Id
GROUP BY Id, Name

計劃一

計劃如我所料。最後有一個雜湊連接和一個雜湊聚合。

但是，您還有其他選擇，因為您有一張FeeTestClient桌子。另一種策略是使用OUTER APPLY. 一種方法如下：

SELECT Id, [Name], oa.Fee
FROM FeeTestClient ftc
OUTER APPLY (
   SELECT TOP 1 ft.Fee
   FROM FeeTest ft
   WHERE ft.ClientId = ftc.Id
   ORDER BY
       CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END
       + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END
   DESC, ft.ClientId
) oa;

有了APPLY並且TOP您幾乎總是希望在內表上有一個好的索引。查詢優化器通過 spool 為我們建立一個臨時索引，查詢需要 14 秒才能在我的機器上執行：

查詢 2

我們正在搜尋，ClientId所以讓我們嘗試一個索引：

CREATE INDEX NOT_COVERING ON FeeTest (ClientId);

索引沒有覆蓋，所以優化器不想使用它。使用它需要大量的鍵查找來獲取不在索引上的列。我可以強制索引與您不應該在生產中使用的惰性、未記錄的技巧一起使用：

SELECT Id, [Name], oa.Fee
FROM FeeTestClient ftc
OUTER APPLY (
   SELECT TOP 1 ft.Fee
   FROM FeeTest ft
   WHERE ft.ClientId = ftc.Id
   ORDER BY
       CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END
       + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END
   DESC, ft.ClientId
) oa
OPTION (QueryRuleOff BuildSpool);

現在查詢在 5 秒內執行。我們可以看到計劃中的關鍵查找：

查詢 3

我們最後一次嘗試使用覆蓋索引：

CREATE INDEX COVERING ON FeeTest (ClientId) INCLUDE (Val, Val2, Fee);

之前的查詢現在在四秒內執行：

查詢 4

您如何將其應用於您的巨大匿名查詢？您有很多鍵查找和索引假離線。嘗試定義覆蓋索引，以便您可以更有效地訪問所需的數據。我無法對整體執行時間做出任何保證，但它應該在某種程度上有所幫助。請注意，我沒有時間仔細查看您發布的計劃。

萬一有人在家裡跟著，有一個奇怪的邊緣情況會導致覆蓋索引仍然不被使用。這是解決它的一種方法：

ALTER TABLE FeeTest
ADD MAGIC_COLUMN AS CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END;

CREATE INDEX COVERING_2 ON FeeTest (ClientId) INCLUDE (MAGIC_COLUMN, Fee);

給定一個完全相同的索引（注意Fee是唯一包含的列）：

CREATE INDEX [IX dbo.FeeTest ClientId, Val, Val2 (Fee)]
ON dbo.FeeTest 
(
   ClientId, 
   Val, 
   Val2
) 
INCLUDE 
(
   Fee
);

兩種選擇：

1.交叉申請

這使用了內部循環連接，減少了比較次數，並避免了排序：

SELECT
   FTC.Id,
   FTC.[Name],
   Fee = COALESCE(CA.F1, CA.F2, CA.F3)
FROM dbo.FeeTestClient AS FTC
CROSS APPLY 
(
   SELECT
       F1 = MAX(CASE WHEN FT.Val <> 'Default' AND FT.Val2 <> 'Default' THEN FT.Fee END),
       F2 = MAX(CASE WHEN FT.Val2 <> 'Default' THEN FT.Fee END),
       F3 = MAX(CASE WHEN FT.Val2 = 'Default' THEN FT.Fee END)
   FROM dbo.FeeTest AS FT
       WITH (FORCESEEK)
   WHERE
       FT.ClientId = FTC.Id
) AS CA;

選項 1 計劃

2.嵌套外應用

這使用了一系列外部應用操作，如果先前的應用沒有找到更高優先級的匹配行，則後面的操作僅執行其關聯的索引查找。

SELECT
   FTC.Id,
   FTC.[Name],
   Fee = COALESCE(F1.Fee, F2.Fee, F3.Fee)
FROM dbo.FeeTestClient AS FTC
OUTER APPLY
(
   SELECT TOP (1)
       FT.Fee 
   FROM dbo.FeeTest AS FT
       WITH (FORCESEEK)
   WHERE
       FT.ClientId = FTC.Id
       AND FT.Val <> 'Default'
       AND FT.Val2 <> 'Default'
) AS F1
OUTER APPLY
(
   -- Only if the F1 check found nothing
   SELECT TOP (1)
       FT.Fee 
   FROM dbo.FeeTest AS FT
       WITH (FORCESEEK)
   WHERE
       FT.ClientId = FTC.Id
       AND FT.Val = 'Default'
       AND FT.Val2 <> 'Default'
       AND F1.Fee IS NULL
) AS F2
OUTER APPLY
(
   -- Only if the F2 check found nothing
   SELECT TOP (1)
       FT.Fee 
   FROM dbo.FeeTest AS FT
       WITH (FORCESEEK)
   WHERE
       FT.ClientId = FTC.Id
       AND FT.Val = 'Default'
       AND FT.Val2 = 'Default'
       AND F2.Fee IS NULL
) AS F3;

選項 2 計劃

現在，這些都不會對您的實際查詢產生太大影響，但它們是您嘗試的替代方案。如果除了索引更改之外沒有什麼能真正改變性能，那麼實際查詢的問題可能只是在其他地方。

引用自：https://dba.stackexchange.com/questions/185898

相關問答

Sql-Server

在 SQL Server 中忽略沒有任何結果的連接

  • April 21, 2021
檢視問答
Sql-Server

為什麼使用外連接選擇 rowversion 永遠不會返回 null

  • October 13, 2019
檢視問答
Sql-Server

將子查詢重構為 JOIN 和 CROSS APPLY，父表中的每條記錄只獲取一行

  • March 26, 2019
檢視問答
Sql-Server

使用 SQL Server 中另一個表中的值更新表

  • February 17, 2019
檢視問答
Sql-Server

比較兩個表中的字元串 SQL Server 2014

  • February 5, 2019
檢視問答
Sql-Server

如何獲得與一年多相關的文件大小總和的綜合 ServiceID

  • August 21, 2018
檢視問答