presto-sql:常用函数

1,925 阅读3分钟
官方文档:https://trino.io/docs/current/functions.html
presto是一个计算引擎,presto-sql,可以理解为能被presto计算引擎所解释的sql语句。

1/date_diff()

基本语法为:
date_diff(unit, timestamp1, timestamp2) 

返回结果:
Returns `timestamp2 - timestamp1` expressed in terms of `unit`
及后者 - 前者
    SELECT date_diff('second', 
                     TIMESTAMP '2020-03-01 00:00:00', 
                     TIMESTAMP '2020-03-02 00:00:00');
    -- 86400

    SELECT date_diff('hour', 
                     TIMESTAMP '2020-03-01 00:00:00 UTC', 
                     TIMESTAMP '2020-03-02 00:00:00 UTC');
    -- 24

    SELECT date_diff('day', 
                     DATE '2020-03-01', 
                     DATE '2020-03-02');
    -- 1

    SELECT date_diff('second', 
                     TIMESTAMP '2020-06-01 12:30:45.000000000', 
                     TIMESTAMP '2020-06-02 12:30:45.123456789');
    -- 86400

    SELECT date_diff('millisecond', 
                      TIMESTAMP '2020-06-01 12:30:45.000000000', 
                      TIMESTAMP '2020-06-02 12:30:45.123456789');
    -- 86400123
    
    
    -- 还有另外一种用法
    select date_diff('day',
                      cast( 'xxxx' as date ),
                      cast( 'xxxx' as date ) 
                    )
    用cast()比较保险

2/substr(string,start,length)

substr(string, start, length) → varchar   
如: select substr('1599319787151',1,10) 

substr(string, start) → varchar        
如: select substr('1599319787151',1)

3/concat(a,b)

拼接字符串

4/last_day_of_month(xxx)

其中的参数xxx是一个日期,必须是date型
所以需要先通过cast()函数把字符串转成date,
得到的也是一个日期型

例子:
last_day_of_month( cast('2021-10-10' as date) )
结果是2021-10-31

5/查看数据类型typeof()

presto> select typeof(1), typeof('a'); 
    _col0 | _col1 
---------+------------ 
integer | varchar(1) 
(1 row) 

6/current_date

类型都是 date,不是字符串。
如果想得到字符串类型,还需cast( as varchar)转换
当前日期 
current_date

昨天日期
current_date - interval '1' day

先往前推1天,得到昨天日期,再往前推1个月,
current_date - interval '1' day - interval '1' month

如果是往前推2天,或者推2个月,daymonth这样的unit单位还是单数,并不是复数
current_date - interval '2' day - interval '2' month

7/if()函数

语法:if(表达式,a,b),可以看出if()函数有3个参数。
意思是:如果表达式成立,则返回a,都则返回b

例如:
if(status='a',user_name,'')
如果员工状态是a,也就是在职,则返回用户名,否则返回''

8/array_agg()

聚合函数
select org,org_code array_agg( user_name ) from raw_db.basic_df group by org;
结果是:
 org |   array_agg    
--------+----------------
     20 | {JONES}
     30 | {ALLEN,MARTIN}

9/array_distinct()

10/arrat_join()

array_join(array_distinct(array_agg( if(status = 'i',employee_number,null) )), ',') as dimission_ids,  

11/union 和 union all

unionunion all 的区别
UNIONUNION ALL关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。
1、对重复结果的处理:UNION在进行表链接后会筛选掉重复的记录,Union All不会去除重复记录。
2、对排序的处理:Union将会按照字段的顺序进行排序;UNION ALL只是简单的将两个结果合并后就返回。

总结一下:
   union 会去重,会排序
   unino all 不会去重,不会排序
   所以,因为union all 干的事情少,所以比union快
从效率上说,UNION ALL 要比UNION快很多,
所以,如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话,那么就使用UNION ALL。


zongj

12/coalesce()

COALESCE(表达式1,表达式2,...,表达式n),适用于Presto

   用途:遇到非NULL值就停止,并返回该值,如果所有的表达式均为NULL,则返回NULL值。