司机画像标签-日表

1、司机画像明细表

CREATE EXTERNAL TABLE `dwd_lms`.`dwd_lms_otp_driver_portrait_detail_di`(
  `task_no` string COMMENT '运输单号',
  `dispatch_no` string COMMENT '发车单号',
  `customer_order_no` string COMMENT '客户订单号',
  `order_no` string COMMENT '物流单号',
  `site_code` string COMMENT '平台编码',
  `site_name` string COMMENT '平台名称',
  `carrier_code` string COMMENT '供应商编码',
  `carrier_name` string COMMENT '供应商名称',
  `wh_code` string COMMENT '运输单始发仓库编码',
  `wh_name` string COMMENT '运输单始发仓库名称',
  `line_code` string COMMENT '运输单线路编码',
  `line_name` string COMMENT '运输单线路名称',
  `mileage` decimal(18, 8) COMMENT '运输单线路里程',
  `sender_province_code` string COMMENT '始发省编码',
  `sender_province_name` string COMMENT '始发省名称',
  `sender_city_code` string COMMENT '始发市编码',
  `sender_city_name` string COMMENT '始发市名称',
  `sender_district_code` string COMMENT '始发区县编码',
  `sender_district_name` string COMMENT '始发区县名称',
  `sender_town_code` string COMMENT '始发街道编码',
  `sender_town_name` string COMMENT '始发街道名称',
  `receiver_province_code` string COMMENT '收货省编码',
  `receiver_province_name` string COMMENT '收货省名称',
  `receiver_city_code` string COMMENT '收货市编码',
  `receiver_city_name` string COMMENT '收货市名称',
  `receiver_district_code` string COMMENT '收货区县编码',
  `receiver_district_name` string COMMENT '收货区县名称',
  `receiver_town_code` string COMMENT '收货街道编码',
  `receiver_town_name` string COMMENT '收货街道名称',
  `customer_code` string COMMENT '客户编码',
  `customer_name` string COMMENT '客户名称',
  `driver_code` string COMMENT '司机编码',
  `driver_name` string COMMENT '司机姓名',
  `driver_phone` string COMMENT '司机手机',
  `supplier_type_code` string COMMENT '采购渠道编码',
  `supplier_type_name` string COMMENT '采购渠道名称',
  `vehicle_card` string COMMENT '车牌号',
  `equipment_code` string COMMENT '车型编码',
  `dispatch_time` string COMMENT '发车时间',
  `navigation_mileage` decimal(18, 8) COMMENT '发车单配送总里程',
  `act_volume` decimal(18, 8) COMMENT '实际发运体积',
  `act_weight` decimal(18, 8) COMMENT '实际发运重量',
  `dsp_arrival_time` string COMMENT '发车单抵达时间',
  `pro_company_code` string COMMENT '专业公司编码',
  `pro_company_name` string COMMENT '专业公司名称',
  `shipping_type_code` string COMMENT '发运类型编码',
  `shipping_type_name` string COMMENT '发运类型名称',
  `industry_type_code` string COMMENT '行业大类编码',
  `industry_type_name` string COMMENT '行业大类名称',
  `modify_time` string COMMENT '修改时间',
  `w_insert_date` string COMMENT '更新时间',
  `direct_control_flag` string COMMENT '是否直控线路',
  `freight_basis` string COMMENT '是否搬仓',
  `business_mode` string COMMENT '业务模式',
  `delivery_type` string COMMENT '配送方式',
  `sc_pos_flag` string COMMENT '是否商超',
  `is_rescheduling` string COMMENT '是否改约',
  `is_lock_mark` string COMMENT '是否固定标',
  `is_onkey_dispatch` string COMMENT '是否使用一键排车',
  `is_fill_dispatch` string COMMENT '是否填完车排',
  `is_dispatch_change` string COMMENT '是否经过转移',
  `create_time` string COMMENT '创建时间'
) COMMENT '司机画像明细表' PARTITIONED BY (`part_dt` string COMMENT '日期分区')
STORED AS PARQUET TBLPROPERTIES ('external.table.purge' = 'true');

2、按发车单统计的维度信息

--按发车单统计的维度信息
insert overwrite table temp_lms.dws_lms_otp_driver_portrait_detail_di_tmp1
select   driver_code      --司机编码
        ,max(case when rn1 = 1 then site_code end) as last_dispatch_site_code            --最近一次合作服务平台编码
        ,max(case when rn1 = 1 then carrier_code end) as last_dispatch_supplier_code     --最近一次合作供应商编码
		,max(case when rn1 = 1 then carrier_name end) as last_dispatch_supplier_name     --最近一次合作供应商名称
		,max(case when rn1 = 1 then dispatch_time end) as last_dispatch_time             --最近一次合作供应商名称
        ,max(case when rn2 = 1 then act_weight end) as max_dispatch_weight               --最近30天最大载重
		,max(case when rn3 = 1 then act_volume end) as max_dispatch_volume               --最近30天最大方量
        ,concat_ws(',',collect_set(industry_list)) as industry_list                      --承运的行业	
