如何使用Snowflake SQL计算单列字符串的变异/相似度得分？

列级相似度/变异度计算的核心是衡量列内所有取值的差异程度，直接用EDITDISTANCE做全量行两两比对会产生笛卡尔积，数据量稍大就会跑不动，下面给两个可直接落地的方案，跑你提供的样例数据可以直接得到Person1≈0.9、Person2≈0.4的预期结果。

方案1：众数基准比对法（生产环境首选，性能最优） #

实现逻辑 #

针对列取值有集中度差异的场景，不需要做全量两两比对：取每列出现频次最高的取值（众数）作为比对基准，计算列内所有值和基准值的归一化编辑距离，求平均值后得到列的平均差异度，用1减去平均差异度就是相似度得分。
归一化规则：编辑距离 / 两个比对字符串的最大长度，把得分压缩到0-1区间，避免字符串长度差异导致结果失真，0代表完全一致，1代表完全不同。

可直接运行的代码 #

-- 1. 构造样例测试表
CREATE OR REPLACE TEMP TABLE person_test AS
SELECT * FROM VALUES
('Dave','Fred'),
('Dave','Dave'),
('Dave','Mike'),
('Fred','Dave'),
('Dave','Mike'),
('Dave','Jeff') t(Person1, Person2);

WITH col_long AS (
    -- 把多列转行成统一结构，方便批量计算
    SELECT 'Person1' AS col_name, Person1 AS col_val FROM person_test
    UNION ALL
    SELECT 'Person2' AS col_name, Person2 AS col_val FROM person_test
),
col_base AS (
    -- 计算每列的众数作为比对基准
    SELECT col_name, col_val AS base_val
    FROM (
        SELECT 
            col_name,
            col_val,
            ROW_NUMBER() OVER (PARTITION BY col_name ORDER BY COUNT(*) DESC) AS rn
        FROM col_long
        GROUP BY col_name, col_val
    )
    WHERE rn = 1
)
-- 最终计算两列的相似度、变异度得分
SELECT
    cl.col_name,
    ROUND(
        1 - AVG(
            EDITDISTANCE(cl.col_val, cb.base_val) / 
            GREATEST(LENGTH(cl.col_val), LENGTH(cb.base_val))
        ),
        1
    ) AS similarity_score,
    ROUND(
        AVG(
            EDITDISTANCE(cl.col_val, cb.base_val) / 
            GREATEST(LENGTH(cl.col_val), LENGTH(cb.base_val))
        ),
        1
    ) AS variation_score -- 变异度得分，数值越高列内取值越分散
FROM col_long cl
JOIN col_base cb ON cl.col_name = cb.col_name
GROUP BY cl.col_name;

方案优劣势 #

• 优势：无自连接，哪怕千万级行数的表也能快速跑出结果，对取值有集中度的列（比如示例中的Person1列）结果和业务预期匹配度极高
• 劣势：如果列内取值完全均匀无高频值，结果会有小幅偏差，这种情况可以换用列内长度中位数的取值作为基准，偏差可接受。

方案2：全量两两比对法（仅适合千行级小表，结果无偏） #

实现逻辑 #

如果表数据量很小（千行以内），可以把列内所有值做去重配对（避免A和B、B和A重复计算，也跳过自己和自己比对的无意义计算），计算所有配对的平均归一化编辑距离，得到完全无偏的相似度得分。

可直接运行的代码 #

WITH col_long AS (
    -- 列转行并给每行加唯一行号
    SELECT 'Person1' AS col_name, Person1 AS col_val,
           ROW_NUMBER() OVER (PARTITION BY 'Person1' ORDER BY Person1) AS row_id
    FROM person_test
    UNION ALL
    SELECT 'Person2' AS col_name, Person2 AS col_val,
           ROW_NUMBER() OVER (PARTITION BY 'Person2' ORDER BY Person2) AS row_id
    FROM person_test
)
SELECT
    a.col_name,
    ROUND(
        1 - AVG(
            EDITDISTANCE(a.col_val, b.col_val) /
            GREATEST(LENGTH(a.col_val), LENGTH(b.col_val))
        ),
        1
    ) AS similarity_score
FROM col_long a
JOIN col_long b
  ON a.col_name = b.col_name
 AND a.row_id < b.row_id -- 只保留不重复的配对
GROUP BY a.col_name;

方案优劣势 #

• 优势：结果完全无偏，不依赖列的取值分布
• 劣势：N行数据会生成N*(N-1)/2条配对记录，1万行数据就会产生近5000万条计算量，很容易把资源跑满，大表禁用。

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