Как преобразовать код пользователя в целое число в AMAZON REDSHIFT

только начинаю экспериментировать и тестировать красное смещение Amazon. Одна вещь, которую мне нужно сделать, что я могу легко сделать в sql, - это изменить userip на целое число. Это делается в mssql с помощью скалярной функции, которая использует parsename для разделения IP-адресов и умножения их на константы.

 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 не является функцией красного смещения, и поэтому возникает мой вопрос. Вы, ребята, знаете, как я могу добиться того же результата?

Догадаться:

(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, LEN ( ЛЕВЫЙ (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)


postgresql sql amazon-redshift
person Pat Rick Allen    schedule 28.06.2013    source источник
comment
в sql - ›В Microsoft SQL Server? SQL - это язык запросов, а не продукт. Спасибо, что упомянули, что вы используете Redshift (проприетарный форк ParAccel чрезвычайно старой версии PostgreSQL), а не просто говорите о PostgreSQL. Кроме того, лучше опубликовать ответ на свой вопрос, чем редактировать его, если это возможно.   -  person Craig Ringer    schedule 29.06.2013

Ответы (2)


arrow_upward
2
arrow_downward

Вау, у меня слезятся глаза при виде этого запроса, хотя я уверен, что у вас нет большого выбора, учитывая ограничения, наложенные 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 и если бы эта функция существовала еще в PostgreSQL 8.1, когда ParAccel разветвлялся от PostgreSQL).

Кстати, я был весьма удивлен, увидев, что в PostgreSQL не определено приведение от inet к bigint, поскольку я ожидал, что смогу просто написать '127.0.0.1'::inet::bigint, что будет сокращением для CAST(CAST('127.0.0.1' AS inet) AS bigint).

person Craig Ringer    schedule 29.06.2013
comment
Redshift не поддерживает функции или типы IP. - person Greg Bowyer; 05.02.2014
comment
+1. . . Первое решение, похоже, работает в RedShift. - person Gordon Linoff; 19.09.2014

arrow_upward
1
arrow_downward

split_part(ip, '.', n) должен это сделать.

person Jakub Kania    schedule 28.06.2013
comment
Я пробовал использовать это уже @jakub, но при использовании amazon redshift split_part не будет работать за пределами главного узла. - person Pat Rick Allen; 29.06.2013
comment
@PatRickAllen Эти вещи стоит упомянуть в исходном вопросе. Я уже пробовал ... Amazon не предлагает удобный способ тестирования материалов на Redshift (без эквивалента SQLFiddle, без бесплатных тестовых серверов и т. Д.), Поэтому, если вы пометите вопрос postgresql люди собираются ответить с ответами, которые работают для PostgreSQL. - person Craig Ringer; 29.06.2013