ClickHouse函数 1.算术，比较，取整，逻辑，哈希，条件，URL 函数

clickhouse

发布日期: 2021-08-08

1. 算数函数

1.1 求和 plus

SELECT plus(12, 21), plus(10, -10), plus(-10, -10) ;
┌─plus(12, 21)─┬─plus(10, -10)─┬─plus(-10, -10)─┐
│           33 │             0 │            -20 │
└──────────────┴───────────────┴────────────────┘

1,2 求差值 minus

SELECT minus(10, 5), minus(10, -10),minus(-10, -10); 
┌─minus(10, 5)─┬─minus(10, -10)─┬─minus(-10, -10)─┐
│            5 │             20 │               0 │
└──────────────┴────────────────┴─────────────────┘

1.3 求积 multiply

SELECT multiply(12, 2), multiply(12, -2), multiply(-12, -2);
┌─multiply(12, 2)─┬─multiply(12, -2)─┬─multiply(-12, -2)─┐
│              24 │              -24 │                24 │
└─────────────────┴──────────────────┴───────────────────┘

1.4 求商 divide

SELECT divide(12, 4), divide(10, 3), divide(2, 4); 
┌─divide(12, 4)─┬──────divide(10, 3)─┬─divide(2, 4)─┐
│             3 │ 3.3333333333333335 │          0.5 │
└───────────────┴────────────────────┴──────────────┘

SELECT divide(-4, -2), divide(-4, 2), divide(-4.5, 3); 
┌─divide(-4, -2)─┬─divide(-4, 2)─┬─divide(-4.5, 3)─┐
│              2 │            -2 │            -1.5 │
└────────────────┴───────────────┴─────────────────┘

-- 除数与被除数都为 0， 返回 nan
SELECT divide(0, 0);
┌─divide(0, 0)─┐
│          nan │
└──────────────┘

-- 当被除数为 0 时，结果会出现无穷大符号 “ ∞ ”或“ -∞ ”
SELECT divide(10, 0), divide(-10, 0);
┌─divide(10, 0)─┬─divide(-10, 0)─┐
│           inf │           -inf │
└───────────────┴────────────────┘

-- 使用 intDivOrZero 函数计算 除法，当被除数为 0 时，结果返回 0
 SELECT intDivOrZero(10, 0);
┌─intDivOrZero(10, 0)─┐
│                   0 │
└─────────────────────┘

SELECT intDivOrZero(-4, -2), intDivOrZero(-4, 2), intDivOrZero(-4.5, 3); 
┌─intDivOrZero(-4, -2)─┬─intDivOrZero(-4, 2)─┬─intDivOrZero(-4.5, 3)─┐
│                    2 │                  -2 │                    -1 │
└──────────────────────┴─────────────────────┴───────────────────────┘

SELECT divide(-4, -2), divide(-4, 2), divide(-4.5, 3); 
┌─divide(-4, -2)─┬─divide(-4, 2)─┬─divide(-4.5, 3)─┐
│              2 │            -2 │            -1.5 │
└────────────────┴───────────────┴─────────────────┘

1.5 求余数 modulo

SELECT modulo(10, 3), modulo(10.5, 3);
┌─modulo(10, 3)─┬─modulo(10.5, 3)─┐
│             1 │             1.5 │
└───────────────┴─────────────────┘

1.6 取反 negate

SELECT negate(10), negate(-10);
┌─negate(10)─┬─negate(-10)─┐
│        -10 │          10 │
└────────────┴─────────────┘

1.7 绝对值 abs

SELECT abs(-10), abs(10);
┌─abs(-10)─┬─abs(10)─┐
│       10 │      10 │
└──────────┴─────────┘

1.8 最大公约数 gcd

SELECT gcd(12, 24), gcd(-12, -24), gcd(10, 6);
┌─gcd(12, 24)─┬─gcd(-12, -24)─┬─gcd(10, 6)─┐
│          12 │            12 │          2 │
└─────────────┴───────────────┴────────────┘

1.9 最小公倍数 lcm

SELECT lcm(12, 24), lcm(-12, -24), lcm(-3, 4); 
┌─lcm(12, 24)─┬─lcm(-12, -24)─┬─lcm(-3, 4)─┐
│          24 │            24 │         12 │
└─────────────┴───────────────┴────────────┘

