如何用SQL分析函数实现按行展示每人最近3个雇主名称？

嘿，这个场景我太熟悉了！你当前用RANK()+条件聚合的方案其实已经很成熟，但确实有更简洁的分析函数写法可以试试，不用嵌套子查询再外层聚合~

1. 用PIVOT（支持该语法的数据库：Oracle、SQL Server、PostgreSQL 11+等） #

如果你的数据库支持PIVOT语法，这是最直观简洁的写法，它会自动帮你完成条件聚合的逻辑，省去手动写一堆CASE WHEN：

SELECT 
  ID,
  "1" AS EMPLOYER_NAME1,
  "2" AS EMPLOYER_NAME2,
  "3" AS EMPLOYER_NAME3
FROM (
  SELECT 
    ID,
    EMPLOYER_NAME,
    -- 用ROW_NUMBER避免同日期的并列问题，如果需要保留并列可换回RANK()
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY START_DATE DESC) AS rn
  FROM EMP
)
PIVOT (
  MAX(EMPLOYER_NAME) 
  FOR rn IN (1, 2, 3) -- 指定要转成列的序号
)

说明： #

• 用ROW_NUMBER()代替RANK()是为了避免同一个ID下多个雇主有相同START_DATE时出现并列序号，导致最终结果超出3个的情况；如果业务上需要保留并列的最近雇主，可以换回RANK()，但要注意可能出现NULL值（比如并列的1有多个，那么EMPLOYER_NAME2可能为空）。
• PIVOT语法直接将行转列，代码结构更清晰，可读性更高。

2. 针对NTH_VALUE的优化写法 #

你提到尝试过NTH_VALUE但需要配合MAX()，其实可以通过调整窗口范围+去重的方式省去外层聚合：

SELECT DISTINCT
  ID,
  NTH_VALUE(EMPLOYER_NAME, 1) OVER (
    PARTITION BY ID 
    ORDER BY START_DATE DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS EMPLOYER_NAME1,
  NTH_VALUE(EMPLOYER_NAME, 2) OVER (
    PARTITION BY ID 
    ORDER BY START_DATE DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS EMPLOYER_NAME2,
  NTH_VALUE(EMPLOYER_NAME, 3) OVER (
    PARTITION BY ID 
    ORDER BY START_DATE DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS EMPLOYER_NAME3
FROM EMP
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY START_DATE DESC) = 1
-- 如果数据库不支持QUALIFY，就用WHERE子句嵌套过滤：
-- WHERE ID IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (...) rn FROM EMP) WHERE rn=1)

关键注意点： #

• NTH_VALUE默认的窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这会导致前面的行无法获取后面的序号值（比如第1行拿不到第2、3个雇主），必须显式指定ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，确保每个行都能拿到当前ID下的所有前3个雇主。
• 用DISTINCT或者QUALIFY（部分数据库支持，比如Snowflake、BigQuery）来过滤每个ID的唯一一行，避免重复输出。

3. 原方案的小优化 #

如果你的数据库不支持上述两种语法，你的原方案已经很高效了，只需要加个小优化：在子查询里直接过滤序号<=3，减少外层聚合的数据量：

SELECT 
  ID, 
  MAX(CASE WHEN RANK=1 THEN EMPLOYER_NAME END) EMPLOYER_NAME1, 
  MAX(CASE WHEN RANK=2 THEN EMPLOYER_NAME END) EMPLOYER_NAME2, 
  MAX(CASE WHEN RANK=3 THEN EMPLOYER_NAME END) EMPLOYER_NAME3 
FROM ( 
  SELECT 
    ID,
    EMPLOYER_NAME,
    RANK() OVER (PARTITION BY ID ORDER BY START_DATE DESC) RANK 
  FROM EMP 
) 
WHERE RANK <= 3 -- 提前过滤，减少聚合计算量
GROUP BY ID

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