计算字符的相似度 (使用编辑距离)
无
无
根据编辑距离来计算相似度
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}]