PostgreSQL连接两个值

kx1ctssn 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(169)

我有下面的表格，并试图查找一个几十万个城市的列表的县代码。

create table counties (
  zip_code_from  char(5) not null,
  zip_code_thru  char(5) not null,
  county_code    char(3) not null
);

create table cities (
  city      text    not null,
  zip_code  char(5) not null
);

我的第一个方法是在join中使用“between”：

select
  ci.city, ci.zip_code, co.county_code
from
  cities ci
  join counties co on
    co.zip_code between ci.zip_code_from and ci.zip_code_thru

我知道在甲骨文的世界里，这是不受欢迎的，而且确实表现得很糟糕。它需要超过8分钟来处理大约16，000个城市。邮政编码表有大约80，000条记录。我猜这个语法是一个美化的交叉连接？
from和thru代码都有索引，我可以控制结构，所以如果有帮助的话，我可以更改表。
我唯一的另一个想法是继续将表扩展到所有可能的值-类似于以下内容：

select
  generate_series (
    cast (zip_code_from as int),
    cast (zip_code_thru as int)
  ) as zip_code,
  *
from counties

这将使数据扩展到超过200，000条记录，这不是什么大问题，但我不确定这是否是我唯一的查询不可怕的资源。
我猜即使是在没有索引的情况下动态地做这件事也比在我的join中使用between更好，但我希望有一个替代方案，无论是在我的SQL方面还是在表本身的结构方面。
我已经看到其他DBMS平台发布的这个问题，但是我已经能够用PostgreSQL实现在其他数据库中不可能（或实际）的小奇迹，所以我希望我错过了一些东西。

postgresql

来源：https://stackoverflow.com/questions/31030589/postgresql-joining-between-two-values

2条答案

按热度按时间

dz6r00yl1#

几个月后，这个问题又出现了，我决定测试一下我的一些理论。
原始查询：

select
  ci.city, ci.zip_code, co.fips_code
from
  cities ci
  join counties co on
    ci.zip_code between co.from_zip_code and co.thru_zip_code

实际上实现了笛卡尔坐标。查询返回34，000行，耗时597秒。
如果我将邮政编码范围“预分解”为离散记录：

with exploded_zip as (
  select
    generate_series (
      cast (from_zip_code as int),
      cast (thru_zip_code as int)
    )::text as zip_code,
    *
  from counties
)
select
  ci.city, ci.zip_code, co.fips_code
from
  cities ci
  join exploded_zip co on
    ci.zip_code = co.zip_code

查询返回完全相同的行，但在2.8秒内完成。
因此，底线似乎是在连接（或任何不等式）中使用between是一个非常糟糕的主意。

赞(0）回复(0）举报 2023-06-22

23c0lvtd2#

虽然很晚了，但我最近遇到了使用BETWEEN条件的join的问题。为了把这个问题具体化为最简单的形式，我有两张表

CREATE TABLE customer_review(
  id              SERIAL PRIMARY KEY,
  review_text     TEXT,
  customer_ip_int BIGINT
);
CREATE TABLE ip_location(
  ip_int          BIGINT,
  country         TEXT,
  latitude        NUMERIC,
  longitude       NUMERIC
);

第一个表大约有720万条记录，第二个表大约有300万条记录。连接需要以这样的方式发生，使得customer_ip_int在ip_int (inclusive)和lead(ip_int) OVER(ORDER BY ip_int)之间（不含）。
我尝试了许多方法来重新表述连接条件、各种索引等。大部分工作是在修改后的ip_location的物化视图上完成的，它允许我将next_ip_int作为列引用（而不是在查询本身中摆弄lead(ip_int) ...）。所有这些都没有产生任何远程可行的东西。
上面的解决方案（生成一个序列）对我来说也不可行，因为整数IP地址的序列（与邮政编码相反）跨越了 * 数十亿 * 个整数。
最后，成功的事情很简单（如果有点丑陋）：

SELECT * 
FROM customer_review cr
LEFT JOIN ip_location ip
  ON ip.ip_int = (SELECT MAX(ip2.ip_int) 
                  FROM ip_location ip2 
                  WHERE ip2.ip_int <= cr.customer_ip_int)

请注意，这需要ip_location.ip_int上的索引才有效。

赞(0）回复(0）举报 2023-06-22

我来回答

PostgreSQL连接两个值

2条答案

相关问题

热门标签

最新问答