PostgreSQL百万行文本：统计高相似度行数并生成自动掩码

1. 前置准备 #

先启用PostgreSQL内置的pg_trgm扩展，用于高效计算文本相似度：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 性能优化：创建相似度索引 #

针对百万级数据，必须创建GIN索引加速相似度查询，避免全表扫描：

CREATE INDEX idx_desc_trgm ON your_table USING GIN (description gin_trgm_ops);

3. 方案实现 #

3.1 场景1：固定模式差异（如数字替换） #

如果相似文本的差异仅为数字（如示例中的something 1/something 2），直接用正则快速生成掩码并统计：

SELECT
    regexp_replace(description, '\d+', '*', 'g') AS "mask (auto-generated)",
    COUNT(*) AS "row count"
FROM your_table
GROUP BY "mask (auto-generated)"
ORDER BY "row count" DESC;

3.2 场景2：通用相似度分组（80%匹配度） #

对于任意形式的文本相似性，使用递归聚类将相似度≥80%的文本归为一组，再生成通用掩码：

步骤1：自定义掩码生成函数

该函数接收同组文本数组，提取公共模板并将差异片段替换为*：

CREATE OR REPLACE FUNCTION generate_mask(text_array text[])
RETURNS text AS $$
DECLARE
    base text := text_array[1];
    mask text := base;
    curr text;
    i int;
    j int;
BEGIN
    IF array_length(text_array, 1) = 1 THEN RETURN mask; END IF;

    -- 逐字符对比所有文本，标记差异位置
    FOR i IN 1..length(base) LOOP
        FOR j IN 2..array_length(text_array, 1) LOOP
            curr := text_array[j];
            IF length(curr) < i OR substr(curr, i, 1) != substr(base, i, 1) THEN
                mask := overlay(mask placing '*' from i for 1);
                -- 跳过连续差异字符
                WHILE length(curr) >= i+1 AND substr(curr, i+1, 1) != substr(base, i+1, 1) LOOP
                    i := i + 1;
                END LOOP;
                EXIT;
            END IF;
        END LOOP;
    END LOOP;

    -- 处理文本长度差异
    FOR j IN 2..array_length(text_array, 1) LOOP
        curr := text_array[j];
        IF length(curr) > length(base) THEN
            mask := mask || '*';
        END IF;
    END LOOP;

    -- 合并连续的*为单个
    mask := regexp_replace(mask, '\*+', '*', 'g');
    RETURN mask;
END;
$$ LANGUAGE plpgsql;

步骤2：递归聚类与统计

WITH RECURSIVE text_clusters AS (
    -- 初始化：每个文本作为独立簇
    SELECT
        description AS cluster_key,
        ARRAY[description] AS cluster_texts
    FROM your_table
    UNION ALL
    -- 合并相似度≥80%的文本到同一簇
    SELECT
        tc.cluster_key,
        tc.cluster_texts || yt.description
    FROM text_clusters tc
    JOIN your_table yt 
        ON similarity(tc.cluster_key, yt.description) >= 0.8
        AND NOT yt.description = ANY(tc.cluster_texts)
)
-- 去重并统计每个簇的行数
SELECT
    generate_mask(cluster_texts) AS "mask (auto-generated)",
    COUNT(DISTINCT unnest(cluster_texts)) AS "row count"
FROM text_clusters
GROUP BY cluster_key, cluster_texts
HAVING COUNT(DISTINCT unnest(cluster_texts)) > 1
ORDER BY "row count" DESC;

注意事项 #

• 递归聚类方案适合通用场景，但百万级数据可能需要分批处理，或调整相似度阈值平衡精度与性能
• 若仅需处理数字/特定格式差异，正则方案性能远高于通用聚类方案
• 务必先创建GIN索引，否则百万级数据的相似度查询会极慢

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