我從兩個不同的來源獲得記錄，目標是在彼此匹配的來源之間創建鏈接。為此，應用了訓練有素的 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
現在我需要從這個表中讀取最有可能src_a用 id 記錄的計數器匹配1。當你用 sql 選擇資料時，SELECT * 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記錄為foo帶有相互分數的id0.8但它不正確。我們可以從另一端查詢來驗證結果。什么是src_b與 id 的計數器匹配foo？使用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匹配src_bid foo。從第二個查詢很明顯，前面的結論是錯誤的，因為src_bIDfoo匹配到src_a的ID 2，因為這對具有較高的相互得分。
考慮到該表將有數千條src_a記錄，我應該如何撰寫查詢以找到與 id 記錄的匹配項1？
我的第一步是在 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火柴bar2火柴foobaz沒有匹配
如何查詢 src_a id1匹配？預期結果是 src_b id bar。而從另一邊。如何查詢 src_b idbar匹配？預期結果是 src_a id 1。
uj5u.com熱心網友回復：
您的問題似乎可以通過使用 window function 來解決而無需遞回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>)
給定 no 查詢將導致得分最高的所有對
src_a_id src_b_id score
-------------------------
1 bar 0.7
2 foo 0.9
編輯：有幾種邊緣情況，上述方法會產生不明確/不正確的結果：
- 給定的所有對的
src_A_id得分低于任何其他共享相同的對src_B_id（查詢是否應該回傳空/0 行/最高的所有行src_A_id？） - 具有相同最高分共享的多對
src_B_id（鑒于 src_A_id 不同，哪一個勝過另一個？） - 多個不同
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）
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/401955.html
標籤：sql PostgreSQL的 递归查询
上一篇：得到錯誤不支持的資料型別：&[]。這是gorm模型 golang/jwt
下一篇：如何在Go中引數化動態查詢