Drill 常用时间函数 drill

　例如：

#根据时间戳　最近三天

select * from sea.`cross_border`.`delivery_response` where `timestamp` > unix_timestamp()-1000*60*60*24*3 order by `timestamp` desc

# 指定时间　
select * from sea.`cross_border`.`delivery_response` where `createDateLoc` > to_date('2021-03-10 12:32:00', 'yyyy-MM-dd HH:mm:ss')

# 最近两天
select * from sea.`cross_border`.`delivery_response` where `createDateLoc` > DATE_ADD(date '2021-12-21', -2)

常用函数：

Drill常用函数

（基于1.18版本）

类型转换

CAST

CAST函数将一种实体（例如，求值为单个值的表达式）从一种类型转换为另一种类型。语法：CAST (<expression> AS <data type>)。使用CONVERT_TO和CONVERT_FROM而不是CAST函数来转换二进制数据类型。您可以将字符串或数字值转换为小数，即使它们包含小数点也是如此。 如果某个值的小数位数和精度大于查询中指定的小数位数和精度，则该值将四舍五入以适合指定的小数位数和精度。您不能将包含小数点的字符串强制转换为INT或BIGINT。 例如，如果JSON文件中包含“ 1200.50”，则尝试选择字符串并将其强制转换为INT会失败。 解决方法是，转换为FLOAT或DOUBLE类型，然后转换为INT，假设您要丢失小数点右边的数字。如：CAST('1' as DECIMAL(28, 2))结果为1.00；数字转字符：CAST(456 as CHAR(3))。

CONVERT_TO 和CONVERT_FROM

CONVERT_TO和CONVERT_FROM函数根据数据的小端或大端字节序编码，将二进制数据与Drill内部类型进行相互转换。CONVERT_FROM('{x:100, y:215.6}' ,'JSON') 将字符串转换为JSON映射。

字符串转日期类型

TO_DATE 字符串转日期

语法：TO_DATE (expression [, 'format']),expression是一个日期字符串或UNIX毫秒时间，format对应日期字符串的格式，必须严格一致。如：to_date(‘2021-03-10 12:32:00’, ‘yyyy-MM-dd HH:mm:ss’)，结果为：2021-03-10。

TO_TIME 字符串转时间

语法：TO_TIME(expression [,’format’]) ，expression是一个时间字符串或UNIX毫秒时间，format对应日期字符串格式。如：to_time(‘12:30:00’,’HH:mm:ss’) 结果为：12：30：00； to_time('2019-08-26 09:41:01', 'yyyy-MM-dd HH:mm:ss') 结果为：09：41：01。

TO_TIMESTAMP 字符串转日期时间

语法：TO_TIMESTAMP (expression [, 'format'])，expression是一个日期字符串或UNIX毫秒时间，format对应日期字符格式。如：TO_TIMESTAMP('2008-2-23 12:00:00', 'yyyy-MM-dd HH:mm:ss')，结果为：2008-2-23 12:00:00；如果只有时间部分，则日期部分为：1970-01-01，如：to_timestamp('09:41:01', 'HH:mm:ss')结果为：1970-01-01 09:41:01；如果只有日期部分，则时间部分为：00:00，如：to_timestamp('2021-03-19', 'yyyy-MM-dd')结果为：2021-03-19 00:00

在查询条件里面使用日期类型，需要注意不同日期转换函数的结果的时间范围是不同的，需要将日期字符串转换为和日期字段的时间类型一致，才能保证比较结果的正确性。语法：SELECT <fields> FROM <plugin> WHERE TO_DATE(<field>, <format>)  <  TO_DATE (<value>, <format>)。以mongodb为例，默认的日期类型是对应这里的TIMESTAMP类型，所以在比较时也可以不对日期字段值做转换，只需处理参数值为to_timestamp(value,format)即可。如：SELECT * from mongo.db_txl.test where updateTime> to_timestamp('2019-08-26 09:10:00', 'yyyy-MM-dd HH:mm:ss')