from (
        select driver_code
              ,site_code
              ,carrier_code
		      ,carrier_name
              ,dispatch_time
              ,act_weight
              ,act_volume
              ,concat_ws(',',collect_set(industry_type_name)) as industry_type_name
              ,row_number() over(partition by driver_code order by dispatch_time desc) as rn1
              ,row_number() over(partition by driver_code order by act_weight desc) as rn2
              ,row_number() over(partition by driver_code order by act_volume desc) as rn3
        from(
              select  driver_code
                     ,site_code
                     ,carrier_code
		             ,carrier_name
                     ,dispatch_no
                     ,concat_ws(',',collect_set(industry_type_name)) as industry_type_name
                     ,max(dispatch_time) as dispatch_time
	  	             ,sum(act_weight/1000) as act_weight
	  	             ,sum(act_volume) as act_volume
              from dwd_lms.dwd_lms_otp_driver_portrait_detail_di
              where dispatch_time >= date_sub('${START_DATE}',30) and dispatch_time < date_add('${END_DATE}',1)
              and driver_code is not null   --过滤司机编码为空的数据
              group by driver_code
                      ,site_code
				      ,carrier_code
		              ,carrier_name
                      ,dispatch_no
             ) d
          group by driver_code
                  ,site_code
		          ,carrier_code
		          ,carrier_name
                  ,dispatch_time
                  ,act_weight
                  ,act_volume
        )t
        lateral view explode(split(industry_type_name,',')) t as industry_list   --行业有重复的，得炸开再去重拼接
group by driver_code
;

3、按车牌统计的维度信息

--按车牌统计的维度信息
insert overwrite table temp_lms.dws_lms_otp_driver_portrait_detail_di_tmp2

select  
	 driver_code																		--司机编码
	,max(case when t.rn=1 then t.vehicle_card end)			as top1_car_no				--司机最近30天常用车牌top1
	,max(case when t.rn=1 then t.car_cp_cnt end)			as top1_car_cp_cnt			--top1车牌发运趟次
	,max(case when t.rn=2 then t.vehicle_card end)			as top2_car_no				--司机最近30天常用车牌top2
	,max(case when t.rn=2 then t.car_cp_cnt end)			as top2_car_cp_cnt			--top2车牌发运趟次
	,max(case when t.rn=3 then t.vehicle_card end)			as top3_car_no				--司机最近30天常用车牌top3
	,max(case when t.rn=3 then t.car_cp_cnt end)			as top3_car_cp_cnt			--top3车牌发运趟次
	,count(vehicle_card)									as last30_work_car_cnt		--司机最近30天使用的车辆数
	,sum(t.car_cp_cnt)										as total_cp_cnt				--最近30天总发车单数
	,sum(t.car_cp_cnt)/30									as avg_day_cp_cnt			--最近30天日均发运趟次
	,sum(t.car_cp_90_cnt)									as last90_dispatch_cnt		--最近90天总发车单数	--20240912	xxxx	新增
	,sum(t.car_cp_365_cnt)									as last365_dispatch_cnt		--最近365天总发车单数	--20240912	xxxx	新增
from(
	select   
		 d.driver_code
		,d.vehicle_card
		,d.car_cp_cnt
		,a.car_cp_90_cnt
		,b.car_cp_365_cnt
		,row_number() over(partition by d.driver_code order by d.car_cp_cnt desc)	as rn
	from(
		select  
			 driver_code
			,vehicle_card
			,count(distinct dispatch_no)											as car_cp_cnt
		from 
			dwd_lms.dwd_lms_otp_driver_portrait_detail_di
		where dispatch_time >= date_sub('${START_DATE}',30) and dispatch_time < date_add('${END_DATE}',1)
			  and driver_code is not null   --过滤司机编码为空的数据
		group by 
			 driver_code
			,vehicle_card
		) d
		inner join 
		(
		select
			 driver_code
			,vehicle_card
			,count(distinct dispatch_no)  as car_cp_90_cnt
		from 
			dwd_lms.dwd_lms_otp_driver_portrait_detail_di
		where dispatch_time >= date_sub('${START_DATE}',90) and dispatch_time < date_add('${END_DATE}',1)
			  and driver_code is not null   --过滤司机编码为空的数据
		group by 
			 driver_code
			,vehicle_card
		) a
		on  d.driver_code=a.driver_code 
			and d.vehicle_card=a.vehicle_card
		inner join 
		(
             select 
				 driver_code
				,vehicle_card
				,count(distinct dispatch_no)  as car_cp_365_cnt
             from 
				dwd_lms.dwd_lms_otp_driver_portrait_detail_di
             where dispatch_time >= date_sub('${START_DATE}',365) and dispatch_time < date_add('${END_DATE}',1)
             and driver_code is not null   --过滤司机编码为空的数据
             group by  driver_code
                      ,vehicle_card
           ) b
		on  d.driver_code=b.driver_code 
			and d.vehicle_card=b.vehicle_card

        group by 
			 d.driver_code
			,d.vehicle_card
			,d.car_cp_cnt
			,a.car_cp_90_cnt
			,b.car_cp_365_cnt
    )t

