如何为每个公司ID筛选最新财务记录？解决同行数据不同步问题

当前SQL的问题在于取了全局最大的PRD_END_DT，仅返回在该日期有数据的公司，忽略了每个公司自身的最新记录日期。要实现目标公司+10家同行各取最新一行记录，可采用以下两种方案：

方法一：窗口函数ROW_NUMBER()（推荐） #

通过窗口函数按公司ID分组，对每组内的记录按PRD_END_DT降序排序，取排序为1的行（即该公司最新的一条记录），同时结合你的peers_list过滤目标公司和同行：

WITH ranked_financials AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY COMPANY_ID ORDER BY PRD_END_DT DESC) AS rn
    FROM Financials_table
    WHERE COMPANY_ID IN ('目标公司ID', '同行1ID', '同行2ID', ...) -- 替换为你的peers_list内容
)
SELECT * 
FROM ranked_financials
WHERE rn = 1

在Spark中结合peers_list变量使用的写法：

# 假设peers_list是包含目标公司+10家同行ID的列表
peers_str = "('" + "','".join(peers_list) + "')"
sql_query = f"""
WITH ranked_financials AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY COMPANY_ID ORDER BY PRD_END_DT DESC) AS rn
    FROM Financials_table
    WHERE COMPANY_ID IN {peers_str}
)
SELECT * 
FROM ranked_financials
WHERE rn = 1
"""
peers_df = spark.sql(sql_query)
peers_df.createOrReplaceTempView('peers_df')
print(shape('peers_df'))
head('peers_df', 50)

方法二：分组子查询关联 #

先按公司ID分组获取每个公司的最新PRD_END_DT，再和原表关联获取完整记录：

SELECT f.*
FROM Financials_table f
JOIN (
    SELECT COMPANY_ID, MAX(PRD_END_DT) AS latest_dt
    FROM Financials_table
    WHERE COMPANY_ID IN {peers_str}
    GROUP BY COMPANY_ID
) latest ON f.COMPANY_ID = latest.COMPANY_ID AND f.PRD_END_DT = latest.latest_dt

两种方法均可保证每个公司仅返回最新的一行记录，最终结果会是11行（目标公司+10家同行各一行）。

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