Presto语法记录

Presto语法记录

presto时间函数与hive区别
1.标准时间格式——时间戳
hive:
select unix_timestamp(cast (’2017-08-30 10:36:15‘ as timestamp))
presto:
select to_unixtime(cast (’2017-08-30 10:36:15‘ as timestamp))

2.时间戳——标准时间格式
presto:
select format_datetime(from_unixtime(1510284058),’yyyy-MM-dd HH:mm:ss‘)
hive:
select from_unixtime(1323308943123,’yyyy-MM-dd HH:mm:ss‘)

3.时间点函数
场景：对某段时间打上类似5分钟，10分钟的标签。需要将当前时间转化为对应最近的整5分钟，整10分钟的时间点。比如2020-09-01 20:14:10 转化到最近的5min,10min 为2020-09-01 20:14:10。在这里需要用到如下几个函数：date_trunc minute
date_trunc 截取函数
类似于保留纪委小数的操作，函数date_trunc支持如下单位

单位	截取后的值
second	2020-08-22 03:04:05.000
minute	2020-08-22 03:04:00.000
hour	2020-08-22 03:00:00.000
day	2020-08-22 00:00:00.000
week	2001-08-20 00:00:00.000
month	2001-08-01 00:00:00.000
quarter	2001-07-01 00:00:00.000
year	2001-01-01 00:00:00.000

实现给时间打标签

SELECT DATE_TRUNC('minute', TIMESTAMP '2012-03-22 03:41:14') - INTERVAL '1' MINUTE * (MINUTE(TIMESTAMP '2012-03-22 03:41:14') % 5);--返回整5分钟的时间
select MINUTE(TIMESTAMP '2012-03-22 03:41:14')；获取整分钟数-->41 
select (MINUTE(TIMESTAMP '2012-03-22 03:41:14') % 5);获取整分钟后对其按5求余数 41%5 ->1
select (TIMESTAMP '2012-03-22 03:41:14') - INTERVAL '1' MINUTE * (MINUTE(TIMESTAMP '2012-03-22 03:41:14') % 5); -->当前时间-最近一个5min差值 
2012-03-22 03:40:14.000 
--最后再套用DATE_TRUNC（）函数返回整分钟数

SELECT DATE_TRUNC('minute', TIMESTAMP '2012-03-22 03:41:14') - INTERVAL '1' MINUTE * (MINUTE(TIMESTAMP '2012-03-22 03:41:14') % 15);

上述将时间换成时间变量后会出现错误，正确应该将代表时间的变量转化为presto能识别的时间

select
date_trunc('minute', cast(create_time as timestamp)) - INTERVAL '1' MINUTE *(MINUTE(cast(create_time as timestamp) ) % 10) as timetype

上述用到时间的减法操作-presto的时间间隔
类似于 date ‘2012-08-08’ + interval ‘2’ day -->2012-08-10
4.presto开窗函数
场景：求截止到某个时间点该店铺的所有下销售额的总值，查询粒度是每间隔5min查询一次，则要求是每个时间点均为截止到改时间点的销售总额

select date_trunc('day', tb1.date),
       sum(tbl1.S) as S,
       sum(tbl1.T) as T,
       sum(sum(tb1.S)) over (order by date_trunc('day', tb1.date) rows unbounded preceding )  as cum_S,
       sum(sum(tb1.T)) over (order by date_trunc('day', tb1.date) rows unbounded preceding)  as cum_T
from esi_dpd_bi_esds_prst.points_tb1_use_dedup_18months_vw tb1
where tb1.reason_id not in (45, 264, 418, 983, 990, 997, 999, 1574) and
      tb1.group_id not in (22) and
      tb1.point_status not in (3) and
      tb1.date between cast(DATE '2019-01-01' as date) and cast( DATE '2019-01-03' as date)
group by 1
order by date_trunc('day', tb1.date) desc ;

参考：https://blog.youkuaiyun.com/sinat_17697111/article/details/89101124 时间函数
参考：参考：https://blog.youkuaiyun.com/sinat_17697111/article/details/89101124