苏州专业建设网站,大学 两学一做专题网站,wordpress如何修改html代码,徐州网站建设外包一、timestamp字段与int字段相加
#xff08;一#xff09;场景
change_time字段是timestamp字段#xff0c;代表一个红绿灯周期的开始时间#xff08;先是绿灯、再是黄灯、最后红灯#xff09;#xff0c;而green是int字段#xff0c;代表绿灯的秒数#xff0c;现在…一、timestamp字段与int字段相加
一场景
change_time字段是timestamp字段代表一个红绿灯周期的开始时间先是绿灯、再是黄灯、最后红灯而green是int字段代表绿灯的秒数现在要求出绿灯的结束时间。即change_time字段green字段
二timestamp字段与int字段无法直接相加可以先把change_time字段转为时间戳然后和green字段相加最后再转为日期
样例from_unixtime(unix_timestamp(change_time, yyyy-MM-dd HH:mm:ss) green) AS new_timestamp
三SQL语句 成功
二、with语句与insert结合使用
一场景
在DWS层中对多层SQL使用with语句嵌套查询然后insert插入数据。如果直接把insert放在with语句上面那么就会如下报错
二报错
org.apache.hadoop.hive.ql.parse.ParseException:line 2:0 cannot recognize input near with a1 as in statement
三解决方法
把insert放在with的后面select的前面
四SQL语句
with a1 as(
selectb1.site_id, b1.phase_id, b1.phase_start, b1.program_id, b1.lane_direction, b1.device_direction,b1.min_gree_end, b1.phase_end, b1.team_id, b1.name,t8.device_no,t9.lane_num lane_no
from dws_pass as b1
left join hurys_dc_dwd.dwd_radar_config as t8
on t8.directionb1.device_direction and t8.device_nob1.device_no --得到真正的雷达编号字段
left join hurys_dc_dwd.dwd_radar_lane as t9
on t9.device_nob1.device_no and t9.lane_directionb1.lane_direction --得到车道编号字段
group by b1.site_id, b1.phase_id, b1.phase_start, b1.program_id, b1.lane_direction, b1.device_direction, b1.min_gree_end, b1.phase_end, b1.team_id, b1.name, t8.device_no, t9.lane_num)
insert overwrite table dws_pass_sparetime_1hour partition(day)
selecta1.site_id,phase_id,program_id,phase_start,min_gree_end,phase_end,a1.device_no,team_id,name,t10.create_time,concat(substr(create_time, 1, 14), 00:00) start_time,a1.lane_no,section_no,coil_no,device_direction direction,lane_direction,target_id,target_type,drive_in_time,day
from a1
left join hurys_dc_dwd.dwd_pass as t10
on t10.device_noa1.device_no and t10.lane_noa1.lane_noand t10.create_time between a1.min_gree_end and a1.phase_end
group by a1.site_id, phase_id, program_id, phase_start, min_gree_end, phase_end, a1.device_no, team_id, name, t10.create_time, a1.lane_no, section_no, coil_no, device_direction, lane_direction, target_id, target_type, drive_in_time, day
;
