# 计算字符的相似度 (使用编辑距离) ## 前置条件 无 ``` ``` ## 依赖函数 无 ## 处理逻辑 根据编辑距离来计算相似度 # 函数
源码 ```plpython 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}]