1.10 随机数 rand

随机函数使用非加密方式生成伪随机数字。
所有随机函数都只接受一个参数或不接受任何参数。
您可以向它传递任何类型的参数，但传递的参数将不会使用在任何随机数生成过程中。
此参数的唯一目的是防止公共子表达式消除，以便在相同的查询中使用相同的随机函数生成不同的随机数

函数	说明
rand	返回一个UInt32类型的随机数字，所有UInt32类型的数字被生成的概率均相等
rand64	返回一个UInt64类型的随机数字，所有UInt64类型的数字被生成的概率均相等。
randConstant	返回一个UInt32类型的随机数字，该函数不同之处在于仅为每个数据块产生一个随机数。

SELECT rand(),rand(10);
┌────rand()─┬─rand(10)─┐
│ 414619051 │ 98368807 │
└───────────┴──────────┘

SELECT  rand64(), rand64(10);
┌────────────rand64()─┬──────────rand64(10)─┐
│ 1728580756103181445 │ 7736991780466826633 │
└─────────────────────┴─────────────────────┘

select randConstant();
┌─randConstant()─┐
│     3801492456 │
└────────────────┘

2. 比较函数

2.1 相等 equals

SELECT equals(12, 12), equals(12, 13),  equals('aa', 'aa'), equals('aa','bb');

┌─equals(12, 12)─┬─equals(12, 13)─┬─equals('aa', 'aa')─┬─equals('aa', 'bb')─┐
│              1 │              0 │                  1 │                  0 │
└────────────────┴────────────────┴────────────────────┴────────────────────┘

2.2 不等 notEquals

SELECT notEquals(12, 12), notEquals(12, 13),  notEquals('aa', 'aa'), notEquals('aa','bb');

┌─notEquals(12, 12)─┬─notEquals(12, 13)─┬─notEquals('aa', 'aa')─┬─notEquals('aa', 'bb')─┐
│                 0 │                 1 │                     0 │                     1 │
└───────────────────┴───────────────────┴───────────────────────┴───────────────────────┘

2.3 大于 greater

SELECT greater(12, 10), greater(10, 12), greater(12, 12);

┌─greater(12, 10)─┬─greater(10, 12)─┬─greater(12, 12)─┐
│               1 │               0 │               0 │
└─────────────────┴─────────────────┴─────────────────┘

2.4 大于等于 greaterOrEquals

SELECT greaterOrEquals(12,10), greaterOrEquals(12,12);

┌─greaterOrEquals(12, 10)─┬─greaterOrEquals(12, 12)─┐
│                       1 │                       1 │
└─────────────────────────┴─────────────────────────┘

2.5 小于 less

SELECT less(12, 21), less(12, 10), less(120, 120);

┌─less(12, 21)─┬─less(12, 10)─┬─less(120, 120)─┐
│            1 │            0 │              0 │
└──────────────┴──────────────┴────────────────┘

2.6 小于等于 lessOrEquals

SELECT lessOrEquals(12, 120), lessOrEquals(12, 11); 

┌─lessOrEquals(12, 120)─┬─lessOrEquals(12, 11)─┐
│                     1 │                    0 │
└───────────────────────┴──────────────────────┘

3. 取整函数

3.1 向下取整：floor

floor(x[,N])

-- 向下取整
SELECT floor(1.2345);
┌─floor(1.2345)─┐
│             1 │
└───────────────┘

-- 向下取整 并保留两位小数
SELECT floor(1.2345, 2);
┌─floor(1.2345, 2)─┐
│             1.23 │
└──────────────────┘

-- 将字符串转成 float 然后向下取整，并保留两位小数
SELECT floor(toFloat32('1.2345'), 2);
┌─floor(toFloat32('1.2345'), 2)─┐
│                          1.23 │
└───────────────────────────────┘

3.2 向上取整：ceil

ceil(x[, N]) 或者 ceiling(x[, N])

-- 向上取整
select ceil(1.234),ceil(5.678);
┌─ceil(1.234)─┬─ceil(5.678)─┐
│           2 │           6 │
└─────────────┴─────────────┘

select ceil(1.234,0),ceil(5.678,0);
┌─ceil(1.234, 0)─┬─ceil(5.678, 0)─┐
│              2 │              6 │
└────────────────┴────────────────┘

