高效部分 DISTINCT ON

我想獲得最有效的查詢，為我提供在一個欄位（ID）上不同但在另一個欄位（有效性）上相同的所有行。

讓我們舉一個具體的例子：你有一個藥物表，每個藥物都有一個 ID、一些值和一個有效日期。當然，在理想世界中，每個 ID 都是單獨的藥物；不幸的是，實際上情況並非如此，因為您可能有同一種藥物的兩種變體，具有相同的 ID、表格中間的一些不同值和相同的有效期。在這個真實世界的範例中，您想要檢索藥物“仍然有效”的所有行（對於仍然的任何定義，超出了我的問題範圍）。

考慮下表：

# ID # VALUE # VALIDITY
# 1  # foo   # 2018/01/10
# 2  # bar   # 2018/01/03
# 2  # flo   # 2018/01/10
# 2  # duv   # 2018/01/10
# 3  # pas   # 2018/01/10
# 4  # cip   # 2018/01/08
# 4  # mao   # 2018/01/10

我希望查詢返回以下行：

# ID # VALUE # VALIDITY
# 1  # foo   # 2018/01/10
# 2  # flo   # 2018/01/10
# 2  # duv   # 2018/01/10
# 3  # pas   # 2018/01/10
# 4  # mao   # 2018/01/10

請注意， (2, bar) 和 (4, cip) 都分別被 (2, flo; 2, duv; 4, cip) “過時”。

您可以想像這是一個相當簡單的練習，您可以使用 INNER JOIN 來解決：

SELECT * FROM TABLE T
INNER JOIN (SELECT ID, MAX(VALIDITY) FROM T GROUP BY ID) AS TT
ON T.ID = TT.ID AND T.VALIDITY = TT.VALIDITY

但是，我注意到這個查詢變得非常昂貴（基本上有兩個完整掃描，加上一個額外的 group by。我不確定是否有更便宜的方法來做到這一點（我嘗試使用 DISTINCT ON 但這會殺死這兩個ID = 2) 的條目。可能還推薦將有助於改進此案例的索引。

太感謝了！

同樣的結果也是 ypercube，但我發現一個簡單的最大視窗函式比密集秩等更容易理解。

select mm.id, mm.value, mm.validity from (
  select m.*, max(m.validity) over (partition by m.id) maxval
     from meds m ) mm
  where mm.validity = mm.maxval;

有關結果，請參見http://sqlfiddle.com/#!17/e478c/4。

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