sql - 如何在 AMAZON REDSHIFT 中将 userip 转换为整数

Question

刚开始尝试和测试 amazon 的 Redshift 。我需要做的一件事我可以在 sql 中轻松完成，就是将 userip 更改为整数。这是在 mssql 中使用标量函数完成的，该函数使用 parsename 分解 ip numbres 并通过常量乘以它们。

 CAST(

       (CAST(PARSENAME(@IP,4) AS BIGINT) * 16777216) +
       (CAST(PARSENAME(@IP,3) AS BIGINT) * 65536) +
       (CAST(PARSENAME(@IP,2) AS BIGINT) * 256) +
        CAST(PARSENAME(@IP,1) AS BIGINT) 
  AS BIGINT)

这就是它的样子，供引用。

正如我所料，parsename 不是 redshift 中的函数，因此出现了我的问题。你们知道我可以达到相同结果的方法吗？

想通了:

( LEFT(ip_address, STRPOS(ip_address, '.')-1) * 16777216) + (LEFT(SUBSTRING(ip_address, LEN(LEFT(ip_address, STRPOS(ip_address, '.')+1)), LEN (ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2) , STRPOS( SUBSTRING(ip_address, LEN(LEFT(ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2), '.')-1) * 65536) + (RIGHT( SUBSTRING(ip_address, LEN(LEFT( ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS( REVERSE(ip_address), '.')-1)) - 2), LEN(SUBSTRING(ip_address, LEN(LEFT(ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT (ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2)) - STRPOS(SUBSTRING(ip_address) , 莱恩 (大号EFT(ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2), '.') ) * 256) + (REVERSE( LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')- 1) ) * 1 )

Answer 1

哇，看到这个查询我的眼睛都湿润了，不过我敢肯定，鉴于 Redshift 施加的限制，您没有太多选择。

我仍然很惊讶你必须做一些非常麻烦的事情。您不能至少创建一个或两个 SQL 函数来整理它吗？或者 Redshift 甚至不支持 CREATE FUNCTION ... LANGUAGE sql？

作为引用，在适当的 PostgreSQL 中你会这样做:

select (split_part(ip, '.', 1)::bigint << 24) +
       (split_part(ip, '.', 2)::bigint << 16) +
       (split_part(ip, '.', 3)::bigint << 8) +
       (split_part(ip, '.', 4)::bigint);

或者使用一个简单的 SQL 函数:

CREATE OR REPLACE FUNCTION inet_to_bigint(inet) AS $$
SELECT sum(split_part($1::text,'.',octetnum)::bigint << (32 - octetnum*8))
FROM generate_series(1,4) octetnum;
$$ LANGUAGE sql;

或者，几乎可以肯定最有效的方法是滥用 inet 数据类型的减法运算符:

SELECT (ip - '0.0.0.0')

(如果 Redshift 保留了 inet 数据类型，并且当 ParAccel 从 PostgreSQL fork 出来时，该功能在 PostgreSQL 8.1 中就存在的话，这个功能甚至可以在 Redshift 中使用。

附带说明一下，我很惊讶地看到在 PostgreSQL 中没有从 inet 到 bigint 的转换定义，因为我希望能够编写 '127.0.0.1 '::inet::bigint，它是 CAST(CAST('127.0.0.1' AS inet) AS bigint) 的简写。