TO_CHAR转字符串

将数字，日期，时间或日期时间转换为字符串。语法：TO_CHAR (expression, 'format')，expression是一个INTEGER，FLOAT，DOUBLE，DATE，TIME或TIMESTAMP表达式。

浮点数转字符，TO_CHAR(1256.789383, '#,###.###') 将FLOAT转换为字符串。 格式规格使用逗号分隔千位并四舍五入到小数点后三位。

日期转字符串，TO_CHAR((cast ('2008-2-23' as date)), 'yyyy-MM-dd') 将日期按指定日期格式转换为字符串。

TO_NUMBER转换数字

 语法：TO_NUMBER（'string', 'format'）使用格式规范将字符串转换为带格式的数字。

格式字符：

l # 数字占位符

l 0 数字占位符。 如果值在格式字符串中出现零“ 0”的位置上有一个数字，则该数字将出现在输出中；否则，输出中的那个位置将显示一个“ 0”

l . 小数点。 使格式字符串中的第一个“.”字符成为值中小数点分隔符的位置； 忽略任何其他“.”字符

l  , 逗号分组分隔符

l  E 指数。 用科学计数法分隔尾数和指数

如：TO_NUMBER('12345', '##0.##E0') 输出：12345.0

日期/时间函数

AGE

语法：AGE (timestamp[, timestamp])，AGE 返回两个timestamp之间的间隔，或者从当前日期的午夜减去一个时间戳,返回值类型为：INTERVAL YEAR或INTERVAL DAY。INTERVAL YEAR类型指定从一年到一个月的值。 INTERVAL DAY类型指定从一天到秒的值。格式：P [qty] Y [qty] M [qty] D T [qty] H [qty] M [qty] S ，P 标志着一段时间的开始;Y 跟随年数;M 跟随月数;D 跟随天数；T是日期与时间部分的分隔符；H 跟随小时数，0-24；M 跟随数分钟；S 跟随秒数和可选的毫秒数。如：AGE('2021-03-19','1957-06-13')结果为P776M10D，表示间隔776个月又10天。使用to_number(AGE('2021-03-19','1957-06-13'),'#') 得到结果64，表示相差64年。

DATE_ADD 

返回日期/时间和天/小时数或日期/时间和日期/时间间隔的总和。

语法：DATE_ADD(keyword literal, integer)、DATE_ADD(keyword literal, interval expr)、DATE_ADD(column, integer)、DATE_ADD(column, interval expr)。

参数说明：Keyword是单词date，time或timestamp;literal是日期，时间或时间戳文字。 例如，以单引号括起来的yyyy-mm-dd格式的日期;integer是要添加到日期/时间的天数;column是数据源列中的日期，时间或时间戳记数据;interval expr如：interval ‘2’ day 表示2天。

如DATE_ADD(date '2015-05-15', 2) 结果2015-05-17。DATE_ADD(CAST(hire_date AS TIMESTAMP), interval '10' hour) 给hire_date字段的值加上10小时。DATE_ADD(timestamp '2015-04-15 22:55:55', interval '1-2' year to month) 给日期加上1年2个月。

DATE_DIFF 

DATE_DIFF返回日期/时间与天/小时数或日期/时间与日期/时间间隔的差。语法：DATE_DIFF(keyword literal, integer)、DATE_DIFF(keyword literal, interval expr)、DATE_DIFF(column, integer)、DATE_DIFF(column, interval expr)、DATE_DIFF(keyword literal, keyword literal) 。

参数说明：Keyword取值：date、time、timestamp；literal 是日期，时间或时间戳文字。 例如，以单引号括起来的yyyy-mm-dd格式的日期；Integer 要从日期或时间中减去的天数；Interval 表示时间间隔的关键字，expr是时间间隔的字符串，如：interval ‘1-1-1’ year to day 表示1年1月1天；Column 表示数据源里的date、time、timestamp类型字段的名称。

