인코딩 함수

bech32Decode(address[, 'raw'])

SELECT tup.1 AS hrp, hex(tup.2) AS data FROM (SELECT bech32Decode('bc1qw508d6qejxtdg4y5r3zarvary0c5xw7kv8f3t4') AS tup)

┌─hrp─┬─data─────────────────────────────────────┐
│ bc  │ 751E76E8199196D454941C45D1B3A323F1433BD6 │
└─────┴──────────────────────────────────────────┘

SELECT tup.1 AS hrp, hex(tup.2) AS data FROM (SELECT bech32Decode('tb1qw508d6qejxtdg4y5r3zarvary0c5xw7kxpjzsx') AS tup)

┌─hrp─┬─data─────────────────────────────────────┐
│ tb  │ 751E76E8199196D454941C45D1B3A323F1433BD6 │
└─────┴──────────────────────────────────────────┘

bech32Encode(hrp, data[, witver | 'bech32' | 'bech32m'])

-- When no witness version is supplied, the default is 1, the updated Bech32m algorithm.
SELECT bech32Encode('bc', unhex('751e76e8199196d454941c45d1b3a323f1433bd6'))

bc1pw508d6qejxtdg4y5r3zarvary0c5xw7kj9wkru

-- A witness version of 0 will result in a different address string.
SELECT bech32Encode('bc', unhex('751e76e8199196d454941c45d1b3a323f1433bd6'), 0)

bc1qw508d6qejxtdg4y5r3zarvary0c5xw7kv8f3t4

-- While 'bc' (Mainnet) and 'tb' (Testnet) are the only allowed hrp values for the
-- SegWit address format, Bech32 allows any hrp that satisfies the above requirements.
SELECT bech32Encode('abcdefg', unhex('751e76e8199196d454941c45d1b3a323f1433bd6'), 10)

abcdefg12w508d6qejxtdg4y5r3zarvary0c5xw7kh526px

-- Using 'bech32' variant encodes raw data without a witness version byte,
-- compatible with Cosmos SDK, Injective, Osmosis, and other non-SegWit chains.
SELECT bech32Encode('inj', unhex('751e76e8199196d454941c45d1b3a323f1433bd6'), 'bech32')

inj1w508d6qejxtdg4y5r3zarvary0c5xw7ks5q7aq

bin(arg)

SELECT bin(14)

┌─bin(14)──┐
│ 00001110 │
└──────────┘

SELECT bin(toFloat32(number)) AS bin_presentation FROM numbers(15, 2)

┌─bin_presentation─────────────────┐
│ 00000000000000000111000001000001 │
│ 00000000000000001000000001000001 │
└──────────────────────────────────┘

SELECT bin(toFloat64(number)) AS bin_presentation FROM numbers(15, 2)

┌─bin_presentation─────────────────────────────────────────────────┐
│ 0000000000000000000000000000000000000000000000000010111001000000 │
│ 0000000000000000000000000000000000000000000000000011000001000000 │
└──────────────────────────────────────────────────────────────────┘

