具有中间Map表的sql匹配表(具有相似字符串的模糊联接)

omtl5h9j 于 2021-08-01 发布在 Java

关注(0)|答案(1)|浏览(410)

我在用bigquery。我有两个简单的表，我们的系统数据质量很差。一个代表收入，另一个代表公共汽车行程的生产行。我需要匹配每个旅程到一个收入交易，但我只有一组字段，没有关键，我真的不知道如何做这个匹配。
以下是数据示例：
收入

Year, Agreement, Station_origin, Station_destination, Product
2020, 123123, London, Manchester, Qwerty

旅行

Year, Agreement, Station_origin, Station_destination, Product
2020, 123123, Kings Cross, Piccadilly Gardens, Qwer
2020, 123123, Kings Cross, Victoria Station, Qwert
2020, 123123, London, Manchester, Qwerty

每个站点最多有9个备选名称，这些名称存储在“station”表中。
车站

Station Name, Station Name 2, Station Name 3,...
London, Kings Cross, Euston,...
Manchester, Piccadilly Gardens, Victoria Station,...

我想先测试表与原始字段的匹配或连接。这将生成一些匹配项，但有许多行程不匹配。对于不匹配的收入行，我想先更改产品名称（缩短为两个字母，并可能从生产表中获得许多匹配项），然后更改站点名称，方法是首先更改station\u origin，然后更改station\u destination。当使用较短的产品名称时，我可能会得到许多匹配项，但我希望生产表中的行具有最常见的产品。像这样：

1. Do a direct match. That is, I can use the fields as they are in the tables.
2. Do a match where the revenue.product is changed by shortening it to two letters. substr(product,0,2)
3. Change the rev.station_origin to the first alternative, Station Name 2, and then try a join. The product or other station are not changed.
4. Change the rev.station_origin to the first alternative, Station Name 2, and then try a join. The product is changed as above with a substr(product,0,2) but rev.station_destination is not changed.
5. Change the rev.station_destination to the first alternative, Station Name 2, and then try a join. The product or other station are not changed.

有人告诉我，也许我应该创建一个包含所有站点和产品组合的中间表，并让rank列决定顺序。站点表中的站点名称按重要性排序，因此“站点名称”比“站点名称2”更重要，依此类推。
我开始用每个列的子查询进行查询，然后进行联合，但是有太多的组合，所以必须有另一种方法来实现。
不知道这是否有任何意义，但我会感谢任何帮助或想法，以更好的方式做这件事。干杯，克里斯

sql google-bigquery

来源：https://stackoverflow.com/questions/62678455/match-tables-with-intermediate-mapping-table-fuzzy-joins-with-similar-strings

1条答案

按热度按时间

snvhrwxg1#

要实现具有近似匹配的复杂连接策略，在javascript中定义策略并从BigQuerySQL查询调用函数可能更有意义。
例如，以下查询执行以下步骤：
以美国前200名男性名字为例。
查找前200名女性姓名中是否有一个匹配。
如果没有，在选项中寻找最相似的女性名字。
注意，选择最近选项的逻辑封装在js udf中 fhoffa.x.fuzzy_extract_one() . 看到了吗https://medium.com/@hoffa/new-在-bigquery-persistent-udfs-c9ea4100fd83中了解更多信息。

WITH data AS (
  SELECT name, gender, SUM(number) c
  FROM `bigquery-public-data.usa_names.usa_1910_2013` 
  GROUP BY 1,2
), top_men AS (
  SELECT * FROM data WHERE gender='M'
  ORDER BY c DESC LIMIT 200
), top_women AS (
  SELECT * FROM data WHERE gender='F'
  ORDER BY c DESC LIMIT 200
)

SELECT name male_name, 
  COALESCE(
    (SELECT name FROM top_women WHERE name=a.name)
    , fhoffa.x.fuzzy_extract_one(name, ARRAY(SELECT name FROM top_women))
  ) female_version
FROM top_men a

赞(0）回复(0）举报 2021-08-01

我来回答

具有中间Map表的sql匹配表(具有相似字符串的模糊联接)

1条答案

相关问题

热门标签

最新问答