如何在ClickHouse中实现IPv4与IPv4范围表的关联查询

核心思路 #

ClickHouse的IPv4类型可通过toUInt32()函数转换为无符号整数，将IPv4的范围匹配转化为整数区间比较，以此实现table1与table2的关联。

基础关联SQL语句 #

SELECT
    t1.user_id,
    t1.ipv4,
    t2.country
FROM table1 t1
LEFT JOIN table2 t2 ON 
    toUInt32(t1.ipv4) >= toUInt32(t2.range_start)
    AND toUInt32(t1.ipv4) <= toUInt32(t2.range_end)

处理重复匹配场景 #

若存在单个IPv4同时落在多个区间的情况（如示例中1.0.0.1同时属于US和JP的区间），可通过窗口函数指定优先级（比如优先匹配更短的区间）：

WITH joined_data AS (
    SELECT
        t1.user_id,
        t1.ipv4,
        t2.country,
        toUInt32(t2.range_end) - toUInt32(t2.range_start) AS range_length,
        ROW_NUMBER() OVER (
            PARTITION BY t1.user_id 
            ORDER BY range_length ASC
        ) AS rn
    FROM table1 t1
    LEFT JOIN table2 t2 ON 
        toUInt32(t1.ipv4) >= toUInt32(t2.range_start)
        AND toUInt32(t1.ipv4) <= toUInt32(t2.range_end)
)
SELECT
    user_id,
    ipv4,
    country
FROM joined_data
WHERE rn = 1

测试示例数据 #

可通过临时表验证效果：

-- 创建table1并插入测试数据
CREATE TEMPORARY TABLE table1 (user_id Int32, ipv4 IPv4) ENGINE = Memory;
INSERT INTO table1 VALUES (122222, toIPv4('1.0.0.1')), (323232, toIPv4('1.0.0.2'));

-- 创建table2并插入测试数据
CREATE TEMPORARY TABLE table2 (range_start IPv4, range_end IPv4, country String) ENGINE = Memory;
INSERT INTO table2 VALUES 
    (toIPv4('1.0.0.1'), toIPv4('1.0.0.256'), 'US'),
    (toIPv4('1.0.0.1'), toIPv4('2.0.0.256'), 'JP');

执行关联SQL后，即可得到对应user_id匹配的country结果。

内容的提问来源于stack exchange，提问作者badkor