mysql - 获取特定范围/半径内的所有行(文档术语矩阵)

Question

我在 mysql 中存储了一个文档术语矩阵，并希望获得如下查询的结果:

示例:获取 token_id '1' 和 token_id '2'(但可能超过 2 个)在 10 个单词范围内的所有行。

我的 table :

dt_matrix_token_id int(11) PK AUTO_INCREMENT,
token_id int(11),
storage_data_id int(11),
position int(11)

所以基本上 token_id 描述了标记，position 描述了标记在原始文本中的位置。

按 token_id 选择行不是问题，问题在于我如何在查询中描述两个词必须在特定的“半径/范围内”。

Select * FROM dt_matrix_token WHERE token_id IN(1,2) AND ???

??? 这就是我卡住的地方，因为我怎么知道它应该查询找到的值？因为当结果包含 position = 12 的行时，所有其他有效行的 position >= 2 & position =< 22

顺便说一句:它可以类似于半径内的地理位置查询吗？

编辑:这是我在样本数据方面的实际进展:http://sqlfiddle.com/#!2/52f48/2

查询工作正常，但还没有完成，所以如果 2x token 1 在文档中匹配，它也是一个“有效”结果，这当然是错误的。只有当所有给定的标记都存在时，它才是正确的。并且解决方案必须可扩展到 3+ 个 token 。

Answer 1

我将从 dt_matrix_token 表中的查询开始，该表与 dt_matrix_token 表的第二个实例相结合，其中两个实例都有一个 token_id在您感兴趣的值范围内，但它们不能具有相同的值。

它们还应具有匹配的 storage_data_id(即它们在同一文档中)，并且第二个标记的位置必须大于或等于第一个。

SELECT mt1.dt_matrix_token_id, mt1.storage_data_id,
  mt1.token_id AS token_id1, mt2.token_id AS token_id2,
  mt1.position AS position1, mt2.position AS position2
FROM dt_matrix_token AS mt1
JOIN dt_matrix_token AS mt2
WHERE mt1.token_id IN (1,2,3) 
  AND mt2.token_id IN (1,2,3)
  AND mt1.token_id <> mt2.token_id
  AND mt1.storage_data_id = mt2.storage_data_id
  AND mt2.position >= mt1.position 

这会为您提供您关心的每一对连续的标记。

现在，如果您按第一个表中的 dt_matrix_token_id 与第二个表中的 token_id 组合，您可以将该组结果缩小为每个 < em>token_id 从第二个表中获取第一个中的每个标记。

当对第二个表的结果进行分组时，这是您关心的最小位置。由于第二个标记总是跟在第一个标记之后，因此这会为您提供最接近第一个标记的位置。

SELECT mt1.dt_matrix_token_id, mt1.storage_data_id,
  mt1.token_id AS token_id1, mt2.token_id AS token_id2,
  mt1.position AS position1, MIN(mt2.position) AS position2
FROM dt_matrix_token AS mt1
JOIN dt_matrix_token AS mt2
WHERE mt1.token_id IN (1,2,3) 
  AND mt2.token_id IN (1,2,3)
  AND mt2.token_id <> mt1.token_id
  AND mt2.storage_data_id = mt1.storage_data_id
  AND mt2.position >= mt1.position 
GROUP BY mt1.dt_matrix_token_id, mt2.token_id

现在，对于您关心的标记的每个实例，您在同一文档中拥有最接近它后面的任何标记的位置。

但您真正想要的是从第一个标记到它后面的任何标记的最大距离。因此，您需要再次按 dt_matrix_token_id 分组，并计算到第二个位置的最大值(即每个 token_id 的最小值的最大值)的距离。

SELECT dt_matrix_token_id, storage_data_id,
  MAX(position2)-position1 AS distance
FROM (
  SELECT mt1.dt_matrix_token_id, mt1.storage_data_id,
    mt1.position AS position1, MIN(mt2.position) AS position2
  FROM dt_matrix_token AS mt1
  JOIN dt_matrix_token AS mt2
  WHERE mt1.token_id IN (1,2,3) 
    AND mt2.token_id IN (1,2,3)
    AND mt2.token_id <> mt1.token_id
    AND mt2.storage_data_id = mt1.storage_data_id
    AND mt2.position >= mt1.position 
  GROUP BY mt1.dt_matrix_token_id, mt2.token_id
) AS temp
GROUP BY dt_matrix_token_id

但是，并不是第一个表中的每个标记后面都会跟着您关心的所有其他标记。所以你需要确保每组结果的 COUNT 等于你关心的标记数减去一个(第一个表中有 1 个标记，第二个表中有 n-1 个标记) .

您可以使用 HAVING 子句 - HAVING COUNT(*) = 3-1 - 表达式中的 3 表示您要搜索的标记数为。

现在对于您关心的标记的每个实例，其后是您关心的所有其他标记(在同一文档中)，您有覆盖所有这些标记的最短距离。

但是每个文档很可能会有多个结果，您实际上只需要知道每种情况下最短的一个。所以现在您需要按 storage_data_id 分组并计算组中的最小距离。

SELECT storage_data_id, MIN(distance) AS distance
FROM (
  SELECT dt_matrix_token_id, storage_data_id,
    MAX(position2)-position1 AS distance
  FROM (
    SELECT mt1.dt_matrix_token_id, mt1.storage_data_id,
      mt1.position AS position1, MIN(mt2.position) AS position2
    FROM dt_matrix_token AS mt1
    JOIN dt_matrix_token AS mt2
    WHERE mt1.token_id IN (1,2,3) 
      AND mt2.token_id IN (1,2,3)
      AND mt2.token_id <> mt1.token_id
      AND mt2.storage_data_id = mt1.storage_data_id
      AND mt2.position >= mt1.position 
    GROUP BY mt1.dt_matrix_token_id, mt2.token_id
  ) AS temp
  GROUP BY dt_matrix_token_id
  HAVING COUNT(*) = 3-1
) AS temp
GROUP BY storage_data_id

这会为您提供包含所有您关心的标记的每个文档，以及覆盖所有这些标记的最小距离。要将结果限制在特定范围内的距离，您可以添加另一个 HAVING 子句。

HAVING distance <= 20

然后该查询的结果数应该告诉您有多少文档包含指定范围内您关心的所有标记。