SELECT bin(toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0')) AS bin_uuid

┌─bin_uuid─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 01100001111100001100010000000100010111001011001100010001111001111001000001111011101001100000000001101010110100111101101110100000 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

bitPositionsToArray(arg)

SELECT bitPositionsToArray(toInt8(1)) AS bit_positions

┌─bit_positions─┐
│ [0]           │
└───────────────┘

SELECT bitPositionsToArray(toInt8(-1)) AS bit_positions

┌─bit_positions─────┐
│ [0,1,2,3,4,5,6,7] │
└───────────────────┘

bitmaskToArray(num)

SELECT bitmaskToArray(50) AS powers_of_two

┌─powers_of_two─┐
│ [2,16,32]     │
└───────────────┘

SELECT bitmaskToArray(8) AS powers_of_two

┌─powers_of_two─┐
│ [8]           │
└───────────────┘

bitmaskToList(num)

SELECT bitmaskToList(50) AS powers_list

┌─powers_list─┐
│ 2,16,32     │
└─────────────┘

char(num1[, num2[, ...]])

SELECT char(104.1, 101, 108.9, 108.9, 111) AS hello;

┌─hello─┐
│ hello │
└───────┘

-- 해당 바이트를 전달하여 임의 인코딩의 문자열을 구성할 수 있습니다.
-- 예: UTF8
SELECT char(0xD0, 0xBF, 0xD1, 0x80, 0xD0, 0xB8, 0xD0, 0xB2, 0xD0, 0xB5, 0xD1, 0x82) AS hello;

┌─hello──┐
│ привет │
└────────┘

hex(arg)

SELECT hex(1)

01

SELECT hex(toFloat32(number)) AS hex_presentation FROM numbers(15, 2)

┌─hex_presentation─┐
│ 00007041         │
│ 00008041         │
└──────────────────┘

SELECT hex(toFloat64(number)) AS hex_presentation FROM numbers(15, 2)

┌─hex_presentation─┐
│ 0000000000002E40 │
│ 0000000000003040 │
└──────────────────┘

SELECT lower(hex(toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0'))) AS uuid_hex

┌─uuid_hex─────────────────────────┐
│ 61f0c4045cb311e7907ba6006ad3dba0 │
└──────────────────────────────────┘

hilbertDecode(tuple_size, code)

SELECT hilbertDecode(2, 31)

(3,4)

-- Hilbert code for one argument is always the argument itself (as a tuple).
SELECT hilbertDecode(1, 1)

(1)

-- A single argument with a tuple specifying bit shifts will be right-shifted accordingly.
SELECT hilbertDecode(tuple(2), 32768)

(8192)

-- First create the table and insert some data
CREATE TABLE hilbert_numbers(
    n1 UInt32,
    n2 UInt32
)
ENGINE=MergeTree()
ORDER BY n1 SETTINGS index_granularity_bytes = '10Mi';
insert into hilbert_numbers (*) values(1,2);

-- Use column names instead of constants as function arguments
SELECT untuple(hilbertDecode(2, hilbertEncode(n1, n2))) FROM hilbert_numbers;

1	2

-- 단순 모드
hilbertEncode(args)

-- 확장 모드
hilbertEncode(range_mask, args)

SELECT hilbertEncode(3, 4)

31

-- 범위 확장은 범위(또는 카디널리티)가 크게 다른 인수들에 대해 유사한 분포가 필요할 때 유용합니다.
-- 예: 'IP 주소' (0...FFFFFFFF)와 '국가 코드' (0...FF).
-- 참고: 튜플 크기는 나머지 인수의 개수와 같아야 합니다.
SELECT hilbertEncode((10, 6), 1024, 16)

4031541586602

-- 튜플 없이 단일 인수만 제공된 경우, 차원 매핑이 필요하지 않으므로
-- 함수는 인수 자체를 Hilbert 인덱스로 반환합니다.
SELECT hilbertEncode(1)

1

-- 비트 시프트를 지정하는 tuple과 함께 단일 인수가 제공되면, 함수는
-- 해당 인수를 지정된 비트 수만큼 왼쪽으로 시프트합니다.
SELECT hilbertEncode(tuple(2), 128)

512

-- 먼저 테이블을 생성하고 데이터를 삽입합니다
CREATE TABLE hilbert_numbers(
    n1 UInt32,
    n2 UInt32
)
ENGINE=MergeTree()
ORDER BY n1;
insert into hilbert_numbers (*) values(1, 2);

-- 함수 인수로 상수 대신 컬럼 이름을 사용합니다
SELECT hilbertEncode(n1, n2) FROM hilbert_numbers;

13

-- Simple mode
mortonDecode(tuple_size, code)

-- Expanded mode
mortonDecode(range_mask, code)

SELECT mortonDecode(3, 53)

(1,2,3)

SELECT mortonDecode(1, 1)

(1)

SELECT mortonDecode(tuple(2), 32768)

(128)

-- First create the table and insert some data
CREATE TABLE morton_numbers(
    n1 UInt32,
    n2 UInt32,
    n3 UInt16,
    n4 UInt16,
    n5 UInt8,
    n6 UInt8,
    n7 UInt8,
    n8 UInt8
)
ENGINE=MergeTree()
ORDER BY n1;
INSERT INTO morton_numbers (*) values(1, 2, 3, 4, 5, 6, 7, 8);

-- Use column names instead of constants as function arguments
SELECT untuple(mortonDecode(8, mortonEncode(n1, n2, n3, n4, n5, n6, n7, n8))) FROM morton_numbers;

1	2	3	4	5	6	7	8

-- 단순 모드
mortonEncode(args)

-- 확장 모드
mortonEncode(range_mask, args)

SELECT mortonEncode(1, 2, 3)

53

-- 범위가 크게 다른 인수(또는 카디널리티가 다른 인수)에 대해 유사한 분포가 필요할 때
-- 범위 확장이 유용할 수 있습니다.
-- 예: 'IP Address' (0...FFFFFFFF)와 'Country code' (0...FF).
-- 참고: Tuple의 크기는 나머지 인수의 수와 같아야 합니다.
SELECT mortonEncode((1,2), 1024, 16)

1572864

-- 인수가 하나인 경우 Morton 인코딩 결과는 항상 해당 인수 자체입니다
SELECT mortonEncode(1)

1

SELECT mortonEncode(tuple(2), 128)

32768

-- 먼저 테이블을 생성하고 데이터를 삽입합니다
CREATE TABLE morton_numbers(
    n1 UInt32,
    n2 UInt32,
    n3 UInt16,
    n4 UInt16,
    n5 UInt8,
    n6 UInt8,
    n7 UInt8,
    n8 UInt8
)
ENGINE=MergeTree()
ORDER BY n1;
INSERT INTO morton_numbers (*) values(1, 2, 3, 4, 5, 6, 7, 8);

-- 함수 인수로 상수 대신 컬럼 이름을 사용합니다
SELECT mortonEncode(n1, n2, n3, n4, n5, n6, n7, n8) FROM morton_numbers;

2155374165

sqidDecode(sqid)

SELECT sqidDecode('gXHfJ1C6dN');

┌─sqidDecode('gXHfJ1C6dN')─┐
│ [1,2,3,4,5]              │
└──────────────────────────┘

sqidEncode(n1[, n2, ...])

SELECT sqidEncode(1, 2, 3, 4, 5);

┌─sqidEncode(1, 2, 3, 4, 5)─┐
│ gXHfJ1C6dN                │
└───────────────────────────┘

unbin(arg)

SELECT UNBIN('001100000011000100110010'), UNBIN('0100110101111001010100110101000101001100')

┌─UNBIN('001100000011000100110010')─┬─UNBIN('0100110101111001010100110101000101001100')─┐
│ 012                               │ MySQL                                             │
└───────────────────────────────────┴───────────────────────────────────────────────────┘

SELECT reinterpretAsUInt64(reverse(unbin('1110'))) AS num

┌─num─┐
│  14 │
└─────┘

unhex(arg)

SELECT unhex('303132'), UNHEX('4D7953514C')

┌─unhex('303132')─┬─UNHEX('4D7953514C')─┐
│ 012             │ MySQL               │
└─────────────────┴─────────────────────┘

SELECT reinterpretAsUInt64(reverse(unhex('FFF'))) AS num

┌──num─┐
│ 4095 │
└──────┘