- argMin和argMax用法:
with toStartOfDay(timestamp) as day
select day, argMin(message, host),argMin(tuple(message,host), host) from @table group by day
这个sql会返回当前的最小host名对应的message,这个sql在group by 分组后查找某个最小值的列对应的另一列的值非常有用
- visitParamExtractRaw 从json中获取某个字段的值:
select visitParamExtractRaw('"xx={"abc":"123456"}"', 'abc') from @table
这个sql会获取json中字段abc对应的value
- topk 获取某个分组中出现频率最高的K个项
with toStartOfDay(timestamp) as day
select day, topK(5)(host) from @table group by day
这个sql会查找出每天host字段出现频率最高的5个主机名称,注意这个函数只是返回近似的结果,并且可能会返回不是最高频的值
- 时间日期函数
WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64),toStartOfMinute(dt64),
toStartOfHour(dt64),toStartOfDay(dt64),now(),
toTypeName(now()),toUnixTimestamp(now()),FROM_UNIXTIME(1652697527)
from @table
包括了获取当前时间的now()函数,从Datetime格式转成时间戳的toUnixTimeStamp函数,从时间戳转成Datetime格式的from_unixtime函数,
以及将DateTime向前取整到当前小时的开始的函数toStartOfHour
5.类型转换函数
with '123' as str,'2016-06-15 23:00:00' AS ts, '66.66' as fstr
SELECT toUInt8(8.8),toInt32(20.1),
toInt32(str),CAST(fstr As float),toInt32(CAST(fstr As float)),
CAST(ts AS DateTime) AS datetime,
CAST(ts AS Date) AS date,
toYYYYMMDD(datetime)
from @table
包括从字符串转float,从float转int,从字符串转成datetime的函数