PostgreSQL Oracle 兼容性之 - BIT_TO_NUM , BITAND , 比特运算 , 比特与整型互相转换
背景
比特类型转换为整型，整型转换为比特类型，以及整型的比特运算。
在数据分析时被经常使用，例如对多个用0和1表示的标签字段叠加，使用一个整型表示。
又或者将数字表述的标签信息转换为比特位，以获取分散的标签信息。
在Oracle中可以使用bit_to_num将多个0,1转换为number，使用bitand对两个number进行比特与运算得到另一个number。
-
bit_to_num(exp1, exp2, …., expn)
BIN_TO_NUM converts a bit vector to its equivalent number. Each argument to this function represents a bit in the bit vector.
This function takes as arguments any numeric datatype, or any nonnumeric datatype that can be implicitly converted to NUMBER.
Each expr must evaluate to 0 or 1.
This function returns Oracle NUMBER.
例子
SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL;
BIN_TO_NUM(1,0,1,0)
-------------------
10
-
BITAND(exp1, exp2)
The BITAND function treats its inputs and its output as vectors of bits; the output is the bitwise AND of the inputs.
The types of expr1 and expr2 are NUMBER, and the result is of type NUMBER. If either argument to BITAND is NULL, the result is NULL.
The arguments must be in the range -(2(n-1)) .. ((2(n-1))-1). If an argument is out of this range, the result is undefined.
The result is computed in several steps. First, each argument A is replaced with the value SIGN(A)*FLOOR(ABS(A)).
This conversion has the effect of truncating each argument towards zero. Next, each argument A (which must now be an integer value) is converted to an n-bit two’s complement binary integer value.
The two bit values are combined using a bitwise AND operation. Finally, the resulting n-bit two’s complement value is converted back to NUMBER.
例子
SELECT BITAND(6,3) FROM DUAL;
BITAND(6,3)
-----------
2
SELECT BITAND(
BIN_TO_NUM(1,1,0),
BIN_TO_NUM(0,1,1)) "Binary"
FROM DUAL;
Binary
----------
2
PostgreSQL BIT_TO_NUM
PostgreSQL的bit转换为整型有隐式的转换，与显示的转换。
固定长度的bit到整型是可以使用隐式转换的，而varbit到整型则需要显示转换。
隐式转换例子
postgres=# select '1010'::bit(4)::int;
int4
------
10
(1 row)
postgres=# select '1010'::varbit::int;
ERROR: cannot cast type bit varying to integer
LINE 1: select '1010'::varbit::int;
^
显示转换例子
postgres=# select int4(bit(4) '1010');
int4
------
10
(1 row)
postgres=# select int4(varbit(4) '1010');
int4
------
10
(1 row)
postgres=# select int4(varbit '1010');
int4
------
10
(1 row)
自定义bit_to_num函数，注意它是动态输入变量
postgres=# create or replace function bit_to_num(VARIADIC arr int[]) returns numeric as $$
declare
mid int2; res numeric;
begin
foreach mid in array arr
loop
if mid not in (0,1) then
raise 'must 1 or 0';
end if;
end loop;
res := (int8(array_to_string(arr,'')::varbit))::numeric;
return res;
end;
$$ language plpgsql;
CREATE FUNCTION
测试
postgres=# select bit_to_num(1,0,1,0,1,0,2);
ERROR: must 1 or 0
postgres=# select bit_to_num(1,0,1,0);
bit_to_num
------------
10
(1 row)
PostgreSQL 多种类型 bitnot bitand bitor bitxor bit 左右移动
PostgreSQL 有操作符直接支持整型、BIT类型以及MAC地址类型的比特操作，如下
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Function | Description
------------+------+-----------------------------+-----------------------------+-----------------------------+--------------------------+------------------------------------------------------------------
pg_catalog | # | bigint | bigint | bigint | int8xor | bitwise exclusive or
pg_catalog | # | bit | bit | bit | bitxor | bitwise exclusive or
pg_catalog | # | integer | integer | integer | int4xor | bitwise exclusive or
pg_catalog | # | smallint | smallint | smallint | int2xor | bitwise exclusive or
pg_catalog | & | bigint | bigint | bigint | int8and | bitwise and
pg_catalog | & | bit | bit | bit | bitand | bitwise and
pg_catalog | & | inet | inet | inet | inetand | bitwise and
pg_catalog | & | integer | integer | integer | int4and | bitwise and
pg_catalog | & | macaddr | macaddr | macaddr | macaddr_and | bitwise and
pg_catalog | & | smallint | smallint | smallint | int2and | bitwise and
pg_catalog | << | bigint | integer | bigint | int8shl | bitwise shift left
pg_catalog | << | bit | integer | bit | bitshiftleft | bitwise shift left
pg_catalog | << | integer | integer | integer | int4shl | bitwise shift left
pg_catalog | << | smallint | integer | smallint | int2shl | bitwise shift left
pg_catalog | >> | bigint | integer | bigint | int8shr | bitwise shift right
pg_catalog | >> | bit | integer | bit | bitshiftright | bitwise shift right
pg_catalog | >> | integer | integer | integer | int4shr | bitwise shift right
pg_catalog | >> | smallint | integer | smallint | int2shr | bitwise shift right
pg_catalog | | | bigint | bigint | bigint | int8or | bitwise or
pg_catalog | | | bit | bit | bit | bitor | bitwise or
pg_catalog | | | inet | inet | inet | inetor | bitwise or
pg_catalog | | | integer | integer | integer | int4or | bitwise or
pg_catalog | | | macaddr | macaddr | macaddr | macaddr_or | bitwise or
pg_catalog | | | smallint | smallint | smallint | int2or | bitwise or
pg_catalog | ~ | | bigint | bigint | int8not | bitwise not
pg_catalog | ~ | | bit | bit | bitnot | bitwise not
pg_catalog | ~ | | inet | inet | inetnot | bitwise not
pg_catalog | ~ | | integer | integer | int4not | bitwise not
pg_catalog | ~ | | macaddr | macaddr | macaddr_not | bitwise not
pg_catalog | ~ | | smallint | smallint | int2not | bitwise not
例子
postgres=# select 6&3;
?column?
----------
2
(1 row)
postgres=# select 6|3;
?column?
----------
7
(1 row)
postgres=# select 6#3;
?column?
----------
5
(1 row)
postgres=# select ~6;
?column?
----------
-7
(1 row)
postgres=# select 6<<1;
?column?
----------
12
(1 row)
postgres=# select 6>>1;
?column?
----------
3
(1 row)
如果你要和ORACLE用法完全一致，可以自定义bitand函数，或者使用orafce插件来支持.
例子
postgres=# create or replace function bitand(numeric,numeric) returns numeric as $$
select ($1::int8 & $2::int8)::numeric ;
$$ language sql strict;
CREATE FUNCTION
postgres=# select bitand(6,3);
bitand
--------
2
(1 row)
PostgreSQL 如何将numeric转换为varbit
第一种方法如下, 涉及符号与类型宽度(假设int为signed int).
do language plpgsql $$
declare
o_bit text;
o_len int;
i_num int;
i_conv int;
i_num_abs int;
i_res int;
i_mod int;
begin
o_len := 32;
i_num := -10234;
i_conv := 2;
i_num_abs := abs(i_num);
i_res := i_num_abs/i_conv;
i_mod := mod(i_num_abs,i_conv);
o_bit := i_mod::text;
loop
if i_res = 0 then
exit;
end if;
i_mod := mod(i_res,i_conv);
i_res := i_res/i_conv;
o_bit := i_mod||o_bit;
end loop;
o_len := o_len - char_length(o_bit) - 1;
o_bit := repeat('0', o_len)||o_bit;
if i_num >=0 then
o_bit := '0'||o_bit;
else
o_bit := '1'||o_bit;
end if;
raise notice '%', o_bit;
end;
$$;
创建函数如下 :
create or replace function i4tob(i_num int) returns varbit as $$
declare
o_bit text;
o_len int;
i_conv int;
i_num_abs int;
i_res int;
i_mod int;
begin
o_len := 32;
i_conv := 2;
i_num_abs := abs(i_num);
i_res := i_num_abs/i_conv;
i_mod := mod(i_num_abs,i_conv);
o_bit := i_mod::text;
loop
if i_res = 0 then
exit;
end if;
i_mod := mod(i_res,i_conv);
i_res := i_res/i_conv;
o_bit := i_mod||o_bit;
end loop;
o_len := o_len - char_length(o_bit) - 1;
if i_num >=0 then
null;
else
o_bit := repeat('0', o_len)||o_bit;
o_bit := '1'||o_bit;
end if;
raise notice '%', o_bit;
return o_bit::varbit;
end;
$$ language plpgsql;
测试 :
postgres=# select i4tob(-100);
NOTICE: 10000000000000000000000001100100
i4tob
----------------------------------
10000000000000000000000001100100
(1 row)
postgres=# select i4tob(100);
NOTICE: 1100100
i4tob
---------
1100100
(1 row)
postgres=# select i4tob(256);
NOTICE: 100000000
i4tob
-----------
100000000
(1 row)
postgres=# select i4tob(-256);
NOTICE: 10000000000000000000000100000000
i4tob
----------------------------------
10000000000000000000000100000000
(1 row)
另一种方法是使用位移算法 :
do language plpgsql $$
declare
o_bit text;
o_len int;
i_num int;
i_conv int;
i_num_abs int;
i_pos int;
begin
o_len := 32;
i_num := -1024;
i_conv := 2;
i_num_abs := abs(i_num);
i_pos := trunc((dlog1(i_num_abs))/0.693147180559945);
o_bit := mod(i_num_abs,i_conv)::text;
if i_pos >= 1 then
for i in 1..i_pos loop
o_bit := mod(i_num_abs>>i, i_conv)||o_bit;
end loop;
end if;
if i_num >=0 then
null;
else
o_len := o_len - char_length(o_bit) - 1;
o_bit := repeat('0', o_len)||o_bit;
o_bit := '1'||o_bit;
end if;
raise notice '%', o_bit;
end;
$$;
改成函数 :
create or replace function si32tob(i_num int) returns varbit as $$
declare
o_bit text;
o_len int;
i_conv int;
i_num_abs int;
i_pos int;
begin
o_len := 32;
i_conv := 2;
i_num_abs := abs(i_num);
i_pos := trunc((dlog1(i_num_abs))/0.693147180559945);
o_bit := mod(i_num_abs,i_conv)::text;
if i_pos >= 1 then
for i in 1..i_pos loop
o_bit := mod(i_num_abs>>i, i_conv)||o_bit;
end loop;
end if;
if i_num >=0 then
null;
else
o_len := o_len - char_length(o_bit) - 1;
o_bit := repeat('0', o_len)||o_bit;
o_bit := '1'||o_bit;
end if;
raise notice '%', o_bit;
return o_bit::varbit;
end;
$$ language plpgsql;
测试 :
digoal=# select si32tob(1);
NOTICE: 1
si32tob
---------
1
(1 row)
Time: 1.715 ms
digoal=# select si32tob(2);
NOTICE: 10
si32tob
---------
10
(1 row)
Time: 1.049 ms
digoal=# select si32tob(-2);
NOTICE: 10000000000000000000000000000010
si32tob
----------------------------------
10000000000000000000000000000010
(1 row)
Time: 1.520 ms
digoal=# select si32tob(234);
NOTICE: 11101010
si32tob
----------
11101010
(1 row)
Time: 1.094 ms
digoal=# select si32tob(-234);
NOTICE: 10000000000000000000000011101010
si32tob
----------------------------------
10000000000000000000000011101010
(1 row)
Time: 1.124 ms
以上函数参考wikihow提供的算法编写.
其他转换可参考本文末尾的函数进行修改.
参考
1. http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions013.htm
2. http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions014.htm
3. http://www.wikihow.com/Convert-from-Decimal-to-Binary
4. http://psoug.org/snippet/Convert-between-Decimal-Binary-Octal-and-Hex_536.htm
5. src/backend/optimizer/path/costsize.c
00096 #define LOG2(x) (log(x) / 0.693147180559945)
6. oracle pl/sql 相关转换函数
SET serveroutput ON
CREATE OR REPLACE PACKAGE dbms_numsystem AS
FUNCTION bin2dec (binval IN CHAR ) RETURN NUMBER;
FUNCTION dec2bin (N IN NUMBER) RETURN VARCHAR2;
FUNCTION oct2dec (octval IN CHAR ) RETURN NUMBER;
FUNCTION dec2oct (N IN NUMBER) RETURN VARCHAR2;
FUNCTION hex2dec (hexval IN CHAR ) RETURN NUMBER;
FUNCTION dec2hex (N IN NUMBER) RETURN VARCHAR2;
END dbms_numsystem;
/
show errors
CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS
FUNCTION bin2dec (binval IN CHAR) RETURN NUMBER IS
i NUMBER;
digits NUMBER;
result NUMBER := 0;
current_digit CHAR(1);
current_digit_dec NUMBER;
BEGIN
digits := LENGTH(binval);
FOR i IN 1..digits LOOP
current_digit := SUBSTR(binval, i, 1);
current_digit_dec := TO_NUMBER(current_digit);
result := (result * 2) + current_digit_dec;
END LOOP;
RETURN result;
END bin2dec;
FUNCTION dec2bin (N IN NUMBER) RETURN VARCHAR2 IS
binval VARCHAR2(64);
N2 NUMBER := N;
BEGIN
WHILE ( N2 > 0 ) LOOP
binval := MOD(N2, 2) || binval;
N2 := TRUNC( N2 / 2 );
END LOOP;
RETURN binval;
END dec2bin;
FUNCTION oct2dec (octval IN CHAR) RETURN NUMBER IS
i NUMBER;
digits NUMBER;
result NUMBER := 0;
current_digit CHAR(1);
current_digit_dec NUMBER;
BEGIN
digits := LENGTH(octval);
FOR i IN 1..digits LOOP
current_digit := SUBSTR(octval, i, 1);
current_digit_dec := TO_NUMBER(current_digit);
result := (result * 8) + current_digit_dec;
END LOOP;
RETURN result;
END oct2dec;
FUNCTION dec2oct (N IN NUMBER) RETURN VARCHAR2 IS
octval VARCHAR2(64);
N2 NUMBER := N;
BEGIN
WHILE ( N2 > 0 ) LOOP
octval := MOD(N2, 8) || octval;
N2 := TRUNC( N2 / 8 );
END LOOP;
RETURN octval;
END dec2oct;
FUNCTION hex2dec (hexval IN CHAR) RETURN NUMBER IS
i NUMBER;
digits NUMBER;
result NUMBER := 0;
current_digit CHAR(1);
current_digit_dec NUMBER;
BEGIN
digits := LENGTH(hexval);
FOR i IN 1..digits LOOP
current_digit := SUBSTR(hexval, i, 1);
IF current_digit IN ('A','B','C','D','E','F') THEN
current_digit_dec := ASCII(current_digit) - ASCII('A') + 10;
ELSE
current_digit_dec := TO_NUMBER(current_digit);
END IF;
result := (result * 16) + current_digit_dec;
END LOOP;
RETURN result;
END hex2dec;
FUNCTION dec2hex (N IN NUMBER) RETURN VARCHAR2 IS
hexval VARCHAR2(64);
N2 NUMBER := N;
digit NUMBER;
hexdigit CHAR;
BEGIN
WHILE ( N2 > 0 ) LOOP
digit := MOD(N2, 16);
IF digit > 9 THEN
hexdigit := CHR(ASCII('A') + digit - 10);
ELSE
hexdigit := TO_CHAR(digit);
END IF;
hexval := hexdigit || hexval;
N2 := TRUNC( N2 / 16 );
END LOOP;
RETURN hexval;
END dec2hex;
END dbms_numsystem;
/
show errors