-- 向上取整，保留 2 位小数
select ceil(1.234,2),ceil(5.678,2);
┌─ceil(1.234, 2)─┬─ceil(5.678, 2)─┐
│           1.24 │           5.68 │
└────────────────┴────────────────┘

3.3 四舍五入：round

round(expression [, decimal_places])
decimal_places = 0：取整数，0 可省略
decimal_places > 0：将值舍入小数点右侧
decimal_places < 0：将小数点左侧的值四舍五入

-- 四舍五入取整
select round(1.234,0),round(5.678,0);
┌─round(1.234, 0)─┬─round(5.678)─┐
│               1 │            6 │
└─────────────────┴──────────────┘

-- 四舍五入取整 同上，省略 decimal_places参数 0 
select round(1.234),round(5.678);
┌─round(1.234)─┬─round(5.678)─┐
│            1 │            6 │
└──────────────┴──────────────┘

-- 四舍五入保留两位小数
select round(1.234,2),round(5.678,2);
┌─round(1.234, 2)─┬─round(5.678, 2)─┐
│            1.23 │            5.68 │
└─────────────────┴─────────────────┘

-- 四舍五入，小数点向左移动一位
select round(1.234,-1),round(5.678,-1);
┌─round(1.234, -1)─┬─round(5.678, -1)─┐
│                0 │               10 │
└──────────────────┴──────────────────┘

3.4 向下舍入到2^x roundToExp2

接受一个数字。它将数字向下舍入到最接近的（整个非负）2的x次幂。

SELECT roundToExp2(9);

┌─roundToExp2(9)─┐
│              8 │ -- 2^3
└────────────────┘

4. 逻辑函数

逻辑函数可以接受任何数字类型的参数，并返回UInt8类型的 0 或 1

当向函数传递零时，函数将判定为 false ，否则，任何其他非零的值都将被判定为 true。

4.1 与 and

SELECT and(equals(12, 12), notEquals(12, 10));
┌─and(equals(12, 12), notEquals(12, 10))─┐
│                                      1 │
└────────────────────────────────────────┘

4.2 或 or

 SELECT or(equals(12, 12), notEquals(12, 10));
┌─or(equals(12, 12), notEquals(12, 10))─┐
│                                     1 │
└───────────────────────────────────────┘

4.3 非 not

SELECT not 12, not 0;
┌─not(12)─┬─not(0)─┐
│       0 │      1 │
└─────────┴────────┘

SELECT not(12), not(0);
┌─not(12)─┬─not(0)─┐
│       0 │      1 │
└─────────┴────────┘

4.4 异或 xor

SELECT xor(0, 1, 1);
┌─xor(0, 1, 1)─┐
│            0 │
└──────────────┘

5. Hash函数

5.1 MD5

SELECT MD5('HELLO WORLD!');
┌─MD5('HELLO WORLD!')─┐
│ ���}dA�g����*�u            │
└─────────────────────┘

5.2 halfMD5

计算字符串的MD5值

SELECT halfMD5('HELLO WORLD!');
┌─halfMD5('HELLO WORLD!')─┐
│    13086268085575473511 │
└─────────────────────────┘

5.3 intHash

为任何类型的整数计算32位的哈希。
这是相对高效的非加密Hash函数
从任何类型的整数计算64位哈希码。它的工作速度比intHash32函数快。
计算任意数量字符串的CityHash64或使用特定实现的Hash函数计算任意数量其他类型的Hash。

SELECT
intHash32(123) AS intHash32,
intHash64(123) AS intHash64,
cityHash64('123') AS cityHash64;
┌──intHash32─┬────────────intHash64─┬───────────cityHash64─┐
│ 1465837921 │ 12585326385145454095 │ 11844464045149276331 │
└────────────┴──────────────────────┴──────────────────────┘

5.4 sha加密

使用sha1或者sha224加密的话，只能用于字符串

SELECT
SHA1('123') AS sha1,
SHA224('123') AS sha224,
SHA256('123') AS sha256;

┌─sha1───────┬─sha224────────┬─sha256─────────┐
│ @�_�Qe2���\^�۾� │ x�]hJ�.��#u�<ׁH��x�$f │ �e�Y B/�A~Hg��O��J?��~�����z� │
└────────────┴───────────────┴────────────────┘

