MySQL中拆分timestamp列，实现问答时间戳关联匹配

方法1：窗口函数分组聚合 #

该方法通过为每组问答分配唯一标识，再聚合合并问答时间戳，适合严格按「问题-回答」顺序排列的数据：

WITH qa_groups AS (
    SELECT 
        timestamp,
        converationId,
        UserId,
        tpMessage,
        -- 按会话、用户分组，按时间排序，每遇到question就递增组ID
        SUM(CASE WHEN tpMessage = 'question' THEN 1 ELSE 0 END) OVER (
            PARTITION BY converationId, UserId 
            ORDER BY timestamp ASC
        ) AS group_id
    FROM your_table_name
)
SELECT 
    MAX(CASE WHEN tpMessage = 'question' THEN timestamp END) AS ts_question,
    MAX(CASE WHEN tpMessage = 'answer' THEN timestamp END) AS ts_answer,
    converationId,
    UserId
FROM qa_groups
GROUP BY converationId, UserId, group_id
ORDER BY group_id;

方法2：自连接匹配对应回答 #

如果数据存在特殊情况（比如问题后有其他无关记录），可以通过子查询定位每个问题之后的第一个回答：

SELECT 
    q.timestamp AS ts_question,
    a.timestamp AS ts_answer,
    q.converationId,
    q.UserId
FROM your_table_name q
INNER JOIN your_table_name a 
    ON q.converationId = a.converationId
    AND q.UserId = a.UserId
    AND a.tpMessage = 'answer'
    -- 匹配当前问题之后的最早回答
    AND a.timestamp = (
        SELECT MIN(timestamp)
        FROM your_table_name
        WHERE converationId = q.converationId
          AND UserId = q.UserId
          AND tpMessage = 'answer'
          AND timestamp > q.timestamp
    )
WHERE q.tpMessage = 'question'
ORDER BY q.timestamp;

单独用CASE语句失效的原因 #

CASE语句仅能在每行生成对应时间戳（其余为NULL），但无法将问题行与回答行合并为一行。必须通过分组聚合或自连接，把同一组问答的两行数据合并，才能得到结构化的配对结果。

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