Postgres 中的递归查询(基于分数的相互匹配)

Question

我从两个不同的来源获得了记录，目标是在彼此匹配的来源之间创建链接。为此，应用了经过训练的 AI 模型，该模型将每条记录从源 A 分配给源 B 的每个记录的匹配概率分数。然后，表score如下所示。

src_a_id    src_b_id    score
-----------------------------
1           foo         0.8
1           bar         0.7
1           baz         0.6
2           foo         0.9
2           bar         0.5
2           baz         0.3

现在我需要从此表中读取与 id1src_a记录最有可能的计数器匹配。当您使用 sqlSELECT * FROM score WHERE src_a_id = 1 ORDER BY score DESC;选择数据时，您将获得此结果。

src_a_id    src_b_id    score
-----------------------------
1           foo         0.8
1           bar         0.7
1           baz         0.6

在这里，看起来第一行是我正在寻找的结果，因此计数器匹配是src_b记录，带有 idfoo具有相互得分0.8但不正确。我们可以从另一端查询以验证结果。什么是 idfoosrc_b的计数器匹配？使用 sqlSELECT * FROM score WHERE src_b_id = 'foo' ORDER BY score DESC;我们得到结果：

src_a_id    src_b_id    score
-----------------------------
2           foo         0.9
1           foo         0.8

从第一个查询开始，它看起来像src_aid1与 idfoosrc_b匹配。 从第二个查询中可以清楚地看出，之前的结论是错误的src_b因为 idfoo与src_aid2匹配，因为这对具有更高的互分。

考虑到该表将有数千条记录，我应该如何编写查询以查找与具有 id1src_a记录的匹配项？

我的第一步是在 Postgres 中搜索一些递归查询，但我发现的教程不适合我的用例，老实说，到目前为止，我完全无法组成任何工作应用程序。

编辑

对于创建测试数据的演示语法：

CREATE TABLE score (
src_a_id integer NOT NULL,
src_b_id varchar(255) NOT NULL,
score decimal(3,2) NOT NULL
);
INSERT INTO score (src_a_id, src_b_id, score)
VALUES 
(1, 'foo', 0.8),
(1, 'bar', 0.7),
(1, 'baz', 0.6),
(2, 'foo', 0.9),
(2, 'bar', 0.5),    
(2, 'baz', 0.3);

从测试数据可以得出存在两对。

• 1匹配bar
• 2比赛foo
• baz没有匹配项

如何查询src_a ID1匹配？预期结果是 src_b idbar。从另一边。如何查询src_b IDbar匹配项？预期结果是 src_a id1。

Answer 1

似乎可以通过使用窗口函数row_number() over(<partition>)来解决您的问题。你想要的是找到这样的对，其中每个ID的分数是最大的。

给定您提供的示例数据集 - 我们可以编写此 CTE，其中我们有 2 个行号(每个 id 一个)，然后将它们相加以得到一对的排名：

with ranks as (
select 
src_a_id, 
src_b_id, score,
row_number() over (partition by src_b_id order by score desc) src_b_idx,
row_number() over (partition by src_a_id order by score desc) 
+ row_number() over (partition by src_b_id order by score desc)  pair_rank
from score
)

这样，你会得到这个结果：

src_a_id  src_b_id  score   pair_rank
-------------------------------------
1         bar        0.7    3
1         baz        0.6    5
1         foo        0.8    3
2         bar        0.5    5
2         baz        0.8    3
2         foo        0.9    2

现在，您可以选择pair_rank最小的对

select src_a_id, src_b_id, score from (
select src_a_id, src_b_id, score, 
row_number() over (partition by src_a_id order by pair_rank, src_b_idx) as index
from ranks
) data where index = 1 and <CONDITION> (e.g. src_a_id = <YOUR ID>)

---

如果否，查询将产生分数最高的所有对

src_a_id  src_b_id  score
-------------------------
1         bar       0.7
2         foo       0.9

>编辑： 在几种边缘情况下，上述方法会产生模棱两可/不正确的结果：

1. 给定src_A_id的所有对的分数低于共享相同src_B_id的任何其他对(查询是否应返回 null/0 行/所有src_A_id中最高？
2. 具有相同最高分的多对共享src_B_id(鉴于src_A_id不同，哪一个胜过另一个？
3. 多个不同的src_A_id为同一提供最高分src_B_id(同样，鉴于src_A_id和src_B_id相同，哪一个胜过另一个？

给定以下数据集，您可以观察所有 3 种情况：

src_a_id src_b_id  score
------------------------
1    foo       0.8  |
1    bar       0.7  | -> all pairs are beanten by some other src_a_id
1    baz       0.6  |
2    foo       0.9
2    bar       0.5
2    baz       0.8  -> higest for `baz`, but 3.baz has the same score
3    foo       0.91 |
3    bar       0.91 | -> both pairs are the higest, but share src_a_id
3    baz       0.8

这是改编的脚本，但您可以根据所需的行为进行调整：

b_rank as (
select 
src_a_id, src_b_id,
rank() over (partition by src_b_id order by score desc) src_b_idx
from score
)
select src_a_id, src_b_id, score from (
select 
rank() over (partition by s.src_a_id order by score desc) score_rank, s.* 
from score s
join b_rank b on s.src_a_id = b.src_a_id and s.src_b_id = b.src_b_id and src_b_idx = 1
) data where score_rank = 1 and src_a_id = XXX

其中产生：

• null是否取所有对(示例src_a_id= 1)
• 得分最高的对，即使相同的分数由另一个src_b_id共享(示例src_a_id= 2)
• 如果所有这些对在给定相同src_a_id的情况下得分最高，则多行(示例src_a_id= 3)