按时间顺序将表 A 中的每条信息与表 B 中的最早回复列出

我有三个表： conversations , user_messages 和 system_messages .它们的基本结构（为简洁起见删除了无关列）是：

创建表 conversations（默认生成 id int 作为身份主键）；

创建表 user_messages(
   创建表 user_messages(
  ，conversation_id int 引用 conversations(id)
  内容文本
  创建时间）；

创建表 system_messages(
   默认情况下作为标识主键生成的 int
  ,conversation_id int 引用 conversations(id)
  内容文本
  创建时间）；

我正试图为给定的对话生成 "system said X" 和 "user responded with Y" 行的导出。我遇到的问题是如何确保 "user responded with Y" 消息与对话中按时间顺序排列的下一条消息相对应，紧随系统消息之后。因此，输出结果将如下所示：

可以使用窗口函数来实现这一点，特别是在这种情况下是 LEAD() 函数。 LEAD() 函数允许窥视分区中的下一行，这对于获取系统消息之后的下一条用户消息是完美的。

以下是如何修改的查询以获得所需结果的方法：

WITH TimeOrderedMessages AS (
    SELECT
        sm.content AS system_said,
        um.content AS user_said,
        LEAD(um.content, 1) OVER (PARTITION BY sm.conversation_id ORDER BY um.created_at) AS next_user_said,
        um.created_at AS user_created_at
    FROM
        system_messages sm
    LEFT JOIN
        user_messages um ON sm.conversation_id = um.conversation_id AND um.created_at > sm.created_at
    WHERE 
        sm.conversation_id = ?
)
SELECT 
    system_said, 
    user_said
FROM 
    TimeOrderedMessages
WHERE 
    user_created_at = (SELECT MIN(user_created_at) FROM TimeOrderedMessages WHERE system_said = TimeOrderedMessages.system_said)
ORDER BY 
    user_created_at;

说明：

1. CTE（公用表表达式）： 查询首先使用 WITH 子句创建一个名为 TimeOrderedMessages 的 CTE。这有助于使查询更具可读性和组织性。
2. LEAD(um.content, 1) OVER (PARTITION BY sm.conversation_id ORDER BY um.created_at) ： 这部分是关键。它使用 LEAD() 函数在 user_messages 表的上下文中获取按 created_at 排序的每个系统消息的 next user_said 值，这些值由 conversation_id 分组。
3. LEFT JOIN： 我们使用 LEFT JOIN 来包含没有相应用户消息的系统消息。如果只需要成对的消息，则可以使用 INNER JOIN 。
4. WHERE 子句： CTE 中的 WHERE 子句过滤结果以仅包含与提供的 conversation_id 匹配的消息。
5. 外部选择和排序： 然后，外部查询选择 system_said 和 user_said 列。 ORDER BY um.created_at 子句确保按时间顺序呈现响应。

此查询避免了之前方法中的子查询，从而使其更加高效。它利用窗口函数来查找按时间顺序排列的下一条用户消息，为提供所需的确切对话流。