较复杂函数¶

基础查询:

子串命中
* | select content where content like '%substring%' limit 100

正则表达式匹配
* | select content where regexp_like(content, '\d+m'）limit 100

JSON内容解析与匹配
* | select content where json_extract(content, '$.store.book')='mybook' limit 100
如果设置json类型索引也可以使用：
field.store.book='mybook'

1.同比和环比函数:

* | select compare( pv , 86400) from (select count(1) as pv from log)

*|select t, diff[1] as current, diff[2] as yestoday, diff[3] as percentage from(select t, compare( pv , 86400) as diff from (select count(1) as pv, date_format(from_unixtime(__time__), '%H:%i') as t from log group by t) group by t order by t) s

2.外部数据源联合查询:

sql
创建外表：
* | create table user_meta ( userid bigint, nick varchar, gender varchar, province varchar, gender varchar,age bigint) with ( endpoint='oss-cn-hangzhou.aliyuncs.com',accessid='LTA288',accesskey ='EjsowA',bucket='testossconnector',objects=ARRAY['user.csv'],type='oss')

使用外表
* | select u.gender, count(1) from chiji_accesslog l join user_meta1 u on l.userid = u.userid group by u.gender

3.地理位置函数:

* | SELECT count(1) as pv, ip_to_province(ip) as province WHERE ip_to_domain(ip) != 'intranet' GROUP BY province ORDER BY pv desc limit 10

* | SELECT mobile_city(try_cast("mobile" as bigint)) as "城市", mobile_province(try_cast("mobile" as bigint)) as "省份", count(1) as "请求次数" group by "省份", "城市" order by "请求次数" desc limit 100

安全分析函数