如何在T-SQL中结合NULL值使用LEAD()函数实现消息日期统计

背景 #

部分代理会发送或不发送不同类型的消息，消息记录日期会被留存。有时多个代理会参与同一事件（对应表中的mess_id）。

需求 #

需按消息类型生成对应列，获取每个消息的最小日期（若代理发送过该消息），同时处理未发送消息的场景。

现有数据表 #

预期结果表 #

尝试的代码 #

WITH first_step AS
(
    SELECT 
        *,
        CONCAT(mess_id, '-', type_message) AS mess_type_id,
        ROW_NUMBER() OVER (PARTITION BY type_message ORDER BY mess_id) AS rn_message
    FROM 
        mytable
),
second_step AS
(
    SELECT 
        *,
        MIN(dt_message) OVER (PARTITION BY mess_type_id ORDER BY mess_type_id) AS min_dt_mess
    FROM 
        first_step
)
SELECT 
    *,
    FIRST_VALUE(min_dt_mess) OVER (PARTITION BY mess_id ORDER BY message_order) AS enter,
    MIN(min_dt_mess) OVER (PARTITION BY mess_id ORDER BY message_order ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) mess_played,
    FIRST_VALUE(min_dt_mess) OVER (PARTITION BY mess_id ORDER BY message_order desc) AS leave
FROM 
    second_step
ORDER BY 
    categ_agent, message_order

解决方案 #

核心思路是先按mess_id分组统计每种消息类型的最小日期，再关联原表并格式化日期输出：

WITH message_dates AS (
    SELECT 
        mess_id,
        MIN(CASE WHEN type_message = 'enter' THEN dt_message END) AS enter,
        MIN(CASE WHEN type_message = 'mess1_to_give' THEN dt_message END) AS mess1_to_give,
        MIN(CASE WHEN type_message = 'mess1_given' THEN dt_message END) AS mess1_given,
        MIN(CASE WHEN type_message = 'mess2_to_give' THEN dt_message END) AS mess2_to_give,
        MIN(CASE WHEN type_message = 'mess2_given' THEN dt_message END) AS mess2_given,
        MIN(CASE WHEN type_message = 'mess_played' THEN dt_message END) AS mess_played,
        MIN(CASE WHEN type_message = 'leave' THEN dt_message END) AS leave
    FROM mytable
    GROUP BY mess_id
)
SELECT 
    t.mess_id,
    t.alltripid,
    t.categ_agent,
    t.message_order,
    t.type_message,
    CASE WHEN t.dt_message IS NOT NULL THEN TO_CHAR(t.dt_message, 'DD/MM/YYYY HH24:MI') END AS dt_message,
    CASE WHEN md.enter IS NOT NULL THEN TO_CHAR(md.enter, 'DD/MM/YYYY HH24:MI') END AS enter,
    CASE WHEN md.mess1_to_give IS NOT NULL THEN TO_CHAR(md.mess1_to_give, 'DD/MM/YYYY HH24:MI') END AS mess1_to_give,
    CASE WHEN md.mess1_given IS NOT NULL THEN TO_CHAR(md.mess1_given, 'DD/MM/YYYY HH24:MI') END AS mess1_given,
    CASE WHEN md.mess2_to_give IS NOT NULL THEN TO_CHAR(md.mess2_to_give, 'DD/MM/YYYY HH24:MI') END AS mess2_to_give,
    CASE WHEN md.mess2_given IS NOT NULL THEN TO_CHAR(md.mess2_given, 'DD/MM/YYYY HH24:MI') END AS mess2_given,
    CASE WHEN md.mess_played IS NOT NULL THEN TO_CHAR(md.mess_played, 'DD/MM/YYYY HH24:MI') END AS mess_played,
    CASE WHEN md.leave IS NOT NULL THEN TO_CHAR(md.leave, 'DD/MM/YYYY HH24:MI') END AS leave
FROM mytable t
JOIN message_dates md ON t.mess_id = md.mess_id
ORDER BY t.categ_agent, t.message_order;

关键说明 #

1. message_dates CTE：按事件mess_id分组，用CASE+MIN()组合提取每种消息类型的最小日期，未发送的消息自动返回NULL，符合需求场景。
2. 关联原表：通过mess_id将原表与统计结果关联，确保每一行都能获取当前事件下所有消息类型的最小日期。
3. 日期格式化：使用TO_CHAR()将日期转换为预期的dd/mm/yyyy hh:mm格式，匹配输出要求。

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