Postgresql

刪除重複記錄，兩者之間沒有變化

  • May 9, 2016

我有一個產品表，我每天在其中插入大約 150,000 條記錄。它們中的大多數都是多餘的，但由於新的到期日期，我需要保留它們。我每天從 30 家供應商中的大約 5 家獲得產品資訊。每個供應商都有大約 35,000 種獨特的產品。任何產品都不能屬於多個供應商。

創建表 vendor_prices (
id 序列主鍵，
供應商整數 NOT NULL，
sku 字元變化（25）不為空，
category_name 字元變化（100）NOT NULL，
價格數字（8,5）不為空，
沒有時區的有效日期時間戳，
expire_date 沒有時區的時間戳 DEFAULT (now() + '1 year'::interval)
);

我正在嘗試刪除沒有價格變化且不再是所述產品的最後更新的不相關記錄，例如：

生效日期價格
'2015-05-01' $1.99
'2015-05-02' $1.99*刪除*
'2015-05-03' $1.59
'2015-05-04' $1.99
'2015-05-05' $1.99*刪除*
'2015-05-06' $1.99*保留新的到期日期*

所以在每次載入之後（我認為一次對一個供應商來說會更容易）我想做某種刪除。這是我想出的長期表現不佳的解決方案。

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor integer)
 RETURNS integer AS
$BODY$
BEGIN
   -- Delete Redundant prices
   delete from vendor_prices
   where id in (
     select id from (
       select vp1.id, vp1.vendor, vp1.sku, vp1.price, vp1.effective_date, vp1.expiration_date
         from vendor_prices vp1 
         inner join (
             select vendor, sku, price from vendor_prices
               where vendor = _vendor
               group by vendor, sku, price 
         ) vp2
         on vp1.vendor = vp2.vendor and vp1.sku = vp2.sku and vp1.price = vp2.price
         where vp1.vendor = _vendor
     ) dupe

     -- fetch the irrelevant record
     WHERE (select a.effective_date from vendor_prices a
       where vendor = _vendor   
       and a.price = dupe.price and a.sku = dupe.sku and dupe.effective_date > a.effective_date

       -- but make sure there's no price change in-between(
       and (select b.effective_date from vendor_prices b 
         where vendor = _vendor     
         and b.sku = dupe.sku and b.effective_date < dupe.effective_date and b.effective_date > a.effective_date limit 1) IS NULL
         limit 1
     ) IS NOT NULL

     -- and that this is not the last update on said product, otherwise we'll keep it for expiration_date
     and ( select c.effective_date from vendor_prices c 
             where vendor = _vendor
             and c.sku = dupe.sku      
             and c.effective_date > dupe.effective_date limit 1
         ) IS NOT NULL
   );    
return 0;
END;
$BODY$
LANGUAGE plpgsql

這個函式執行了幾個小時，所以我把它殺死了。該表有大約 500 萬條記錄。我嘗試了各種不同的索引和組合索引，但似乎沒有任何幫助。在我執行此功能時，可能還有其他插入和刪除。

在 Solaris 11.2 上執行 PostgreSQL 9.3.4。

我有足夠的 RAM 和磁碟空間。

核心功能是視窗功能**lag()**。

還要特別注意避免並發刪除和插入的死鎖和競爭條件（這會影響要刪除的行！）：

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int)
 RETURNS integer AS
$func$
DECLARE
  del_ct int;
BEGIN
  -- this may or may not be necessary:
  -- lock rows to avoid race conditions with concurrent deletes
  PERFORM 1
  FROM   vendor_prices
  WHERE  vendor = _vendor
  ORDER  BY sku, effective_date, id  -- guarantee row locks in consistent order
  FOR    UPDATE;

  -- delete redundant prices
  DELETE FROM vendor_prices v
  USING (
     SELECT id
          , price = lag(price) OVER w  -- same as last row
            AND (lead(id) OVER w) IS NOT NULL AS del  -- not last row
     FROM   vendor_prices
     WHERE  vendor = _vendor
     WINDOW w AS (PARTITION BY sku ORDER BY effective_date, id)
     ) d
  WHERE v.id = d.id
  AND   d.del;

  GET DIAGNOSTICS del_ct = ROW_COUNT;  -- optional:
  RETURN del_ct;  -- return number of deleted rows
END
$func$  LANGUAGE plpgsql;

稱呼：

SELECT remove_vendor_price_dupes(1);

筆記

對於正在使用的任何主要版本，所有使用者都執行最新的可用次要版本。

  • 一個多列索引將(vendor, sku, effective_date, id)是完美的 - 以這個特定的順序。但是 Postgres 也可以相當有效地組合索引。將其他不相關的項添加為索引的最後一項以從中獲得僅索引掃描可能是值得的

。你必須進行測試。price

  • 由於您有並發刪除，因此最好為每個供應商執行單獨的刪除以減少競爭條件和死鎖的可能性。由於只有少數供應商，這似乎是一個合理的劃分。（許多微小的呼叫會比較慢。）
  • 我正在執行一個單獨的SELECT（PERFORM在 plpgsql 中，因為我們不使用結果），因為行鎖定子句FOR UPDATE不能與視窗函式一起使用。不要讓關鍵字誤導您，這不僅僅是為了更新。我鎖定了給定供應商的所有行，因為結果取決於所有行。並發讀取不會受到影響，只有並發寫入必須等到我們完成。這就是為什麼最好在單獨的事務中一次刪除一個供應商的行的另一個原因。
  • sku每個產品都是獨一無二的，所以我們可以PARTITION BY做到。
  • ORDER BY effective_date, id：您的第一個版本的問題包括重複行的程式碼，所以我添加了 idORDER BY作為額外的決勝局。這樣，它也適用於重複項(sku, effective_date)。
  • 保留每組的最後一行：AND (lead(id) OVER w) IS NOT NULL. 重用同一個視窗很lead()便宜——獨立於添加的顯式WINDOW子句——這只是為了方便起見的語法簡寫。
  • 我以相同的順序鎖定行：ORDER BY sku, effective_date, id. 確保並發 DELETE 以相同的順序操作以避免死鎖。如果所有其他事務在同一事務中刪除的行不超過一行，則不會出現死鎖，並且您根本不需要行鎖定。
  • 如果並發 INSERT 可能導致不同的結果（使不同的行過時），則必須將整個表鎖定為 EXCLUSIVE 模式以避免競爭條件：
LOCK TABLE vendor_prices IN EXCLUSIVE MODE;

只有在必要時才這樣做。它阻止所有並發寫訪問。

  • 我正在返回刪除的行數，但這完全是可選的。您不妨不返回任何內容並將函式聲明為RETURNS void.

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

相關問答

Postgresql

刪除大量行的最佳方法，知道欄位不刪除

  • December 15, 2021
檢視問答
Postgresql

從大表中刪除舊行，每個給定時間間隔保留一個

  • October 31, 2021
檢視問答
Postgresql

如何加快查詢時間序列中的最後一個值？

  • February 28, 2021
檢視問答
Postgresql

僅選擇表中的最後一行作為值列表

  • April 28, 2019
檢視問答
Postgresql

子查詢中的慢左連接橫向

  • February 27, 2019
檢視問答
Postgresql

存在子選擇與內部連接？

  • December 15, 2017
檢視問答