5.5 farmHash

farmHash64(s) 计算字符串的FarmHash64。
接受一个String类型的参数。
返回UInt64。

SELECT farmHash64('hello world')

┌─farmHash64('hello world')─┐
│       6381520714923946011 │
└───────────────────────────┘

5.6 javaHash

javaHash(s) 计算字符串的JavaHash。
接受一个String类型的参数。
返回Int32。

SELECT javaHash('hello world');
┌─javaHash('hello world')─┐
│              1794106052 │
└─────────────────────────┘

5.7 hiveHash

hiveHash(s) 计算字符串的HiveHash。
与JavaHash相同，但不会返回负数
接受一个String类型的参数。
返回Int32。

SELECT hiveHash('hello world');
┌─hiveHash('hello world')─┐
│              1794106052 │
└─────────────────────────┘

6. 类型转换函数

6.1 转换为数值

SELECT toInt8(12.3334343), toFloat32(10.001), toFloat64(1.000040);
┌─toInt8(12.3334343)─┬─toFloat32(10.001)─┬─toFloat64(1.00004)─┐
│                 12 │            10.001 │            1.00004 │
└────────────────────┴───────────────────┴────────────────────┘

6.2 转换为字符串

-- 时间转换字符串
SELECT toString(now())as n,toTypeName(n) as typeName;
┌─n───────────────────┬─typeName─┐
│ 2021-08-10 14:52:03 │ String   │
└─────────────────────┴──────────┘

-- 时间转换字符串加时区
SELECT
    now() AS now_local,
    toString(now(), 'Asia/Shanghai') AS now_Shanghai,
    toString(now(), 'Asia/Tokyo') AS now_Toyko

Query id: 40014843-cdd1-49d4-81be-259d3e05e99f

┌───────────now_local─┬─now_Shanghai────────┬─now_Toyko───────────┐
│ 2021-08-10 16:24:04 │ 2021-08-10 16:24:04 │ 2021-08-10 17:24:04 │
└─────────────────────┴─────────────────────┴─────────────────────┘

6.3 转换为值类型

SELECT
    toTypeName(0),
    toTypeName(-10),
    toTypeName(12.43),
    toTypeName('hello'),
    toTypeName(toDateTime(1629788289))

┌─toTypeName(0)─┬─toTypeName(-10)─┬─toTypeName(12.43)─┬─toTypeName('hello')─┬─toTypeName(toDateTime(1629788289))─┐
│ UInt8         │ Int8            │ Float64           │ String              │ DateTime                           │
└───────────────┴─────────────────┴───────────────────┴─────────────────────┴────────────────────────────────────┘

7 条件函数

7.1 三元运算符

if ( cond, then, else )
cond != 0 则返回 then，如果 cond == 0 则返回else 。
cond 必须是 UInt8 类型，then 和 else 必须存在最低的共同类型。
then 和 else 可以是 NULL
中文字符使用双引号，英文字符可不使用引号也可使用当引号或双引号

SELECT
12 > 10 ? 'desc' : 'asc' AS "三元操作符",
if(12 > 10, 'desc' , 'asc') AS "if()函数",
if(12 > 10, NULL, NULL);

┌─三元操作符─┬─if()函数─┬─if(greater(12, 10), NULL, NULL)─┐
│ desc       │ desc     │ ᴺᵁᴸᴸ                            │
└────────────┴──────────┴─────────────────────────────────┘

7.2 多个条件判断 multiIf

multiIf( cond_1, then_1, cond_2, then_2, …, else )
cond_N— 函数返回的条件then_N。
then_N — 函数执行时的结果。
else — 不满足任何条件时的函数结果。
编写类似 case 的运算符，可以接收 2n+1 个参数
该函数返回值之一then_Nor else，具体取决于条件cond_N

SELECT
    10 as left,
    11 as right,
    multiIf(
      left < right, 'left is smaller', 
      left > right, 'left is greater', 
      left = right, 'Both equal', 
      'Null value'
    ) AS result;
    
┌─left─┬─right─┬─result──────────┐
│   10 │    11 │ left is smaller │
└──────┴───────┴─────────────────┘

8. UUID函数

8.1 generateUUIDv4 (生成随机UUID)

generateUUIDv4() 返回 UUID类型的值。

