我有一个包含7列的mysql表,其中每一行都包含整数值。
我有一个简单的站点,它接收来自用户的值,我必须尝试查看用户发送的值是否与表中的任何行匹配或相似。
因此,用户写入例如1 2 3 4 5 6 7
作为输入。
我必须找出我的表中是否有行在没有顺序的情况下与它相似。所以1 2 3 4 5 6 7 = 7 6 5 4 3 2 1
等等。该表可能包含超过40,000行数据。
我还必须看看它们是否至少有相同的5
、6
或7
数字。
这意味着使用排列来找到所有可能的组合。然而,解决此类问题的最佳方法是什么?
的大表时,内存和CPUusage又如何呢?
发布于 2012-11-26 19:14:12
在完全规范化的模式中,这是一个单一的具有查询的
假设您的主键为:
create table T1
( pk char (1), a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int);
insert into T1 values
('a',1,2,3,4,5,6,7),
('b',2,3,4,5,6,7,8),
('z',10,11,12,13,14,15,16);
此时,我们可以将数据标准化为:
select
pk,
case a
when 1 then a1
when 2 then a2
when 3 then a3
when 4 then a4
when 5 then a5
when 6 then a6
when 7 then a7
end
as v
from T1
cross join
(select 1 as a from dual union all
select 2 as a from dual union all
select 3 as a from dual union all
select 4 as a from dual union all
select 5 as a from dual union all
select 6 as a from dual union all
select 7 as a from dual ) T2
在前面的查询中,很容易将您的需求与一个具有:
select pk
from
(
select
pk,
case a
when 1 then a1
when 2 then a2
when 3 then a3
when 4 then a4
when 5 then a5
when 6 then a6
when 7 then a7
end
as v
from T1
cross join
(select 1 as a from dual union all
select 2 as a from dual union all
select 3 as a from dual union all
select 4 as a from dual union all
select 5 as a from dual union all
select 6 as a from dual union all
select 7 as a from dual ) T2
) T
where
T.v in ( 4,5,6,7,8,9,10)
group by pk
having <-- The Having
count( pk ) > 4
| PK |
------
| b |
发布于 2012-11-26 18:43:56
一种简单的方法可能是在数据库中添加一个额外的字段,它是所有7个字段组合在一起的数字排序版本。
例如:如果数据库中的数据是2 4 7 6 5 1 3,则组合字段将为1234567
然后,在比较时,按数字对用户响应进行排序,并与数据库中的组合字段进行比较。
根据您正在做的事情,您可以像这样编写查询
select * from table where combination like '12%' or combination like '123%'
如果您知道需要的最小匹配数是多少,这将简化查询
找出他们写的东西和数据库里的东西有多相似。您可以使用levenshtein PHP函数:http://php.net/manual/en/function.levenshtein.php
$result = levenshtein($input,$combination);
发布于 2012-11-26 18:46:08
我担心你不能在这样的问题上有效地构建查询。
您可以构建如下的WHERE
子句:
(`1` IN ARRAY(1,2,3,4,5,6,7)
AND `2` IN ARRAY(1,2,3,4,5,6,7)
AND `3` IN ARRAY(1,2,3,4,5,6,7)
AND `4` IN ARRAY(1,2,3,4,5,6,7)
AND `5` IN ARRAY(1,2,3,4,5,6,7))
OR
(`1` IN ARRAY(1,2,3,4,5,6,7)
AND `2` IN ARRAY(1,2,3,4,5,6,7)
AND `3` IN ARRAY(1,2,3,4,5,6,7)
AND `4` IN ARRAY(1,2,3,4,5,6,7)
AND `6` IN ARRAY(1,2,3,4,5,6,7))
-- Each combination
但那将是一种地狱般的状况。另一方面,您可以尝试组合使用以下各项:
首先检查列1
是否包含信息:
IF( `1` IN ARRAY(1,2,3,4,5,6,7), 1, 0)
然后将所有这些数据相加:
SELECT (
IF( `1` IN ARRAY(1,2,3,4,5,6,7), 1, 0) +
IF( `2` IN ARRAY(1,2,3,4,5,6,7), 1, 0) +
IF( `3` IN ARRAY(1,2,3,4,5,6,7), 1, 0) +
IF( `4` IN ARRAY(1,2,3,4,5,6,7), 1, 0) +
IF( `5` IN ARRAY(1,2,3,4,5,6,7), 1, 0) +
IF( `6` IN ARRAY(1,2,3,4,5,6,7), 1, 0) +
IF( `7` IN ARRAY(1,2,3,4,5,6,7), 1, 0)
) AS `matches_cnt`
FROM t1
HAVING `matches_cnt` >= 5
这将遍历所有行,并且条件非常复杂(因此床性能)。
您也可以尝试将值替换为二进制字符串,例如:
1,2,7 = 01000011
然后计算检查记录和数据库之间的Hamming distance,但这只会降低条件的复杂性,但需要迭代所有记录都保持不变。
mysql中的实现使用:
将第一部分替换为:
SELECT (
$MAX_NUMBER$ - BIT_COUNT( XOR( `binary_representation`, $DATA_FROM_USER$))
) AS `matches_cnt`
https://stackoverflow.com/questions/13562570
复制相似问题