group by driver_code

;

4、以司机编码为主键，整合发车单统计部分及车牌统计部分信息

--以司机编码为主键，整合发车单统计部分及车牌统计部分信息

insert overwrite table temp_lms.dws_lms_otp_driver_portrait_detail_di_tmp3

select  
	 t1.driver_code																	--司机编码
	,wsc.company_code					as last_dispatch_company_code				--最近一次合作分公司编码
	,wsc.company_name					as last_dispatch_company_name				--最近一次合作分公司名称
	,wsc.center_code					as last_dispatch_center_code				--最近一次合作经营中心编码
	,wsc.center_name					as last_dispatch_center_name				--最近一次合作经营中心名称
	,t1.last_dispatch_site_code														--最近一次合作服务平台编码
	,wsc.site_name						as last_dispatch_site_name					--最近一次合作服务平台名称
	,t1.last_dispatch_supplier_code													--最近一次合作供应商编码
	,t1.last_dispatch_supplier_name													--最近一次合作供应商名称
	,t1.last_dispatch_time															--最近一次合作时间
	,t2.top1_car_no																	--司机最近30天常用车牌top1
	,t2.top1_car_cp_cnt																--top1车牌发运趟次
	,t2.top2_car_no																	--司机最近30天常用车牌top2
	,t2.top2_car_cp_cnt																--top2车牌发运趟次
	,t2.top3_car_no																	--司机最近30天常用车牌top3
	,t2.top3_car_cp_cnt																--top3车牌发运趟次
	,t2.last30_work_car_cnt															--司机最近30天使用的车辆数
	,t2.total_cp_cnt																--最近30天总发车单数
	,t2.avg_day_cp_cnt																--最近30天日均发运趟次
	,t1.max_dispatch_weight															--最近30天最大载重
	,t1.max_dispatch_volume															--最近30天最大方量
	,t1.industry_list																--承运的行业
	,t2.last90_dispatch_cnt															--最近90天总发车单数	--20240912	xxxx	新增
	,t2.last365_dispatch_cnt														--最近365天总发车单数	--20240912	xxxx	新增
from 
     temp_lms.dws_lms_otp_driver_portrait_detail_di_tmp1 t1
inner join 
	temp_lms.dws_lms_otp_driver_portrait_detail_di_tmp2 t2 
on t1.driver_code = t2.driver_code
left join (
	select 
		site_code
		,site_name
		,company_code
		,company_name
		,center_code
		,center_name
	from 
		dim.dim_lms_warehouse_site_company_d    --组织架构维表
	group by 
		 site_code
		,site_name
		,company_code
		,company_name
		,center_code
		,center_name
) wsc 
on t1.last_dispatch_site_code = wsc.site_code  

;

5、更新数据写入宽表

--更新数据写入宽表

insert overwrite table dws_lms.dws_lms_otp_driver_portrait_detail_di partition(part_dt='${START_DATE}')

select  
	 a.driver_code                                                				--司机编码
	,a.last_dispatch_company_code                                 				--最近一次合作分公司编码
	,a.last_dispatch_company_name                                 				--最近一次合作分公司名称
	,a.last_dispatch_center_code                                  				--最近一次合作经营中心编码
	,a.last_dispatch_center_name                                  				--最近一次合作经营中心名称
	,a.last_dispatch_site_code                                    				--最近一次合作服务平台编码
	,a.last_dispatch_site_name                                    				--最近一次合作服务平台名称
	,a.last_dispatch_supplier_code                                				--最近一次合作供应商编码
	,a.last_dispatch_supplier_name                                				--最近一次合作供应商名称
	,a.last_dispatch_time                                         				--最近一次合作时间
	,a.top1_car_no                                                				--司机最近30天常用车牌top1
	,a.top1_car_cp_cnt                                            				--top1车牌发运趟次
	,a.top2_car_no                                                				--司机最近30天常用车牌top2
	,a.top2_car_cp_cnt                                            				--top2车牌发运趟次
	,a.top3_car_no                                                				--司机最近30天常用车牌top3
	,a.top3_car_cp_cnt                                            				--top3车牌发运趟次
	,a.last30_work_car_cnt                                        				--司机最近30天使用的车辆数
	,a.total_cp_cnt                                               				--最近30天总发车单数
	,a.avg_day_cp_cnt                                             				--最近30天日均发运趟次
	,a.max_dispatch_weight                                        				--最近30天最大载重
	,a.max_dispatch_volume                                        				--最近30天最大方量
	,a.industry_list	                                          				--承运的行业
	,from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as w_insert_date    --本表更新时间
	,a.last90_dispatch_cnt														--最近90天总发车单数	--20240912	xxxx	新增
	,a.last365_dispatch_cnt														--最近365天总发车单数	--20240912	xxxx	新增
from 
      temp_lms.dws_lms_otp_driver_portrait_detail_di_tmp3 a
;