iwuming
commented
5 years ago Closed iwuming closed 5 years ago
iwuming
commented
5 years ago 
ketingli1
commented
5 years ago 
yangfan1995
commented
5 years ago SELECT a.vehicle_vin, a.begin_time, a.end_time, a.mileage, a.num, a.total_time, a.avg_speed, b.redlight, b.prescribed_lane, b.soline_change, b.over_speed, (b.acceleration / a.mileage) as acceleration, (b.deceleration / a.mileage) as deceleration, (b.sharp_turn / a.mileage) as sharp_turn, (b.fatigue_acceleration / a.mileage) as fatigue_acceleration, (b.fatigue_deceleration / a.mileage) as fatigue_deceleration, (b.fatigue_sharp_turn / a.mileage) as fatigue_sharp_turn, (b.distracted_acceleration / a.mileage) as distracted_acceleration, (b.distracted_deceleration / a.mileage) as distracted_deceleration, (b.distracted_sharp_turn / a.mileage) as distracted_sharp_turn, (b.belt_acceleration / a.mileage) as belt_acceleration, (b.belt_deceleration / a.mileage) as belt_deceleration, (b.belt_sharp_turn / a.mileage) as belt_sharp_turn, (b.turnlight_sharp_turn / a.mileage) as turnlight_sharp_turn, (b.over_speed_time / a.total_time) as over_speed_time, (b.without_gear / a.total_time) as without_gear, (b.too_close / a.mileage) as too_close, (b.over_speed_too_close / a.mileage) as over_speed_too_close, (b.without_seat_belt / a.total_time) as without_seat_belt, (b.speeding_door / a.mileage) as speeding_door, (b.without_door / a.mileage) as without_door, (b.without_turnlight / a.mileage) as without_turnlight, (b.smoke / a.mileage) as smoke, (b.fatigue_driving / a.total_time) as fatigue_driving, (b.distracted_driving / a.mileage) as distracted_driving FROM ( SELECT vehicle_vin, min(begin_time) as begin_time, max(end_time) as end_time, sum(mileage) as mileage, count(1) as num, sum(abs(end_time - begin_time)) as total_time, sum(effect_time) as total_effect_time, sum(mileage) / sum(effect_time) as avg_speed FROM $journey_tmp where avg_speed <= 150 GROUP BY vehicle_vin ) a INNER JOIN ( SELECT vehicle_vin, sum(IF(event_code = ${Event_Mapping.eneu_redlight},1,0)) AS redlight, sum(IF(event_code = ${Event_Mapping.eneu_prescribed_lane},1,0)) AS prescribed_lane, sum(IF(event_code = ${Event_Mapping.eneu_soline_change},1,0)) AS soline_change, sum(IF(event_code = ${Event_Mapping.eneu_over_speed},1,0)) AS over_speed, sum(IF(event_code = ${Event_Mapping.eneu_acceleration},1,0)) AS acceleration, sum(IF(event_code = ${Event_Mapping.eneu_deceleration},1,0)) AS deceleration, sum(IF(event_code = ${Event_Mapping.eneu_sharp_turn},1,0)) AS sharp_turn, sum(IF(event_code = ${Event_Mapping.eneu_fatigue_acceleration},1,0)) AS fatigue_acceleration, sum(IF(event_code = ${Event_Mapping.eneu_fatigue_deceleration},1,0)) AS fatigue_deceleration, sum(IF(event_code = ${Event_Mapping.eneu_fatigue_sharp_turn},1,0)) AS fatigue_sharp_turn, sum(IF(event_code = ${Event_Mapping.eneu_distracted_acceleration},1,0)) AS distracted_acceleration, sum(IF(event_code = ${Event_Mapping.eneu_distracted_deceleration},1,0)) AS distracted_deceleration, sum(IF(event_code = ${Event_Mapping.eneu_distracted_sharp_turn},1,0)) AS distracted_sharp_turn, sum(IF(event_code = ${Event_Mapping.eneu_belt_acceleration},1,0)) AS belt_acceleration, sum(IF(event_code = ${Event_Mapping.eneu_belt_deceleration},1,0)) AS belt_deceleration, sum(IF(event_code = ${Event_Mapping.eneu_belt_sharp_turn},1,0)) AS belt_sharp_turn, sum(IF(event_code = ${Event_Mapping.eneu_turnlight_sharp_turn},1,0)) AS turnlight_sharp_turn, sum(IF(event_code = ${Event_Mapping.eneu_over_speed},NVL(event_last_time,0),0)) AS over_speed_time, sum(IF(event_code = ${Event_Mapping.eneu_without_gear},NVL(event_last_time,0),0)) AS without_gear, sum(IF(event_code = ${Event_Mapping.eneu_too_close},1,0)) AS too_close, sum(IF(event_code = ${Event_Mapping.eneu_over_speed_too_close},1,0)) AS over_speed_too_close, sum(IF(event_code = ${Event_Mapping.eneu_without_seat_belt},NVL(event_last_time,0),0)) AS without_seat_belt, sum(IF(event_code = ${Event_Mapping.eneu_speeding_door},1,0)) AS speeding_door, sum(IF(event_code = ${Event_Mapping.eneu_without_door},1,0)) AS without_door, sum(IF(event_code = ${Event_Mapping.eneu_without_turnlight},1,0)) AS without_turnlight, sum(IF(event_code = ${Event_Mapping.eneu_smoke},1,0)) AS smoke, sum(IF(event_code = ${Event_Mapping.eneu_fatigue_driving},NVL(event_last_time,0),0)) AS fatigue_driving, sum(IF(event_code = ${Event_Mapping.eneu_distracted_driving},1,0)) AS distracted_driving FROM $event_tmp where journey_id not null GROUP BY vehicle_vin ) b ON a.vehicle_vin = b.vehicle_vin
ketingli1
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago create_table tmp_app_da_ads_auto_event_group_d as
select a.org_id as org_id,
a.score_type as event_type,
a.event_name as event_name,
a.event_number as drive_event_number,
abs(a.event_number * a.event_weights) as incidence, --事件权重
pt AS statistical_time, --统计时间精确到
current_timestamp() AS gmt_create,
current_timestamp() AS gmt_modify,
'1' AS status,
a.event_code as event_code,
pt,
a.month as month
from (select event.org_id,
event.score_type,
event.event_code,
event.event_name,
count(1) as event_number,
event_weights,
pt,
event.month as month
from ( --智能安全
SELECT org_id,
fe.event_code,
et.score_type,
fe.event_name,
et.safety_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, safety_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 1
union all
--智能能耗
SELECT org_id,
fe.event_code,
et.score_type,
fe.event_name,
et.fuel_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, fuel_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 2
union all
--安全能耗事件转安全事件
SELECT org_id,
fe.event_code,
1 as score_type,
fe.event_name,
et.safety_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, safety_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 3
union all
--安全能耗事件转能耗事件
SELECT org_id,
fe.event_code,
2 as score_type,
fe.event_name,
et.fuel_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, fuel_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 3) event
group by event.org_id,
event.score_type,
event.event_code,
event.event_name,
event.event_weights,
pt,
event.month) a --查询所有分类事件结果
;
create_table tmp_app_da_ads_auto_event_group_d2 as
-- 20 zixiu start
select a.org_id as org_id,
-1 as event_type,
a.event_name as event_name,
a.event_number as drive_event_number,
-1 as incidence, --事件权重
pt AS statistical_time, --统计时间精确到
current_timestamp() AS gmt_create,
current_timestamp() AS gmt_modify,
'1' AS status,
a.event_code as event_code,
pt as pt,
fe.month as month
from (select event.org_id,
event.event_code,
event.event_name,
count(1) as event_number,
pt,
fe.month
from ( --智能安全
SELECT org_id,
fe.event_code,
fe.event_name,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month
FROM #app_da_fct_event fe
left join (SELECT event_code
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm}'
and fe.event_code IN ( '37','19003302','36')
) event
group by event.org_id,
event.event_code,
event.event_name,
pt,
event.month) a --查询所有分类事件结果 20 zixiu end
;
-- 2、计算行号 window(tmp_app_da_ads_auto_event_group_d, select , row_number() partition_by org_id,event_type order_by incidence desc) as rn; window(tmp_app_da_ads_auto_event_group_d2, select , row_number() partition_by org_id,event_type order_by incidence desc) as rn;
-- 3、设置行号 INSERT overwrite into #app_da_ads_auto_event_group_d partition (month=month_column,pt=pt_column) SELECT org_id, event_type, event_name, rn as rank, drive_event_number, incidence, statistical_time, gmt_create, gmt_modify, status, event_code, pt, month FROM tmp_app_da_ads_auto_event_group_d union all SELECT org_id, event_type, event_name, rn as rank, drive_event_number, incidence, statistical_time, gmt_create, gmt_modify, status, event_code, pt, month FROM tmp_app_da_ads_auto_event_group_d2
yangfan1995
commented
5 years ago insert overwrite into #app_da_ads_nonauto_vehicle_event_d partition (pt='${yyyymmdd-1}', table_name=pt_column)
select a.org_id as org_id,
a.vehicle_vin as vehicle_vin,
nvl(c.vehicle_ln, "") as vehicle_ln, --车牌号
a.event_num as drive_event_number, --当天该车事件总数
b.org_event_num / b.vehicle_vin_num as avg_drive_event_number, --当天该机构平均事件次数
from_unixtime(unix_timestamp('${yyyymmdd-1}','yyyymmdd'),'yyyy-mm-dd 00:00:00') as drive_event_time, --统计的时间日期精确到天
'${yyyymm}' as statistical_time,
current_timestamp() as gmt_create,
current_timestamp() as gmt_modify,
'1' as status,
concat('nonauto_vehicle_event_dtxt', lpad(abs(hash_code(a.vehicle_vin)) % 15, 2, 0)) as t_name,
'${yyyymmdd-1}' as month,
concat('nonauto_vehicle_eventd', lpad(abs(hash_code(a.vehicle_vin)) % 15, 2, 0)) as table_name
from (
--细化到车辆的事件次数
select org_id,
vehicle_vin,
count(1) as event_num
from #app_da_fct_event
where month = '${yyyymm}'
and from_unixtime(event_begin_time, 'yyyyMMdd') = ${pt}
AND event_code IN ('19003303'
, '19003302'
, '19003301'
, '19003239'
, '40'
, '41'
, '19003304'
, '42')
group by org_id,
vehicle_vin
) a
left outer join
(
--机构下的时间次数
select org_id,
count(1) as org_event_num,
count(distinct vehicle_vin) as vehicle_vin_num
from #app_da_fct_event
where month = '${yyyymm}}'
and from_unixtime(event_begin_time, 'yyyyMMdd') = ${pt}
AND event_code IN ('19003303'
, '19003302'
, '19003301'
, '19003239'
, '40'
, '41'
, '19003304'
, '42')
group by org_id
) b
ON a.org_id = b.org_id
left outer join
(
--车牌号等信息
select distinct vehicle_vin,
vehicle_ln,
vehicle_id
from #app_da_dim_vehicle
) c
ON a.vehicle_vin = c.vehicle_vin
;
yangfan1995
commented
5 years ago create_table tmp_app_da_ads_auto_event_group_d as
select a.org_id as org_id,
a.score_type as event_type,
a.event_name as event_name,
a.event_number as drive_event_number,
abs(a.event_number * a.event_weights) as incidence, --事件权重
pt AS statistical_time, --统计时间精确到
current_timestamp() AS gmt_create,
current_timestamp() AS gmt_modify,
'1' AS status,
a.event_code as event_code,
pt,
a.month as month
from (select event.org_id,
event.score_type,
event.event_code,
event.event_name,
count(1) as event_number,
event_weights,
pt,
event.month as month
from ( --智能安全
SELECT org_id,
fe.event_code,
et.score_type,
fe.event_name,
et.safety_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, safety_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 1
union all
--智能能耗
SELECT org_id,
fe.event_code,
et.score_type,
fe.event_name,
et.fuel_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, fuel_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 2
union all
--安全能耗事件转安全事件
SELECT org_id,
fe.event_code,
1 as score_type,
fe.event_name,
et.safety_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, safety_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 3
union all
--安全能耗事件转能耗事件
SELECT org_id,
fe.event_code,
2 as score_type,
fe.event_name,
et.fuel_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, fuel_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 3) event
group by event.org_id,
event.score_type,
event.event_code,
event.event_name,
event.event_weights,
pt,
event.month) a --查询所有分类事件结果
;
create_table tmp_app_da_ads_auto_event_group_d2 as
-- 20 zixiu start
select a.org_id as org_id,
-1 as event_type,
a.event_name as event_name,
a.event_number as drive_event_number,
-1 as incidence, --事件权重
pt AS statistical_time, --统计时间精确到
current_timestamp() AS gmt_create,
current_timestamp() AS gmt_modify,
'1' AS status,
a.event_code as event_code,
pt as pt,
a.month as month
from (select event.org_id,
event.event_code,
event.event_name,
count(1) as event_number,
pt,
event.month
from ( --智能安全
SELECT org_id,
fe.event_code,
fe.event_name,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month
FROM #app_da_fct_event fe
left join (SELECT event_code
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm}'
and fe.event_code IN ( '37','19003302','36')
) event
group by event.org_id,
event.event_code,
event.event_name,
pt,
event.month) a --查询所有分类事件结果 20 zixiu end
;
-- 2、计算行号 window(tmp_app_da_ads_auto_event_group_d, select , row_number() partition_by org_id,event_type order_by incidence desc) as rn; window(tmp_app_da_ads_auto_event_group_d2, select , row_number() partition_by org_id,event_type order_by incidence desc) as rn;
-- 3、设置行号 INSERT overwrite into #app_da_ads_auto_event_group_d partition (month=month_column,pt=pt_column) SELECT org_id, event_type, event_name, rn as rank, drive_event_number, incidence, statistical_time, gmt_create, gmt_modify, status, event_code, pt, month FROM tmp_app_da_ads_auto_event_group_d union all SELECT org_id, event_type, event_name, rn as rank, drive_event_number, incidence, statistical_time, gmt_create, gmt_modify, status, event_code, pt, month FROM tmp_app_da_ads_auto_event_group_d2
yangfan1995
commented
5 years ago create_table tmp_app_da_ads_auto_event_group_d as
select a.org_id as org_id,
a.score_type as event_type,
a.event_name as event_name,
a.event_number as drive_event_number,
abs(a.event_number * a.event_weights) as incidence, --事件权重
pt AS statistical_time, --统计时间精确到
current_timestamp() AS gmt_create,
current_timestamp() AS gmt_modify,
'1' AS status,
a.event_code as event_code,
pt,
month
from (select event.org_id,
event.score_type,
event.event_code,
event.event_name,
count(1) as event_number,
event_weights,
pt,
event.month as month
from ( --智能安全
SELECT org_id,
fe.event_code,
et.score_type,
fe.event_name,
et.safety_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, safety_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 1
union all
--智能能耗
SELECT org_id,
fe.event_code,
et.score_type,
fe.event_name,
et.fuel_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, fuel_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 2
union all
--安全能耗事件转安全事件
SELECT org_id,
fe.event_code,
1 as score_type,
fe.event_name,
et.safety_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, safety_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 3
union all
--安全能耗事件转能耗事件
SELECT org_id,
fe.event_code,
2 as score_type,
fe.event_name,
et.fuel_event_weights as event_weights,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month as month
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, fuel_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 3) event
group by event.org_id,
event.score_type,
event.event_code,
event.event_name,
event.event_weights,
pt,
event.month) a --查询所有分类事件结果
;
create_table tmp_app_da_ads_auto_event_group_d2 as
-- 20 zixiu start
select a.org_id as org_id,
-1 as event_type,
a.event_name as event_name,
a.event_number as drive_event_number,
-1 as incidence, --事件权重
pt AS statistical_time, --统计时间精确到
current_timestamp() AS gmt_create,
current_timestamp() AS gmt_modify,
'1' AS status,
a.event_code as event_code,
pt,
month
from (select event.org_id,
event.event_code,
event.event_name,
count(1) as event_number,
pt,
event.month
from ( --智能安全
SELECT org_id,
fe.event_code,
fe.event_name,
from_unixtime(event_begin_time, 'yyyyMMdd') as pt,
fe.month
FROM #app_da_fct_event fe
left join (SELECT event_code
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm}'
and fe.event_code IN ( '37','19003302','36')
) event
group by event.org_id,
event.event_code,
event.event_name,
pt,
event.month) a --查询所有分类事件结果 20 zixiu end
;
-- 2、计算行号 window(tmp_app_da_ads_auto_event_group_d, select , row_number() partition_by org_id,event_type order_by incidence desc) as rn; window(tmp_app_da_ads_auto_event_group_d2, select , row_number() partition_by org_id,event_type order_by incidence desc) as rn;
-- 3、设置行号 INSERT overwrite into #app_da_ads_auto_event_group_d partition (month=month_column,pt=pt_column) SELECT org_id, event_type, event_name, rn as rank, drive_event_number, incidence, statistical_time, cast(gmt_create as TIMESTAMP) as gmt_create, cast(gmt_modify as timestamp) as gmt_modify, status, event_code, pt, month FROM tmp_app_da_ads_auto_event_group_d union all SELECT org_id, event_type, event_name, rn as rank, drive_event_number, incidence, statistical_time, cast(gmt_create as TIMESTAMP) as gmt_create, cast(gmt_modify as timestamp) as gmt_modify, status, event_code, pt, month FROM tmp_app_da_ads_auto_event_group_d2;
yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago create_table tmp_app_da_ads_auto_event_group_m as select event.org_id as org_id, event.score_type as event_type, event.event_code as event_code, event.event_name as event_name, count(1) as event_number, event_weights from ( --智能安全 SELECT org_id, fe.event_code, et.score_type, fe.event_name, et.safety_event_weights as event_weights FROM #app_da_fct_event fe left join (SELECT event_code, score_type, event_type, safety_event_weights FROM #app_da_dim_event_type) et on fe.event_code = et.event_code where month = '${yyyymm-1}' and score_type = 1 union all --智能能耗 SELECT org_id, fe.event_code, et.score_type, fe.event_name, et.fuel_event_weights as event_weights FROM #app_da_fct_event fe left join (SELECT event_code, score_type, event_type, fuel_event_weights FROM #app_da_dim_event_type) et on fe.event_code = et.event_code where month = '${yyyymm-1}' and score_type = 2 union all --安全能耗事件转安全事件 SELECT org_id, fe.event_code, 1 as score_type, fe.event_name, et.safety_event_weights as event_weights FROM #app_da_fct_event fe left join (SELECT event_code, score_type, event_type, safety_event_weights FROM #app_da_dim_event_type) et on fe.event_code = et.event_code where month = '${yyyymm-1}' and score_type = 3 union all --安全能耗事件转能耗事件 SELECT org_id, fe.event_code, 2 as score_type, fe.event_name, et.fuel_event_weights as event_weights FROM #app_da_fct_event fe left join (SELECT event_code, score_type, event_type, fuel_event_weights FROM #app_da_dim_event_type) et on fe.event_code = et.event_code where month = '${yyyymm-1}' and score_type = 3) event group by event.org_id, event.score_type, event.event_code, event.event_name, event.event_weights;
-- 2、计算行号 window(tmp_app_da_ads_auto_event_group_m, select *, row_number() partition_by org_id,event_type order_by incidence desc) as rn;
insert overwrite into #app_da_ads_auto_event_group_m partition (month = '${yyyymm-1}') --查询事件排名
select org_id,
event_type,
event_name,
rn as rank,
event_name as drive_event_number,
abs(a.event_number * a.event_weights) as incidence, --事件权重
'${yyyymm-1}' AS statistical_time, --统计时间精确到月
current_timestamp() AS gmt_create,
current_timestamp() AS gmt_modify,
'1' AS status,
event_code
from tmp_app_da_ads_auto_event_group_m
;
yangfan1995
commented
5 years ago create_table tmp_app_da_ads_auto_event_group_m as
select a.org_id as org_id,
a.score_type as event_type,
a.event_name as event_name,
a.event_number as drive_event_number,
abs(a.event_number * a.event_weights) as incidence, --事件权重
pt AS statistical_time, --统计时间精确到
current_timestamp() AS gmt_create,
current_timestamp() AS gmt_modify,
'1' AS status,
a.event_code as event_code
from ( --智能安全
SELECT org_id,
fe.event_code,
et.score_type,
fe.event_name,
et.safety_event_weights as event_weights
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, safety_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 1
union all
--智能能耗
SELECT org_id,
fe.event_code,
et.score_type,
fe.event_name,
et.fuel_event_weights as event_weights
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, fuel_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 2
union all
--安全能耗事件转安全事件
SELECT org_id,
fe.event_code,
1 as score_type,
fe.event_name,
et.safety_event_weights as event_weights
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, safety_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 3
union all
--安全能耗事件转能耗事件
SELECT org_id,
fe.event_code,
2 as score_type,
fe.event_name,
et.fuel_event_weights as event_weights
FROM #app_da_fct_event fe
left join (SELECT event_code, score_type, event_type, fuel_event_weights
FROM #app_da_dim_event_type) et
on fe.event_code = et.event_code
where month = '${yyyymm-1}'
and score_type = 3) event
group by event.org_id,
event.score_type,
event.event_code,
event.event_name,
event.event_weights;
-- 2、计算行号 window(tmp_app_da_ads_auto_event_group_m, select *, row_number() partition_by org_id,event_type order_by incidence desc) as rn;
insert overwrite into #app_da_ads_auto_event_group_m partition (month = '${yyyymm-1}') --查询事件排名 SELECT org_id, event_type, event_name, rn as rank, drive_event_number, incidence, statistical_time, cast(gmt_create as string) as gmt_create, cast(gmt_modify as string) as gmt_modify, status, event_code from tmp_app_da_ads_auto_event_group_m ;
yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago selelct "15078544A95C4037B32049D3B93AFB56","杭州长运运输集团有限公司","浙A1V753","FFCEF1464C5145D69264B95F30205F3E","LZYTFTC25J1054033","违反夜间禁行行驶","1","2","2019/9/1 5:19","2019/9/1 5:20","0","0","120.085903","120.086343","30.262798","30.262922","0","0","0","LZYTFTC25J1054033_1567286340_42","LZYTFTC25J1054033-201909-p-1-s-1","0","201909","2019/9/10 13:54","2019/9/10 13:54","1","event_detail_txt_0615","42","201909","20190909","event_detail_0615" union all selelct "15078544A95C4037B32049D3B93AFB56","杭州长运运输集团有限公司","浙A1V753","FFCEF1464C5145D69264B95F30205F3E","LZYTFTC25J1054033","违反夜间禁行行驶","1","2","2019/9/1 5:38","2019/9/1 5:59","0","0","120.085915","120.085915","30.262985","30.262985","0","0","0","LZYTFTC25J1054033_1567287537_42","LZYTFTC25J1054033-201909-p-1-s-3","0","201909","2019/9/10 13:54","54:13.3","1","event_detail_txt_0615","42","201909","20190909","event_detail_0615"
yangfan1995
commented
5 years ago insert overwrite into #app_da_ads_nonauto_vehicle_event_d partition (pt='${yyyymmdd-1}', table_name=pt_column)
select a.org_id as org_id,
a.vehicle_vin as vehicle_vin,
nvl(c.vehicle_ln, "") as vehicle_ln, --车牌号
a.event_num as drive_event_number, --当天该车事件总数
nvl(b.org_event_num / b.vehicle_vin_num,0) as avg_drive_event_number, --当天该机构平均事件次数
from_unixtime(unix_timestamp('${yyyymmdd-1}', 'yyyymmdd'),
'yyyy-mm-dd 00:00:00') as drive_event_time, --统计的时间日期精确到天
'${yyyymm}' as statistical_time,
current_timestamp() as gmt_create,
current_timestamp() as gmt_modify,
'1' as status,
concat('nonauto_vehicle_event_dtxt', lpad(abs(hash_code(a.vehicle_vin)) % 15, 2, 0)) as t_name,
'${yyyymmdd-1}' as month,
concat('nonauto_vehicle_eventd', lpad(abs(hash_code(a.vehicle_vin)) % 15, 2, 0)) as table_name
from (
--细化到车辆的事件次数
select a.org_id,
a.vehicle_vin,
if(b.vehicle_vin is null, 0, b.event_num) as event_num
from (
select distinct org_id,
vehicle_vin
from #app_da_fct_event
where month = '${yyyymm}'
) a
left outer join
(select org_id,
vehicle_vin,
count(1) as event_num
from #app_da_fct_event
where month = '${yyyymm}'
and from_unixtime(event_begin_time
, 'yyyyMMdd') = '${yyyymmdd-1}'
AND event_code IN ('19003303'
, '19003302'
, '19003301'
, '19003239'
, '40'
, '41'
, '19003304'
, '42')
group by org_id,
vehicle_vin
) b
on a.org_id = b.org_id
and a.vehicle_vin = b.vehicle_vin
) a
left outer join
(
--机构下的时间次数
select org_id,
count(1) as org_event_num,
count(distinct vehicle_vin) as vehicle_vin_num
from #app_da_fct_event
where month = '${yyyymm}'
and from_unixtime(event_begin_time
, 'yyyyMMdd') = '${yyyymmdd-1}'
AND event_code IN ('19003303'
, '19003302'
, '19003301'
, '19003239'
, '40'
, '41'
, '19003304'
, '42')
group by org_id
) b
ON a.org_id = b.org_id
left outer join
(
--车牌号等信息
select distinct vehicle_vin,
vehicle_ln,
vehicle_id
from #app_da_dim_vehicle
) c
ON a.vehicle_vin = c.vehicle_vin
;
yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago iwuming
commented
5 years ago select vehicle_vin,count(*) from #app_da_fct_manual_score_event where month=201811 and event_code IN ('19003303' , '19003302' , '19003301' , '19003239' , '40' , '41' , '19003304' , '42') group by vehicle_vin
iwuming
commented
5 years ago select vehicle_vin,count(*) from #app_da_fct_event where month=201811 and event_code IN ('19003303' , '19003302' , '19003301' , '19003239' , '40' , '41' , '19003304' , '42') group by vehicle_vin
iwuming
commented
5 years ago select * from #app_da_fct_vehicle_scoring where month=201811
iwuming
commented
5 years ago select * from #app_da_s_od_manual_score_rule;
iwuming
commented
5 years ago exe_shell_app_da_ManualScoreApplication
yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago "vehicle_vin","org_id","org_name","safe_score","manual_safe_score","energy_score","safe_drive_suggest","energy_drive_suggest","safe_score_order","energy_score_order","manual_safe_score_order","total_fuel","total_mileage"
yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago "event_id","driver_id","vehicle_vin","event_code_id","org_id","journey_id","org_name","event_code","event_name","event_weights","event_begin_time","event_end_time","begin_latitude","begin_longitude","end_latitude","end_longitude","event_last_time","speed","steer_state","braking_state","journey_cumulative_mileage","journey_cumulative_fuel"
yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago yangfan1995
commented
5 years ago iwuming
commented
5 years ago set -e
source /home/hadoop/spark-2.2.2-bin-hadoop2.7/conf/spark-env.sh
/home/hadoop/spark-2.2.2-bin-hadoop2.7/bin/spark-submit --verbose \ --class com.icongtai.zebra.db.dwd.ManualScoreApplication \ --master yarn \ --deploy-mode cluster \ --num-executors "15" \ --executor-memory "5g" \ --executor-cores "2" \ --driver-memory "2g" \ --conf spark.yarn.am.waitTime=600s \ /home/hadoop/banma/driver-behavior-bigdata-source-1.0-SNAPSHOT.jar \ /ytdw/app ${yyyymm}
sh /home/hadoop/banma/resource/shell/datax/yt_shell/Move2.sh app_da_fct_vehicle_scoring ${yyyymm}
iwuming
commented
5 years ago yangfan1995
commented
5 years ago 15078544A95C4037B32049D3B93AFB56
iwuming
commented
5 years ago insert overwrite into #app_da_dim_driver_mid select driver_id, driver_tel, a.driver_org as sub_org_id, if(b.belong_end_id is null or b.belong_end_id = '', a.driver_org, b.belong_end_id) as driver_org, driver_name, gender, identification_card, driver_no, driver_code, driver_cert_code, a.gmt_create, a.gmt_modify, a.status from #app_da_dim_driver a LEFT OUTER JOIN
ON a.driver_org = b.org_id ;
insert overwrite into #app_da_dim_driver select driver_id, driver_tel, driver_org, sub_org_id, driver_name, gender, identification_card, driver_no, driver_code, driver_cert_code, gmt_create, gmt_modify, status from #app_da_dim_driver_mid ;
iwuming
commented
5 years ago insert overwrite into #app_da_dim_vehicle_mid select vehicle_id, vehicle_vin, a.org_id as sub_org_id, if(b.belong_end_id is null or b.belong_end_id = '', a.org_id, b.belong_end_id) as org_id, vehicle_sn, vehicle_ln, vehicle_plate_color, vehicle_color, reg_state,reg_date, memt_state, a.gmt_create, a.gmt_modify, a.status from #app_da_dim_vehicle a left outer join
ON a.org_id = b.org_id ;
insert overwrite into #app_da_dim_vehicle select vehicle_id, vehicle_vin, org_id, sub_org_id, vehicle_sn, vehicle_ln, vehicle_plate_color, vehicle_color, reg_state,reg_date, memt_state, gmt_create, gmt_modify, status from #app_da_dim_vehicle_mid ;
iwuming
commented
5 years ago insert overwrite into #app_da_dim_driver select driver_id,mobile_phone as driver_tel,'' as sub_org_id,org_id as driver_org,driver_name,gender,identification_card,driver_no,driver_code,driver_cert_code, row_create_time as gmt_create,row_modify_time as gmt_modify,1 as status from #dim_bsp_tb_driver
iwuming
commented
5 years ago SELECT driver_id, driver_tel as mobile_phone, driver_org as org_id, sub_org_id, driver_name, gender, -- identification_card, driver_no, driver_code, driver_cert_code, gmt_create, gmt_modify -- status FROM #app_da_dim_driver
iwuming
commented
5 years ago insert overwrite into #app_da_dim_vehicle select vehicle_id,vehicle_vin,'' as sub_org_id,org_id,vehicle_sn,vehicle_ln,vehicle_plate_color,vehicle_color,reg_state,reg_date, mgmt_state as memt_state,row_create_time as gmt_create,row_modify_time as gmt_modify,1 as status from #dim_bsp_tb_vehicle
iwuming
commented
5 years ago SELECT vehicle_vin, org_id, sub_org_id, vehicle_sn, vehicle_ln, if (length(vehicle_plate_color)> 6,null,vehicle_plate_color) as vehicle_plate_color, vehicle_color, reg_state, reg_date, memt_state, gmt_create, gmt_modify FROM #app_da_dim_vehicle
计算数据导出