上個月的項目，如果不可能，本月的項目

所以標題不是很清楚，但我想不出如何更好地把它寫成更短的詞。

我的問題是：

我有一個用於網路抓取的數據庫，其中包含不同的文章及其價格。價格可能每個月都有變化。為了能夠有歷史，我創建了兩個表：

• items其中包含目前正在本月抓取的所有文章。該表包含每篇文章的所有資訊
• log它只有一個article-id,price和date

所以基本上在items每篇文章中都是獨一無二的，而一篇文章log可以有多次出現。

我現在想做什麼：我創建了一個視圖，它產生每篇文章items，除其他外，計算與上個月的價格差異（因為價格和日期items每個月都會更新）。

以下是我觀點的摘錄：

CREATE VIEW XLSX AS 
   SELECT DISTINCT ON(items.article)
          items.article,
          items.price as "price_new",
          ROUND(CAST(items.price - log.price as numeric), 2) as "price_difference",
          log.price as "price_old"
   FROM   items
   INNER JOIN log 
   ON     items.article = log.article
   WHERE  (items.date - log.date) < 25
   ORDER BY items.article, log.date 

我現在遇到的問題是視圖中目前沒有包含新文章。例如，在 7 月初我有 20 篇新文章，它們插入items到log. date = '2017-07-01'顯然它們不包含在視圖中，因為限制只包含log超過 25 天的文章（因為爬蟲可能每月定期執行多次，不止一次更新價格）。

我可以用CASE這裡來獲取超過 25 天的文章，或者如果沒有，獲取具有相同日期的文章？

用於LEFT JOIN LATERAL ... ON TRUE保留右表的所有行：

SELECT i.article
    , i.price AS price_new
    , ROUND((i.price - l.price)::numeric, 2) AS price_difference
    , l.price AS price_old
FROM   items i
LEFT   JOIN LATERAL (
  SELECT price, date
  FROM   log
  WHERE  article = i.article
  AND    date < (i.date - 25)  -- make expression sargable, plus fix off-by-1 error
  ORDER  BY date DESC          -- to make sure ...
  LIMIT  1                     -- ... we pick at most 1 row
  ) l ON TRUE
ORDER  BY i.article, l.date;

返回 中的所有行，如果在 中未找到早於 25 天的之前items，您將獲得 NULL 。price_difference``log

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

我添加ORDER BY ... LIMIT 1以確保我們只選擇一個最新條目。如果log可以信任表中的數據永遠不會返回超過一行，則可以跳過添加的行。

或者，如果查詢實際上不依賴於items.date，則取決於目前日期的單個子查詢使用DISTINCT ON可能更便宜（使用正確的索引）：

SELECT i.article
    , i.price AS price_new
    , ROUND((i.price - l.price)::numeric, 2) AS price_difference
    , l.price AS price_old
FROM   items i
LEFT   JOIN (
  SELECT DISTINCT ON (article)
         article, date, price
  FROM   log
  WHERE  date < (CURRENT_DATE - 25)
  ORDER  BY article, date DESC
  ) l USING (article)
ORDER  BY i.article, l.date;

關於DISTINCT ON：

• 在每個 GROUP BY 組中選擇第一行？

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