WITH email AS (
SELECT
tenant_id,
short_session_id,
session_id,
session_subject,
product_id,
strategy_id,
customer_id,
customer_email,
staff_id,
first_staff_id,
session_status,
offline_status,
queue_status,
session_type,
email_group_id,
first_email_group_id,
CASE
WHEN DATE(start_time) < '2025-05-22'
THEN FROM_UNIXTIME(UNIX_TIMESTAMP(start_time) - 3600)
ELSE start_time
END AS start_time,
CASE
WHEN DATE(end_time) < '2025-05-22'
THEN FROM_UNIXTIME(UNIX_TIMESTAMP(end_time) - 3600)
ELSE end_time
END AS end_time,
CASE
WHEN DATE(create_time) < '2025-05-22'
THEN FROM_UNIXTIME(UNIX_TIMESTAMP(create_time) - 3600)
ELSE create_time
END AS create_time
FROM lods.lods_smartxma_workorder_email_session
WHERE
customer_email NOT IN (
'1178627646@qq.com', 'advertise-noreply@support.facebook.com', '360541391@qq.com',
'554041631@qq.com', 'ivan.gonzalezcruz@icloud.com', 'notification@facebookmail.com',
'adrianaguilera834@gmail.com', 'reminders@facebookmail.com', 'zjy703@qq.com',
'xueshi_oo@163.com', 'miaandrea2530@gmail.com', 'jiayannan0724@126.com',
'dani.coyolxauhqui63@gmail.com', 'miaandrea2530@gmail.com', 'miguel.cetis37fv@gmail.com',
'Miki.cekan63@gmail.com', 'miguel.hernandez21918@gmail.com', 'Ivan.gonzalezcruz@icloud.com',
'ivangzcruz@hotmail.com', 'Adrianaguilera834@gmai.com', 'Cobranzaprevia925@gmail.com',
'rtrinidadmaldonado@gmail.com','zjy703@qq.com',
'jiaxiong.wen@xiaolongcloud.com',
'360541391@qq.com',
'suhuanzheng7784877@163.com'
)
and customer_email not like '%qq.com' and customer_email not like '%163.com' and customer_email not like '%xiaolongcloud.com'
and session_subject not like '%Prueba%' and ( session_subject NOT RLIKE '[\\u4e00-\\u9fa5]' or session_subject like '%外部邮件%') and session_subject not like '%test%'
),
note (
SELECT DISTINCT session_id , field_val from
lods.lods_smartxma_workorder_email_session_question_ext where field_val is not null and field_val!='' and field_name= 'Notas de servicio' ),
response_time AS (
SELECT
session_id,
MIN(send_msg_time) AS f_send_msg_time
FROM lods.lods_smartxma_workorder_email_session_message
WHERE
from_terminal = 'kf_web'
AND msg_type = 1
GROUP BY session_id
),
xiaojie AS (
SELECT *
FROM lods.lods_smartxma_basic_basic_summary
),
bf AS (
SELECT
session_id,
SPLIT(question, '-')[0] AS brief_sum1,
SPLIT(question, '-')[1] AS brief_sum2,
case when to_date(create_time)>='2025-07-24' then '' else SPLIT(question, '-')[2] end AS brief_sum3
FROM lods.lods_smartxma_workorder_email_session_question
),email_base AS (
SELECT
*,
LAG(create_time) OVER (
PARTITION BY customer_email
ORDER BY create_time
) AS prev_time
FROM email
),
email_groups AS (
SELECT
*,
-- 邮箱内分组 (48小时连续)
SUM(CASE
WHEN prev_time IS NULL
OR unix_timestamp(create_time) - unix_timestamp(prev_time) > 172800
THEN 1
ELSE 0
END) OVER (
PARTITION BY customer_email
ORDER BY create_time
) AS group_id,
-- 标记重复进线
CASE
WHEN prev_time IS NOT NULL
AND unix_timestamp(create_time) - unix_timestamp(prev_time) <= 172800
THEN '48h重复进线'
ELSE NULL
END AS repeat_mark
FROM email_base
),
group_info AS (
SELECT
customer_email,
group_id,
-- 正确获取每个组的最早创建时间
MIN(create_time) AS group_start_time ,max(start_time) as group_max_time
FROM email_groups
GROUP BY customer_email, group_id
),
-- 全局问题ID分配
global_groups AS (
SELECT
customer_email,
group_id,
group_start_time,group_max_time,
ROW_NUMBER() OVER (ORDER BY group_start_time) AS question_id
FROM group_info
)
INSERT OVERWRITE TABLE dm_opr.dm_opr_email_realtime_df PARTITION(dt)
SELECT distinct
eg.tenant_id,
eg.short_session_id,
eg.session_id,
eg.session_subject,
eg.product_id,
eg.strategy_id,
eg.customer_id,
eg.customer_email,
eg.staff_id,
eg.first_staff_id,
eg.session_status,
eg.offline_status,
eg.queue_status,
eg.session_type,
eg.email_group_id,
eg.first_email_group_id,
eg.start_time,
eg.end_time,
eg.create_time,
eg.repeat_mark,
gg.question_id,
gg.group_start_time,gg.group_max_time,
f.unique_id AS uniqueid,
case when SPLIT(b.channel_name, '_')[0] ='Ala' then 'ala' else SPLIT(b.channel_name, '_')[0] end AS app_code,
response_time.f_send_msg_time,
bf.brief_sum1,
bf.brief_sum2,
bf.brief_sum3,
note.field_val as notas ,
UNIX_TIMESTAMP(response_time.f_send_msg_time) - UNIX_TIMESTAMP(eg.start_time) AS avg_responstime,
g.name AS staff_name,
CASE WHEN w.unique_id IS NOT NULL THEN '凭证' ELSE '咨询' END AS type ,
case when oc.call_id is not null and oc.answer_time is not null then '已外呼客户接起'
when oc.call_id is not null then '已外呼未接起'
else '未外呼' end as type2,
case when sw.session_id is not null then '有号码' else '无号码' end as type3,
date(eg.create_time) as dt
FROM email_groups eg
JOIN global_groups gg
ON eg.customer_email = gg.customer_email
AND eg.group_id = gg.group_id
LEFT JOIN response_time ON response_time.session_id = eg.session_id
LEFT JOIN bf ON bf.session_id = eg.session_id
LEFT JOIN (
SELECT channel_id, channel_name
FROM lods.lods_smartxma_workorder_wo_email_channel
) b ON eg.product_id = b.channel_id
LEFT JOIN (
SELECT *
FROM lods.lods_smartxma_basic_basic_customer_identity
) k ON k.customer_id = eg.customer_id
LEFT JOIN (
SELECT distinct
unique_id,
default.denc2md5(email) AS email
FROM dwd.dwd_ip_cust_info_df
WHERE dt = REPLACE(DATE_SUB(CURRENT_DATE(), 1), '-', '')
) f ON f.email = denc2md5(eg.customer_email)
LEFT JOIN (
SELECT *
FROM lods.lods_smartxma_basic_basic_user_global
) g ON cast(eg.staff_id as string)= cast(g.id as string)
LEFT JOIN (
SELECT distinct
unique_id,
app_code,
mobile,
create_time,
body_text
FROM lods.lods_smartxma_workbench_whats_app_send_record
where body_text LIKE '%.com%'
------- AND send_state = '1'
) w ON w.unique_id = f.unique_id AND cast(w.create_time as string) <= cast(eg.create_time as string)
left join dm_opr.dm_opr_out_call_df oc on oc.uniqueid=f.unique_id and cast(gg.group_max_time as string) <= cast(oc.room_start_time as string)
left join (
SELECT distinct
session_id , msg_content
FROM lods.lods_smartxma_workorder_email_session_message
WHERE
from_terminal ='user_email' and msg_type=1
and msg_content RLIKE '\\d{10,}' -- 匹配10位及以上数字
AND NOT msg_content RLIKE '(?i)zkj\\d{10}'
) sw on sw.session_id=eg.session_id
left join note on note.session_id=eg.session_id;
以上是我的逻辑中间表逻辑,下面是我的计算指标sql,我想要计算时候取源表数据来计算,指标sql该怎么修改
select
to_date(create_time) as date,
'总计' as brief_sum2,
app_code,
count(distinct customer_email) as `邮件进线客户数`
from
dm_opr.dm_opr_email_realtime_df WHERE to_date(create_time) >=DATE_SUB(CURRENT_DATE(), 3)
and app_code='pulsar'
group by 1,2,3
order by 1 desc
limit 1;