SQL - 查找給定列的最大結果

我有一個關於如何在 PostgreSQL 中為給定結果/條件選擇最佳匹配的問題：

我有一個包含 3 個表的設置。1 是具有可靠性和狀態的結果概覽表| 圖 2 是詳細的細分概覽，其中包含每個細分的詳細資訊 3 是包含目標資訊的總表。

目標是根據表 1 的結果將細分映射到目標上

設置：

表 1：Results_table

DROP TABLE IF EXISTS result_fcd;
CREATE TABLE result_fcd
(
   source_geometry_id integer NOT NULL,
   reliability double precision NOT NULL,
   status text COLLATE pg_catalog."default" NOT NULL,
   geometry geometry(LineString,4326) NOT NULL,
   CONSTRAINT result_fcd_pkey PRIMARY KEY (source_geometry_id)
);

表2：段（連結到原件）

DROP TABLE IF EXISTS  segments;
CREATE TABLE segments
(
   source_geometry_id integer NOT NULL,
   index integer NOT NULL,
   target_link_id integer NOT NULL,
   reversed_target_link boolean NOT NULL,
   start_fraction double precision NOT NULL,
   end_fraction double precision NOT NULL,
   geometry geometry(LineString,4326) NOT NULL,
   CONSTRAINT segments_pkey PRIMARY KEY (source_geometry_id, index)
);

表 3：原件

DROP TABLE IF EXISTS originals;
CREATE TABLE originals
(
   gid integer NOT NULL DEFAULT nextval('originals_gid_seq'::regclass),
   linknr double precision,
   name character varying(50) COLLATE pg_catalog."default",
   direction double precision,
   length numeric,
   anode double precision,
   bnode double precision,
   namenr double precision,
   geom geometry(LineString,28992),
   CONSTRAINT originals_pkey PRIMARY KEY (gid)
)

輸出範例（簡單左連接）

SELECT target_link_id,reversed_target_link,segs.source_geometry_id,reliability
FROM segments segs
LEFT JOIN result_fcd res ON res.source_geometry_id = segs.source_geometry_id
WHERE reliability > 80
AND status = 'match'
LIMIT 10

結果：

target_link_id  reversed_target_link    source_geometry_id  reliability
750 true    6156655 "96.90882243480968"
750 true    6156654 "96.3385845004076"
759 false   299963  "97.88820671302537"
759 false   299962  "90.81686447596023"
759 false   4339973 "83.83639328022115"
981 true    304625  "94.25089913251661"
981 true    304624  "96.521137788089"
981 true    304626  "94.21941014522639"
981 true    304621  "91.93785338486143"
981 true    304622  "95.27831743865612"

現在的目標是將結果合併為：

750 true    6156655 "96.90882243480968"
-- 750  true    6156654 "96.3385845004076"
759 false   299963  "97.88820671302537"
-- 759  false   299962  "90.81686447596023"
-- 759  false   4339973 "83.83639328022115"
-- 981  true    304625  "94.25089913251661"
981 true    304624  "96.521137788089"
-- 981  true    304626  "94.21941014522639"
-- 981  true    304621  "91.93785338486143"
-- 981  true    304622  "95.27831743865612"

我嘗試使用 MAX（可靠性）並按 target_link_id、reversed_target_link、source_geometry_id 分組，但這並沒有真正得到結果。

本質上，我需要每個 target_link_id 性能最好的 source_geometry_id （最大可靠性）

任何人都可以提供幫助嗎？

我已將此數據用作起點：

create table t 
(
   target_link_id int,
   reversed_target_link boolean, 
   source_geometry_id  int, 
   reliability double precision
);

insert into t values
(750, true,    6156655, 96.90882243480968),
(750, true,    6156654, 96.3385845004076),
(759, false,   299963,  97.88820671302537),
(759, false,   299962,  90.81686447596023),
(759, false,   4339973, 83.83639328022115),
(981, true,    304625,  94.25089913251661),
(981, true,    304624,  96.521137788089),
(981, true,    304626,  94.21941014522639),
(981, true,    304621,  91.93785338486143),
(981, true,    304622,  95.27831743865612);

如果將 row_number() 添加到先前的查詢中，則可以通過以下方式獲得所需的結果：

with ct as
(
 select
     target_link_id,
     reversed_target_link,
     source_geometry_id,
     reliability,
     row_number() over (partition by target_link_id order by reliability desc) rn
 from
     t
)
select
   target_link_id,
   reversed_target_link,
   source_geometry_id,
   reliability,
from
   ct
where 
   rn = 1;

target_link_id | reversed_target_link | source_geometry_id | 可靠性 | rn
-------------: | :------------------- | -----------------: | :--------------- | -:
 750 | 噸 | 6156655 | 96.9088224348097 | 1
 第759章 f | 299963 | 97.8882067130254 | 1
 981 | 噸 | 304624 | 96.521137788089 | 1

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/251795