计算两个TIMESTAMP的差值时，结果是INTERVAL DAY类型，表示相差的天数和秒数。时间部分会影响天数的计算，如：DATE_DIFF(timestamp '2021-03-19 20:59:59', timestamp '2006-01-05 19:59:59')得到P5552DT3600S，表示间隔5552天又3600秒；TO_NUMBER(DATE_DIFF(timestamp1,timestamp2), '#')得到的结果是天数，秒被舍弃了。

DATE_DIFF(current_timestamp, interval '1' hour)，当前时间戳加上一小时，注意时间间隔值必须用’’包裹。DATE_DIFF(current_timestamp, interval '1-2' year to month) 将当前时间减去1年2个月。对时间进行间隔差值结算，结果与日期的不同。如DATE_DIFF(time '23:55:55', interval '22:55:55' hour to second) 时间差值计算，结果为：01:00表示相差1小时，但是两个调换位置，结果就是：23:00，注意不是负数。

DATE_PART 

DATE_PART返回date、timstamp、interval的某个域（字段）。语法：date_part(keyword, expression) keyword取值：year、month、day、hour、minute、second，需用’’包裹，expression 表示日期时间字符串。

date_part('year', '2021-03-18')返回2021；date_part('day', '2021-03-18')返回18。DATE_PART('month', '1:2:3')返回2。date_part('minute',interval '10:09:12' hour to second)返回9。计算年龄：date_part('year',age(updateTime))利用age计算出当前时间与数据源日期字段updateTime值的间隔时间，然后再用date_part取出year部分，如果结果不超过一年，则得到0。

DATE_SUB

DATE_SUB日期时间减去指定的天数或小时数。语法：DATE_SUB(keyword literal|column, integer) 如：DATE_SUB(date '2015-05-15', 2) 减去2天结果为：2015-05-13。DATE_SUB(keyword literal|column, interval expr) 如：DATE_SUB(date '2015-04-15', interval '2' month) 减去2月结果为：2015-02-15。 

EXTRACT 

EXTRACT返回时间戳，时间，日期或时间间隔的组成部分。语法：EXTRACT (extract_expression)  extract_expression格式：component FROM (timestamp | time | date | interval)。component 是以下时间单位之一：年year，月month，日day，小时hour，分钟miniute，秒second。如：EXTRACT(day FROM CURRENT_DATE) 返回18，当前日期为：2021-03-18;extract(minute from interval '20:15:10' hour to second)或者extract(minute from time '20:15:10' ) 返回15（分钟）。与DATE_PART功能类似。

UNIX_TIMESTAMP 

UNIX_TIMESTAMP返回自1970年1月1日以来经过的秒数。语法：UNIX_TIMESTAMP()、UNIX_TIMESTAMP(string date)、UNIX_TIMESTAMP(string date, string pattern)。UNIX_TIMESTAMP() 返回自1970年1月1日至当前时间经过的秒数。UNIX_TIMESTAMP(string date) 给定日期字符串按格式:yyyy-MM-dd HH:mm:ss转换后的时间据1970年1月1日经过的秒数。UNIX_TIMESTAMP(string date, string pattern) 用给定的日期格式来转换时间字符串为TIMESTAMP类型，再返回据1970年1月1日经过的秒数。如：unix_timestamp('1970-01-01 00:10:00 +0000', 'yyyy-MM-dd HH:mm:ss Z')返回0，注意这里需要指定时区，否则默认情况下：1970-01-01 08:00:00才返回0。

NEARESTDATE 

NEARESTDATE返回最近的日期。最近的日期是指在日期的某个部分上相同的日期。语法：NEARESTDATE(column, ‘interval’ ) column表示数据源中的timestamp类型字段，interval取值：YEAR、MONTH、WEEK_SUNDAY、WEEK_MONDAY、DAY、HOUR、MINUTE、SECOND、QUARTER、HALF_HOUR、QUARTER_HOUR、30SECOND、15SECOND。

将当前时间按不同interval处理结果对比：

