组合/值的mysql分布

Question

我有一个 mysql 表，其中包含一些随机数字组合。为简单起见，以下表为例:

index|n1|n2|n3
1     1  2  3
2     4  10 32
3     3  10 4 
4     35  1 2
5     27  1 3 
etc

我想知道的是表中某个组合出现的次数。例如，4 10 或 1 2 或 1 2 3 或 3 10 4 等的组合出现了多少次。

我是否必须创建另一个包含所有可能组合的表并从那里进行比较，或者是否有其他方法可以做到这一点？

Answer 1

对于单个组合，这很简单:

SELECT COUNT(*)
FROM my_table
WHERE n1 = 3 AND n2 = 10 AND n3 = 4

如果您想对多个组合执行此操作，您可以创建一个(临时)表，并将该表与您的数据连接起来，如下所示:

CREATE TEMPORARY TABLE combinations (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  n1 INTEGER, n2 INTEGER, n3 INTEGER
);

INSERT INTO combinations (n1, n2, n3) VALUES
  (1, 2, NULL), (4, 10, NULL), (1, 2, 3), (3, 10, 4);

SELECT c.n1, c.n2, c.n3, COUNT(t.id) AS num
FROM combinations AS c
  LEFT JOIN my_table AS t
    ON (c.n1 = t.n1 OR c.n1 IS NULL)
   AND (c.n2 = t.n2 OR c.n2 IS NULL)
   AND (c.n3 = t.n3 OR c.n3 IS NULL)
GROUP BY c.id;

( demo on SQLize )

请注意，由于 OR c.n，这个查询不是很有效？ IS NULL 子句，MySQL 不够智能，无法对其进行优化。如果您的所有组合都包含相同数量的术语，您可以将它们排除在外，这将允许查询使用数据表上的索引。

附言。对于上面的查询，组合 (1, 2, NULL) 将不匹配 (35, 1, 2)。但是，(NULL, 1, 2) 会，因此，如果您需要两者，一个简单的解决方法是将这两种模式都包含在您的组合表中。

如果您实际上有比您的示例中显示的更多的列，并且您想要匹配出现在任何一组连续列中的模式，那么您真的应该将您的列打包成一个字符串并使用 LIKE 或 REGEXP 查询。例如，如果您将所有数据列连接到一个名为 data 的列中以逗号分隔的字符串，您可以像这样搜索它:

INSERT INTO combinations (pattern) VALUES
  ('1,2'), ('4,10'), ('1,2,3'), ('3,10,4'), ('7,8,9');

SELECT c.pattern, COUNT(t.id) AS num
FROM combinations AS c
  LEFT JOIN my_table AS t
    ON CONCAT(',', t.data, ',') LIKE CONCAT('%,', c.pattern, ',%')
GROUP BY c.id;

( demo on SQLize )

您可以通过使使用 CONCAT() 添加的前缀和后缀成为表中实际数据的一部分来加快此查询的速度，但是如果您这样做，这仍然是一个相当低效的查询有很多数据要搜索，因为它不能使用索引。如果您需要在大型数据集上高效地执行这种子字符串搜索，您可能希望使用比 MySQL 更适合特定目的的东西。