如何基于时间间隔将SQLite聊天日志分组为对话？

要实现将is_new_convo=1开始到后续is_new_convo=0的消息归为同一组，核心是通过累积求和生成对话组ID，具体步骤如下：

1. 生成对话组ID #

在你已有的conversations_identified CTE基础上，新增convo_id字段——对is_new_convo做按时间顺序的累积求和。每当is_new_convo=1（新对话起始），求和值就会递增，后续属于同一对话的is_new_convo=0的消息会继承这个组ID，自然归为同一组。

2. 按组ID聚合消息 #

最后用GROUP BY convo_id分组，结合group_concat(content, CHAR(10))将同组消息用换行符连接，得到完整对话。

完整SQL代码如下：

WITH ordered_messages AS (
    SELECT content, timestamp
    FROM messages
    ORDER BY timestamp
), conversations_identified AS (
    SELECT *,
        CASE
            WHEN timestamp - LAG(timestamp, 1, timestamp) OVER (ORDER BY timestamp) < 900
            THEN 0
            ELSE 1
        END AS is_new_convo
    FROM ordered_messages 
), conversation_groups AS (
    SELECT *,
        SUM(is_new_convo) OVER (ORDER BY timestamp) AS convo_id
    FROM conversations_identified
)
SELECT 
    convo_id,
    MIN(timestamp) AS conversation_start,
    MAX(timestamp) AS conversation_end,
    group_concat(content, CHAR(10)) AS conversation_content
FROM conversation_groups
GROUP BY convo_id
ORDER BY conversation_start;

代码说明 #

• SUM(is_new_convo) OVER (ORDER BY timestamp)：按时间顺序累积求和，每个新对话的起始消息会让convo_id加1，确保同对话的消息共享同一个ID。
• MIN(timestamp)和MAX(timestamp)：可选字段，用于标记对话的起始和结束时间，方便后续查看。
• group_concat(content, CHAR(10))：将同组消息用换行符拼接成完整对话内容。

这样就能完美实现你要的分组效果：单个孤立消息（前后15分钟无其他消息）会单独成为一个对话组，连续的时间间隔小于15分钟的消息会被归为同一组。

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