Netezza环境下合并多日期列取非空最小日期值实现咨询

解决方案 #

方案1：转置后聚合取最小值（推荐，列数较多的场景更易用） #

你可以先将多个日期列转成行，过滤空值后按表主键分组取最小值即可。假设你的表名为your_table，主键列是id，待比较的日期列为date1、date2、date3、date4，示例代码如下：

SELECT 
    id,
    MIN(dt) AS min_date
FROM (
    SELECT id, date1 AS dt FROM your_table
    UNION ALL
    SELECT id, date2 AS dt FROM your_table
    UNION ALL
    SELECT id, date3 AS dt FROM your_table
    UNION ALL
    SELECT id, date4 AS dt FROM your_table
) unpivoted_data
WHERE dt IS NOT NULL
GROUP BY id

如果你的Netezza版本支持UNNEST函数，可以写得更简洁：

SELECT 
    t.id,
    MIN(u.dt) AS min_date
FROM your_table t
CROSS JOIN UNNEST(ARRAY(t.date1, t.date2, t.date3, t.date4)) u(dt)
WHERE u.dt IS NOT NULL
GROUP BY t.id

方案2：CASE WHEN手动判断（列数较少的场景性能更优） #

如果待比较的日期列只有2-3列，可以直接写判断逻辑实现，示例代码（以3个日期列为例）：

SELECT
    date1,
    date2,
    date3,
    CASE
        WHEN date1 IS NULL AND date2 IS NULL AND date3 IS NULL THEN NULL
        WHEN date1 IS NULL AND date2 IS NULL THEN date3
        WHEN date1 IS NULL AND date3 IS NULL THEN date2
        WHEN date2 IS NULL AND date3 IS NULL THEN date1
        WHEN date1 IS NULL THEN CASE WHEN date2 < date3 THEN date2 ELSE date3 END
        WHEN date2 IS NULL THEN CASE WHEN date1 < date3 THEN date1 ELSE date3 END
        WHEN date3 IS NULL THEN CASE WHEN date1 < date2 THEN date1 ELSE date2 END
        ELSE CASE 
            WHEN date1 < date2 AND date1 < date3 THEN date1
            WHEN date2 < date1 AND date2 < date3 THEN date2
            ELSE date3 
        END
    END AS min_date
FROM your_table

内容的提问来源于stack exchange，提问作者Prajwal Mani Pradhan