-- 随机生成一个UUIDv4的字符串
SELECT generateUUIDv4() as randomUUID;
┌─randomUUID───────────────────────────┐
│ 878c378e-fce3-4323-9486-45ce0d57af60 │
└──────────────────────────────────────┘

-- 随机生成一个UUIDv4的字符串 并删除 '-' 符号
SELECT replaceAll(toString(generateUUIDv4()), '-', '') AS replaceUUID;
┌─replaceUUID──────────────────────┐
│ fc1aff33bd4c42aeb8061e6162b19e74 │
└──────────────────────────────────┘

8.2 toUUID(s) (String 转 UUID)

SELECT
    toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS uuid,
    toTypeName(uuid) AS typeName

┌─uuid─────────────────────────────────┬─typeName─┐
│ 61f0c404-5cb3-11e7-907b-a6006ad3dba0 │ UUID     │
└──────────────────────────────────────┴──────────┘

8.3 UUIDNumToString() (FixedString(16) 转换成 UUID)

接受一个FixedString(16)类型的值，返回其对应的String表现形式。

SELECT 'abcdefghijklmnop' AS bytes,UUIDNumToString(toFixedString(bytes, 16)) AS uuid;
┌─bytes────────────┬─uuid─────────────────────────────────┐
│ abcdefghijklmnop │ 61626364-6566-6768-696a-6b6c6d6e6f70 │
└──────────────────┴──────────────────────────────────────┘

SELECT 'a' AS bytes,UUIDNumToString(toFixedString(bytes, 16)) AS uuid;
┌─bytes─┬─uuid─────────────────────────────────┐
│ a     │ 61000000-0000-0000-0000-000000000000 │
└───────┴──────────────────────────────────────┘

8.4 UUIDStringToNum (将 UUID 转换成FixedString(16))

接受一个String类型的值，
其中包含36个字符且格式为xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx，
将其转换为UUID的数值并以FixedString(16)将其返回。

SELECT '61626364-6566-6768-696a-6b6c6d6e6f70' AS uuid,UUIDStringToNum(uuid) AS bytes;
┌─uuid─────────────────────────────────┬─bytes────────────┐
│ 61626364-6566-6768-696a-6b6c6d6e6f70 │ abcdefghijklmnop │
└──────────────────────────────────────┴──────────────────┘

SELECT '61000000-0000-0000-0000-000000000000' AS uuid,UUIDStringToNum(uuid) AS bytes;
┌─uuid─────────────────────────────────┬─bytes─┐
│ 61000000-0000-0000-0000-000000000000 │ a     │
└──────────────────────────────────────┴───────┘

9. URL函数

https://clickhouse.tech/docs/zh/sql-reference/functions/url-functions/#cuturlparameterurl-name

9.1 protocol(截取传输协议)

-- 截取协议
SELECT protocol('http://www.baidu.com'); 
┌─protocol('http://www.baidu.com')─┐
│ http                             │
└──────────────────────────────────┘

-- 没有协议的时候返回空字符串
SELECT protocol('www.baidu.com');
┌─protocol('www.baidu.com')─┐
│                           │
└───────────────────────────┘

9.2 domain (获取域名)

SELECT domain('http://www.baidu.com');
┌─domain('http://www.baidu.com')─┐
│ www.baidu.com                  │
└────────────────────────────────┘

9.3 domainWithoutWWW (返回域名并删除首个3w)

SELECT domainWithoutWWW('http://www.baidu.com'),domainWithoutWWW('www.baidu.com');
┌─domainWithoutWWW('http://www.baidu.com')─┬─domainWithoutWWW('www.baidu.com')─┐
│ baidu.com                                │ baidu.com                         │
└──────────────────────────────────────────┴───────────────────────────────────┘

9.4 topLevelDomain(获取顶级域名)

SELECT topLevelDomain('http://www.google.com.cn');
┌─topLevelDomain('http://www.google.com.cn')─┐
│ cn                                         │
└────────────────────────────────────────────┘
SELECT topLevelDomain('http://www.google.com');
┌─topLevelDomain('http://www.google.com')─┐
│ com                                     │
└─────────────────────────────────────────┘

9.5 firstSignificantSubdomain(第一个有效子域名)

