SQL技术求助：如何保留action_time_end为空的最后一行并移除其他同条件行

场景与示例数据 #

以下是能源供应商纠纷操作记录示例，其中：

• action_time_start：供应商1发送操作的时间
• action_time_end：供应商2回复的时间
• row_num仅为便于查看添加，原表无此字段

查询需求 #

• 结果需包含supplier_1_action_sent和action_time_start列
• 当action_time_end为NULL时，仅保留每个dispute_id对应的最后一行
• 移除action_time_end为NULL的行中的supplier_2_action_response列

核心规则 #

对每个dispute_id：

1. 保留所有action_time_end不为NULL的行
2. 若存在action_time_end为NULL的行，仅保留其中时间最晚的一行；若该dispute_id的最后一行（时间最晚）action_time_end不为NULL，则移除所有action_time_end为NULL的行

尝试过的无效方案 #

• 使用MAX(COALESCE(TO_DATE(action_time_end), DATE '9999-01-01'))过滤action_time_start < action_time_end且action_time_end != '9999-01-01'的记录
• 添加行号并过滤WHERE row_num = 1 and action_time_end is not null
• 在WHERE子句中使用复杂CASE WHEN逻辑

解决方案 #

使用窗口函数标记每行的排序位置及最后一行的状态，再通过过滤条件实现需求：

WITH dispute_rows AS (
    SELECT
        dispute_id,
        supplier_1_action_sent,
        supplier_2_action_response,
        action_time_start,
        action_time_end,
        -- 按纠纷ID分组，按操作时间倒序生成行号，行号1为最新记录
        ROW_NUMBER() OVER (PARTITION BY dispute_id ORDER BY action_time_start DESC) AS rn,
        -- 标记当前行是否为该纠纷的最后一行
        CASE WHEN ROW_NUMBER() OVER (PARTITION BY dispute_id ORDER BY action_time_start DESC) = 1 THEN 1 ELSE 0 END AS is_last_row,
        -- 标记该纠纷的最后一行是否有供应商2的回复
        MAX(CASE WHEN ROW_NUMBER() OVER (PARTITION BY dispute_id ORDER BY action_time_start DESC) = 1 THEN action_time_end IS NOT NULL END) OVER (PARTITION BY dispute_id) AS last_has_response
    FROM your_table_name -- 替换为实际表名
)
SELECT
    dispute_id,
    supplier_1_action_sent,
    -- 仅当有回复时显示供应商2的响应内容
    CASE WHEN action_time_end IS NOT NULL THEN supplier_2_action_response END AS supplier_2_action_response,
    action_time_start,
    action_time_end
FROM dispute_rows
WHERE
    -- 保留所有有供应商2回复的行
    action_time_end IS NOT NULL
    -- 仅保留无回复行中的最新行，且该纠纷的最新行本身无回复
    OR (action_time_end IS NULL AND is_last_row = 1 AND last_has_response = FALSE)
ORDER BY dispute_id, action_time_start DESC;

方案说明 #

1. CTE阶段：通过窗口函数为每个纠纷的记录按时间倒序排序，标记出最新行，并判断最新行是否有回复
2. 过滤阶段：
   • 直接保留所有有供应商2回复的记录
   • 仅在纠纷的最新行无回复时，保留该最新的无回复行，其余无回复行全部过滤
3. 字段处理：通过CASE语句隐藏无回复行的supplier_2_action_response列内容

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