如何在MySQL GROUP BY查询中实现COMMON_PREFIX聚合函数获取公共前缀

问题场景 #

现有数据集：

需要按Category分组，计算金额总和的同时，提取每组Description字段的最长公共前缀，得到如下结果：

相当于需要一个虚构的COMMON_PREFIX()聚合函数，但MySQL没有内置该功能。之前尝试用GROUP_CONCAT()拼接后加正则匹配，因MySQL不支持正则反向引用失败。数据集规模小，无需重构数据库。

可行实现方案 #

方案1：自定义聚合函数（推荐，逻辑清晰） #

利用分组内最短的描述作为基准，逐字符验证所有描述是否都匹配当前前缀，直到出现不匹配为止。如果允许创建自定义函数，这是最直观的方式。

步骤1：创建获取公共前缀的函数

DELIMITER //
CREATE FUNCTION GET_COMMON_PREFIX(all_descs TEXT, delimiter CHAR(1)) 
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE shortest_str TEXT;
    DECLARE prefix TEXT DEFAULT '';
    DECLARE i INT DEFAULT 1;
    DECLARE current_char CHAR(1);
    DECLARE has_mismatch INT DEFAULT 0;
    
    -- 先拿到分组里最短的描述，减少遍历次数
    SELECT MIN(val) INTO shortest_str 
    FROM UNNEST(STRING_TO_ARRAY(all_descs, delimiter)) AS val;
    
    -- 逐字符遍历最短描述，验证所有描述是否都包含当前前缀
    WHILE i <= LENGTH(shortest_str) AND has_mismatch = 0 DO
        SET current_char = SUBSTRING(shortest_str, i, 1);
        SET prefix = CONCAT(prefix, current_char);
        
        -- 检查是否有描述不匹配当前前缀
        SELECT COUNT(*) INTO has_mismatch
        FROM UNNEST(STRING_TO_ARRAY(all_descs, delimiter)) AS val
        WHERE NOT val LIKE CONCAT(prefix, '%');
        
        SET i = i + 1;
    END WHILE;
    
    -- 回退到最后一个完全匹配的前缀（因为最后一次循环可能已经不匹配）
    IF has_mismatch > 0 THEN
        SET prefix = SUBSTRING(prefix, 1, LENGTH(prefix)-1);
    END IF;
    
    -- 如果需要提取完整单词的前缀，可在这里加逻辑截断到最近的空格
    -- 比如：SET prefix = LEFT(prefix, LENGTH(prefix) - LOCATE(' ', REVERSE(prefix)) + 1);
    RETURN prefix;
END //
DELIMITER ;

步骤2：调用函数完成查询

SELECT 
    GET_COMMON_PREFIX(all_descs, '|') AS Description,
    TotalAmount,
    Category
FROM (
    SELECT 
        Category,
        SUM(Amount) AS TotalAmount,
        GROUP_CONCAT(Description SEPARATOR '|') AS all_descs
    FROM your_table
    GROUP BY Category
) AS grouped_data;

方案2：纯SQL递归实现（无需自定义函数） #

如果无法创建自定义函数，利用MySQL 8.0+的CTE递归特性，逐字符验证前缀：

WITH grouped_data AS (
    SELECT 
        Category,
        SUM(Amount) AS TotalAmount,
        MIN(Description) AS shortest_desc,
        GROUP_CONCAT(Description SEPARATOR '|') AS all_descs
    FROM your_table
    GROUP BY Category
),
prefix_recursive AS (
    SELECT 
        Category,
        TotalAmount,
        shortest_desc,
        all_descs,
        1 AS pos,
        SUBSTRING(shortest_desc, 1, 1) AS current_prefix
    FROM grouped_data
    UNION ALL
    SELECT 
        Category,
        TotalAmount,
        shortest_desc,
        all_descs,
        pos + 1,
        SUBSTRING(shortest_desc, 1, pos + 1)
    FROM prefix_recursive
    WHERE pos < LENGTH(shortest_desc)
        AND NOT EXISTS (
            SELECT 1 
            FROM UNNEST(STRING_TO_ARRAY(all_descs, '|')) AS s
            WHERE NOT s.value LIKE CONCAT(current_prefix, '%')
        )
)
SELECT 
    MAX(current_prefix) AS Description,
    TotalAmount,
    Category
FROM prefix_recursive
GROUP BY Category, TotalAmount;

注：如果你的MySQL版本低于8.0，需要替换STRING_TO_ARRAY和UNNEST为自定义的字符串拆分逻辑（比如用SUBSTRING_INDEX循环拆分）。

方案3：针对完整单词的公共前缀提取 #

如果需求是提取连续的开头完整单词（而非逐字符的前缀），可以修改函数逻辑，按空格拆分单词后，依次验证每个位置的单词是否在所有描述中一致：

DELIMITER //
CREATE FUNCTION GET_COMMON_WORD_PREFIX(all_descs TEXT, delimiter CHAR(1)) 
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE word_arrays TEXT[];
    DECLARE min_word_count INT DEFAULT 999;
    DECLARE prefix_words TEXT[];
    DECLARE i INT DEFAULT 1;
    DECLARE current_word TEXT;
    DECLARE has_mismatch INT DEFAULT 0;
    
    -- 拆分每个描述为单词数组
    SET word_arrays = ARRAY(
        SELECT STRING_TO_ARRAY(val, ' ')
        FROM UNNEST(STRING_TO_ARRAY(all_descs, delimiter)) AS val
    );
    
    -- 找到分组内最少的单词数，减少遍历次数
    SELECT MIN(ARRAY_LENGTH(val)) INTO min_word_count
    FROM UNNEST(word_arrays) AS val;
    
    -- 逐位置验证单词是否一致
    WHILE i <= min_word_count AND has_mismatch = 0 DO
        -- 获取第一个描述的第i个单词作为基准
        SELECT val[i] INTO current_word
        FROM UNNEST(word_arrays) AS val
        LIMIT 1;
        
        -- 检查所有描述的第i个单词是否和基准一致
        SELECT COUNT(*) INTO has_mismatch
        FROM UNNEST(word_arrays) AS val
        WHERE val[i] != current_word;
        
        IF has_mismatch = 0 THEN
            SET prefix_words = ARRAY_APPEND(prefix_words, current_word);
        END IF;
        
        SET i = i + 1;
    END WHILE;
    
    -- 拼接单词为前缀字符串
    RETURN ARRAY_TO_STRING(prefix_words, ' ');
END //
DELIMITER ;

调用方式和方案1一致，只需把函数名换成GET_COMMON_WORD_PREFIX即可。

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