从SQLite聊天日志中选择对话

gz5pxeao 于 2023-01-02 发布在 SQLite

关注(0)|答案(1)|浏览(117)

我有一个表示聊天日志的SQLite表。这个问题的两个重要列是“内容”和“时间戳”。
我需要按会话对聊天日志中的消息进行分组，每条消息都是单独的一行，因此可以使用group_concat将每条消息添加到一个新行中，从而选择一个会话

group_concat(content, CHAR(10)

我想通过在一段时间内（例如15分钟）的任何消息来识别会话。稍后，我想限制会话的长度（例如，在3到30条消息之间），但现在我只想假设会话可以是任何长度（如果在15分钟内没有其他消息，则只包括单条消息）。
知道了这一点，我就可以确定消息是对话的开始还是部分，如下所示

WHEN timestamp - LAG(timestamp, 1, timestamp) OVER (ORDER BY timestamp) < 900

但这是我目前所能做到的。我可以使用以下命令创建一个列“is_new_convo

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 
) SELECT * FROM conversations_identified

然后，如何形成从where is_new_convo = 1到最后一个后续is_new_convo = 0的消息组？
这里是some sample data和the expected result。

sqlite

来源：https://stackoverflow.com/questions/74978963/select-conversations-from-a-sqlite-chatlog

1条答案

按热度按时间

m3eecexj1#

如果对is_new_convo列从开始到某行进行求和，则会得到形成新会话的次数，从而得到会话中所有消息的唯一ID（由于is_new_convo对于继续对话的消息是0，因此它们导致相同的对话ID）。使用这个，我们可以找到所有消息的对话ID，然后将它们组合在一起以得到group_concat。这不需要多次引用原始表，因此不需要'WITH'子句。

SELECT group_concat(content, CHAR(10)) as conversation
FROM (
    SELECT content, timestamp,
        SUM(is_new_convo) OVER (ORDER BY timestamp) as conversation_id
    FROM (
        SELECT content, timestamp,
            CASE
                WHEN timestamp - LAG(timestamp, 1, timestamp) OVER (ORDER BY timestamp) < 900
                THEN 0
                ELSE 1
            END AS is_new_convo
        FROM messages
    )
) GROUP BY conversation_id

赞(0）回复(0）举报 2023-01-02

我来回答

从SQLite聊天日志中选择对话

1条答案

相关问题

热门标签

最新问答