如何使用GROUP_CONCAT仅去除连续重复项

问题背景 #

我的trafficlog表结构及数据如下：

当前使用的MySQL查询语句：

SELECT COUNT(A.strUserPath) AS intUserPathCount, A.strUserPath FROM (
  SELECT SUBSTRING_INDEX (
    GROUP_CONCAT (
      strPage ORDER BY intLogID ASC SEPARATOR '>>'
    ), '>>' , 10
  ) AS strUserPath 
  FROM trafficlog 
  GROUP BY strSessionID 
) A 
GROUP BY A.strUserPath 
ORDER BY intUserPathCount DESC

该查询的输出结果：

我需要修改查询，仅移除按intLogID排序后序列中连续重复的条目（比如示例中连续的./contact），但保留中间隔了其他内容的非连续重复条目（比如./about）。我知道用DISTINCT可以移除所有重复项，但这不是我要的效果。

期望的输出结果：

解决方案 #

要实现移除连续重复的页面路径，需要先对每个strSessionID过滤掉连续重复的strPage，再进行分组拼接。以下分两种MySQL版本给出实现方案：

方案1：MySQL 8.0+（支持窗口函数）

SELECT COUNT(A.strUserPath) AS intUserPathCount, A.strUserPath
FROM (
    SELECT SUBSTRING_INDEX(
               GROUP_CONCAT(t.strPage ORDER BY t.intLogID ASC SEPARATOR '>>'),
               '>>',
               10
           ) AS strUserPath
    FROM (
        SELECT intLogID, strSessionID, strPage
        FROM (
            SELECT 
                intLogID,
                strSessionID,
                strPage,
                LAG(strPage) OVER (PARTITION BY strSessionID ORDER BY intLogID) AS prev_page
            FROM trafficlog
        ) AS sub
        WHERE prev_page IS NULL OR strPage != prev_page
    ) AS t
    GROUP BY t.strSessionID
) AS A
GROUP BY A.strUserPath
ORDER BY intUserPathCount DESC;

方案2：MySQL 5.x（不支持窗口函数，用变量实现）

SELECT COUNT(A.strUserPath) AS intUserPathCount, A.strUserPath
FROM (
    SELECT SUBSTRING_INDEX(
               GROUP_CONCAT(t.strPage ORDER BY t.intLogID ASC SEPARATOR '>>'),
               '>>',
               10
           ) AS strUserPath
    FROM (
        SELECT 
            intLogID,
            strSessionID,
            strPage
        FROM (
            SELECT 
                intLogID,
                strSessionID,
                strPage,
                @prev_page := CASE 
                    WHEN @current_session = strSessionID THEN @prev_page 
                    ELSE NULL 
                END AS prev_page,
                @current_session := strSessionID
            FROM trafficlog, (SELECT @current_session := NULL, @prev_page := NULL) AS vars
            ORDER BY strSessionID, intLogID
        ) AS sub
        WHERE prev_page IS NULL OR strPage != prev_page
    ) AS t
    GROUP BY t.strSessionID
) AS A
GROUP BY A.strUserPath
ORDER BY intUserPathCount DESC;

逻辑解释 #

1. 过滤连续重复项：
   • 窗口函数版本用LAG()获取同一会话中前一条记录的页面路径，仅保留当前页面与前一条不同的记录（或会话的第一条记录）。
   • 变量版本通过自定义变量跟踪当前会话和上一个页面，实现同样的连续重复过滤逻辑。
2. 拼接路径：对过滤后的记录按会话分组，用GROUP_CONCAT拼接成路径，再用SUBSTRING_INDEX限制最多10个节点。
3. 统计路径频次：最后对拼接好的路径分组统计出现次数，得到符合需求的结果。

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