在子表中查找帶有 WHERE 條件的頂級記錄的方法

設置

最近，我對返回患者最近使用的處方產品的 SQL 查詢進行了優化。原始查詢通過 3 個嵌套CTE的 ’s 處理此問題，並且在遷移到新伺服器後性能非常差。我們沒有嘗試使原始版本與伺服器調整或索引更改一起工作，而是使用ROW_NUMBER()分區修改了查詢。這使最終輸出中大約 215,000 行的查詢執行時間從大約 10 分鐘縮短到大約 6 秒。

問題

我正在尋找是否有比我重寫它的方式更好的方法，因為在我看來，這幾乎像是一個 hack。我只是想看看這是否僅僅因為我們的數據集的大小而運作良好，或者這是否真的是一個很好的解決方案。我假設我們寧願使用某種類型的解決方案，該解決方案利用幾個不同JOIN的 s 來找到這種資訊，而不是使用ROW_NUMBER()and a CTE。

額外細節

為了這個問題，我將在我們的數據庫中省略某些不太理想的細節（複合主鍵，使用VarChar(1)而不是 aBIT等），以便更好地解決問題的根源。

我們在範圍內有 4 個表：

• Patient- 我們只打算從這個表中返回標識列
• OrderHeader- 保存有關包裹的資訊，例如它要去的地址。並且是 的父記錄OrderDetail。Patient該表通過列綁定PatientID。
• OrderDetail- 建立關係，OrderHeader並Product指明哪些產品在發貨時放入實際包裝盒中
• Product- 持有產品清單

我們需要一個查詢，它為每個患者返回頂部OrderDetail記錄，頂部OrderHeader記錄，其中Product記錄與記錄綁定OrderDetail= Product.IsRx1。

不幸的是，我們不能只獲得where = 1的MAX值，因為它可能不屬於. 我們也不能依賴value ，因為它不能保證有記錄，也不能保證那裡的任何記錄都有 a = 1OrderDetail.ID``Product.IsRx``OrderDetail.ID``MAX``OrderHeader.ID``MAX``OrderHeader.ID``OrderDetail``Product.IsRx

我通過以下查詢解決了這個問題：

WITH CTE
(
   PatientID,
   OrderHeaderID,
   OrderDetaillID,
   ProductNDCCode,
   ProductNDCDescription,
   RowNumber
)
AS
(
   SELECT P.ID AS 'PatientID',
   H.ID AS 'OrderHeaderID',
   D.ID AS 'OrderDetaillID',
   P.NDCCode AS 'ProductNDCCode',
   P.NDCDescription AS 'ProductNDCDescription',
   ROW_NUMBER() 
   OVER
   (
   PARTITION BY P.ID
   ORDER BY 
       P.ID ASC, --This part in the ORDER BY may not be needed, I apologize if it is unnecessary
       SH.OrderHdrID DESC, 
       SD.OrderDtlID DESC
   ) AS 'RowNumber'
   FROM Patient P
       INNER JOIN OrderHeader H
           ON P.ID = H.PatientID
       INNER JOIN OrderDetail D
           ON H.ID = D.OrderHeaderID
       INNER JOIN Product PR
           ON PR.ID = D.ProductID
   WHERE PR.IsRx = 1
)
SELECT PatientID,
OrderHeaderID,
OrderDetaillID,
ProductNDCCode,
ProductNDCDescription
FROM CTE
WHERE RowNumber = 1

我可以獲得的指導或專業知識將非常有幫助。

我不會認為這是黑客行為。包括 ROW_NUMBER 在內的視窗函式相當有效，並且通常執行等於或優於替代方法。在 SQL Server 的新實例上，我假設 FIRST_VALUE 的性能會更高，但尚未具體確認這一點。

注意：我已經看到 LAST_VALUE 產生不正確的結果，建議不要使用它，而是選擇使用 FIRST_VALUE 和相反的排序。

但是，請注意。如果您的訂購標準不夠具體，無法生成唯一的“最佳”記錄（例如，假設您省略了 OrderDtlID，則該訂單的任何詳細記錄都可能是第一個），那麼選擇的商品在功能上是隨機的（或者，更準確地說，基於在許多情況下您既不容易發現也無法控制的隱藏變數）。如果您從 ROW_NUMBER 更改為 RANK 並且您的隨機性發生了變化（您開始獲得不同數量的結果），這是一個很好的問題指標。同樣值得指出的是，這可能是其他選擇最新/最佳記錄的方法的問題，而不僅僅是 ROW_NUMBER。

即使您對選擇哪個不關心，這樣的查詢的結果也可能無法重複，儘管您可能不會立即看到它（隱藏變數在每次查詢後通常不會改變）。它可能會在以後導致故障排除問題，並且如果您執行任何類型的並行環境 (dev/qa)，則無法在兩者之間進行準確比較。

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