如何使用MySQL统计字符串中重复字符序列的出现次数？

需求说明 #

输入数据：

期望输出：

你的BigQuery代码中文翻译及解释 #

WITH Input AS (
  -- 定义输入字符串
  SELECT 'abcccaaaaddeeaabbaa' AS str
),

Exploded AS (
  -- 将字符串拆分为单个字符，同时记录字符位置和前一个位置的字符
  SELECT 
    SUBSTR(str, pos, 1) AS char,
    pos,
    LAG(SUBSTR(str, pos, 1)) OVER (ORDER BY pos) AS prev_char
  FROM Input, 
  -- 生成从1到字符串长度的数字序列，用于逐个提取字符
  UNNEST(GENERATE_ARRAY(1, LENGTH(str))) AS pos
),

Grouped AS (
  -- 判断当前字符是否与前一个字符相同，不同则标记为新分组的起始
  SELECT
    char,
    pos,
    IF(char = prev_char, 0, 1) AS new_group
  FROM Exploded
),

Sequenced AS (
  -- 累计新分组标记值，得到每个字符所属的连续序列ID
  SELECT
    char,
    pos,
    SUM(new_group) OVER (ORDER BY pos) AS sequence_id
  FROM Grouped
),

Aggregated AS (
  -- 按序列ID分组，拼接同组字符形成连续序列
  SELECT
    sequence_id,
    STRING_AGG(char, '' ORDER BY pos) AS sequence
  FROM Sequenced
  GROUP BY sequence_id
),

FinalCounts AS (
  -- 统计每个连续序列的出现次数
  SELECT
    sequence,
    COUNT(*) AS occurrences
  FROM Aggregated
  GROUP BY sequence
)
select * from FinalCounts

MySQL实现方案 #

MySQL没有UNNEST(GENERATE_ARRAY)这类函数，需要用递归生成数字表来拆分字符串，以下是完整实现：

通用表版本（适用于已有数据表） #

-- 步骤1：递归生成数字序列，用于拆分字符串的每个字符位置（假设字符串最长不超过1000，可按需调整）
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 1000
),

-- 步骤2：拆分字符串为单个字符，记录位置和前一个字符
exploded AS (
    SELECT 
        SUBSTRING(t.ColumnA, nums.n, 1) AS char,
        nums.n AS pos,
        LAG(SUBSTRING(t.ColumnA, nums.n, 1)) OVER (ORDER BY nums.n) AS prev_char
    FROM your_table t
    JOIN nums ON nums.n <= LENGTH(t.ColumnA)
),

-- 步骤3：标记新的连续序列起始
grouped AS (
    SELECT
        char,
        pos,
        CASE WHEN char = prev_char THEN 0 ELSE 1 END AS new_group
    FROM exploded
),

-- 步骤4：生成每个字符所属的连续序列ID
sequenced AS (
    SELECT
        char,
        pos,
        SUM(new_group) OVER (ORDER BY pos) AS sequence_id
    FROM grouped
),

-- 步骤5：拼接同序列ID的字符，形成连续序列字符串
aggregated AS (
    SELECT
        sequence_id,
        GROUP_CONCAT(char ORDER BY pos SEPARATOR '') AS sequence
    FROM sequenced
    GROUP BY sequence_id
),

-- 步骤6：统计每个连续序列的出现次数
final_counts AS (
    SELECT
        sequence,
        COUNT(*) AS occurrences
    FROM aggregated
    GROUP BY sequence
)

SELECT * FROM final_counts;

单行字符串直接输入版本 #

如果仅需处理单行固定字符串，可替换为以下代码：

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 1000
),

input AS (
    SELECT 'abcccaaaaddeeaabbaa' AS ColumnA
),

exploded AS (
    SELECT 
        SUBSTRING(t.ColumnA, nums.n, 1) AS char,
        nums.n AS pos,
        LAG(SUBSTRING(t.ColumnA, nums.n, 1)) OVER (ORDER BY nums.n) AS prev_char
    FROM input t
    JOIN nums ON nums.n <= LENGTH(t.ColumnA)
),

grouped AS (
    SELECT
        char,
        pos,
        CASE WHEN char = prev_char THEN 0 ELSE 1 END AS new_group
    FROM exploded
),

sequenced AS (
    SELECT
        char,
        pos,
        SUM(new_group) OVER (ORDER BY pos) AS sequence_id
    FROM grouped
),

aggregated AS (
    SELECT
        sequence_id,
        GROUP_CONCAT(char ORDER BY pos SEPARATOR '') AS sequence
    FROM sequenced
    GROUP BY sequence_id
),

final_counts AS (
    SELECT
        sequence,
        COUNT(*) AS occurrences
    FROM aggregated
    GROUP BY sequence
)

SELECT * FROM final_counts;

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