mysql - 如何在 mySQL 查询中使用模糊分组

Question

这可能吗？我似乎找不到任何合适的指南来设置它。我找到的所有内容都是在 SISS 上给出的，我一点也不熟悉。我发现的其他选项涉及 SOUNDEX()，这与我想做的事情无关，因为数据的语言不是英语。

我将添加一些关于我想要完成的事情的信息:

我试图在数据库中查找重复项，但它们不是完全匹配的重复项。我可以在使用 php 和 levenshtein() 从我的数据库中获取所有记录后执行此操作，但是这种方法的扩展性非常差，因为它需要遍历每条记录并将其与所有未测试的记录进行匹配 - 所以我最终得到 (records .length)! 效率不高的最小步数。

我想在实际的 SQL 查询中进行比较以提高效率并能够在大型数据库(超过 100,000 条记录)上运行此重复测试。

我觉得代码示例与这个问题无关，所以不添加任何内容，如果我应该添加一些具体的内容，请告诉我。

Answer 1

您可以创建一个 levenshtein 函数并使用交叉连接

select 
t1.id,t2.id,
levenshtein(t1.action_date,t2.action_date) as levenshtein
from yourTable t1
CROSS join yourTable t2 on t1.id <> t2.id;

编辑函数

 DELIMITER //

CREATE DEFINER=`root`@`localhost` FUNCTION `levenshtein`( s1 varchar(255), s2 varchar(255) ) RETURNS int(11)
    DETERMINISTIC
BEGIN 
  DECLARE s1_len, 
    s2_len, 
    i, 
    j, 
    c, 
    c_temp, 
    cost          int; 
  declare s1_char char; 
  -- max strlen=255 
  declare cv0, 
    cv1 varbinary(256); 
  set s1_len = char_length(s1), 
    s2_len = char_length(s2), 
    cv1 = 0x00, 
    j = 1, 
    i = 1, 
    c = 0; 
  if s1 = s2 THEN 
  RETURN 0; 
elseif s1_len = 0 THEN 
  RETURN s2_len; 
elseif s2_len = 0 THEN 
  RETURN s1_len; 
  else 
  WHILE j <= s2_len do 
  SET cv1 = concat(cv1, unhex(hex(j))), 
    j = j + 1; 
end WHILE;
WHILE i <= s1_len do 
SET s1_char = substring(s1, i, 1), 
  c = i, 
  cv0 = unhex(hex(i)), 
  j = 1;WHILE j <= s2_len do 
SET c = c + 1;IF s1_char = Substring(s2, j, 1) then 
SET cost = 0; 
else 
SET cost = 1;END IF;SET c_temp = conv(hex(substring(cv1, j, 1)), 16, 10) + cost;IF c > c_temp then
SET c = c_temp;END IF;SET c_temp = conv(hex(substring(cv1, j+1, 1)), 16, 10) + 1;IF c > c_temp then
SET c = c_temp;END IF;SET cv0 = concat(cv0, unhex(hex(c))), 
  j = j + 1;END WHILE;SET cv1 = cv0, 
  i = i + 1;END WHILE;END IF;RETURN c;
END;//

DELIMITER ;

这个函数不是我的。参见:How to add levenshtein function in mysql?

样本

MariaDB [test]> select * from yourTable;
+----+-------------+-------------+---------------------+-------------+
| id | ministry_id | building_id | action_date         | action_type |
+----+-------------+-------------+---------------------+-------------+
|  1 |          14 |        1653 | 2011-12-23 11:22:33 |           1 |
|  2 |          14 |        1653 | 2012-02-29 11:51:12 |           2 |
|  3 |          14 |        1653 | 2013-06-25 11:29:22 |           1 |
|  4 |          14 |        1653 | 2017-05-15 17:01:37 |           2 |
+----+-------------+-------------+---------------------+-------------+
4 rows in set (0.00 sec)

MariaDB [test]> select 
    -> t1.id,t2.id,
    -> levenshtein(t1.action_date,t2.action_date) as levenshtein
    -> from yourTable t1
    -> CROSS join yourTable t2 on t1.id <> t2.id;
+----+----+-------------+
| id | id | levenshtein |
+----+----+-------------+
|  2 |  1 |           7 |
|  3 |  1 |           7 |
|  4 |  1 |           9 |
|  1 |  2 |           7 |
|  3 |  2 |           6 |
|  4 |  2 |           8 |
|  1 |  3 |           7 |
|  2 |  3 |           6 |
|  4 |  3 |           8 |
|  1 |  4 |           9 |
|  2 |  4 |           8 |
|  3 |  4 |           8 |
+----+----+-------------+
12 rows in set (0.09 sec)

MariaDB [test]>