PostgreSQL中获取最新非当前推荐值，用ROW_NUMBER+JOIN是否最优？

JOIN + ROW_NUMBER 是不是最优方案？ #

在PostgreSQL里，用JOIN结合ROW_NUMBER()窗口函数来获取目标值，确实是这类"按分组取最新符合条件记录"场景的标准高效方案，几乎可以说是最优选择之一。

为什么这个方案可行？ #

• ROW_NUMBER()能按ticker分组，把历史记录按时间倒序排序，精准筛选出第一条和current_recommendation不等的记录，逻辑清晰，代码可读性强。
• 只要给recommendations_history表建对索引（比如ticker+历史时间字段的复合索引），PostgreSQL能直接利用索引完成排序和筛选，避免全表扫描，性能拉满。

替代方案：LATERAL JOIN + LIMIT 1 #

还有一种写法也常用，就是LATERAL JOIN配合LIMIT 1，性能和ROW_NUMBER方案差不多，写法更简洁：

SELECT 
  -- 保留原查询所有字段
  concat_ws(' ', z.ticker,
         CASE
           WHEN a.date_approved IS NOT NULL THEN TO_CHAR(a.date_approved,'YYMMDD')
           WHEN z.date_recommended IS NULL THEN '000000'
           ELSE TO_CHAR(z.date_recommended,'YYMMDD')
         END,
          CASE
            WHEN s.ticker IS NOT NULL THEN 6
            WHEN a.approved_recommendation IS NOT NULL THEN approved_recommendation
            ELSE current_recommendation
          END,
          REPLACE(CASE
                    WHEN comments IS NULL THEN 'N/C'
                    ELSE comments
                  END,' ','_')),
  -- 新增的最新历史推荐值
  rh.prev_recommendation
FROM recommendations z
LEFT JOIN (SELECT ticker FROM scr_tickers) s ON z.ticker = s.ticker
LEFT JOIN (SELECT ticker, date_approved, approved_recommendation FROM approved_recommendations) a ON z.ticker = a.ticker
-- 新增LATERAL JOIN获取目标值
LEFT JOIN LATERAL (
  SELECT recommendation AS prev_recommendation
  FROM recommendations_history rh
  WHERE rh.ticker = z.ticker
    AND rh.recommendation != z.current_recommendation
  ORDER BY rh.history_date DESC -- 假设历史表的时间字段是history_date，按最新排序
  LIMIT 1
) rh ON true
WHERE z.ticker NOT LIKE 'T.%'
  AND z.ticker NOT LIKE 'V.%'
ORDER BY z.ticker;

两种方案性能没本质区别，看个人习惯选：

• ROW_NUMBER方案扩展性更好，如果以后需要取多条符合条件的记录，改起来方便；
• LATERAL JOIN写法更紧凑，专门针对"只取第一条"的场景。

必做优化：加索引 #

不管用哪种方案，一定要给recommendations_history建复合索引：

CREATE INDEX idx_rh_ticker_date_rec ON recommendations_history(ticker, history_date DESC, recommendation);

这个索引能让数据库直接定位到每个ticker的最新符合条件记录，不用全表排序，速度会快很多。

完整ROW_NUMBER版本的修改后SQL #

SELECT concat_ws(' ', z.ticker,
       CASE
         WHEN a.date_approved IS NOT NULL THEN TO_CHAR(a.date_approved,'YYMMDD')
         WHEN z.date_recommended IS NULL THEN '000000'
         ELSE TO_CHAR(z.date_recommended,'YYMMDD')
       END,
        CASE
          WHEN s.ticker IS NOT NULL THEN 6
          WHEN a.approved_recommendation IS NOT NULL THEN approved_recommendation
          ELSE current_recommendation
        END,
        REPLACE(CASE
                  WHEN comments IS NULL THEN 'N/C'
                  ELSE comments
                END,' ','_'),
        -- 新增的最新历史推荐值
        rh.prev_recommendation)
FROM recommendations z
LEFT JOIN (SELECT ticker FROM scr_tickers) s
       ON z.ticker = s.ticker
LEFT JOIN (SELECT ticker, date_approved, approved_recommendation
           FROM approved_recommendations) a
       ON z.ticker = a.ticker
-- 用ROW_NUMBER筛选符合条件的最新历史记录
LEFT JOIN (
  SELECT 
    ticker,
    recommendation AS prev_recommendation,
    ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY history_date DESC) AS rn
  FROM recommendations_history rh
  JOIN recommendations r ON rh.ticker = r.ticker
  WHERE rh.recommendation != r.current_recommendation
) rh ON z.ticker = rh.ticker AND rh.rn = 1
WHERE z.ticker NOT LIKE 'T.%'
  AND z.ticker NOT LIKE 'V.%'
ORDER BY z.ticker;

内容的提问来源于stack exchange，提问作者Landon Statis