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

clickhouse

发布日期: 2021-08-08

文章字数: 3.7k

阅读时长: 18 分

阅读次数:

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 │
└────────┴─────────┘

hnbian

https://www.hnbian.cn/posts/748d0ba8.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !

clickhouse olap

记录 Ambari 安装 Agent 异常

新添加的 ambari -agent 组件添加hbase regionserver 服务失败，报错日志如下；日志内容Traceback (most recent call last): File "/usr/lib/ambari-a

2021-08-12 ambari

exception ambari others

ClickHouse 14.常用操作符介绍

1. 介绍 ClickHouse 的操作符所有的操作符（运算符）都会在查询时，依据他们的优先级及其结合顺序，在被解析时转换为对应的函数。下面按优先级从高到低列出各组运算符及其对应的函数： 2. 下标运算符-- 数组中的下标运算符 -- a[

2021-08-05 clickhouse

clickhouse olap

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

1. 算数函数

1.1 求和 plus

1,2 求差值 minus

1.3 求积 multiply

1.4 求商 divide

1.5 求余数 modulo

1.6 取反 negate

1.7 绝对值 abs

1.8 最大公约数 gcd

1.9 最小公倍数 lcm

1.10 随机数 rand

2. 比较函数

2.1 相等 equals

2.2 不等 notEquals

2.3 大于 greater

2.4 大于等于 greaterOrEquals

2.5 小于 less

2.6 小于等于 lessOrEquals

3. 取整函数

3.1 向下取整：floor

3.2 向上取整：ceil

3.3 四舍五入：round

3.4 向下舍入到2^x roundToExp2

4. 逻辑函数

4.1 与 and

4.2 或 or

4.3 非 not

4.4 异或 xor

5. Hash函数

5.1 MD5

5.2 halfMD5

5.3 intHash

5.4 sha加密

5.5 farmHash

5.6 javaHash

5.7 hiveHash

6. 类型转换函数

6.1 转换为数值

6.2 转换为字符串

6.3 转换为值类型

7 条件函数

7.1 三元运算符

7.2 多个条件判断 multiIf

8. UUID函数

8.1 generateUUIDv4 (生成随机UUID)

8.2 toUUID(s) (String 转 UUID)

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

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

9. URL函数

9.1 protocol(截取传输协议)

9.2 domain (获取域名)

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

9.4 topLevelDomain(获取顶级域名)

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

9.6 path(返回URL路径)

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

9.8 queryString(请求参数)

9.9 fragment (fragment标识)

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

9.11 cutWWW(删除 3w)

9.12 cutQueryString(删除请求参数)

9.13 cutFragment (删除fragment标识)

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

9.15 cutURLParameter(删除URL参数)

10. 字典函数

10.1 创建字典

10.2 dictGet

10.3 dictGetTOrDefault

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

10.5 dictHas

你的赏识是我前进的动力