如何在大型数据库中查找name字段高频子串并统计出现次数

1. 查找字段值为其他记录同字段值子串的条目 #

使用自连接查询实现，同时考虑去重和性能优化：

SELECT DISTINCT t1.name
FROM your_table t1
INNER JOIN your_table t2 
    ON t1.name != t2.name 
    AND t2.name LIKE CONCAT('%', t1.name, '%');

• 逻辑说明：通过自连接匹配，找出所有存在另一条记录的name包含当前记录name的条目，DISTINCT用于避免同一name被多个匹配项重复返回。
• 性能提示：针对大型数据库，建议给name字段创建全文索引或合适的前缀索引，避免全表扫描；如果数据库支持全文搜索（如MySQL的MATCH AGAINST、PostgreSQL的tsvector），可替换LIKE为全文搜索语法，大幅提升匹配效率。

2. 统计name字段中重复子串的出现次数 #

分两种场景给出解决方案：

场景A：已知目标子串（如TESCO、ALDI） #

直接针对指定子串进行统计：

SELECT 
    'TESCO' AS substring_name,
    COUNT(*) AS occurrence_count
FROM your_table
WHERE name LIKE CONCAT('%', 'TESCO', '%')
UNION ALL
SELECT 
    'ALDI' AS substring_name,
    COUNT(*) AS occurrence_count
FROM your_table
WHERE name LIKE CONCAT('%', 'ALDI', '%');

• 逻辑说明：通过UNION ALL合并多个子串的统计结果，LIKE CONCAT('%', 子串, '%')匹配所有包含目标子串的记录。

场景B：自动发现所有重复出现的子串 #

如果需要自动提取所有重复子串，可通过递归CTE生成所有可能的子串后统计（注意：该方法对大型数据库资源消耗较大，建议限定子串长度范围）：

-- PostgreSQL示例，其他数据库需调整生成序列的语法
WITH substrings AS (
    SELECT 
        SUBSTRING(name, pos, len) AS substring_name
    FROM your_table
    CROSS JOIN GENERATE_SERIES(1, LENGTH(name)) pos
    CROSS JOIN GENERATE_SERIES(3, 10) len -- 限定子串长度为3-10，可按需调整
    WHERE pos + len - 1 <= LENGTH(name)
)
SELECT 
    substring_name,
    COUNT(*) AS occurrence_count
FROM substrings
GROUP BY substring_name
HAVING COUNT(*) > 1
ORDER BY occurrence_count DESC;

• 逻辑说明：
  1. 通过GENERATE_SERIES生成所有可能的子串起始位置和长度，提取name中的所有子串；
  2. 对提取出的子串分组统计，筛选出现次数大于1的结果；
  3. 建议根据业务需求限定子串长度（比如只统计3-10个字符的子串），否则会生成海量子串，导致数据库负载过高。

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