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支持如下单位

单位截取后的值second2020-08-22 03:04:05.000minute2020-08-22 03:04:00.000hour2020-08-22 03:00:00.000day2020-08-22 00:00:00.000week2001-08-20 00:00:00.000month2001-08-01 00:00:00.000quarter2001-07-01 00:00:00.000year2001-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.csdn.net/sinat_17697111/article/details/89101124 时间函数 参考：参考：https://blog.csdn.net/sinat_17697111/article/details/89101124