如何有效地獲取“最近的對應行”？
我有一個查詢模式一定很常見，但我不知道如何為它編寫一個有效的查詢。我想查找與另一個表的行“最近的日期不是之後”相對應的表的行。
例如，我有一張表，
inventory它代表我在某一天持有的庫存。date | good | quantity ------------------------------ 2013-08-09 | egg | 5 2013-08-09 | pear | 7 2013-08-02 | egg | 1 2013-08-02 | pear | 2和一個表格，“價格”，它保存了某一天的商品價格
date | good | price -------------------------- 2013-08-07 | egg | 120 2013-08-06 | pear | 200 2013-08-01 | egg | 110 2013-07-30 | pear | 220如何有效地獲取庫存表每一行的“最新”價格，即
date | pricing date | good | quantity | price ---------------------------------------------------- 2013-08-09 | 2013-08-07 | egg | 5 | 120 2013-08-09 | 2013-08-06 | pear | 7 | 200 2013-08-02 | 2013-08-01 | egg | 1 | 110 2013-08-02 | 2013-07-30 | pear | 2 | 220我知道這樣做的一種方法：
select inventory.date, max(price.date) as pricing_date, good from inventory, price where inventory.date >= price.date and inventory.good = price.good group by inventory.date, good然後再次將此查詢加入庫存。對於大型表，即使進行第一次查詢（無需再次加入庫存）也非常慢。
max(price.date) ... where price.date <= date_of_interest ... order by price.date desc limit 1但是，如果我簡單地使用我的程式語言從庫存表中為每個查詢發出一個查詢，同樣的問題很快就解決date_of_interest了，所以我知道沒有計算障礙。但是，我更願意使用單個 SQL 查詢來解決整個問題，因為它允許我對查詢結果進行進一步的 SQL 處理。有沒有一種標準的方法可以有效地做到這一點？感覺它必須經常出現，並且應該有一種方法可以為其編寫快速查詢。
我正在使用 Postgres，但我們將不勝感激 SQL 通用的答案。
這在很大程度上取決於情況和確切的要求。考慮我的評論。
簡單的解決方案
在
DISTINCT ONPostgres 中：SELECT DISTINCT ON (i.good, i.the_date) i.the_date, p.the_date AS pricing_date, i.good, p.price FROM inventory i LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date ORDER BY i.good, i.the_date, p.the_date DESC;返回的行是有序的。看：
或者使用
NOT EXISTS標準 SQL（適用於我知道的每個 RDBMS）：SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price FROM inventory i LEFT JOIN price p ON p.good = i.good AND p.the_date <= i.the_date WHERE NOT EXISTS ( SELECT FROM price p1 WHERE p1.good = p.good AND p1.the_date <= i.the_date AND p1.the_date > p.the_date );結果相同，但具有任意排序順序 - 除非您添加
ORDER BY.根據數據分佈、確切要求和指標，其中任何一個都可能更快。看：
每個商品只有幾行，
DISTINCT ON通常更快，並且您會在其上獲得排序結果。但在某些情況下，其他查詢技術（更快）更快。見下文。具有計算最大值/最小值的子查詢的解決方案通常較慢。然而，具有 CTE 的變體通常較慢。（使用 Postgres 12 改進了 CTE。）
普通視圖（如另一個答案所建議的）對 Postgres 的性能毫無幫助。
db<>fiddle here
適當的解決方案
字元串和排序規則
首先，您的表格佈局是次優的。這可能看起來微不足道，但規範化您的模式可能會有很長的路要走。
按字元類型（
text,varchar, …）排序是根據 current 完成的COLLATION。通常，您的數據庫會使用一些本地規則集，例如在我的情況下：de_AT.UTF-8. 通過以下方式了解：SHOW lc_collate;這使得排序和索引查找變慢。您的字元串（商品名稱）越長越差。如果您實際上並不關心輸出中的排序規則（或排序順序），則使用以下命令可以更快
COLLATE "C"：SELECT DISTINCT ON (i.good **COLLATE "C"**, i.the_date) i.the_date, p.the_date AS pricing_date, i.good, p.price FROM inventory i LEFT JOIN price p ON i.good = p.good AND i.the_date >= p.the_date ORDER BY i.good **COLLATE "C"**, i.the_date, p.the_date DESC;請注意在兩個地方添加的排序規則。
在我的測試中，每行 20k 行和非常基本的名稱（‘good123’）的速度是原來的兩倍。
指數
如果您的查詢應該使用索引，則具有字元數據的列必須使用匹配的排序規則（
good在範例中）：CREATE INDEX inventory_good_date_desc_collate_c_idx ON price(good **COLLATE "C"**, the_date DESC);閱讀我上面連結的相關答案的最後兩章。
您甚至可以在同一列上有多個具有不同排序規則的索引 - 如果您還需要根據其他查詢中的另一個（或預設）排序規則對商品進行排序。
標準化
冗餘字元串（好的名稱）膨脹的表和索引，這使得一切都變慢了。適當的表格佈局可以避免大部分問題。可能看起來像這樣：
CREATE TABLE good ( good_id serial PRIMARY KEY , good text NOT NULL ); CREATE TABLE inventory ( good_id int REFERENCES good (good_id) , the_date date NOT NULL , quantity int NOT NULL , PRIMARY KEY(good_id, the_date) ); CREATE TABLE price ( good_id int REFERENCES good (good_id) , the_date date NOT NULL , price numeric NOT NULL , PRIMARY KEY(good_id, the_date));主鍵自動提供（幾乎）我們需要的所有索引。
根據缺少的詳細資訊，在第二列上按降序排列的多列索引可能會提高性能：
priceCREATE INDEX price_good_date_desc_idx ON price(good, the_date DESC);同樣，排序規則必須與您的查詢匹配（見上文）。
由於 Postgres 9.2 “覆蓋索引”用於僅索引掃描可以提供更多幫助 - 特別是如果表包含額外的列，使表比索引大得多。
這些生成的查詢要快得多：
DISTINCT ONSELECT DISTINCT ON (i.the_date) i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price FROM inventory i JOIN good g USING (good_id) LEFT JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date ORDER BY i.the_date, p.the_date DESC;
NOT EXISTSSELECT i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price FROM inventory i JOIN good g USING (good_id) LEFT JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date AND NOT EXISTS ( SELECT 1 FROM price p1 WHERE p1.good_id = p.good_id AND p1.the_date <= i.the_date AND p1.the_date > p.the_date );db<>fiddle here
更快的解決方案
如果這還不夠快，可能會有更快的解決方案。
Recursive CTE
JOIN LATERAL//相關子查詢特別是對於每件商品價格眾多的數據分佈：
物化視圖
如果您需要經常快速地執行它，我建議您創建一個物化視圖。我認為可以安全地假設過去日期的價格和庫存很少變化。計算一次結果並將快照儲存為物化視圖。
Postgres 9.3+ 自動支持物化視圖。您可以輕鬆地在舊版本中實現基本版本。
正如 Erwin 和其他人所指出的，一個有效的查詢依賴於很多變數，PostgreSQL 非常努力地根據這些變數優化查詢執行。通常，您希望先編寫清晰，然後在確定瓶頸後修改性能。
此外，PostgreSQL 有很多技巧可以用來提高效率（部分索引），因此根據您的讀/寫負載，您可以通過仔細研究索引來優化這一點。
嘗試的第一件事就是做一個視圖並加入它：
CREATE VIEW most_recent_rows AS SELECT good, max(date) as max_date FROM inventory GROUP BY good;這在執行以下操作時應該表現良好：
SELECT price FROM inventory i JOIN goods g ON i.goods = g.description JOIN most_recent_rows r ON i.goods = r.goods WHERE g.id = 123;然後你就可以加入了。該查詢最終將針對基礎表加入視圖，但假設您在 (date,good in that order ) 有一個唯一索引，那麼您應該一切順利（因為這將是一個簡單的記憶體查找）。這在查找幾行時效果很好，但如果您試圖消化數百萬種商品的價格，效率將非常低。
您可以做的第二件事是向庫存表添加 most_recent bool 列和
create unique index on inventory (good) where most_recent;然後，當插入商品的新行時，您可能希望使用觸發器將 most_recent 設置為 false。這增加了更多的複雜性和更多的錯誤機會，但它是有幫助的。
同樣，這在很大程度上取決於適當的索引是否到位。對於最近的日期查詢，您可能應該有一個日期索引，並且可能有一個以日期開頭並包括您的連接條件的多列索引。
在下面更新Per Erwin 的評論，看來我誤解了這一點。重新閱讀問題，我完全不確定要問什麼。我想在更新中提到我看到的潛在問題是什麼，以及為什麼這會讓人不清楚。
提供的數據庫設計沒有真正使用 IME 與 ERP 和會計系統。它可以在假設的完美定價模型中工作，其中在給定產品的給定日期銷售的所有東西都具有相同的價格。然而，這並非總是如此。甚至像貨幣兌換這樣的事情也不是這樣（儘管有些模型假裝它確實如此）。如果這是一個人為的例子，那就不清楚了。如果是一個真實的例子，那麼數據層面的設計存在更大的問題。我在這裡假設這是一個真實的例子。
您不能假設僅日期就指定了給定商品的價格。任何業務的價格都可以根據交易對手進行協商，有時甚至可以根據交易進行協商。出於這個原因，您確實應該將價格儲存在實際處理進出庫存的表（庫存表）中。在這種情況下，您的日期/貨物/價格表僅指定了一個基準價格，該基準價格可能會根據協商進行更改。在這種情況下，這個問題從一個報告問題變成了一個事務性問題，並且一次對每個表的一行進行操作。例如，您可以在給定日期查找給定產品的預設價格，如下所示：
SELECT price FROM prices p JOIN goods g ON p.good = g.good WHERE g.id = 123 AND p."date" >= '2013-03-01' ORDER BY p."date" ASC LIMIT 1;使用價格指數（好，日期），這將表現良好。
我這是一個人為的例子，也許更接近你正在研究的東西會有所幫助。