时间戳

首页 > 解决方案 > 检测和填充 PostgreSQL 街道地址数据中的缺失数据

sql - 检测和填充 PostgreSQL 街道地址数据中的缺失数据

问题描述

我有一个从公共数据源构建的芬兰街道地址数据库。格式是<street name> <number> [<a possible suffix of letters and dashes>]，例如Aurakatu 8或Aurakatu 12b。在对源数据进行一些过滤以删除异常或垃圾数据之后，数据库大约有 180 万行。其中大约 195k 包含后缀。

源数据合理但不完整。我要处理的问题是：街道名称+号码组合存在IRL（例如Aurakatu 12），但数据仅包含字母后缀形式（Aurakatu 12a和Aurakatu 12b）。例如，所有三种形式都是有效的，并指向谷歌地图中的离散位置。

总而言之，这就是我想要实现的目标：在地址表中找到仅存在后缀版本的每条街道名称 + 号码组合，并创建一个不带后缀的条目。

在上述示例的情况下，查询将发现对于假设的Aurakatu 12街道名称/号码组合，仅存在后缀版本12a，12b并将创建普通12版本。

数据导入在设置（或显着更新）服务器实例时很少运行，因此最大效率并不是最重要的。

street_name并且number是表格中单独的表格列addresses。数字本身不一定是连续的；Somestreet 24像存在但Somestreet 25不存在的东西很常见。

标签： sqlstringpostgresqlcountsql-insert

解决方案

考虑：

insert into addresses (street_name, street_number)
select street_name, regexp_replace(street_number, '\D+$', '')
from addresses
group by 1, 2
having count(*) filter(where street_number ~ '\d$') = 0

该regexp_replace()表达式去除字符串末尾的（潜在的）尾随非数字字符；street_name然后查询将所有具有相同和 (stripped)的行组合在一起street_number。然后该having子句过滤掉已经包含无后缀street_number（即以数字结尾的街道号码）的组：将剩余的内容插入表中。

您可以（并且应该！）select首先独立运行查询以查看将插入的内容。

推荐阅读