with mid_dmd_opr_lia_call_info_df as (
select
t1.call_id
,concat_ws('-',collect_list(node_id_cd)) node_id_cd
,concat_ws('-',collect_list(mission_name)) mission_name
from (
select
t1.call_id
,node_id_cd
,t2.mission_name
from (
select
t1.call_id
,t1.node_id
,node_id_cd
,node_id_name
from (
select *,row_number() over(PARTITION by call_id ORDER BY cast(order_id as bigint) desc) rn
from lods.lods_icrm_dezhu_ivroperatelog ) t1
lateral view posexplode(split ( node_id,'-' )) t as node_id_cd, node_id_name
where t1.rn = 1 ) t1
left join (
SELECT mission_code ,mission_name from
( SELECT * ,row_number() over (PARTITION by mission_code order by _hoodie_commit_time desc ) as num
from lods.lods_msxfcc_wf_mission where is_report_node ='true' )
where num=1) t2
on t2.mission_code = t1.node_id_name
order by 1,2 asc,3 ) t1
group by 1
)
insert overwrite table dm_opr.dmd_opr_lia_call_info_iceberg_df partition(dt)
select
t1.call_room_id
,t1.tenant_id
,case t1.call_type
when 1 then '呼入'
when 2 then '外呼'
when 3 then '外呼任务'
when 4 then '内呼'
when 11 then '无坐席呼叫'
else cast(t1.call_type as string) end call_type
,t1.icc_call_id
,t1.caller_phone
,t1.caller_participant_id
,t1.callee_phone
,t1.callee_participant_id
,substr(t1.start_time,1,19) room_start_time
,substr(t1.hang_up_time,1,19) room_hang_up_time
,case t1.hang_up_result
when 'CONTACT_DROPPED_CALL' then 'C端挂断'
when 'AGENT_DROPPED_CALL' then 'B端挂断'
when 'SYSTYEM_SCHED_DROPPED_CALL' then '系统挂断'
when 'SYSTYEM_RTS_TIMEOUT_CALL' then 'rts超时挂断'
when 'AGENT_TIMEOUT_DROPPED_CALL' then '坐席超时挂断'
when 'CONTACT_TIMEOUT_DROPPED_CALL' then '客户超时挂断'
else t1.hang_up_result end room_hang_up_dirct
,t1.ring_duration
,t1.queue_duration
,t1.hold_duration
,t1.talk_duration
,t1.agent_talk_duration
,t1.icc_call_result
,t1.source
,t1.source_identity
,t1.source_data
,t2.transfer_id
,t2.from_transfer_id
,t2.digit_url
,substr(t3.start_time,1,19) queue_start_time
,substr(t3.end_time,1,19) queue_end_time
,t3.duration
,t3.skillsets_id
,t3.queue_role
,t3.queue_role_id
,t4.participant_id
,t4.login_type
,t4.skillsets_id
,t4.out_phone
,t4.did_phone
,t4.line_phone
,substr(t5.start_time,1,19) agent_start_time
,substr(t5.parking_time,1,19) agent_parking_time
,substr(t5.ring_time,1,19) agent_ring_time
,substr(t5.answer_time,1,19) agent_answer_time
,substr(t5.bridge_time,1,19) agent_bridge_time
,substr(t5.hangup_time,1,19) agent_hangup_time
,t5.acw_configuration
,t5.acw_duration
,t5.ring_duration
,t5.queue_duration
,t5.hold_duration
,t5.talk_duration
,t5.hang_up_result
,t5.hang_up_dirct
,t5.icc_call_result
,t5.call_result
,t5.icc_call_result_type
,t5.icc_recording_id
,t5.icc_record_date
,split(t7.summary,'-')[0]
,split(t7.summary,'-')[1]
,split(t7.summary,'-')[2]
,t6.start_time
,t6.end_time
,case t6.score
when -1 then '未参与'
when 0 then '未评价'
when 5 then '非常满意'
when 4 then '满意'
when 3 then '一般'
when 2 then '不满意'
when 1 then '非常不满意'
else cast(t6.score as string) end
,t1.dt
,t8.mission_name
,t5.participant_role
,t5.participant_role_id
from lods.lods_smartxma_call_call_room t1
left join (
select t1.tenant_id,t1.call_room_id,t1.from_participant_id,t1.digit_url,t1.transfer_id,t1.from_transfer_id
from lods.lods_smartxma_call_call_room_tranfer t1
where t1.hoodie_record_type !='DELETE'
and t1.is_deleted = 'false'
) t2
on t2.tenant_id =t1.tenant_id
and t2.call_room_id = t1.call_room_id
left join (
select *
from lods.lods_smartxma_call_call_room_participant t1
where t1.hoodie_record_type !='DELETE'
and t1.is_deleted = 'false'
) t5
on t5.tenant_id = t1.tenant_id
and t5.call_room_id = t1.call_room_id
and t5.transfer_id = t2.transfer_id
left join (
select *
from lods.lods_smartxma_call_call_room_queue t1
where t1.hoodie_record_type !='DELETE'
and t1.is_deleted = 'false'
) t3
on t3.tenant_id = t1.tenant_id
and t3.call_room_id = t1.call_room_id
and t3.transfer_id = t2.transfer_id
-- and t3.queue_role = t5.participant_role
and t3.queue_id = t5.queue_id
left join (
select *
from lods.lods_smartxma_call_call_room_participant_agent t1
where t1.hoodie_record_type !='DELETE'
and t1.is_deleted = 'false'
) t4
on t4.tenant_id = t1.tenant_id
and t4.call_room_id = t1.call_room_id
and t4.transfer_id = t2.transfer_id
and t4.participant_id = t5.participant_id
left join (
select *
from lods.lods_smartxma_call_call_room_evaluate t1
where t1.hoodie_record_type !='DELETE'
and t1.is_deleted = 'false'
) t6
on t6.tenant_id = t1.tenant_id
and t6.call_room_id = t1.call_room_id
and t6.transfer_id = t2.transfer_id
and t6.from_participant_id = t5.participant_id
left join (
select *
from lods.lods_smartxma_call_call_room_participant_summary t1
where t1.is_deleted = 'false'
and t1.hoodie_record_type !='DELETE'
) t7
on t7.tenant_id = t1.tenant_id
and t7.call_room_id = t1.call_room_id
and t7.participant_id = t5.participant_id
left join mid_dmd_opr_lia_call_info_df t8
on t8.call_id = t1.icc_call_id
where t1.hoodie_record_type !='DELETE'
;
这是完整代码请帮我优化一下呢
最新发布
博客记录了在Oracle数据库中，用户删除物化视图时相关session卡住的问题。通过查询等待事件、等待的object的session及spid，最终kill相关session，使物化视图顺利删除，提供了解决数据库操作卡顿的思路。
扫一扫
890
到【灌水乐园】发言
