工作中一些函数的应用

1,炸裂函数应用：列转行操作
应用背景1：一个外呼客户回调标识 对应 一串客户编号，需要把客户编号分割开
otbd_cust_calb_id 外呼客户回调标识: ‘_SSJVLNTF10Q3V9GQKV064D15R’
rela_cust_lsts 关联客户列表: ‘170005863,170006774,170007193,170007541,170009139,170013559’
一个回调标识对应’一串’的客户列表，需要用炸裂函数给炸开 -> 列转行操作
otbd_cust_calb_id rela_cust_lsts
‘_SSJVLNTF10Q3V9GQKV064D15R’ ‘170005863’
‘_SSJVLNTF10Q3V9GQKV064D15R’ ‘170006774’
‘_SSJVLNTF10Q3V9GQKV064D15R’ ‘170007193’
‘_SSJVLNTF10Q3V9GQKV064D15R’ ‘170007541’
‘_SSJVLNTF10Q3V9GQKV064D15R’ ‘170009139’
‘_SSJVLNTF10Q3V9GQKV064D15R’ ‘170013559’
select
coalesce(a.cust_no,b.cust_no) as cust_no
,‘1’ as serv_chan
,coalesce(a.tel_serv_cnt,b.tel_serv_cnt) as cnt_serv_num
,coalesce(a.otbd_cust_calb_id,b.otbd_cust_calb_id) as serv_flag
from ( select – 1.1 员工工作日志 客户本年电话服务次数
t2.cust_no as cust_no – 客户编号
,t2.otbd_cust_calb_id as otbd_cust_calb_id – 外呼客户回调标识
,count(*) as tel_serv_cnt – 电话服务次数
from(select
cust_no
, otbd_cust_calb_id
from(select
split(rela_cust_lsts,‘,’) as rela_cust_lsts
, nvl(otbd_cust_calb_id,‘aa’) as otbd_cust_calb_id
from db_edw.mkt_emp_work_log – 员工工作日志
where dt = ‘${v_{d}ate}^{\prime }--该表为全量分区表，dt需要固定为最新分区andcus{t}_{s}er{v}_{m}od{e}_{c}d{=}^{\prime }{1}^{\prime }--电话访问andwor{k}_{o}cc{u}_{d}ate<{=}^{\prime }${v_date}’ – 工作发生日期 <= 统计日期
and substr(work_occu_date,1,4) = substr(‘ v d a t e ′ , 1 , 4 ) − − 限制时间范围：本年 ) t 1