将Scala窗口函数代码转换为Hive SQL CTE表的技术求助

原Scala代码功能：筛选出评级机构为指定三家、评级对象类型为公司的数据，按rating_agency和fininstid分区，以dt降序排名后取每个分组的第一条记录，最终移除排名列。

原Scala代码 #

val window1 = Window.partitionBy("rating_agency","fininstid").orderBy(desc("dt"))

val table1= table2.where(
    col("rating_agency").isInCollection(List("Moody's", "Standard & Poor's", "Fitch Ratings"))
    and col("rating_object_type")==="Company"
).withColumn("rn", rank().over(window1)).where(col("rn") === 1).drop("rn")

你尝试的Hive SQL代码存在两处问题 #

1. 外层SELECT a.*后多了一个逗号，会触发语法错误
2. 缺少CTE完整定义的前缀WITH（单独定义CTE时必须添加）

修正后的Hive SQL CTE版本 #

WITH table1 AS (
    SELECT a.*
    FROM
        (SELECT m.*,
                RANK() OVER (PARTITION BY rating_agency, fininstid ORDER BY dt DESC) AS rn
        FROM table2 m
        WHERE rating_agency IN ('Moody''s', 'Standard & Poor''s', 'Fitch Ratings') 
          AND rating_object_type = 'Company'
        ) a
    WHERE a.rn = 1
)
-- 后续可直接使用table1，例如：
-- SELECT * FROM table1;

额外说明：如果dt字段存在重复值，RANK()会返回相同排名，可能导致一个分组返回多条记录。若需要严格取唯一一条，可根据实际需求调整：

• 用ROW_NUMBER()替代RANK()：确保每个分组仅返回一条，即使dt重复
• 扩展ORDER BY字段：比如ORDER BY dt DESC, fininstid，给重复dt的情况增加排序依据

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