时间戳

首页 > 解决方案 > 如果值在列之间的范围内，则左连接

sql - 如果值在列之间的范围内，则左连接

问题描述

在 BigQuery 中，我有一个完整的 IP 主表（十进制形式），还有另一个用作 IP 范围黑名单的表。我想检查一个IP是否属于黑名单。

问题是黑名单包含 IP 范围，所以我必须检查有问题的 IP 是否是两个范围列之间的值。

主 ip_int_table

ip_int
12345678
22400006
22400005

第二个 ip_int_blacklist_table

ban_id from_ip_int to_ip_int
0 12345678 22345678
1 22345679 22345680
2 22400000 22400005

想要的结果

ip_int is_banned
12345678 1
22400006 0
22400005 1

这是我尝试过的 SQL，但我收到一条错误消息，提示如果没有字段相等，我无法使用左连接。（但我没有任何匹配的键，所以我不能使用相等）。

SELECT
  ip.ip_int
FROM ip_int_table ip
LEFT JOIN ip_int_blacklist_table bl
  ON ip.ip_int BETWEEN bl.from_ip_int AND bl.to_ip_int

编辑：我尝试了UNNEST(GENERATE_ARRAY(...))解决方案，但由于一个范围可以包含大量 IP 地址，我收到错误“GENERATE_ARRAY ...产生了太多元素”（显然 BigQuery 的上限为 1048575 个元素）。

2021-08-24 更新

所以我意识到我实际上并不需要ban_id. 我只需要知道IP是否被禁止。因此，我ban_id将输出中的列替换为is_banned（其中 0 表示假，1 表示真）。

标签： sqlgoogle-bigquery

解决方案

在 BigQuery 中，您可以展开这些值：

SELECT ip.ip_int, bl.ban_id
FROM ip_int_table ip LEFT JOIN
     (ip_int_blacklist_table bl CROSS JOIN
      UNNEST(GENERATE_ARRAY(bl.from_ip_int, bl.to_ip_int, 1)) bl_ip_int
     )
     ON ip.ip_int = bl.bl_ip_int

推荐阅读