presto sql语法总结

presto sql语法问题可以参考：https://prestodb.io/docs/current/functions/array.html

Presto： array_agg 转为数组，array_distinct 去重，array_join 将数组用逗号间隔连接成字符串

select user_id
    , array_join(array_distinct(array_agg(order_id)), ',') as order_ids
from tmp.tmp_row_to_col
where 1 = 1
group by user_id 

array_join(array_distinct(array_agg(order_id)), ‘,’)

unixtime 日期转换

// presto
format_datetime(from_unixtime(cast(substr(crowd_create_time,1,10) as double)),'yyyy-MM-dd HH:mm:ss')

// hive
from_unixtime(CAST(SUBSTR(crowd_create_time, 1, 10) AS INT), 'yyyy-MM-dd HH:mm:ss')