-- 返回“第一个有效子域名”
-- 如果顶级域名为‘com’，‘net’，‘org’或者‘co’则第一个有效子域名为二级域名。否则则返回三级域名
SELECT firstSignificantSubdomain('https://news.yandex.com.tr/');
┌─firstSignificantSubdomain('https://news.yandex.com.tr/')─┐
│ yandex                                                   │
└──────────────────────────────────────────────────────────┘

SELECT firstSignificantSubdomain('http://www.google.com.cn');
┌─firstSignificantSubdomain('http://www.google.com.cn')─┐
│ google                                                │
└───────────────────────────────────────────────────────┘

9.6 path(返回URL路径)

-- 返回URL路径
SELECT path('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10');
┌─path('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10')─┐
│ /aaa/bbb/ccc/ddd                                   │
└────────────────────────────────────────────────────┘

9.7 pathFull(返回URL路径包括参数)

SELECT pathFull('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10');
┌─pathFull('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10')─┐
│ /aaa/bbb/ccc/ddd?i=10                                  │
└────────────────────────────────────────────────────────┘

9.8 queryString(请求参数)

SELECT queryString('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10');
┌─queryString('https://www.baidu.com/aaa/bbb/ccc/ddd?i=10')─┐
│ i=10                                                      │
└───────────────────────────────────────────────────────────┘

9.9 fragment (fragment标识)

返回URL的fragment标识。fragment不包含#。

SELECT fragment('https://clickhouse.yandex/#quick-start');
┌─fragment('https://clickhouse.yandex/#quick-start')─┐
│ quick-start                                        │
└────────────────────────────────────────────────────┘

9.10 queryStringAndFragment(请求参数和fragment标识)

SELECT queryStringAndFragment('https://www.baidu.com/s?ie=utf-8&rsv_sug7=100#eiai');
┌─queryStringAndFragment('https://www.baidu.com/s?ie=utf-8&rsv_sug7=100#eiai')─┐
│ ie=utf-8&rsv_sug7=100#eiai                                                   │
└──────────────────────────────────────────────────────────────────────────────┘

9.11 cutWWW(删除 3w)

删除URL中的部分内容 (如果URL中不包含指定的部分，则URL不变。)

SELECT cutWWW('https://www.baidu.com');
┌─cutWWW('https://www.baidu.com')─┐
│ https://baidu.com               │
└─────────────────────────────────┘

9.12 cutQueryString(删除请求参数)

SELECT cutQueryString('http://www.baidu.com/1?page=1');

┌─cutQueryString('http://www.baidu.com/1?page=1')─┐
│ http://www.baidu.com/1                          │
└─────────────────────────────────────────────────┘

9.13 cutFragment (删除fragment标识)

SELECT cutFragment('http://www.baidu.com/#quick-demo');
┌─cutFragment('http://www.baidu.com/#quick-demo')─┐
│ http://www.baidu.com/                           │
└─────────────────────────────────────────────────┘
-- '#' 同样也会被删除。

9.14 cutQueryStringAndFragment(删除请求参数以及fragment标识)

SELECT cutQueryStringAndFragment('http://www.baidu.com/1?page=23#we'); 

┌─cutQueryStringAndFragment('http://www.baidu.com/1?page=23#we')─┐
│ http://www.baidu.com/1                                         │
└────────────────────────────────────────────────────────────────┘

-- '?','#'都会被删除。

9.15 cutURLParameter(删除URL参数)

cutURLParameter(URL, name) 删除URL中名称为‘name’的参数。

SELECT cutURLParameter('http://www.baidu.com/1?page=1#erre&resv=23&name=user','name');
┌─cutURLParameter('http://www.baidu.com/1?page=1#erre&resv=23&name=user', 'name')─┐
│ http://www.baidu.com/1?page=1#erre&resv=23                                      │
└─────────────────────────────────────────────────────────────────────────────────┘

-- 删除前 
http://www.baidu.com/1?page=1#erre&resv=23&name=user
-- 删除后
http://www.baidu.com/1?page=1#erre&resv=23

10. 字典函数

https://clickhouse.tech/docs/en/sql-reference/functions/ext-dict-functions/

10.1 创建字典

数据格式，以 csv 格式上传到服务器

id	code	name
1	a0001	研发部
2	a0002	产品部
3	a0003	数据部

编辑配置文件

vim /etc/clickhouse-server/flat_dictionary.xml

