六 ClickHouse函数

ClickHouse主要提供两类函数—普通函数和聚合函数。普通函数由IFunction接口定义，拥有数十种函数实现，例如FunctionFormatDateTime、FunctionSubstring等。除了一些常见的函数 ( 诸如四则运算、日期转换等 ) 之外，也不乏一些非常实用的函数，例如网址提取函数、IP地址脱敏函数等。普通函数是没有状态的，函数效果作用于每行数据之上。当然，在函数具体执行的过程中，并不会一行一行地运算，而是采用向量化的方式直接作用于一整列数据。聚合函数由IAggregateFunction接口定义，相比无状态的普通函数，聚合函数是有状态的。以COUNT聚合函数为例，其AggregateFunctionCount的状态使用整UInt64记录。聚合函数的状态支持序列化与反序列化，所以能够在分布式节点之间进行传输，以实现增量计算。

6.1 普通函数

6.1.1 类型转换函数

toInt8(expr) — Results in the Int8 data type.
toInt16(expr) — Results in the Int16 data type.
toInt32(expr) — Results in the Int32 data type.
toInt64(expr) — Results in the Int64 data type.
toInt128(expr) — Results in the Int128 data type.
toInt256(expr) — Results in the Int256 data type.

SELECT toInt64(nan), toInt32(32), toInt16('16'), toInt8(8.8);
┌─────────toInt64(nan)─┬─toInt32(32)─┬─toInt16('16')─┬─toInt8(8.8)─┐
│ -9223372036854775808 │          32 │            16 │           8 │
└──────────────────────┴─────────────┴───────────────┴─────────────┘

toUInt(8|16|32|64|256)OrZero
toUInt(8|16|32|64|256)OrNull
toFloat(32|64)
toFloat(32|64)OrZero
toFloat(32|64)OrNull
toDate
toDateOrZero
toDateOrNull
toDateTime
toDateTimeOrZero
toDateTimeOrNull
toDecimal(32|64|128|256)

toString

 now() AS now_local,
    toString(now(), 'Asia/Yekaterinburg') AS now_yekat;
    ┌───────────now_local─┬─now_yekat───────────┐
    │ 2016-06-15 00:11:21 │ 2016-06-15 02:11:21 │
    └─────────────────────┴─────────────────────┘

CAST(x, T)

Arguments
- `x` — Any type.
- `T` — Destination type. String
**Returned value**

SELECT
    '2016-06-15 23:00:00' AS timestamp,
    CAST(timestamp AS DateTime) AS datetime,
    CAST(timestamp AS Date) AS date,
    CAST(timestamp, 'String') AS string,
    CAST(timestamp, 'FixedString(22)') AS fixed_string;
    ┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string──────────
│ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-15 23:00:00 │ 2016-06-15 23:00:00\0\0\0 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴───────────────────────

6.1.2 日期函数

SELECT
    toDateTime('2016-06-15 23:00:00') AS time,
    toDate(time) AS date_local,
    toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
    toString(time, 'US/Samoa') AS time_samoa
┌────────────────time─┬─date_local─┬─date_yekat─┬─time_samoa──────────┐
│ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-16 │ 2016-06-15 09:00:00 │
└─────────────────────┴────────────┴────────────┴─────────────────────┘

toDate
toYear
toMonth
toHour
toMinute
toSecond
toUnixTimestamp
date_trunc 将时间截断 date_trunc(unit, value[, timezone])

second
minute
hour
day
week
month
quarter
year

SELECT now(), date_trunc('hour', now());
┌───────────────now()─┬─date_trunc('hour', now())─┐
│ 2021-05-21 13:52:42 │       2021-05-21 13:00:00 │
└─────────────────────┴───────────────────────────┘

date_add

date_add(unit, value, date)
second
minute
hour
day
week
month
quarter
year
SELECT date_add(YEAR, 3, toDate('2018-01-01'));
date_diff('unit', startdate, enddate, [timezone])

date_diff

date_sub
timestamp_add
timestamp_sub
toYYYYMM
toYYYYMMDD
toYYYYMMDDhhmmss
formatDateTime

