SELECT
DATE(xma_begin_time) AS date,
app_code,
COUNT(DISTINCT xma_session_id) AS session_count
FROM dm_opr.online_realtime_df
WHERE DATE(xma_begin_time) >= date_sub(CURRENT_DATE(), 3)
and app_code='pulsar'
AND ima_begin_time IS NOT NULL -- 有效会话条件
GROUP BY DATE(xma_begin_time), app_code;如果我计算这个指标时候想要从下面这个dm_opr.online_realtime_df的源头表取数计算，sql怎么修改
with
online_base as
( select
a.session_id as xma_session_id ,
k.uniqueid as unique_id,
a.customer_id as dz_customer_id,
a.user_id ,
case when date(a.create_time)<'2025-05-22' then from_unixtime(unix_timestamp(a.create_time) - 3600) else a.create_time end as xma_begin_time , ---会话开始时间
case when date(a.end_time)<'2025-05-22' then from_unixtime(unix_timestamp(a.end_time) - 3600) else a.create_time end as end_time , ---会话结束时间
--------a.bot_start_time as xma_begin_time, ---- bot会话开始时间 这个不够准确
a.total_time , ----------通话总时长
a.is_im , ----是否转人工的意愿 是/否
a.is_closed , --是否关闭 是/否
a.is_artificial_first, --是否人工优先 是/否
a.to_user , --坐席用户号
g.name , --坐席名字
a.im_status , --im会话状态
a.distribute_status , ----im分配类型 正式分配客服 接通看这个
a.offline_status, ----------im会话结束类型 是否自动结束
case when a.distribute_status ='正式分配客服' then a.im_start_time else null end ima_begin_time , --------ima开始时间
case when a.distribute_status ='正式分配客服' then a.end_time else null end ima_end_time ,--------ima结束时间
a.first_response_time /1000 as first_response_time, --首次响应时间 这是客户发了第一条消息到客服回复时间差值 单位毫秒
a.first_reply_time/1000 as first_reply_time , --人工首次回复时长 接通人工之后，客服发的第一条消息时间 单位毫秒
b.avg_response_time/1000 as avg_reply_dur ,
a.end_type , ------结束类型 手动结束还是超时未回复结束
a.channel_type ,
a.first brief_sum1 ,
a.second brief_sum2 ,
a.third brief_sum3,
a.ima_product_name , --------- 来源渠道
k.app_code,
a.dt
from dm_opr.dmd_opr_lia_online_iceberg_df a
left join
lods.lods_smartxma_basic_basic_user_global g on a.to_user=g.id
left join ( select session_id, avg_response_time from lods.lods_smartxma_im_chat_im_chat_kpi ) b on a.session_id=b.session_id
left join dm_opr.xma_testuu x on x.customer_id=a.customer_id
left join
(select * from lods.lods_smartxma_basic_basic_customer_identity
--------- where app_code='pulsar'
) k on k.customer_id=a.customer_id
left join (selECT distinct SUBSTRING(CAST (phone AS STRING), GREATEST(LENGTH(CAST(phone AS STRING)) - 9, 1), 10) as phone from dm_opr.testphone)pp on k.phone=pp.phone
where a.dt>='20250501'
and x.customer_id is null ----剔除测试uuid
and pp.phone is null -----剔除测试phone
-------- and a.robot_id !='NEW650f7067d474c8beadb0facfd10f6' --- app3
------- NEWfc2f84ec94b2f8d52f1a68035e231 pulsar
),
note
(
SELECT session_id , field_val from lods.lods_smartxma_im_chat_im_chat_session_question_ext
where field_name='Notas de servicio'
),
t_xma as (
SELECT DISTINCT
xma_begin_time,
xma_session_id,
unique_id,
dz_customer_id,
ima_product_name as xma_prod_name,
app_code,distribute_status
from
online_base
where
to_date (xma_begin_time) >= '2025-05-08'
),
xma_base (
--触发转人工意图
select disTINCT
a.session_id ,answer_source, case when is_standard_ask=2 then '标准问' when is_standard_ask=3 then '相似问' else '其他' end as mingzhong_type
from lods.lods_smartxma_chat_chat_record a
where
hour(a.create_time)>=9 and hour(a.create_time)<=17 --工作时间
),
t_xma_flow as (
select
xma_session_id,
'xma未分流' as is_flow
from
online_base
where
is_im='是'
union all
selECT disTINCT session_id , 'xma未分流' as is_flow from xma_base where answer_source in (97,98)
),
t_next_call as (
select
*,
lead (xma_begin_time) over (
partition by
case when unique_id is null then dz_customer_id else unique_id end
order by
xma_begin_time
) as next_xma_time,
lead (xma_session_id) over (
partition by
case when unique_id is null then dz_customer_id else unique_id end
order by
xma_begin_time
) as next_xma_session_id
from
(
select
*
from
t_xma
) a
),
t_ima as (
SELECT DISTINCT
a.xma_session_id,
a.xma_session_id as ima_session_id,
a.name as to_cust_svc,
a.ima_begin_time,
a.ima_end_time,
a.first_reply_time as first_resp_dur,
avg_reply_dur, --逻辑检查
first_value (a.brief_sum1) over (
PARTITION BY
a.xma_session_id
order by
ima_begin_time
) as brief_sum1,
first_value (a.brief_sum2) over (
PARTITION BY
a.xma_session_id
order by
ima_begin_time
) as brief_sum2,
first_value (a.brief_sum3) over (
PARTITION BY
a.xma_session_id
order by
ima_begin_time
) as brief_sum3
from
online_base a
where
to_date (a.xma_begin_time) >= '2025-05-08'
and a.distribute_status ='正式分配客服'
and a.ima_begin_time is not null
),
t_workorder as ( --提单判定 先模糊匹配
selECT distinct order_id as work_order_number,
customer_id as uniqueid,
session_id, --关联在线会话号
call_record_id, --关联callid 这个目前关联不到有问题
rela_order_id , --关联工单号
order_desc, --描述
create_time
from
dm_opr.dmd_opr_bso_wo_info_iceberg_df
where
order_status_desc !='草稿'
and (process_name != 'Ticket de aprobación de exención' or process_name is null )
),
t_tmp as (
select DISTINCT
t_xma.xma_begin_time,
t_xma.xma_session_id,
t_xma.xma_prod_name,
t_xma.unique_id,
t_xma.dz_customer_id,
t_xma.app_code,
t_xma.distribute_status,
if (
t_xma_flow.is_flow is null,
'xma分流',
t_xma_flow.is_flow
) is_flow,
t_ima.ima_session_id,
t_ima.ima_begin_time,
t_ima.ima_end_time,
t_ima.to_cust_svc,
t_ima.first_resp_dur,
t_ima.avg_reply_dur,
t_ima.brief_sum1,
t_ima.brief_sum2,
t_ima.brief_sum3,
t_next_call.next_xma_time,
t_next_call.next_xma_session_id,
t_workorder.work_order_number,
t_workorder.create_time as work_order_create_time,
note.field_val as notas ,
first_value (a.begin_call_dt) over (
PARTITION BY
t_xma.xma_session_id
order by
a.begin_call_dt
) as next_ivr_call,
first_value (a.call_id) over (
PARTITION BY
t_xma.xma_session_id
order by
a.begin_call_dt
) as next_call_id
from
t_xma
left join t_xma_flow on t_xma.xma_session_id = t_xma_flow.xma_session_id
left join t_next_call on t_xma.xma_session_id = t_next_call.xma_session_id
left join
(selECT disTINCT case when uniqueid is null then participant_role_id else uniqueid end customer_id ,
a.call_id,
case when date(a.room_start_time)<'2025-05-22' then from_unixtime(unix_timestamp(a.room_start_time) - 3600) else a.room_start_time end as begin_call_dt ,k.app_code
from dm_opr.dmd_opr_lia_call_info_iceberg_df a
left join (select * from lods.lods_smartxma_basic_basic_customer_identity
) k on k.customer_id=a.participant_role_id
where
a.participant_role='CUSTOMER'
and a.call_type='呼入'
) a on (case when t_xma.unique_id is null then t_xma.dz_customer_id else t_xma.unique_id end) = a.customer_id
and unix_timestamp (t_xma.xma_begin_time) < unix_timestamp (a.begin_call_dt)
and a.app_code=t_xma.app_code
left join t_ima on t_ima.xma_session_id = t_xma.xma_session_id
left join t_workorder on t_xma.xma_session_id= t_workorder.session_id
left join note on note.session_id=t_xma.xma_session_id
)
insert OVERWRITE ${dm_opr}.dm_opr_online_realtime_df partition(dt)
SELECT
*,
-- 重复进线
case
when (
unix_timestamp (next_xma_time) - unix_timestamp (xma_begin_time) BETWEEN 0 and 3600 * 24 * 2
) then 'xma48h重复进线'
else 'xma48h未重复进线'
end xma_is_repeat_48h,
case
when (
unix_timestamp (next_ivr_call) - unix_timestamp (xma_begin_time) BETWEEN 0 and 3600 * 24 * 2
) then 'ivr48h重复进线'
else 'ivr48h未重复进线'
end ivr_is_repeat_48h,
case
when (
unix_timestamp (next_xma_time) - unix_timestamp (xma_begin_time) BETWEEN 0 and 3600 * 24 * 2
)
or (
unix_timestamp (next_ivr_call) - unix_timestamp (xma_begin_time) BETWEEN 0 and 3600 * 24 * 2
)
or work_order_number is not null then '48h未解决'
else '48h解决'
end is_solve_48h
,to_date (xma_begin_time) as dt
from
t_tmp WHERE notas not in ('test','TEST','prueba','Prueba','Test','PRUEBA') or notas is null;
本文介绍了如何使用 SQL 中的 CONCAT 和 SUBSTRING 函数来处理字符串。通过具体示例展示了如何截取字符串的一部分并将其与其他部分组合起来，这对于保护敏感信息非常有用。
扫一扫
1564
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