<!-- 通过手动创建的flat字典配置 -->
<dictionaries>
    <dictionary>
        <name>test_flat_dict</name>
        <source>
            <!-- 准备好的测试数据 -->
            <file>
                <path>/etc/clickhouse-server/dict_data/organization.csv</path>
                <format>CSV</format>
            </file>
        </source>
        <layout>
	          <flat/>
        </layout>
        <!-- 与测试数据的结构对应 -->
        <structure>
            <id>
             	 <name>id</name>
            </id>
            <attribute>
                <name>code</name>
                <type>String</type>
                <null_value></null_value>
            </attribute>
            <attribute>
                <name>name</name>
                <type>String</type>
                <null_value></null_value>
            </attribute>
        </structure>
        <lifetime>
            <min>300</min>
            <max>360</max>
        </lifetime>
    </dictionary>
</dictionaries>

验证字典是否创建成功

在上述的配置中，source数据源是 CSV 格式的文件，structure 数据结构与其对应。将配置文件复制到 ClickHouse 服务节点的 /etc/clickhouseserver 目录后，即完成了对该字典的创建过程。查验 system.dictionaries 系统表后，能够看到flat字典已经创建成功。

SELECT name,type,key.names,key.types,attribute.names,attribute.types,origin FROM system.dictionaries;


┌─name───────────┬─type─┬─key.names─┬─key.types──┬─attribute.names─┬─attribute.types─────┬─origin─────────────────────────────────────┐
│ test_flat_dict │      │ ['id']    │ ['UInt64'] │ ['code','name'] │ ['String','String'] │ /etc/clickhouse-server/flat_dictionary.xml │
└────────────────┴──────┴───────────┴────────────┴─────────────────┴─────────────────────┴────────────────────────────────────────────┘

10.2 dictGet

dictGet(‘dict_name’, attr_names, id_expr)
dict_name— 字典名称。字符串文字
attr_names— 字典列的名称，字符串文字，或列名称元组
id_expr- ID表达式返回字典键类型值或元组类型值，具体取决于字典配置。
如果字典中没有 id 键，则返回字典描述中指定的默认值。

select dictGet('test_flat_dict','name',toUInt64(1)) as dept_name;
┌─dept_name─┐
│ 研发部    │
└───────────┘

10.3 dictGetTOrDefault

dictGetOrDefault(‘dict_name’, attr_names, id_expr, default_value_expr)
dict_name— 字典名称。字符串文字
attr_names— 字典列的名称，字符串文字，或列名称元组
id_expr- ID表达式返回字典键类型值或元组类型值，具体取决于字典配置。
default_value_expr— 如果字典不包含带有id_expr键的行，则返回值。表达式或元组( Expression )，返回为attr_names属性配置的数据类型中的一个或多个值。

SELECT dictGetOrDefault('test_flat_dict', 'name', toUInt64(1),'未找到') AS dept_name;
┌─dept_name─┐
│ 研发部     │
└───────────┘

SELECT dictGetOrDefault('test_flat_dict', 'name', toUInt64(50),'未找到') AS dept_name;
┌─dept_name─┐
│ 未找到    │
└───────────┘

10.4 dictGetXX (获取指定类型数据)

获取整型数据	获取整型数据	获取浮点数据	获取日期数据	获取字符串数据
dictGetUInt8	dictGetInt8	dictGetFloat32	dictGetDate	dictgetuid
dictGetUInt16	dictGetInt16	dictGetFloat64	dictGetDateTime	dictGetString
dictGetUInt32	dictGetInt32
dictGetUInt64	dictGetInt64

SELECT dictGetString('test_flat_dict', 'name', toUInt64(1)) AS dept_name;

┌─dept_name─┐
│ 研发部     │
└───────────┘

10.5 dictHas

dictHas(‘dict_name’, id_expr)
dict_name— 字典名称。字符串文字
id_expr- 核心价值。表达式返回字典键类型值或元组类型值，具体取决于字典配置。
检查字典是否存在指定的 id
如果不存在，则返回0
如果存在，则返回1

SELECT dictHas('test_flat_dict',toUInt64(1)) AS hasVal,dictHas('test_flat_dict',toUInt64(50)) AS hasVal2;
┌─hasVal─┬─hasVal2─┐
│      1 │       0 │
└────────┴─────────┘