mysql - k mysql 中的最近邻

Question

我在 MySQL 中有下表:

DATE   EDGE   VALUE
D      E1       X1
D      E2       Y1
D      E3       Z1


D1      E1       X2
D1      E2       Y2
D1      E3       Z2


D2      E1       X3
D2     E2       Y3
D2      E3       Z3

现在我想计算 D 到 D1 和 D 到 D2 的欧几里得距离，例如距离(D-D1)= Sqrt((X1-X2)^2 +(Y1-Y2)^2 +(Z1-Z2)^2 );距离(D-D2)= Sqrt((X1-X3)^2 +(Y1-Y3)^2 +(Z1-Z3)^2 );......等等..

根据这个距离，我想选择 D 的“k”个最近邻居。(请注意，记录 D 可能具有任意数量的边条目(E1、E2...En)。在这种情况下，其他 D1、D2、D3 将具有相同数量的边条目...

请建议我解决方案作为 MySQL 中的存储过程...

提前致谢

<小时/>

@eggyal:

我尝试构建与您回答的类似的查询。

查询:

SELECT   b.id,SQRT(SUM(POW(a.score - b.score, 2))) score1
FROM     (select * from data d1 where  d1.id = (select max(t1.id) from Timestamp t1) 
and d1.edge_id in (select m1.src_edge from mapping m1
where m1.dst = (select m2.src from mapping m2 where m2.src_edge=2 limit 1))) a
JOIN (select * from data d2 where d2.id in ( select t2.id from Timestamp t2 where DAYOFWEEK(NOW())=DAYOFWEEK(t2.timestamp)) and d2.edge_id in (select m3.src_edge from mapping m3 
where m3.dst = (select m4.src from mapping m4 
where m4.src_edge=2 limit 1))) as b
ON b.id <> a.id AND b.edge_id = a.edge_id 
GROUP BY b.id
ORDER BY score1
LIMIT    5;

然而，这不是一个设计良好的查询。请建议上述查询所需的所有改进..

提前致谢

用于上述查询的表:数据表:

CREATE TABLE `data` (
  `id` bigint(20) DEFAULT NULL,
  `edge_id` bigint(20) NOT NULL,
  `score` int(11) NOT NULL,
  KEY `edge_id` (`edge_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (edge_id)
(PARTITION p0 VALUES LESS THAN (10000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (30000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (40000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (50000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (60000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (70000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (80000) ENGINE = InnoDB) */$$

映射表；

CREATE TABLE `mapping` (
  `dst` bigint(20) NOT NULL DEFAULT '0',
  `src` bigint(20) DEFAULT NULL,
  `src_edge` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`dst`,`src_edge`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

时间戳表:

CREATE TABLE `Timestamp` (
  `timestamp` datetime NOT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `time` (`timestamp`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1$$

Answer 1

如 my comment above 中所述，为什么不简单地执行自连接并按计算的距离排序？

SELECT   b.date
FROM     my_table a
    JOIN my_table b ON b.date <> a.date AND b.edge = a.edge
WHERE    a.date = ?
GROUP BY b.date
ORDER BY SUM(POW(a.value - b.value, 2))
LIMIT    ?

查看 sqlfiddle .