Functions support

features

All features following require ES with groovy script enabled.

• Distinct precision_threshold depends on ES or you can specify by second parameters. When you have lot of shards, 40000 consume too much memory.
• select,groupBy now support functions and field alias
• nested function is also available,eg. split(substring('newtype',0,3),'c')[0]
• Binary operation support now, eg. floor(substring(newtype,0,14)/100)/5)*5

functions support

• floor
• split
• trim
• log
• log10
• substring
• round
• sqrt
• concat_ws
• +
• -
• *
• /
• %
• date_format
• coalesce
• min_bw
• max_bw
• case_new
• if
• percentile_ranks
• movingavg
• rollingstd
• parse
• now
• date
• date_add

Example

check Example file:

org.nlpcn.es4sql.Test

SQLs:

SELECT newtype as nt  from  twitter2 

SELECT sum(num) as num2,newtype as nt  
from  twitter2 
group by nt  order by num2 

SELECT sum(num_d) as num2,split(newtype,',') as nt  
from  twitter2 
group by nt  
order by num2

SELECT sum(num_d) as num2,floor(num) as nt  
from  twitter2 
group by floor(num),newtype  
order by num2

SELECT split('newtype','b')[1] as nt,sum(num_d) as num2   
from  twitter2 
group by nt

SELECT split(substring('newtype',0,3),'c')[0] as nt,num_d   
from  twitter2 
group by nt

SELECT trim(newtype) as nt 
from  twitter2


SELECT floor(floor(substring(time,0,14)/100)/5)*5 as nt,
count(distinct(mid)) as cvalue 
FROM twitter2  
where ty='buffer' and day='20160815' and domain='baidu.com' 
group by nt 
order by cvalue 


SELECT name, coalesce(age2, age1) as myAge from mytest  LIMIT 0, 1


SELECT name, max_bw(age1, age2) as maxAge from mytest  LIMIT 0, 10


select name, case_new(age<=1, 'LOW', age=2, 'SENIOR', age>=3, 'HIGH',  default, '未知1') as myAge from bank  LIMIT 0, 10


select  name, if(age <= 18, 'Y', 'O') as myGender from bank  LIMIT 0, 10


SELECT percentile_ranks(age, 3, 8, 12, alias=rankAge) FROM bank


SELECT income / 10 AS myincome, sum(income / 10) AS incomeSum, movingavg(field=incomeSum,window=3,alias=incomeSume_avg) FROM bank3   GROUP BY dhg(field=insert_time, 'interval'=1d, alias=insert_time_dhg) ORDER BY insert_time ASC LIMIT 10



SELECT income / 10 AS myincome, sum(income / 10) AS incomeSum, rollingstd(field=incomeSum,window=2,alias=incomeSume_avg) FROM bank3   GROUP BY dhg(field=insert_time, 'interval'=1d, alias=insert_time_dhg) ORDER BY insert_time ASC LIMIT 10



SELECT parse(hobby, '(?\\S+)球', 'NOT_MATCH') AS ballType, COUNT(_index) FROM bank GROUP BY ballType


SELECT * FROM myindex WHERE time >= date(date_add(date(now()), interval -100 day)) AND time <= now()