函数	结果
nearestDate (CURRENT_TIMESTAMP, 'YEAR')	2021-01-01 00:00
nearestDate(CURRENT_TIMESTAMP,'QUARTER')	2021-04-01 00:00
nearestDate (CURRENT_TIMESTAMP, 'MONTH')	2021-03-01 00:00
nearestDate (CURRENT_TIMESTAMP, 'DAY')	2021-03-18 00:00
nearestDate(CURRENT_TIMESTAMP,'WEEK_SUNDAY')	2021-03-14 00:00
nearestDate(CURRENT_TIMESTAMP,'WEEK_MONDAY' )	2021-03-15 00:00
nearestDate (CURRENT_TIMESTAMP, 'HOUR')	2021-03-18 16:00
nearestDate ( CURRENT_TIMESTAMP,'HALF_HOUR' )	2021-03-18 16:30
nearestDate(CURRENT_TIMESTAMP,'QUARTER_HOUR' )	2021-03-18 16:30
nearestDate (CURRENT_TIMESTAMP, 'MINUTE')	2021-03-18 16:40
nearestDate (CURRENT_TIMESTAMP, 'SECOND')	2021-03-18 16:40:12

如，按年份相同的进行分组统计：

SELECT NEARESTDATE(hire_date, 'YEAR' ) AS hireDate, COUNT(*) AS `count` FROM cp.`employee.json` GROUP BY NEARESTDATE(hire_date, 'YEAR')。

time_bucket

将输入分组到给定的时间间隔中，可用于按时间间隔分组统计。语法：time_bucket(<timestamp>, <interval>)、time_bucket_ns(<timestamp>,<interval>)，这两个函数均接受BIGINT时间戳和以毫秒为单位的间隔作为参数。 time_bucket_ns（）函数接受以纳秒为单位的时间戳，time_bucket（）函数接受以毫秒为单位的时间戳。 两者均以原始格式返回时间戳。如：SELECT time_bucket(updateTime,1800000) as grouptime,count(*) from mongo.db_txl.test group by grouptime; 将数据源中的数据按照日期字段的值每30分钟一个间隔进行分组，再统计各分组的数量。结果为：

grouptime	total
2019-08-23T18:00	1
2019-08-26T09:00	3
2019-08-26T09:30	1
2019-08-26T10:00	1

字符串操作

CHAR_LENGTH 

CHAR_LENGTH 返回字符串中的字符数。语法；CHAR_LENGTH(string)，如：char_length('1234567890') 返回10；char_length('五星红旗迎风飘扬！') 返回9。

CONCAT 

