如何用SQL基于连续用户值创建分组并合并会话文本？

一、生成分组标识列C的中间表 #

核心思路是利用窗口函数LAG()对比当前行与上一行的persons，再通过累计求和生成连续相同发言者的分组标识：

WITH session_groups AS (
    SELECT 
        id,
        text,
        persons,
        timestamp,
        -- 同一id内，当前发言者与上一行不同则触发分组，累计求和得到组号C
        SUM(CASE 
                WHEN prev_persons != persons OR prev_persons IS NULL THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY id ORDER BY timestamp) AS C
    FROM (
        SELECT 
            id,
            text,
            persons,
            timestamp,
            -- 获取同一id内上一行的发言者
            LAG(persons) OVER (PARTITION BY id ORDER BY timestamp) AS prev_persons
        FROM your_table_name -- 替换为你的表名
    ) AS lagged_data
)

这段SQL先通过子查询用LAG()拿到每个行的上一行发言者，再用SUM() OVER()窗口函数累计分组触发信号，最终生成的C列会把同一id下连续相同的persons标记为同一个组号。

二、按分组聚合合并文本 #

基于上面的中间表，按id和C分组，用字符串拼接函数合并text，同时保留会话顺序：

SELECT 
    id,
    persons,
    -- 按时间顺序拼接文本，分隔符可自行调整（比如换为'\n'）
    STRING_AGG(text, ' ' ORDER BY timestamp) AS merged_text,
    MIN(timestamp) AS session_start,
    MAX(timestamp) AS session_end
FROM session_groups
GROUP BY id, persons, C
ORDER BY id, session_start;

不同数据库的适配说明 #

• MySQL：替换STRING_AGG为GROUP_CONCAT，写法为GROUP_CONCAT(text ORDER BY timestamp SEPARATOR ' ')
• SQL Server（2017以前版本）：需要用STUFF+FOR XML PATH的方式实现拼接，示例：

  SELECT 
      id,
      persons,
      STUFF(
          (SELECT ' ' + text FROM session_groups sg2 
           WHERE sg1.id = sg2.id AND sg1.C = sg2.C 
           ORDER BY sg2.timestamp FOR XML PATH('')),
          1, 1, ''
      ) AS merged_text,
      MIN(timestamp) AS session_start,
      MAX(timestamp) AS session_end
  FROM session_groups sg1
  GROUP BY id, persons, C
  ORDER BY id, session_start;
  

性能优化建议 #

给id和timestamp字段建立联合索引：

CREATE INDEX idx_id_timestamp ON your_table_name(id, timestamp);

索引能加速窗口函数的排序和分区操作，在数据量较大时效果明显。

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