%C	year divided by 100 and truncated to integer (00-99)	20
%d	day of the month, zero-padded (01-31)	02
%D	Short MM/DD/YY date, equivalent to %m/%d/%y	01/02/18
%e	day of the month, space-padded ( 1-31)	2
%F	short YYYY-MM-DD date, equivalent to %Y-%m-%d	2018-01-02
%G	four-digit year format for ISO week number, calculated from the week-based year [defined by the ISO 860 standard, normally useful only with %V	2018
%g	two-digit year format, aligned to ISO 8601, abbreviated from four-digit notation	18
%H	hour in 24h format (00-23)	22
%I	hour in 12h format (01-12)	10
%j	day of the year (001-366)	002
%m	month as a decimal number (01-12)	01
%M	minute (00-59)	33
%n	new-line character (‘’)
%p	AM or PM designation	PM
%Q	Quarter (1-4)	1
%R	24-hour HH:MM time, equivalent to %H:%M	22:33
%S	second (00-59)	44
%t	horizontal-tab character (’)
%T	ISO 8601 time format (HH:MM:SS), equivalent to %H:%M:%S	22:33:44
%u	ISO 8601 weekday as number with Monday as 1 (1-7)	2
%V	ISO 8601 week number (01-53)	01
%w	weekday as a decimal number with Sunday as 0 (0-6)	2
%y	Year, last two digits (00-99)	18
%Y	Year	2018
%%	a % sign	%

SELECT formatDateTime(now(), '%D')

┌─formatDateTime(now(), '%D')─┐
│ 05/21/21                    │
└─────────────────────────────┘

FROM_UNIXTIME

SELECT FROM_UNIXTIME(423543535)

┌─FROM_UNIXTIME(423543535)─┐
│      1983-06-04 10:58:55 │
└──────────────────────────┘

6.1.3 条件函数

if(exp1 , exp2,exp3)
multiIf()

drop table if exists tb_if;
create table if not exists tb_if(
    uid Int16, 
    name String ,
    gender String
)engine = TinyLog ;
insert into tb_if values(1,'zss1','M') ;
insert into tb_if values(2,'zss2','M') ;
insert into tb_if values(3,'zss3','F') ;
insert into tb_if values(4,'zss4','O') ;
insert into tb_if values(5,'zss5','F') ;
--------单条件判断---------
SELECT 
    *,
    if(gender = 'M', '男', '女')
FROM tb_if

┌─uid─┬─name─┬─gender─┬─if(equals(gender, 'M'), '男', '女')─┐
│   1 │ zss1 │ M      │ 男                                  │
│   2 │ zss2 │ M      │ 男                                  │
│   3 │ zss3 │ F      │ 女                                  │
│   4 │ zss4 │ O      │ 女                                  │
│   5 │ zss5 │ F      │ 女                                  │
-------------------------------------------------------------
多条件判断

SELECT 
    *,
    multiIf(gender = 'M', '男', gender = 'F', '女', '保密') AS sex
FROM tb_if

┌─uid─┬─name─┬─gender─┬─sex──┐
│   1 │ zss1 │ M      │ 男   │
│   2 │ zss2 │ M      │ 男   │
│   3 │ zss3 │ F      │ 女   │
│   4 │ zss4 │ O      │ 保密 │
│   5 │ zss5 │ F      │ 女   │
└─────┴──────┴────────┴──────┘

6.1.6 其他

visitParamExtractRaw('{"abc":"\\n\\u0000"}', 'abc') = '"\\n\\u0000"';
visitParamExtractRaw('{"abc":{"def":[1,2,3]}}', 'abc') = '{"def":[1,2,3]}';
select  JSONExtract('{"a":"hello","b":"tom","c":12}', 'Tuple(String,String,UInt8)') as kn;
-- 元组函数
select tupleElement((1,2,3,4,66),5);

-- BitMap 函数
-- bitmapBuild
SELECT 
    bitmapBuild([1, 2, 3, 4, 5]) AS res,
    toTypeName(res)
┌─res─┬─toTypeName(bitmapBuild([1, 2, 3, 4, 5]))─┐
│     │ AggregateFunction(groupBitmap, UInt8)    │
└─────┴──────────────────────────────────────────┘
-- bitmapToArray
SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res;
-- bitmapSubsetInRange
SELECT bitmapToArray(bitmapSubsetInRange(bitmapBuild([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 100, 200, 500]), toUInt32(30), toUInt32(200))) AS res
┌─res───────────────┐
│ [30,31,32,33,100] │
└───────────────────┘
-- bitmapContains
SELECT bitmapContains(bitmapBuild([1, 5, 7, 9]), toUInt32(9)) AS res

┌─res─┐
│   1 │
└─────┘
-- bitmapHasAny 有任意一个元素
SELECT bitmapHasAny(bitmapBuild([1, 2, 3]), bitmapBuild([3, 4, 5])) AS res

┌─res─┐
│   1 │
└─────
-- bitmapHasAll 有任意一个元素

-- bitmapMin 
-- bitmapMax
-- bitmapAnd 交集
-- bitmapOr 并集
-- bitmapAndnot差集

posted @ 2021-12-11 20:22 花未全开*月未圆阅读(687) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

花未全开*月未圆

谦虚谨慎，戒骄戒躁，稳中求进，悟道行之