MySQL 字符串与百分比输出的比较

Question

我试图比较两个 6 个数字的条目，每个数字可以是 0 或 1（即 100001 或 011101）。如果 6 个匹配中的 3 个匹配，我希望输出为 0.5。如果 6 个匹配中的 2 个匹配，我希望输出为 0.33 等。

以下是创建表的 SQL 命令

CREATE TABLE sim
(sim_key int,
 string int);

INSERT INTO sim (sim_key, string)
VALUES (1, 111000);

INSERT INTO sim (sim_key, string)
VALUES (2, 111111);

，我希望输出比较两个字符串，这两个字符串共享 50% 的字符，并输出 50%。

SQL 中可以进行这种比较吗？提前致谢

Answer 1

这将返回两个字符串中相等 1 位的百分比：

select bit_count(conv(a.string, 2, 10) & conv(b.string, 2, 10))/6*100 as percent_match
from sim a, sim b where
a.sim_key=1 and b.sim_key=2;

当您将位字段存储为转换为数字的基数 2 表示形式时，我们首先需要进行转换：conv(a.string, 2, 10)， conv(b.string, 2, 10)。

然后我们只保留每个字段中为 1 的位： conv(a.string, 2, 10) & conv(b.string, 2, 10)

我们对它们进行计数：bit_count(conv(a.string, 2, 10) & conv(b.string, 2, 10))

最后我们只计算百分比：bit_count(conv(a.string, 2, 10) & conv(b.string, 2, 10)) / 6 * 100。

对于 111000 和 111111，查询返回 50。

这是另一个版本，也计算匹配的零：

select bit_count((conv(a.string, 2, 10) & conv(b.string, 2, 10)) | ((0xFFFFFFFF>>(32-6))&~(conv(a.string, 2, 10)|conv(b.string, 2, 10))))/6*100 as percent_match
from sim a, sim b where
a.sim_key=1 and b.sim_key=2;

---

请注意，虽然此解决方案有效，但您实际上应该像这样存储此字段：

INSERT INTO sim (sim_key, string)
VALUES (1, conv("111000", 2, 10));

INSERT INTO sim (sim_key, string)
VALUES (2, conv("111111", 2, 10));

或者更新现有数据：

UPDATE sim SET string=conv(string, 10, 2);

然后此查询给出相同的结果（如果您按照描述更新了数据）上面）：

select bit_count(a.string & b.string)/6*100 as percent_match
from sim a, sim b where
a.sim_key=1 and b.sim_key=2;

也计算零：（

select bit_count((a.string & b.string) | ((0xFFFFFFFF>>(32-6))&~(a.string|b.string)))/6*100 as percent_match
 from sim a, sim b where
 a.sim_key=1 and b.sim_key=2;

用位域的大小替换 6）

Answer 2

由于您将它们存储为数字，因此您可以这样做

SELECT BIT_COUNT(s1.string & s2.string) / BIT_COUNT(s1.string  | s1.string)
FROM sim s1, sim s2
WHERE s1.sim_key = 1 AND s2.sim_key = 2