postgresql - 为连接表查找(更大的)最接近的值

Question

我的 PostgreSQL 中有两个表。

• 地址(街道、城市、 zip )
• ZIP(zip、gps_lat、gps_long)

两个 ZIP 都是 CHAR(5)，但每个 ZIP 的格式都是:'XXXXX' 5 个数字。示例:“55555”或“12345”。

我想选择所有包含 ZIP 的地址。问题是，一些地址有 zip，它不在 ZIP 表中。对于这种情况，我想加入 NEAREST (BIGGER) zip 值。

我为它创建了数据库函数(psc == zip):

CREATE OR REPLACE FUNCTION lekari.get_psc(pscx character)
RETURNS character
LANGUAGE plpgsql
AS $function$
begin
    if exists (select 1 from spravni_celky.zip where psc = pscx) then
        return pscx;
    end if;

    while not exists (select 1 from spravni_celky.zip where psc = pscx) loop  
        pscx =  cast(cast(pscx as integer) + 1 as char(5));
    end loop;

return pscx;
end;$function$;

然后创建简单的选择:

select * from lekari.address lad
join spravni_celky.zip p on p.psc = lekari.get_psc(lad.psc)

它有效，但是对于 ADDRESS 中的 12 行(ZIP 中大约 200 行)，查询时间几乎是 4 分钟!

Plan B 在数据库中存储了两个 ZIP，一个当前的，一个用于加入。

非常感谢!

Answer 1

demo:db<>fiddle

SELECT DISTINCT ON (a.city, a.street, a.zip)
    *
FROM
    address a
JOIN
    zip z
ON a.zip <= z.zip
ORDER BY a.city, a.street, a.zip, z.zip

加入所有等于或大于的 ZIP。然后给出每个加入地址的第一条记录。

如果 zip 存在:第一个合适的 ZIP 等于。所以最先排序的记录是自己的zip。

如果 zip 不存在:第一个合适的 ZIP 是下一个更大的。

DISTINCT ON 给出有序组的第一条记录。