如何在SQL中实现向量同索引三元组组合查询？

你的核心问题是拆分向量时没保留元素的索引位置，导致关联时出现所有元素的交叉组合，而非同索引的元素配对。下面针对主流SQL引擎给出解决方案：

方案思路 #

1. 拆分每个向量时，同时记录元素的索引位置（比如第0位、第1位...）；
2. 按索引位置关联三个拆分后的结果集，确保取到同索引的元素；
3. 筛选出不重复的三向量组合（用X.id < Y.id < Z.id避免重复组合，比如A0,A1,A2和A1,A0,A2视为同一组合）；
4. 将三个同索引元素拼接成新向量。

适用于Hive/Spark SQL的实现 #

这类引擎支持posexplode函数，直接返回元素和对应的索引：

SELECT
    x.id AS id1,
    y.id AS id2,
    z.id AS id3,
    concat_ws(',', x.element, y.element, z.element) AS vector
FROM
    (SELECT id, pos, element FROM vector LATERAL VIEW posexplode(split(vector, ',')) t AS pos, element) x
JOIN
    (SELECT id, pos, element FROM vector LATERAL VIEW posexplode(split(vector, ',')) t AS pos, element) y
    ON x.pos = y.pos AND x.id < y.id
JOIN
    (SELECT id, pos, element FROM vector LATERAL VIEW posexplode(split(vector, ',')) t AS pos, element) z
    ON y.pos = z.pos AND y.id < z.id
ORDER BY
    id1, id2, id3, x.pos;

适用于PostgreSQL的实现 #

PostgreSQL用unnest ... WITH ORDINALITY来获取元素和索引：

SELECT
    x.id AS id1,
    y.id AS id2,
    z.id AS id3,
    concat(x.element, ',', y.element, ',', z.element) AS vector
FROM
    (SELECT id, unnest(string_to_array(vector, ',')) AS element, ordinality - 1 AS pos FROM vector WITH ORDINALITY) x
JOIN
    (SELECT id, unnest(string_to_array(vector, ',')) AS element, ordinality - 1 AS pos FROM vector WITH ORDINALITY) y
    ON x.pos = y.pos AND x.id < y.id
JOIN
    (SELECT id, unnest(string_to_array(vector, ',')) AS element, ordinality - 1 AS pos FROM vector WITH ORDINALITY) z
    ON y.pos = z.pos AND y.id < z.id
ORDER BY
    id1, id2, id3, x.pos;

关键说明 #

• split(vector, ',')（Hive/Spark）或string_to_array(vector, ',')（PostgreSQL）先把逗号分隔的向量转成数组；
• posexplode/WITH ORDINALITY负责拆分数组并返回每个元素的索引位置，确保三个表能按索引精准关联；
• concat_ws/concat用来把三个元素拼接成新的逗号分隔向量；
• x.id < y.id < z.id确保每个三向量组合只出现一次，避免重复生成顺序不同的相同组合。

内容的提问来源于stack exchange，提问作者Sidious0907