目录
此内容是否有帮助?

# Trino SQL 进阶函数介绍

本章节将介绍 Trino SQL 中进阶函数的用法,如果希望了解 Trino SQL 的更多内容,可以访问 Trino 的官网文档 (opens new window)

# 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)

# 时间/日期函数

使用current_datecurrent_timecurrent_timestamplocaltimelocaltimestamp时不用加圆括号,Trino 也不支持加圆括号的写法,使用时请注意。

字符串与时间的转换

可以直接在字符串格式的时间表达式前加关键字timestamp,比如timestamp '2020-01-01 00:00:00',即可直接获得对应的时间

date_parsedate_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_datetimeparse_datetime

时间计算函数

函数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

# 开窗函数

Trino 支持开窗函数,窗口函数中有不少非常实用的函数,如 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_valuelast_value需要搭配over子句使用,over子句中的partition by类似于group by,即按照所给字段分组,order by则会决定进行排序的字段。

# JSON 解析

如果您上报的数据或导入的历史数据中存在 JSON 类型的字段,在入库时都会转为文本类型(字符串),您可以在查询语句中提取使用。

字符串转 JSON

json_parse可以将符合 JSON 格式的字符串转成 JSON 类型数据:

json_parse(JSON '{"abc":[1, 2, 3]}')

JSON 转其他类型

您可以通过CAST将 JSON 转化为类型的数据,比如将刚刚转成 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]}'))

直接提取 JSON 数据

在很多情况下,您可能只需要提取 JSON 中的部分数据,此时可以使用json_extract_scalar,通过 json_path 表达式找到所需内容:

json_extract_scalar(json, json_path)

您也可以使用json_extract_scalar直接对 JSON 字符串进行提取,无需手动转化为 JSON 类型,比如提取abc的第一个元素:

json_extract_scalar('{"abc":[1, 2, 3]}','$.abc[0]')