参加范围的最佳方式？ [英] Best way to join on a range?

问题描述

我认为这可能是一个普遍的问题，可能无法解决所有工具。现在，我们正在尝试使用Amazon Redshift。现在唯一的问题是我们正在尝试查找IP地址的邮政编码。将IP连接到城市的表格是IP转换为整数的范围。

I think this may be a common problem that may not have an answer for every tool. Right now we are trying to use amazons Redshift. The only problem we have now is we are trying to do a look up of zip code for an IP address. The table we have that connects IP to city is a range by IP converted to an integer.

示例：

Start IP | End IP  | City

| 123123 | 123129 | Rancho Cucamonga|

我已经尝试了intip> = startip和intip<

I have tried the obvious inner join on intip >= startip and intip < endip.

有人知道这样做的好方法吗？

Does anyone know a good way to do this?

推荐答案

从PostgreSQL 9.2 开始，您可以使用新的 范围类型 ， int4range 或 int8range 。

Beginning with PostgreSQL 9.2 you could use one of the new range types,int4range or int8range.

CREATE TABLE city (
  city_id serial PRIMARY KEY 
 ,ip_range int4range
 ,city text
 ,zip  text
);

然后您的查询可以简单地是：

Then your query could simply be:

SELECT c.zip
FROM   city_ip 
WHERE  $intip <@ i.ip_range;

< @ .. 元素包含在

使用GiST索引可以使大表快速移动：

To make this fast for a big table use a GiST index:

CREATE INDEX city_ip_range_idx ON city USING gist (ip_range);

但是我怀疑Amazon Redshift是最新的。我们最近还有其他人遇到问题：

在redshift中使用sql函数generate_series（）

But I doubt Amazon Redshift is up to date. We had other people with problems recently:
Using sql function generate_series() in redshift

这篇关于参加范围的最佳方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！