时间&日期函数
-- 2021-04-15 14:40:18.791 Asia/Shanghai
select now()
-- 2021-04-15
select current_date
-- 14:10:41.586 Asia/Shanghai
select current_time
-- 2021-04-15 14:13:03.720 Asia/Shanghai
select current_timestamp
-- 昨天的年月日
select current_date - interval '1' day;
-- Asia/Shanghai
select current_timezone()
-- 输出long类型timestamp:1610085216
select to_unixtime(cast('2021-01-08 13:53:36' as timestamp))
-- 2021-01-08
select cast('2021-01-08' as date)
-- 2021-01-08 00:00:00.000
select cast('2021-01-08' as timestamp)
-- 2021-01-10 00:00:00.000
select date_parse('20210110','%Y%m%d')
-- 输出date类型日期:2021-01-08
select date(cast('2021-01-08 10:36:15' as timestamp))
select date(cast('2021-01-08' as timestamp))
-- 输出varchar类型日期:2021-01-10
select format_datetime(date_parse('20210110','%Y%m%d'),'yyyy-MM-dd')
-- 输出varchar类型日期:2021-01-08
select format_datetime(from_unixtime(to_unixtime(cast('2021-01-08 13:53:36' as timestamp))),'yyyy-MM-dd')
select format_datetime(from_unixtime(to_unixtime(cast('2021-01-08' as timestamp))),'yyyy-MM-dd')
-- 输出varchar类型日期:2021-01-08 13:53:36
select format_datetime(from_unixtime(1610085216),'yyyy-MM-dd HH:mm:ss')
select format_datetime(from_unixtime(1610085216),'yyyy-MM-dd')
-- 输出date类型日期:2021-01-08
select date_add('day',-2,cast('2020-01-10' as date))
-- 输出date类型日期:2021-04-13
select date_add('day',-2,current_date)
select date_add('week',-2,current_date)
select date_add('month',-2,current_date)
select date_add('quarter',-2,current_date)
select date_add('year',-2,current_date)
select date_add('millisecond',-2,date_parse('20210110','%Y%m%d'))
select date_add('second',-2,date_parse('20210110','%Y%m%d'))
select date_add('minute',-2,date_parse('20210110','%Y%m%d'))
select date_add('hour',-2,date_parse('20210110','%Y%m%d'))
-- date_diff(unit, timestamp1, timestamp2) → bigint
select date_diff('day','2021-04-10','2021-04-15')
select date_diff('day',date_parse('20210105','%Y%m%d'),date_parse('20210110','%Y%m%d'))
select date_diff('day',cast('2020-01-05' as date),cast('2020-01-10' as date))
select date_diff('month',cast('2020-01-05' as date),cast('2020-01-10' as date))
Presto SQL 特殊用法与进阶函数
1 try 函数 与 try_cast 函数
try(expression)
try
函数会其中表达式的异常情况,并将异常值返回为 NULL,如果不使用try
函数,则语句出现异常时将直接报错导致查询失败您还可以搭配
coalesce
函数使用特定值替换 NULL 值,比如下列,将字段a
转换为整数,转换失败则可转换为 0
coalesce(try(cast('a' as integer)), 0)
以上类型转换可以使用 try_cast 函数来实现,
try_cast
的作用与cast
函数一致,都是对值进行类型转换,区别在于try_cast
在类型转换错误时会返回 NULL,避免造成查询失败
coalesce(try_cast('a' as integer), 0)
2 时间/日期函数
使用
current_date
、current_time
、current_timestamp
、localtime
与localtimestamp
时不用加圆括号,Presto 也不支持加圆括号的写法,使用时请注意
2.1 字符串与时间的转换
可以直接在字符串格式的时间表达式前加关键字
timestamp
,比如timestamp '2020-01-01 00:00:00'
,即可直接获得对应的时间
date_parse
与date_format
分别是字符串转时间以及时间转字符串,用法都是传入需要转化的字段以及对应的 format,如下分别是字符串$part_date
转时间以及时间#event_time
转字符串:
date_parse("$part_date", '%Y-%m-%d')
date_format("#event_time", '%Y-%m-%d %T')
以上函数的 format 格式使用的是 MySQL 的格式,如需使用 JAVA 格式,可使用函数
format_datetime
与parse_datetime
2.2 时间计算函数
函数
date_add
可对时间进行偏移,unit
为单位,value
为偏移量,如果value
为负数,则为往前偏移
date_add(unit, value, timestamp)
函数
date_diff
,用来计算两个时间的差值,算法是timestamp2 - timestamp1
,返回的是单位为unit
的整数
date_diff(unit, timestamp1, timestamp2)
两个函数的
unit
的取值范围,可以参考以下表格
单位 | 描述 |
---|---|
millisecond | 毫秒 |
second | 秒 |
minute | 分钟 |
hour | 小时 |
day | 日 |
week | 周 |
month | 月 |
quarter | 季度 |
year | 年 |
3 开窗函数
Presto 支持开窗函数,窗口函数中有不少非常实用的函数,如 first_value 与 last_value 很适合计算一段时间内第一次或最后一次做某事的值。
比如计算每名用户首次产生购买商品行为时购买的物品:
SELECT user_id,first_purchase_product FROM
(SELECT user_id,first_value(product_name) over(partition by user_id order by time) AS first_purchase_product FROM log.purchase)
GROUP BY user_id,first_purchase_product
first_value
与last_value
需要搭配over
子句使用,over
子句中的partition by
类似于group by
,即按照所给字段分组,order by
则会决定进行排序的字段
4 JSON 解析
在一些特殊场景下,我们建议通过字符串的方式记录复杂的数据结构,上传到后台。那么在之后的使用过程中,可以使用 JSON 解析函数转化或提取能在 SQL 中使用的数据
4.1 字符串转 JSON
json_parse
可以将符合 JSON 格式的字符串转成 SQL 中的 JSON 类型数据,两函数等价:
json_parse('{"abc":[1, 2, 3]}')
4.2 JSON 转其他类型
转化为 JSON 的数据,可以通过
CAST
转化为其他 SQL 类型的数据,比如将刚刚转成 JSON 的字符串再次转成 MAP:
CAST(json_parse('{"abc":[1, 2, 3]}') AS MAP(varchar,array(integer)))
如果希望将字符串重新转成 JSON,可以使用
json_format
:
json_format(json_parse('{"abc":[1, 2, 3]}'))
4.3 直接提取 JSON 数据
在很多情况下,只需要提取 JSON 中的部分数据即可,此时可以使用
json_extract_scalar
进行提取,其通过 JSONPath 表达式进行提取,返回字符串:
json_extract_scalar(json, json_path)
并且
json_extract_scalar
可以直接对 JSON 的字符串进行提取,无需手动转化为 JSON 类型,比如以下,提取abc
的第一个元素:
json_extract_scalar('{"abc":[1, 2, 3]}','$.abc[0]')