Oracle 数据库中的数据质量运算符
Oracle数据库23c引入了FUZZY_MATCH和PHONIC_ENCODE数据质量运算符来执行模糊字符串匹配。
UTL_MATCH软件包在Oracle 11g Release 2中得到支持。它包含各种有助于测试字符串之间的相似性/差异性级别的函数。在Oracle 23c中FUZZY_MATCH和PHONIC_ENCODE运算符扩展了数据库的模糊字符串匹配功能。
配置（初始化数据表）
本文中的示例需要以下测试表。
drop table if exists match_tab;
create table match_tab (
id number,
col1 varchar2(15),
col2 varchar2(15),
constraint match_tab_pk primary key (id)
);
insert into match_tab values
(1, 'Peter Parker', 'Pete Parker'),
(2, 'Peter Parker', 'peter parker'),
(3, 'Clark Kent', 'Claire Kent'),
(4, 'Wonder Woman', 'Ponder Woman'),
(5, 'Superman', 'Superman'),
(6, 'The Hulk', 'Iron Man');
commit;请注意，COL1和COL2值具有不同程度的相似性，包括完全匹配、接近匹配和完全不同的值。
FUZZY_MATCH
FUZZY_MATCH运算符与语言无关。它确定两个字符串之间的相似性，并支持此处列出的几种算法。
FUZZY_MATCH 将要使用的算法作为第一个参数，要处理的字符串作为第二个和第三个参数，以及一些控制所需输出质量的可选参数。
UTL_MATCH 包逐字节计算，而 FUZZY_MATCH 逐字符计算。因此UTL_MATCH仅适用于单字节字符串之间的比较，而FUZZY_MATCH处理多字节字符集。
当指定 UNSCALED 选项时， FUZZY_MATCH 返回以下算法的字符度量：LEVENSHTEIN 、 DAMERAU_LEVENSHTEIN 、 BIGRAM 、 TRIGRAM 、 LONGEST_COMMON_SUBSTRING 。
支持的算法有：
LEVENSHTEIN 对应于 UTL_MATCH.EDIT_DISTANCE 或 UTL_MATCH.EDIT_SIMILARITY，并给出字符编辑距离或相似性的度量。
DAMERAU_LEVENSHTEIN 距离与经典 LEVENSHTEIN 距离的不同之处在于，除了三种经典的单字符编辑操作（插入、删除和替换）之外，其允许的操作中还包括转置。
JARO_WINKLER 对应于 UTL_MATCH.JARO_WINKLER（0-1 之间的百分比）或 UTL_MATCH.JARO_WINKLER_SIMILARITY（相同，但范围为 0-100）。
BIGRAM 和 TRIGRAM 是 N-gram 匹配技术的实例，该技术计算两个字符串之间公共连续子字符串（gram）的数量。
WHOLE_WORD_MATCH 对应于 Oracle Enterprise Data Quality 中的单词匹配百分比或计数比较。 它计算以单词（而不是字母）作为匹配单位的两个短语的 LEVENSHTEIN 或编辑距离。
LONGEST_COMMON_SUBSTRING 查找两个字符串之间的最长公共子字符串。
两个 字符串 参数都可以是 CHAR、VARCHAR2、NCHAR、NVARCHAR2 中的任何数据类型。
set linesize 100
column col1 format a12
column col2 format a12
select col1,
col2,
fuzzy_match(levenshtein, col1, col2) as levenshtein,
fuzzy_match(jaro_winkler, col1, col2) as jaro_winkler,
fuzzy_match(bigram, col1, col2) as bigram,
fuzzy_match(trigram, col1, col2) as trigram,
fuzzy_match(whole_word_match, col1, col2) as wwm,
fuzzy_match(longest_common_substring, col1, col2) as lcs
from match_tab;
COL1 COL2 LEVENSHTEIN JARO_WINKLER BIGRAM TRIGRAM WWM LCS
------------ ------------ ----------- ------------ ---------- ---------- ---------- ----------
Peter Parker Pete Parker 92 92 90 70 50 58
Peter Parker peter parker 84 88 72 60 0 41
Clark Kent Claire Kent 82 90 60 44 50 45
Wonder Woman Ponder Woman 92 94 100 90 50 91
Superman Superman 100 100 100 100 100 100
The Hulk Iron Man 0 41 0 0 0 12
6 rows selected.
SQL>默认情况下，输出相似性的百分比，但可以添加UNSCALED关键字以返回原始值。
关键字 UNSCALED 是可选的。如果指定 UNSCALED，则返回以下之一：
- LEVENSHTEIN 或编辑距离
- JARO_WINKLER 百分比值
- N-grams，公共子串的数量
- LCS，最长公共子串的长度
select col1,
col2,
fuzzy_match(levenshtein, col1, col2, unscaled) as levenshtein,
fuzzy_match(jaro_winkler, col1, col2, unscaled) as jaro_winkler,
fuzzy_match(bigram, col1, col2, unscaled) as bigram,
fuzzy_match(trigram, col1, col2, unscaled) as trigram,
fuzzy_match(whole_word_match, col1, col2, unscaled) as wwm,
fuzzy_match(longest_common_substring, col1, col2, unscaled) as lcs
from match_tab;
COL1 COL2 LEVENSHTEIN JARO_WINKLER BIGRAM TRIGRAM WWM LCS
------------ ------------ ----------- ------------ ---------- ---------- ---------- ----------
Peter Parker Pete Parker 1 .92 10 7 1 7
Peter Parker peter parker 2 .88 8 6 0 5
Clark Kent Claire Kent 2 .9 6 4 1 5
Wonder Woman Ponder Woman 1 .94 11 9 1 11
Superman Superman 0 1 7 6 1 8
The Hulk Iron Man 8 .41 0 0 0 1
6 rows selected.
SQL>如果使用了UTL_MATCH包，则缩放值与未缩放值表示以下调用。
算法 | Scaled | Uncaled |
|---|---|---|
LEVENSHTEIN | UTL_MATCH.EDIT_DISTANCE_SIMILARITY | UTL_MATCH.EDIT_DISTANCE |
JARO_WINKLER | UTL_MATCH.JARO_WINKLER_SIMILARITY | UTL_MATCH.JARO_WINKLER |
默认情况下，输出按较长输入字符串的长度进行缩放。RELATE_TO_SHORTER关键字会将其更改为缩放到较短的输入字符串。
select col1,
col2,
fuzzy_match(levenshtein, col1, col2, relate_to_shorter) as levenshtein,
fuzzy_match(jaro_winkler, col1, col2, relate_to_shorter) as jaro_winkler,
fuzzy_match(bigram, col1, col2, relate_to_shorter) as bigram,
fuzzy_match(trigram, col1, col2, relate_to_shorter) as trigram,
fuzzy_match(whole_word_match, col1, col2, relate_to_shorter) as wwm,
fuzzy_match(longest_common_substring, col1, col2, relate_to_shorter) as lcs
from match_tab;
COL1 COL2 LEVENSHTEIN JARO_WINKLER BIGRAM TRIGRAM WWM LCS
------------ ------------ ----------- ------------ ---------- ---------- ---------- ----------
Peter Parker Pete Parker 91 92 100 77 50 63
Peter Parker peter parker 84 88 72 60 0 41
Clark Kent Claire Kent 80 90 66 50 50 50
Wonder Woman Ponder Woman 92 94 100 90 50 91
Superman Superman 100 100 100 100 100 100
The Hulk Iron Man 0 41 0 0 0 12
6 rows selected.
SQL>EDIT_TOLERANCE关键字可以与WHOLE_WORD_MATCH算法一起使用。容差是指一个单词中可能不同的字符的百分比，同时仍将其视为同一个单词。
select col1,
col2,
fuzzy_match(whole_word_match, col1, col2) as wwm,
fuzzy_match(whole_word_match, col1, col2, edit_tolerance 20) as wwm20,
fuzzy_match(whole_word_match, col1, col2, edit_tolerance 82) as wwm82
from match_tab;
COL1 COL2 WWM WWM20 WWM82
------------ ------------ ---------- ---------- ----------
Peter Parker Pete Parker 50 100 50
Peter Parker peter parker 0 100 50
Clark Kent Claire Kent 50 100 50
Wonder Woman Ponder Woman 50 100 100
Superman Superman 100 100 100
The Hulk Iron Man 0 0 0
6 rows selected.
SQL>PHONIC_ENCODE
PHONIC_ENCODE运算符根据文本的发音将文本转换为特定语言的代码。它实现了双元电话算法和一种替代算法。
PHONIC_ENCODE 将要使用的算法作为第一个参数，要处理的字符串作为第二个参数，以及一个可选的 max_code_len 参数来控制所需输出的长度。max_code_len 必须是 1 到 12 之间的整数。
参数说明
- DOUBLE_METAPHONE 返回主代码。
- DOUBLE_METAPHONE_ALT 返回替代代码（如果存在）。 如果替代代码不存在，则返回主代码。
- 第二个参数 字符串 可以是 CHAR、VARCHAR2、NCHAR、NVARCHAR2数据类型。
- 可选参数 max_code_len 必须是整数。它允许为原始 Metaphone 算法返回长于默认 4 个字符的代码。
返回值
- 该运算符返回 VARCHAR2。
set linesize 100
column col1 format a12
column col2 format a12
column col1_dm format a8
column col2_dm format a8
column col1_dma format a8
column col2_dma format a8
select col1,
col2,
phonic_encode(double_metaphone, col1) as col1_dm,
phonic_encode(double_metaphone, col2) as col2_dm,
phonic_encode(double_metaphone_alt, col1) as col1_dma,
phonic_encode(double_metaphone_alt, col2) as col2_dma
from match_tab;
COL1 COL2 COL1_DM COL2_DM COL1_DMA COL2_DMA
------------ ------------ -------- -------- -------- --------
Peter Parker Pete Parker PTRP PTPR PTRP PTPR
Peter Parker peter parker PTRP PTRP PTRP PTRP
Clark Kent Claire Kent KLRK KLRK KLRK KLRK
Wonder Woman Ponder Woman ANTR PNTR FNTR PNTR
Superman Superman SPRM SPRM SPRM SPRM
The Hulk Iron Man 0LK ARNM TLK ARNM
6 rows selected.
SQL>使用DOUBLE_METAPHONE_ALT时，如果没有替代代码，则返回主代码。
代码的最大长度由可选的第三个参数控制，该参数接受从1到12的整数值。
column col1_dm1 format a9
column col2_dm1 format a9
column col1_dm6 format a9
column col2_dm6 format a9
column col1_dm12 format a9
column col2_dm12 format a9
select col1,
col2,
phonic_encode(double_metaphone, col1, 1) as col1_dm1,
phonic_encode(double_metaphone, col2, 1) as col2_dm1,
phonic_encode(double_metaphone, col1, 6) as col1_dm6,
phonic_encode(double_metaphone, col2, 6) as col2_dm6,
phonic_encode(double_metaphone, col1, 12) as col1_dm12,
phonic_encode(double_metaphone, col2, 12) as col2_dm12
from match_tab;
COL1 COL2 COL1_DM1 COL2_DM1 COL1_DM6 COL2_DM6 COL1_DM12 COL2_DM12
------------ ------------ --------- --------- --------- --------- --------- ---------
Peter Parker Pete Parker P P PTRPRK PTPRKR PTRPRKR PTPRKR
Peter Parker peter parker P P PTRPRK PTRPRK PTRPRKR PTRPRKR
Clark Kent Claire Kent K K KLRKKN KLRKNT KLRKKNT KLRKNT
Wonder Woman Ponder Woman A P ANTRMN PNTRMN ANTRMN PNTRMN
Superman Superman S S SPRMN SPRMN SPRMN SPRMN
The Hulk Iron Man 0 A 0LK ARNMN 0LK ARNMN
6 rows selected.
SQL>PL/SQL支持
在本版本中，PL/SQL中不直接支持FUZZY_MATCH或PHONIC_ENCODE运算符，因此无法进行直接赋值。
declare
l_output number;
begin
l_output := fuzzy_match(levenshtein, 'Peter Parker', 'peter parker');
end;
/
*
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00201: identifier 'FUZZY_MATCH' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
SQL>
declare
l_output varchar2(10);
begin
l_output := phonic_encode(double_metaphone, 'Peter Parker');
end;
/
*
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00201: identifier 'PHONIC_ENCODE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
SQL>我们可以使用SELECT。。。INTO进行分配。
declare
l_output number;
begin
select fuzzy_match(levenshtein, 'Peter Parker', 'peter parker')
into l_output;
end;
/
PL/SQL procedure successfully completed.
SQL>
declare
l_output varchar2(10);
begin
select phonic_encode(double_metaphone, 'Peter Parker')
into l_output;
end;
/
PL/SQL procedure successfully completed.
SQL>