rel_str_similar.md 2.4 KB

计算字符的相似度 (使用编辑距离)

前置条件

依赖函数

处理逻辑

根据编辑距离来计算相似度

函数

源码
CREATE OR REPLACE FUNCTION "public"."rel_str_similar"("word" varchar, "possibilities" _varchar, "num" int4, "sim" float8)
  RETURNS "pg_catalog"."text" AS $BODY$
import json
import logging
from difflib import SequenceMatcher
from heapq import nlargest as _nlargest



def get_close(word, possibilities, n=1, cutoff=0.0):
    """Use SequenceMatcher to return list of the best "good enough" matches.
    word is a sequence for which close matches are desired (typically a
    string).
    possibilities is a list of sequences against which to match word
    (typically a list of strings).
    Optional arg n (default 3) is the maximum number of close matches to
    return.  n must be > 0.
    Optional arg cutoff (default 0.6) is a float in [0, 1].  Possibilities
    that don't score at least that similar to word are ignored.
    The best (no more than n) matches among the possibilities are returned
    in a list, sorted by similarity score, most similar first.
    """

    if not n > 0:
        raise ValueError("n must be > 0: %r" % (n,))
    if not 0.0 <= cutoff <= 1.0:
        raise ValueError("cutoff must be in [0.0, 1.0]: %r" % (cutoff,))
    result = []
    s = SequenceMatcher()
    s.set_seq2(word)
    for x in possibilities:
        s.set_seq1(x)
        if s.real_quick_ratio() >= cutoff and s.quick_ratio() >= cutoff and s.ratio() >= cutoff:
            result.append((s.ratio(), x))
    result = _nlargest(n, result)
    return [{'key': x, 'value': score} for score, x in result]


try:
    result = get_close(word, possibilities, num, sim)
    return result
except Exception as e:
    plpy.warning(e)
    return ""
else:
    plpy.warning(e)
$BODY$
  LANGUAGE plpython3u VOLATILE
  COST 100

入参

1. "word" varchar                    需要匹配的字符串
2. "possibilities" _varchar          待匹配的字符串数组
3. "num" int4                        指定返回结果个数
4. "sim" float8                      只有相似度超过sim的结果才会返回
例子: select public.rel_str_similar('北京', ARRAY['青岛', '北京市'], 2, 0.0)

返回结果

字符串 eg. [{'key': '北京市', 'value': 0.8}, {'key': '青岛', 'value': 0.0}]