CONCAT 字符串连接。语法：CONCAT(string [, string [, ...] )，如：concat('1234567890','<->','五星红旗迎风飘扬！') 结果：1234567890<->五星红旗迎风飘扬！

ILIKE 

ILIKE对输入字符串和模式执行不区分大小写的比较，如果匹配则返回true。类似于SQL中的like，%表示通配符，匹配任意字符，任意长度；_表示匹配一个字符。

语法：ILIKE(string, pattern)，如：ilike('1340858xxxx','134%xxxx') 匹配134开头，xxxx结尾的字符串。ilike('1340858xxxx','134____xxxx') 匹配134开头，中间任意四个字符，xxxx结尾的字符。

INITCAP 

INITCAP返回字符串中每个单词的首字母大写形式。

语法：INITCAP(string)，如：initcap(‘apple 2323 orange’)返回:Apple 2323 Orange。

LENGTH 

LENGTH返回字符串用给定字符集表示的字符数。

语法：LENGTH( string [, encoding] )，如：length('红旗飘飘123','UTF8')返回：7。

LIKE

LIKE 与ilike功能类似，区别是区分大小写。

LOWER

LOWER 字符串转小写。语法：LOWER (string)，如：lower('kjdkf SJKKdf dkfjd')返回：kjdkf sjkkdf dkfjd。

LPAD

LPAD 将字符串填充或截断到指定长度，填充使用给定的字符或空格，填充模式采用的是前置填充。语法：LPAD (string, length [, fill text])。如：lpad('apple','10', 'B') 结果为：BBBBBapple；lpad('apple','3', 'B')结果为：app。

REGEXP_MATCHES

REGEXP_MATCHES将正则表达式模式与目标字符串匹配。 返回一个布尔值：如果该值与正则表达式匹配，则返回true；如果该值与正则表达式不匹配，则返回false。语法：REGEXP_MATCHES(string_expression, pattern)，如：regexp_matches('13660360455','(134|135|136).*') 匹配134，135，136开头的任意字符串。

REVERSE

REVERSE 字符串倒序。语法：REVERSE (string)，如：reverse('apple') 返回：elppa 。

RPAD

RPAD 将字符串填充或截断到指定长度，填充使用给定的字符或空格，填充模式采用的是后置填充。语法：RPAD (string, length [, fill text])，如： RPAD('Apache Drill', 22, 'Release 1.0') 返回：Apache Drill Release 1.。

SUBSTR

SUBSTR 返回输入字符串的子字符串，从指定位置开始，并且可以选择具有指定的长度，或者输入字符串的第一个子字符串与指定的正则表达式匹配。语法：SUBSTR(string, start)、SUBSTR(string, start, length)、SUBSTR(string, regexp)，如：从第8个字符的位置截取子字符串SUBSTR('Apache Drill', 8)返回Drill；从第3个字符的位置开始截取长度为2的子字符串SUBSTR('Apache Drill', 3, 2)返回ac；从截取第一个满足匹配模式的子字符串SUBSTR('On 1970-01-01 the quick 2021-03-16', '[\d-]+') 返回1970-01-01。

UPPER

UPPER 字符串转大写。UPPER('Apache Drill')结果APACHE DRILL。

汇总和汇总统计

汇总

下表列出了可在Drill查询中使用的聚合函数。

函数	参数类型	返回值类型	描述
ANY_VALUE(expr)	BIT,INT,BIGINT,FLOAT4,FLOAT8,DATE, TIMESTAMP,TIME,VARCHAR, ARBINARY, LIST,MAP,INTERVAL,INTERVALDAY, INTERVALYEAR, VARDECIMAL	与参数类型一致	返回所有输入值中value的值之一。 SQL标准中未指定此功能。
AVG(expr)	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, INTERVAL	DECIMAL类型返回的是DECIAML类型，其他类型都是返回DOUBLE类型	返回数值表达式的平均值。
BOOL_AND(expr), BOOL_OR(expr)	BIT	BIT	返回指定表达式上逻辑与（或）的结果。如：SELECT BOOL_AND(last_name = 'Spence') FROM cp.`employee.json`;
COUNT(*)		BIGINT	返回与给定条件匹配的行数。
COUNT([DISTINCT] expr)	any	BIGINT	返回与给定条件匹配的去重后的行数。如：SELECT COUNT(salary) FROM cp.`employee.json`;
MAX(expr), MIN(expr)	BINARY, DECIMAL, VARCHAR, DATE, TIME, or TIMESTAMP	与参数值类型一致	返回所选表达式的最小值和最大值。
SUM(expr)	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, INTERVAL	DECIMAL类型返回DECIAML类型；整数类型返回BIGINT类型；浮点数类型返回DOUBLE类型	返回数值表达式的总和。如：SUM(DISTINCT salary) FROM cp.`employee.json`;

说明：AVG，COUNT，MIN，MAX和SUM接受ALL和DISTINCT关键字。 默认为ALL。

汇总统计

下表列出了可在Drill查询中使用的汇总统计函数。

函数	参数	返回类型	说明
APPROX_COUNT_DUPS(expr)	any	BIGINT	返回重复值的近似计数（不是唯一的）。
STDDEV(expr)	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL	DECIMAL返回DECIMAL，其他类型返回DOUBLE
STDDEV_POP(expr)	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL	DECIMAL返回DECIMAL，其他类型返回DOUBLE
VARIANCE(expr)	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL	DECIMAL返回DECIMAL，其他类型返回DOUBLE
VAR_POP(expr)	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL	DECIMAL返回DECIMAL，其他类型返回DOUBLE

APPROX_COUNT_DUPS

返回重复值的近似计数，不一定准确，即重复的值有多少个。基础的布隆过滤器是一种概率数据结构，当测试元素进行重复时，该数据结构可能返回假阳性。 因此，返回的近似计数高估了真实的重复计数。 作为这种不准确性的回报，Bloom过滤器在大规模上具有很高的空间和时间效率，具体细节由过滤器的参数确定。如：SELECT 

count(*)-APPROX_COUNT_DUPS(age) FROM mongo.db_txl.test; 可以实现统计去重后age的数量（drill对mongodb好像不支持count(age)的方式）。

空值处理函数

COALESCE

返回第一个非NULL值，如果都为NULL则返回NULL。语法：COALESCE( expr1[, expr2, ... expr_n] )。如：SELECT COALESCE(birthday,img,email,appId) FROM mongo.db_txl.test; 返回数据源中birthday、img、email、appId第一个不为空的列的值。

NULLIF

如果两个表达式不相等，则返回第一个表达式；如果两个表达式相等，则返回第一个表达式的类型的空值。语法：NULLIF ( expr1, expr2 )。

非标准SQL函数

IF

如果条件为真，则返回then_value，否则返回else_value，从而为CASE语句提供了简写。语法：`IF`( condition, then_value, else_value )，因为IF是DRILL中的关键词，所以使用的时候需要用反括号包裹`IF`。如：SELECT `IF`( current_date < '2021-12-21', 'World still going', 'World has ended' );

LEAST

返回最小值。语法：LEAST( expr1[, expr2, ... expr_n] )。需要保证输入类型一致，如果输入值有NULL，则返回NULL。如：SELECT LEAST( 3,4,5,2,1 );结果是：1。

GREATEST

返回最大值。语法：GREATEST( expr1[, expr2, ... expr_n] )。需要保证输入类型一致，如果输入值有NULL，则返回NULL。如：SELECT GREATEST(1,2,3,4,5,4,3,9);结果是：9。

LEFT and RIGHT

返回输入字符串的子字符串，该子字符串以给定的长度在输入的开头或结尾开始。语法：LEFT( expr, length )、RIGHT( expr, length )。如果expr为Null，则返回Null；如果length=0则返回空字符串；如果长度超过expr长度，则返回expr。如：SELECT LEFT('The quick brown fox...', 7);结果是：The qui，返回从开头开始，长度为7的子字符串；SELECT RIGHT('The quick brown fox..', 5);结果是：fox..，从结尾开始，长度为5的子字符串。

NVL

从输入中返回第一个非空值。语法：NVL( expr1, expr2 )。如果输入值都为Null，则返回Null。如：SELECT NVL(CAST(NULL AS INT), 123);结果是：123。

SPACE

返回给定长度的空格字符串。语法：SPACE( length )。如：SELECT 'Foo' || SPACE(10) || 'bar';结果是：Foo          bar。

TRANSLATE

返回输入字符串，其中所有出现的指定字符集都被指定的替换字符集的相应成员所替换。语法：TRANSLATE( string, search_chars, replacement_chars )。search_chars和replacement_chars中的字符没有定界，而是通过它们的位置相互映射。 当replace_chars包含的字符多于search_chars时，则多余的字符将被忽略。 当replace_chars包含的字符较少时，那么search_chars中的多余字符将替换为空字符串。如：SELECT TRANSLATE('[The|fox|jumps|...]', '[]|', '"" ') 结果为："The fox jumps ..."，字符串中的[被替换为”，字符串中的]被替换为”，字符串中的|被替换为空字符，被替换的字符和用于替换的字符是按照顺序对应的。