Hive SQL按日期获取指定应用最新记录问题求助

核心逻辑 #

要同时覆盖活跃应用（appl_support_end_date年份为9999）和非活跃应用（取最新结束日期的记录），用窗口函数row_number()是最优方案：

• 按应用名称分组，给每个应用的所有记录排序
• 活跃记录直接排最前面，非活跃记录按结束日期倒序排列
• 取每个分组里的第一条记录，就是该应用的最后一位可靠性工程师信息

优化后的SQL代码 #

SELECT 
    appl_name, 
    prod_problem_reliability_engineer, 
    appl_support_start_date, 
    appl_support_end_date
FROM (
    SELECT 
        *,
        -- 按应用分组排序：活跃记录优先级最高，非活跃按结束日期倒序
        row_number() OVER (
            PARTITION BY appl_name 
            ORDER BY 
                CASE WHEN year(appl_support_end_date) = 9999 THEN 0 ELSE 1 END,
                appl_support_end_date DESC
        ) AS rn
    FROM datamart_core.dm_appmaintenance_mapping
    -- 用IN简化指定应用的筛选，比一堆OR更清晰
    WHERE appl_name IN (
        'GALAXY-WEBMETHODS-PROD',
        'MD ENTERPRISE LIMS NA-PROD',
        'JJCC CCV2 NA - PROD',
        'MENTOR - MENTORDIRECT.COM PROD',
        'CONSUMER LATIN AMERICA-ECC-PROD-PLA'
    )
) t
-- 只保留每个应用的第一条记录
WHERE rn = 1;

代码说明 #

1. 内层子查询：

   • PARTITION BY appl_name：将数据按应用名称分组，每个应用单独处理
   • ORDER BY子句：
     • 第一个排序条件：用CASE把活跃记录标记为0，非活跃标记为1，0排前面，确保活跃记录优先被选中
     • 第二个排序条件：非活跃记录按appl_support_end_date倒序，取最新的那条
   • row_number()：给每个分组内的记录按排序结果分配序号，第一条为1

2. 外层查询：

   • 筛选出序号为1的记录，就是每个应用的目标记录

这样不仅能拿到原来4条活跃应用的结果，还能补上CONSUMER LATIN AMERICA-ECC-PROD-PLA这个非活跃应用的最新记录。

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