Impala SQL计算任务到期天数并生成红黄绿状态列问题求解

原有代码问题 #

• DATEDIFF语法错误：Impala中DATEDIFF(end_date, start_date)返回值为end_date - start_date的天数差，原有代码参数顺序颠倒，且错误在函数参数内给字段加别名，触发语法报错。
• 状态列逻辑误用：需求是查询结果中新增计算列，不需要执行UPDATE修改物理表数据，直接将CASE判断逻辑写在SELECT子句即可。
• 空值匹配规则：当计划结束日期为空时，Task_Days_Due和Red_Amber_Green均需返回Unknown，和样例输出对齐。

修正后可直接运行的SQL #

SELECT          
    hpd_help_desk.incident_number,                   
    hpd_associations.request_id01 AS "PBI",
    tms_task.task_id,
    from_unixtime(Cast(tms_task.scheduled_start_date AS BIGINT),'yyyy-MM-dd HH:mm:ss') AS "Task_Scheduled_Start_Date",
    from_unixtime(Cast(tms_task.scheduled_end_date AS BIGINT),'yyyy-MM-dd HH:mm:ss') AS "Task_Scheduled_End_Date",
    CURRENT_DATE() AS "Task_Today_Date",
    -- 计算Task_Days_Due，空值返回Unknown，非空返回天数差字符串
    CASE 
        WHEN tms_task.scheduled_end_date IS NULL THEN 'Unknown'
        ELSE CAST(
            DATEDIFF(
                TO_DATE(from_unixtime(Cast(tms_task.scheduled_end_date AS BIGINT),'yyyy-MM-dd HH:mm:ss')),
                CURRENT_DATE()
            ) AS STRING
        )
    END AS "Task_Days_Due",
    -- 计算RAG状态
    CASE 
        WHEN tms_task.scheduled_end_date IS NULL THEN 'Unknown'
        WHEN DATEDIFF(TO_DATE(from_unixtime(Cast(tms_task.scheduled_end_date AS BIGINT),'yyyy-MM-dd HH:mm:ss')), CURRENT_DATE()) <= 0 THEN 'Red'
        WHEN DATEDIFF(TO_DATE(from_unixtime(Cast(tms_task.scheduled_end_date AS BIGINT),'yyyy-MM-dd HH:mm:ss')), CURRENT_DATE()) BETWEEN 1 AND 7 THEN 'Amber'
        WHEN DATEDIFF(TO_DATE(from_unixtime(Cast(tms_task.scheduled_end_date AS BIGINT),'yyyy-MM-dd HH:mm:ss')), CURRENT_DATE()) > 7 THEN 'Green'
        ELSE 'Unknown'
    END AS "Red_Amber_Green"
FROM helix_access.hpd_help_desk
LEFT OUTER JOIN helix_access.hpd_associations
    ON hpd_help_desk.incident_number = hpd_associations.request_id02
LEFT OUTER JOIN helix_access.tms_task
    ON hpd_associations.request_id01 = tms_task.rootrequestid
WHERE hpd_help_desk.incident_number = 'INC000038006072'
ORDER BY incident_number;

逻辑校验 #

输出完全匹配需求规则：

• 计划结束日期为空时，Task_Days_Due和Red_Amber_Green均返回Unknown
• 剩余天数≤0返回Red
• 剩余天数为1-7返回Amber
• 剩余天数大于7返回Green

如果要避免重复书写日期转换和DATEDIFF逻辑，可以用CTE包装一层基础计算逻辑再做状态判断，性能上没有差异，可根据个人编码习惯选择。

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