首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >地理同义词

地理同义词
EN

Database Administration用户
提问于 2013-12-16 21:40:44
回答 1查看 154关注 0票数 3

假设我有一个表代表主要的国家司(例如国家):

代码语言:javascript
复制
create table principal_country_divisions (
  id int primary key,
  name text not null,
  country_code char(2)
);

insert into principal_country_divisions values (1, 'New York', 'US');

我希望用户能够通过“纽约”、“纽约”或“纽约州”这样的同义词轻松找到纽约。

所以我有一个同义词表:

代码语言:javascript
复制
create table synonyms (
  syn text,
  name text,

  primary key (syn, name)
);

insert into synonyms values 
('NY', 'New York'),
('New York State', 'New York');

什么是有效和容易的方式来查询这一点,并返回一个记录的纽约?

特别是,他们应该能够找到默认名称'New York‘或任何同义词的结果:

代码语言:javascript
复制
select * from principal_country_divisions where name = 'NY';
result: {1, 'New York', 'US'}

我想我会从这样的开始:

代码语言:javascript
复制
select
id,
name,
country_code
from principal_country_divisions a
where name = 'NY'
or exists (select 1 from synonyms where name = a.name and syn = 'NY')

我可以只用一个视图来完成这个任务,还是应该使用一个函数?

EN

回答 1

Database Administration用户

发布于 2013-12-17 05:09:15

首先,在principal_country_divisions上有一个整数主键。使用它。由于多种原因(存储大小、索引大小、更快的整数算法、不涉及排序规则、固定长度),比通过name连接更有效。

代码语言:javascript
复制
create table principal_country_divisions (
  country_id    int primary key
  ,name         text not null
  ,country_code char(2)
);

create table synonyms (
   country_id int REFERENCES principal_country_divisions (country_id)
  ,syn        text
  ,primary    key (syn, country_id)
);

syn需要索引的第一列(pk),你已经说对了。所附索引自动涵盖synonyms.syn上的等式测试。

确保在principal_country_divisions.name上添加索引:

代码语言:javascript
复制
CREATE INDEX foo ON principal_country_divisions (name);

如果您想要匹配模式,而不是整个字符串,那么任务就会变得更加复杂。

接下来,你怎么能确定

把一张唱片还给纽约?

显然,namesyn是一样的。这两列都没有唯一的约束,甚至在syn上也没有唯一的约束。否则,EXISTS查询是一种很好的方法--通常是快速的。你只需要避免多行。EXISTS的额外好处是仅从synonyms中消除重复项,但pk排除了这一点。这对本案来说可能更快:

代码语言:javascript
复制
SELECT DISTINCT ON (1)
       a.country_id, a.name, a.country_code
FROM   principal_country_divisions a
LEFT   JOIN synonyms               s USING (country_id)
WHERE  a.name = 'NY'
OR     s.syn  = 'NY'
-- ORDER BY 1, <more expressions to pick from peers>

正如您所评论的,LEFT JOIN是为了保留name中的查找结果。

在多个查找的情况下,可以通过添加更多的ORDER BY表达式来选择要选择的内容。不过,领先专栏必须同意DISTINCT ON的观点。有关答案的详细资料。

票数 4
EN
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://dba.stackexchange.com/questions/55142

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档