如何修改SQL查询实现按最长清理后字符串分组聚合？

现有数据表中，部分行的grp字段值相同但name字段值不同，需求如下：

1. 对name字段去除非字母数字字符
2. 将所有子串（null值视为所有字符串的子串）聚合到对应的最长字符串组中
3. 按grp和该最长字符串分组并求和value字段

原始数据表 #

期望结果 #

当前查询语句 #

Select grp, 
regexp_replace(name,'[^a-zA-Z0-9]+', '', 'g') name, sum(value) value
from table
group by grp,
regexp_replace(name,'[^a-zA-Z0-9]+', '', 'g');

当前查询结果 #

要实现需求，需要先清洗字符串，再为每个字符串匹配同一grp下的最长父串，最后按父串聚合求和。以下是适配不同数据库的修改方案：

PostgreSQL 版本 #

WITH cleaned_data AS (
    -- 清洗name字段，将null转为空串统一处理
    SELECT 
        grp,
        COALESCE(regexp_replace(name, '[^a-zA-Z0-9]+', '', 'g'), '') AS cleaned_name,
        value
    FROM your_table
),
matched_groups AS (
    SELECT 
        cd1.grp,
        cd1.cleaned_name,
        cd1.value,
        -- 找到当前字符串对应的最长父串（同一grp下）
        MAX(cd2.cleaned_name) OVER (
            PARTITION BY cd1.grp 
            ORDER BY LENGTH(cd2.cleaned_name) DESC
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            WHERE cd2.cleaned_name LIKE CONCAT('%', cd1.cleaned_name, '%') 
               OR cd1.cleaned_name = ''
        ) AS longest_parent
    FROM cleaned_data cd1
    LEFT JOIN cleaned_data cd2 ON cd1.grp = cd2.grp
)
-- 按grp和最长父串分组求和，处理空串的展示问题
SELECT 
    grp,
    CASE WHEN longest_parent = '' THEN MAX(cleaned_name) ELSE longest_parent END AS name,
    SUM(value) AS value
FROM matched_groups
GROUP BY grp, longest_parent
HAVING longest_parent != '' OR (longest_parent = '' AND MAX(cleaned_name) IS NOT NULL);

MySQL 8.0+ 版本 #

WITH cleaned_data AS (
    SELECT 
        grp,
        COALESCE(REGEXP_REPLACE(name, '[^a-zA-Z0-9]+', '', 'g'), '') AS cleaned_name,
        value
    FROM your_table
),
matched_groups AS (
    SELECT 
        cd1.grp,
        cd1.cleaned_name,
        cd1.value,
        -- 用关联子查询找到最长父串
        (SELECT cd2.cleaned_name
         FROM cleaned_data cd2
         WHERE cd2.grp = cd1.grp
           AND (cd2.cleaned_name LIKE CONCAT('%', cd1.cleaned_name, '%') OR cd1.cleaned_name = '')
         ORDER BY LENGTH(cd2.cleaned_name) DESC
         LIMIT 1) AS longest_parent
    FROM cleaned_data cd1
)
SELECT 
    grp,
    CASE WHEN longest_parent = '' THEN MAX(cleaned_name) ELSE longest_parent END AS name,
    SUM(value) AS value
FROM matched_groups
GROUP BY grp, longest_parent
HAVING longest_parent != '' OR (longest_parent = '' AND MAX(cleaned_name) IS NOT NULL);

逻辑说明 #

1. 数据清洗：用COALESCE将null转换为空串，同时通过正则去除name中的非字母数字字符，统一后续处理规则。
2. 匹配最长父串：通过自连接/关联子查询，为每个清洗后的字符串找到同一grp下包含它的最长字符串；空串（原null值）会匹配所有字符串，最终归属到最长的那个组。
3. 分组聚合：按grp和最长父串分组求和，同时处理空串对应的展示问题，确保结果与期望一致。

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