Vertica:将位删除线转换为整数

基本上有两种解决方案(不幸的是,两种方案都不是直接解决的): 第一:写一个SQL函数,它需要根据位的长度进行扩展(这个例子有8位)

CREATE OR REPLACE FUNCTION bitstring_to_int(bs VARCHAR)
RETURN INTEGER
AS BEGIN
    RETURN (
        CASE WHEN SUBSTR(RIGHT('0'||bs, 1), 1, 1) = '1' THEN 1 ELSE 0 END + 
        CASE WHEN SUBSTR(RIGHT('0'||bs, 2), 1, 1) = '1' THEN 2 ELSE 0 END + 
        CASE WHEN SUBSTR(RIGHT('0'||bs, 3), 1, 1) = '1' THEN 4 ELSE 0 END + 
        CASE WHEN SUBSTR(RIGHT('0'||bs, 4), 1, 1) = '1' THEN 8 ELSE 0 END + 
        CASE WHEN SUBSTR(RIGHT('0'||bs, 5), 1, 1) = '1' THEN 16 ELSE 0 END + 
        CASE WHEN SUBSTR(RIGHT('0'||bs, 6), 1, 1) = '1' THEN 32 ELSE 0 END + 
        CASE WHEN SUBSTR(RIGHT('0'||bs, 7), 1, 1) = '1' THEN 64 ELSE 0 END + 
        CASE WHEN SUBSTR(RIGHT('0'||bs, 8), 1, 1) = '1' THEN 128 ELSE 0 END 
    ) ;
    END ;

或者用python编写一个udf:

import vertica_sdk

class bitstring_to_int(vertica_sdk.ScalarFunction):
    def processBlock(self, server_interface, arg_reader, res_writer):
        while(True):
            bs = arg_reader.getString(0)
            res_writer.setInt(sum([int(character) * 2 ** index \
                              for index,character in enumerate(str(bs)[::-1])]))
            res_writer.next()
            if not arg_reader.next():
                break
    def destroy(self, server_interface, col_types):
        pass

class bitstring_to_int_factory(vertica_sdk.ScalarFunctionFactory):
    def createScalarFunction(self, srv):
        return bitstring_to_int()
    def getPrototype(self, srv_interface, arg_types, return_type):
        arg_types.addVarchar()
        return_type.addInt()
    def getReturnType(self, srv_interface, arg_types, return_type):
        return_type.addInt()