写过的sql代码总结自用（PART1）

用join时应该制定左表和右表的新名称（t1 t2或者a b ）这样写on条件更便捷；同时select时必须制定是哪个表的这个字段（即使是on条件的字段）；此时返回的列名是user_id,不是a。user_id

 给字段或表命名时不需要加as

在sql中，BETWEEN操作符用于选取介于两个值之间的数据范围内的值。其中，“between and”是包括边界值的，即包括两端；“not between”是不包括边界值的。SQL BETWEEN 操作符

 加单引号里面是提取的格式

一、埋点缺失排查代码：

    with temp as (selectt1.Oid as Oid1,t2.*from(selectOid,max(is_sw) as is_sw,max(is_ck) as is_ckfrom(selectdt,Oid,casewhen eventid like '%sw%' then 1else 0end as is_sw,casewhen eventid like '%ck%' then 1else 0end as is_ckfromfin_ds.dwd_flowpool_event_di_v2wheredt = '$[YYYY-MM-DD - 1D]'and task_name like '%保险%') t0group byOidhavingmax(is_sw) = 1and max(is_ck) = 0) t1inner join(selectconcat_ws('-', di.year, di.month, di.day) as dt,eventid,uid,Oid,nvl(attrs ['channel_id'], 'unknown') as channel_id,nvl(ch.channel_name, 'unknown') as channel_name,attrs ['root_channel_id'] as root_channel_id,ch1.channel_name as root_channel_name,nvl(attrs ['product_id'], 'unknown') as product_id,server_time_ts as time0,third_level_nmfrommanhattan_dw.dwd_flow_pub_d_increment as dileft join manhattan_dw.dim_market_channel as ch on concat_ws('-', ch.year, ch.month, ch.day) = '$[YYYY-MM-DD - 1D]'and ch.channel_id = di.attrs ['channel_id']and ch.business_name = '非车险'left join manhattan_dw.dim_market_channel as ch1 on concat_ws('-', ch1.year, ch1.month, ch.day) = '$[YYYY-MM-DD - 1D]'and ch1.channel_id = di.attrs ['root_channel_id']and ch1.business_name = '非车险'left join (selectfirst_level_nm,second_level_nm,third_level_nm,channel_id,material_id,third_level_idfrommanhattan_dw.dim_pt_flow_channelid_v2_dfwheredt = '$[YYYY-MM-DD - 1D]') a on di.attrs ['channel_id'] = a.channel_idwhereconcat_ws('-', di.year, di.month, di.day) = '$[YYYY-MM-DD - 1D]'and biz_type = 'ins'and attrs ['channel_id'] is not null -- in (and eventid in ('fin_ins_didibao_insure_home_sw','fin_ins_didibao_insure_home_insurebtn_ck','fin_ins_didibao_purchase_success_bt')and ch.channel_name like '%流量池%' -- and attrs ['product_id'] in ('2020103195445788673')) t2 on t1.Oid = t2.Oid)select distinct Oid1,uid,channel_id,channel_name,third_level_nm,eventid,time0 from temp

二、联盟合作代码

selectdt,a.user_id as user_id,cast(a.user_id as bigint) | 281474976710656 as uid,utm_source,card_type,action_type,first_bind_time,last_bind_time,user_curr_bind_card_num,user_trade_active_tag,is_nopasspay,e.channel_id,case when card_entry_type=2 then '一站式签约' else '非一站式签约' end as card_entry_type,chl_category,chl_medium,chl_name,--last_bind_issuer_name,first_bind_scene_type,last_bind_scene_type,last_bind_scene_type_name,(casewhen first_bind_time = dt then '首次绑卡'else '非首次'end) as is_first,coupon_cnt,coupon_amount / 100 as coupon_amount,spend_coupon_cnt,spend_amount,(casewhen coupon_cnt > 0 then '收券'else '未收券'end) as is_dispatch,(casewhen spend_coupon_cnt > 0 then '核销'else '未核销'end) as is_spend
from(select*from(selectuser_id,action_type,card_type,get_json_object(remark, '$.utm_source') as utm_source,to_date(update_time) as dt,coalesce(get_json_object(remark, '$.x-channel-id'), '') as channel_id,coalesce(get_json_object(remark, '$.card_entry_type'), '') as card_entry_type,row_number() OVER(PARTITION BY user_idorder byupdate_time) as rn0fromddpay.dwd_pay_user_card_action_log_dfwheredt = '$[YYYY-MM-DD - 1D]'and to_date(update_time) > '2023-07-09'and status = 1and issuer_name like '%招商银行%') a0wherern0 = 1) aleft join (selectuser_id,uid_role1,to_date(first_bind_time) as first_bind_time,to_date(last_bind_time) as last_bind_time,user_curr_bind_card_num,user_trade_active_tag,is_nopasspayfromddpay.dwm_pay_user_didipay_feature_summary_dfwheredt = '$[YYYY-MM-DD - 1D]') b on a.user_id = b.user_idleft join(selectuser_id,count(*) as coupon_cnt,sum(get_json_object(reward_info, '$.coupon_amount')) as coupon_amountfrombizdata.dwd_trip_mkt_virus_reward_diwheredt > '2023-07-09'and get_json_object(reward_info, '$.remark') like "%滴滴支付招商银行%"group byuser_id) c on b.uid_role1 = c.user_idleft join(selectpayer_user_id,sum(charge_amount) as spend_amount,count(distinct trade_id) as spend_coupon_cntfrom(selectpayer_user_id,charge_amount,trade_id,act_name,row_number() OVER(PARTITION BY trade_id,act_name,charge_amountorder byreal_order_id desc) as rnfrommanhattan_dw.dwd_pay_mkt_eva_coupon_pope_dfwheredt = '$[YYYY-MM-DD - 1D]'and act_name like '%ut%'and to_date(order_finish_time) > '2023-07-09') tempwherern = 1group bypayer_user_id) d on a.user_id = d.payer_user_idleft join (selectuser_id,last_bind_issuer_name,first_bind_scene_type,last_bind_scene_type,last_bind_scene_type_name,first_bind_utm_channel_id as channel_idfromddpay.dwm_pay_user_bind_card_dfwheredt = '$[YYYY-MM-DD - 1D]') e on a.user_id = e.user_idleft join (selectdistinct chl_id,casewhen chl_category = '收银台'or chl_name like '%收银台%' then '收银台'when chl_name in ('滴滴出行-滴滴支付-零钱页-青桔骑行金提现','滴滴出行-DiDi支付-公交乘车码-开通免密支付','滴滴出行-滴滴支付主页2.0-金融导航icon-默认','CmbcApp-didizhifu-yjbk-addNewCard','乘客端-滴滴支付-钱包-资产下拉') then '固定入口'when chl_name = '滴滴出行-DiDi支付-钱包-零钱落地页' then '资源位'when chl_name rlike '工行|建行|交行|银行app' then '银行app'when chl_name in ('滴滴出行-特惠快车-无-无','滴滴出行-特惠快车-无-无','全渠道-滴滴保险-默认-绑卡开户','全渠道-信贷-默认-绑卡开户','微信-信贷-默认-绑卡开户') then '非收银台'when resource_type in ('资源位', '固定入口') then resource_typeelse chl_categoryend as chl_category,chl_medium,chl_namefrommanhattan_dw.dim_ddpay_market_channelwhereconcat_ws('-', year, month, day) = date_sub(CURRENT_DATE, 1)) f on e.channel_id = f.chl_id;

三、近一年没有提现的司机数据（ERROR版）

selectbank_name2,count (distinct driver_id) as total,count (distinct casewhen last_time < '2022-07-01' then driver_idend) as no_withdraw
from(selectdriver_id,bank_name2,max(withdraw_dt) as last_timefrom(select*,casewhen (bank_name LIKE '%建设银行%'or bank_name LIKE '%建設銀行%'or bank_name LIKE '%建行%')and reg_city_name = '重庆市' then "建行重庆"when (bank_name LIKE '%建设银行%'or bank_name LIKE '%建設銀行%'or bank_name LIKE '%建行%') then "建行其他"when bank_name LIKE '%平安银行%'and reg_city_name = '深圳市' then "平安深圳"when bank_name LIKE '%平安银行%' then "平安其他"when (bank_name LIKE '%邮储银行%'or bank_name LIKE '%邮储%')and reg_city_name = '重庆市' then "邮储重庆"when bank_name LIKE '%邮储银行%'or bank_name LIKE '%邮储%' then "邮储其他"when bank_name LIKE '%工商银行%'or bank_name LIKE '%工商銀行%' then "工行"when bank_name LIKE '%中信银行%'or bank_name LIKE '%中信銀行%'or bank_name LIKE '%中信实业銀行%' then "中信"when bank_name LIKE '%光大银行%' then "光大"when bank_name LIKE '%渤海银行%'or bank_name LIKE '%渤海銀行%' then "渤海"else "其他"end as bank_name2from(selectto_date(succ_time) as withdraw_dt,driver_id,casewhen bank_name is nullor bank_name = ' 'or bank_name = '' then 'unkown'else bank_nameend as bank_namefromddpay.dwd_pay_trd_whole_driver_withdraw_dfwheredt = '2023-06-30'and status = 1) a0left join (selectdriver_id as driver_id0,nvl(reg_city_name, 'unknown') as reg_city_namefromdp_det_data.tmp_driver) b on a0.driver_id = b.driver_id0) agroup bydriver_id,bank_name2) c
group bybank_name2;

四、司机提现需求正解

-- 需求2：by银行，看不同银行司机提现规模数据
-- 「银行」建行全国、建行重庆、平安全国、平安深圳、邮储全国、邮储重庆、工行、平安、中信、光大、渤海银行
-- 「需求」2023.1-6，by月+上半年合计，提现司机数量、提现总额、提现次数、提现笔均、提现人均，
-- 最近一年（2022.7-2023.6）没有使用上述银行卡提现的司机数量
--外层再加一个select  group by month 就可以了
selectbank_name2,nvl(month0, '上半年合计') as month0,count(distinct driver_id) as driver_num,count(distinct withdraw_id) as withdraw_num,count(*) as test --sum(withdraw_amount) as sum_amount,--sum(withdraw_amount) / count(distinct withdraw_id) as every_order_amount,--sum(withdraw_amount) / count(distinct driver_id) as every_driver_amount
from(select*,month(withdraw_dt) as month0,casewhen (bank_name LIKE '%建设银行%'or bank_name LIKE '%建設銀行%'or bank_name LIKE '%建行%')and reg_city_name = '重庆市' then "建行重庆"when (bank_name LIKE '%建设银行%'or bank_name LIKE '%建設銀行%'or bank_name LIKE '%建行%') then "建行其他"when bank_name LIKE '%平安银行%'and reg_city_name = '深圳市' then "平安深圳"when bank_name LIKE '%平安银行%' then "平安其他"when (bank_name LIKE '%邮储银行%'or bank_name LIKE '%邮储%')and reg_city_name = '重庆市' then "邮储重庆"when bank_name LIKE '%邮储银行%'or bank_name LIKE '%邮储%' then "邮储其他"when bank_name LIKE '%工商银行%'or bank_name LIKE '%工商銀行%' then "工行"when bank_name LIKE '%中信银行%'or bank_name LIKE '%中信銀行%'or bank_name LIKE '%中信实业銀行%' then "中信"when bank_name LIKE '%光大银行%' then "光大"when bank_name LIKE '%渤海银行%'or bank_name LIKE '%渤海銀行%' then "渤海"else "其他"end as bank_name2from(selectwithdraw_dt,driver_id,withdraw_id,--withdraw_amount,bank_namefrom(selectto_date(succ_time) as withdraw_dt,driver_id,withdraw_id,--withdraw_amount / 100 as withdraw_amount,casewhen bank_name is nullor bank_name = ' 'or bank_name = '' then 'unkown'else bank_nameend as bank_namefromddpay.dwd_pay_trd_whole_driver_withdraw_dfwheredt = '2023-06-30'and status = 1) a0wherewithdraw_dt between '2023-01-01'and '2023-06-30') aleft join (selectdriver_id as driver_id0,nvl(reg_city_name, 'unknown') as reg_city_namefromdp_det_data.tmp_driver) b on a.driver_id = b.driver_id0) c
group bybank_name2,month0 grouping sets((bank_name2),(bank_name2,month0))
order bymonth0,bank_name2;-- ——————————————————————————
selectbank_name2,count (distinct driver_id) as total,count (distinct casewhen last_time < '2022-07-01' then driver_idend) as no_withdraw
from(selectdriver_id,bank_name2,max(withdraw_dt) as last_timefrom(select*,casewhen (bank_name LIKE '%建设银行%'or bank_name LIKE '%建設銀行%'or bank_name LIKE '%建行%')and reg_city_name = '重庆市' then "建行重庆"when (bank_name LIKE '%建设银行%'or bank_name LIKE '%建設銀行%'or bank_name LIKE '%建行%') then "建行其他"when bank_name LIKE '%平安银行%'and reg_city_name = '深圳市' then "平安深圳"when bank_name LIKE '%平安银行%' then "平安其他"when (bank_name LIKE '%邮储银行%'or bank_name LIKE '%邮储%')and reg_city_name = '重庆市' then "邮储重庆"when bank_name LIKE '%邮储银行%'or bank_name LIKE '%邮储%' then "邮储其他"when bank_name LIKE '%工商银行%'or bank_name LIKE '%工商銀行%' then "工行"when bank_name LIKE '%中信银行%'or bank_name LIKE '%中信銀行%'or bank_name LIKE '%中信实业銀行%' then "中信"when bank_name LIKE '%光大银行%' then "光大"when bank_name LIKE '%渤海银行%'or bank_name LIKE '%渤海銀行%' then "渤海"else "其他"end as bank_name2from(selectto_date(succ_time) as withdraw_dt,driver_id,casewhen bank_name is nullor bank_name = ' 'or bank_name = '' then 'unkown'else bank_nameend as bank_namefromddpay.dwd_pay_trd_whole_driver_withdraw_dfwheredt = '2023-06-30'and status = 1) a0left join (selectdriver_id as driver_id0,nvl(reg_city_name, 'unknown') as reg_city_namefromdp_det_data.tmp_driver) b on a0.driver_id = b.driver_id0) agroup bydriver_id,bank_name2) c
group bybank_name2;

五、城市银行曝光数据代码1

selectnvl(city_name, '汇总'),count(*) as total_uv,sum(casewhen situation = '一银行曝光' then 1else 0end) as one_b,sum(casewhen situation = '二银行曝光' then 1else 0end) as two_b,sum(casewhen situation = '三银行曝光' then 1else 0end) as three_b,sum(casewhen situation = '四银行曝光' then 1else 0end) as four_b,sum(casewhen situation = '五银行曝光' then 1else 0end) as five_b,sum(casewhen situation = '六银行曝光' then 1else 0end) as six_b,sum(casewhen situation = '七银行曝光' then 1else 0end) as seven_b,sum(casewhen situation = 'unknown' then 1else 0end) as unknown,sum(GDYH_sw) as GDYH_sw,sum(GFYH_sw) as GFYH_sw,sum(PAYH_sw) as PAYH_sw,sum(NBYH_sw) as NBYH_sw,sum(QDYH_sw) as QDYH_sw,sum(PFYH_sw) as PFYH_sw,sum(qita_sw) as qita_sw,sum(GDYH_applyck) as GDYH_applyck,sum(GFYH_applyck) as GFYH_applyck,sum(PAYH_applyck) as PAYH_applyck,sum(NBYH_applyck) as NBYH_applyck,sum(QDYH_applyck) as QDYH_applyck,sum(PFYH_applyck) as PFYH_applyck,sum(qita_applyck) as qita_applyck
from(selectdt,uid,city_name,case(GDYH + NBYH + PFYH + GFYH + PAYH + QDYH + qita)when 1 then '一银行曝光'when 2 then '二银行曝光'when 3 then '三银行曝光'when 4 then '四银行曝光'when 5 then '五银行曝光'when 6 then '六银行曝光'when 7 then '七银行曝光'else 'unknown'end as situation,GDYH_sw,GFYH_sw,PAYH_sw,NBYH_sw,QDYH_sw,PFYH_sw,qita_sw,GDYH_applyck,GFYH_applyck,PAYH_applyck,NBYH_applyck,QDYH_applyck,PFYH_applyck,qita_applyckfrom(selectdt,uid,city_name,max(casewhen bank_name0 = 'GDYH' and is_sw=1 then 1else 0end) as GDYH_sw,max(casewhen bank_name0 = 'NBYH' and is_sw=1 then 1else 0end) as NBYH_sw,max(casewhen bank_name0 = 'PFYH' and is_sw=1 then 1else 0end) as PFYH_sw,max(casewhen bank_name0 = 'GFYH' and is_sw=1 then 1else 0end) as GFYH_sw,max(casewhen bank_name0 = 'PAYH' and is_sw=1 then 1else 0end) as PAYH_sw,max(casewhen bank_name0 = 'QDYH'and is_sw=1 then 1else 0end) as QDYH_sw,max(casewhen bank_name0 = '其他'and is_sw=1 then 1else 0end) as qita_sw,max(casewhen bank_name0 = 'GDYH' and is_apply_ck=1 then 1else 0end) as GDYH_applyck,max(casewhen bank_name0 = 'NBYH' and is_apply_ck=1 then 1else 0end) as NBYH_applyck,max(casewhen bank_name0 = 'PFYH' and is_apply_ck=1 then 1else 0end) as PFYH_applyck,max(casewhen bank_name0 = 'GFYH' and is_apply_ck=1 then 1else 0end) as GFYH_applyck,max(casewhen bank_name0 = 'PAYH' and is_apply_ck=1 then 1else 0end) as PAYH_applyck,max(casewhen bank_name0 = 'QDYH'and is_apply_ck=1 then 1else 0end) as QDYH_applyck,max(casewhen bank_name0 = '其他'and is_apply_ck=1 then 1else 0end) as qita_applyckfrom(select*,max(casewhen eventid like '%sw%' then 1else 0end) as is_sw,max(casewhen eventid like '%ck%' then 1else 0end) as is_ck,max(casewhen eventid like '%ck%'and is_apply_ck = 1 then 1else 0end) as is_apply_ckfrom(selecta0.*,nvl(city_name, 'unknown') as city_name,casewhen bank_name = ''or bank_name = 'undefined'or bank_name is null then 'unkown'when bank_name like '%PAYH%' then 'PAYH'when bank_name not in ('GDYH', 'PFYH', 'GFYH', 'QDYH', 'NBYH', 'PAYH') then '其他'else bank_nameend as bank_name0from(selectdt,uid,eventid,explode(split(pub_bank_code, ',')) as bank_name,city_idfromfin_ds.dwd_credit_card_user_visit_event_diwheredt between '2023-05-01'and '2023-07-18'and uid > 0and attrs ['pub_page_location'] = 0) a0left join (selectnvl(city_id, 'unknown') as city_id,nvl(city_name, 'unknown') as city_namefromwhole_dw.dim_citywheredt = '$[YYYY-MM-DD - 1D]') a on a0.city_id = a.city_id) a1group bydt,uid,city_name,bank_name0) bgroup bydt,uid,city_name) d) e
group byrollup(city_name)
order bytotal_uv desc;

六、城市银行曝光数据代码2

select*
from(select*,casewhen sw_order = 1and is_apply_ck = 1 then '申请首次曝光银行'when is_apply_ck = 0 then '未申请'when sw_order != 1and is_apply_ck = 1 then '申请非首次曝光银行'end as tag,count(*) over(partition by dt,uidorder bybank_name0) as sw_numfrom(selectdt,uid,bank_name0,city_name,is_sw,is_apply_ck,sw_time,row_number() over(partition by dt,uid,city_nameorder bysw_time) as sw_orderfrom(selectdt,uid,bank_name0,city_name,max(casewhen eventid like '%sw%' then 1else 0end) as is_sw,max(casewhen eventid like '%ck%'and is_apply_ck = 1 then 1else 0end) as is_apply_ck,min(casewhen eventid like '%sw%' then client_timeelse nullend) as sw_timefrom(selecta0.*,nvl(city_name, 'unknown') as city_name,casewhen bank_name = ''or bank_name = 'undefined'or bank_name is null then 'unkown'when bank_name like '%PAYH%' then 'PAYH'when bank_name not in ('GDYH', 'PFYH', 'GFYH', 'QDYH', 'NBYH', 'PAYH') then '其他'else bank_nameend as bank_name0from(selectdt,uid,eventid,is_apply_ck,client_time,bank_name0 as bank_name,city_idfromfin_ds.dwd_credit_card_user_visit_event_di lateral view outer explode(split(pub_bank_code, ',')) t as bank_name0wheredt between '2023-05-01'and '2023-07-18'and uid > 0and attrs ['pub_page_location'] = 0) a0left join (selectnvl(city_id, 'unknown') as city_id,nvl(city_name, 'unknown') as city_namefromwhole_dw.dim_citywheredt = '$[YYYY-MM-DD - 1D]') a on a0.city_id = a.city_id) a1group bydt,uid,city_name,bank_name0) b) c) d
wheresw_num > 1;

七、城市银行曝光数据代码3

with my_table as(selecta.*,a2.first_visit_bankfrom(selectdt,uid,city_name,concat_ws('-', dt, uid, city_name) as k,case(GDYH_sw + NBYH_sw + PFYH_sw + GFYH_sw + PAYH_sw + QDYH_sw + qita_sw)when 1 then '一银行曝光'when 2 then '二银行曝光'when 3 then '三银行曝光'when 4 then '四银行曝光'when 5 then '五银行曝光'when 6 then '六银行曝光'when 7 then '七银行曝光'else 'unknown'end as situation,(casewhen (GDYH_sw + NBYH_sw + PFYH_sw + GFYH_sw + PAYH_sw + QDYH_sw + qita_sw) > 1and is_apply_ck = 1 then '多家曝光且点击申请'when (GDYH_sw + NBYH_sw + PFYH_sw + GFYH_sw + PAYH_sw + QDYH_sw + qita_sw) = 1and is_apply_ck = 1 then '单家曝光且点击申请'else '未点击申请'end) as tag,GDYH_sw,GFYH_sw,PAYH_sw,NBYH_sw,QDYH_sw,PFYH_sw,qita_sw,GDYH_applyck,GFYH_applyck,PAYH_applyck,NBYH_applyck,QDYH_applyck,PFYH_applyck,qita_applyckfrom(selectdt,uid,city_name,max(is_apply_ck) as is_apply_ck,max(casewhen bank_name0 = 'GDYH'and is_sw = 1 then 1else 0end) as GDYH_sw,max(casewhen bank_name0 = 'NBYH'and is_sw = 1 then 1else 0end) as NBYH_sw,max(casewhen bank_name0 = 'PFYH'and is_sw = 1 then 1else 0end) as PFYH_sw,max(casewhen bank_name0 = 'GFYH'and is_sw = 1 then 1else 0end) as GFYH_sw,max(casewhen bank_name0 = 'PAYH'and is_sw = 1 then 1else 0end) as PAYH_sw,max(casewhen bank_name0 = 'QDYH'and is_sw = 1 then 1else 0end) as QDYH_sw,max(casewhen bank_name0 = '其他'and is_sw = 1 then 1else 0end) as qita_sw,max(casewhen bank_name0 = 'GDYH'and is_apply_ck = 1 then 1else 0end) as GDYH_applyck,max(casewhen bank_name0 = 'NBYH'and is_apply_ck = 1 then 1else 0end) as NBYH_applyck,max(casewhen bank_name0 = 'PFYH'and is_apply_ck = 1 then 1else 0end) as PFYH_applyck,max(casewhen bank_name0 = 'GFYH'and is_apply_ck = 1 then 1else 0end) as GFYH_applyck,max(casewhen bank_name0 = 'PAYH'and is_apply_ck = 1 then 1else 0end) as PAYH_applyck,max(casewhen bank_name0 = 'QDYH'and is_apply_ck = 1 then 1else 0end) as QDYH_applyck,max(casewhen bank_name0 = '其他'and is_apply_ck = 1 then 1else 0end) as qita_applyckfrom(selectdt,uid,bank_name0,city_name,max(casewhen eventid like '%sw%' then 1else 0end) as is_sw,max(casewhen eventid like '%ck%' then 1else 0end) as is_ck,max(casewhen eventid like '%ck%'and is_apply_ck = 1 then 1else 0end) as is_apply_ckfrom(selecta0.*,nvl(city_name, 'unknown') as city_name,casewhen bank_name = ''or bank_name = 'undefined'or bank_name is null then 'unkown'when bank_name like '%PAYH%' then 'PAYH'when bank_name not in ('GDYH', 'PFYH', 'GFYH', 'QDYH', 'NBYH', 'PAYH') then '其他'else bank_nameend as bank_name0from(selectdt,uid,eventid,is_apply_ck,client_time,bank_name0 as bank_name,--explode(split(pub_bank_code, ',')) as bank_name,city_idfromfin_ds.dwd_credit_card_user_visit_event_di lateral view outer explode(split(pub_bank_code, ',')) t as bank_name0wheredt between '2023-05-01'and '2023-07-18'and uid > 0and attrs ['pub_page_location'] = 0) a0left join (selectnvl(city_id, 'unknown') as city_id,nvl(city_name, 'unknown') as city_namefromwhole_dw.dim_citywheredt = '$[YYYY-MM-DD - 1D]') a on a0.city_id = a.city_id) a1group bydt,uid,city_name,bank_name0) bgroup bydt,uid,city_name) dwhere(GDYH_sw + NBYH_sw + PFYH_sw + GFYH_sw + PAYH_sw + QDYH_sw + qita_sw) > 1) aleft join (selectdt,uid,bank_name0 as first_visit_bank,city_name,concat_ws('-', dt, uid, city_name) as kfrom(selectdt,uid,bank_name0,city_name,sw_time,row_number() over(partition by dt,uid,city_nameorder bysw_time) as sw_orderfrom(selectdt,uid,bank_name0,city_name,max(casewhen eventid like '%sw%' then 1else 0end) as is_sw,max(casewhen eventid like '%ck%' then 1else 0end) as is_ck,max(casewhen eventid like '%ck%'and is_apply_ck = 1 then 1else 0end) as is_apply_ck,min(casewhen eventid like '%sw%' then client_timeelse nullend) as sw_timefrom(selecta0.*,nvl(city_name, 'unknown') as city_name,casewhen bank_name = ''or bank_name = 'undefined'or bank_name is null then 'unkown'when bank_name like '%PAYH%' then 'PAYH'when bank_name not in ('GDYH', 'PFYH', 'GFYH', 'QDYH', 'NBYH', 'PAYH') then '其他'else bank_nameend as bank_name0from(selectdt,uid,eventid,is_apply_ck,client_time,bank_name0 as bank_name,--explode(split(pub_bank_code, ',')) as bank_name,city_idfromfin_ds.dwd_credit_card_user_visit_event_di lateral view outer explode(split(pub_bank_code, ',')) t as bank_name0wheredt between '2023-05-01'and '2023-07-18'and uid > 0and attrs ['pub_page_location'] = 0) a0left join (selectnvl(city_id, 'unknown') as city_id,nvl(city_name, 'unknown') as city_namefromwhole_dw.dim_citywheredt = '$[YYYY-MM-DD - 1D]') a on a0.city_id = a.city_id) a1group bydt,uid,city_name,bank_name0having(sw_time is not null)) b) tempwheresw_order = 1) a2 on a.k = a2.k
),
temp2 as (selectcasewhen tag2 >= 1and tag1 is null then '只申请第二个'when tag2 = 1and tag1 is not null then '只申请第一个'when tag2 > 1and tag1 is not null then '第一个+第二个'when tag2 = 0 then '未申请'else nullend as tag3,tag1,tag2,dt,uid,city_namefrom(selectcasewhen GDYH_applyck = 1and first_visit_bank = 'GDYH' then '申请第一个-光大银行'when PFYH_applyck = 1and first_visit_bank = 'PFYH' then '申请第一个-浦发银行'when GFYH_applyck = 1and first_visit_bank = 'GFYH' then '申请第一个-广发银行'when QDYH_applyck = 1and first_visit_bank = 'QDYH' then '申请第一个-青岛银行'when NBYH_applyck = 1and first_visit_bank = 'NBYH' then '申请第一个-宁波银行'when PAYH_applyck = 1and first_visit_bank = 'PAYH' then '申请第一个-平安银行'when qita_applyck = 1and first_visit_bank = 'qita' then '申请第一个-其他银行'end as tag1,(GDYH_applyck + PFYH_applyck + GFYH_applyck + QDYH_applyck + NBYH_applyck + PAYH_applyck + qita_applyck) AS tag2,dt,uid,city_namefrommy_table) t1
)
selecttag3,count(*)
fromtemp2
group bytag3;

八、城市银行曝光数据代码4

with temp as (selectcity_name,--nvl(city_name, '汇总'),sum(total_uv) as total_uv,sum(one_b) as one_b,sum(two_b) as two_b,sum(three_b) as three_b,sum(four_b) as four_b,sum(fivemore_b) as fivemore_b,sum(one_b_ck) as one_b_ck,sum(two_b_ck) as two_b_ck,sum(three_b_ck) as three_b_ck,sum(four_b_ck) as four_b_ck,sum(fivemore_b_ck) as fivemore_b_ck,sum(one_b_pf1) as one_b_pf1,sum(two_b_pf1) as two_b_pf1,sum(three_b_pf1) as three_b_pf1,sum(four_b_pf1) as four_b_pf1,sum(fivemore_b_pf1) as fivemore_b_pf1,sum(one_b_pf2) as one_b_pf2,sum(two_b_pf2) as two_b_pf2,sum(three_b_pf2) as three_b_pf2,sum(four_b_pf2) as four_b_pf2,sum(fivemore_b_pf2) as fivemore_b_pf2,-- sum(one_b_ck) / sum(one_b) as one_b_apprate,-- sum(two_b_ck) / sum(two_b) as two_b_apprate,-- sum(three_b_ck) / sum(three_b) as three_b_apprate,-- sum(four_b_ck) / sum(four_b) as four_b_apprate,-- sum(fivemore_b_ck) / sum(fivemore_b) as fivemore_b_apprate,-- sum(GDYH_sw) as GDYH_sw,-- sum(GFYH_sw) as GFYH_sw,-- sum(PAYH_sw) as PAYH_sw,-- sum(NBYH_sw) as NBYH_sw,-- sum(QDYH_sw) as QDYH_sw,-- sum(PFYH_sw) as PFYH_sw,-- sum(qita_sw) as qita_sw,-- sum(GDYH_applyck) as GDYH_applyck,-- sum(GFYH_applyck) as GFYH_applyck,-- sum(PAYH_applyck) as PAYH_applyck,-- sum(NBYH_applyck) as NBYH_applyck,-- sum(QDYH_applyck) as QDYH_applyck,-- sum(PFYH_applyck) as PFYH_applyck,-- sum(qita_applyck) as qita_applyck,(CASEwhen avg(GDYH_bgrate) > 0.07 THEN 1ELSE 0END) as GDYH_bg,(CASEwhen avg(GFYH_bgrate) > 0.07 THEN 1ELSE 0END) as GFYH_bg,(CASEwhen avg(PAYH_bgrate) > 0.07 THEN 1ELSE 0END) as PAYH_bg,(CASEwhen avg(QDYH_bgrate) > 0.07 THEN 1ELSE 0END) as QDYH_bg,(CASEwhen avg(NBYH_bgrate) > 0.07 THEN 1ELSE 0END) as NBYH_bg,(CASEwhen avg(PFYH_bgrate) > 0.07 THEN 1ELSE 0END) as PFYH_bg,(CASEwhen avg(qita_bgrate) > 0.07 THEN 1ELSE 0END) as qita_bg -- avg(GFYH_bgrate) as GFYH_bgrate,from(selectcount(*) as total_uv,dt,city_name,sum(casewhen situation = '一银行曝光' then 1else 0end) as one_b,sum(casewhen situation = '二银行曝光' then 1else 0end) as two_b,sum(casewhen situation = '三银行曝光' then 1else 0end) as three_b,sum(casewhen situation = '四银行曝光' then 1else 0end) as four_b,sum(casewhen situation in ('五银行曝光','六银行曝光','七银行曝光') then 1else 0end) as fivemore_b,sum(casewhen situation = '一银行曝光' then is_apply_ckelse 0end) as one_b_ck,sum(casewhen situation = '二银行曝光' then is_apply_ckelse 0end) as two_b_ck,sum(casewhen situation = '三银行曝光' then is_apply_ckelse 0end) as three_b_ck,sum(casewhen situation = '四银行曝光' then is_apply_ckelse 0end) as four_b_ck,sum(casewhen situation in ('五银行曝光', '六银行曝光', '七银行曝光') then is_apply_ckelse 0end) as fivemore_b_ck,sum(casewhen situation = '一银行曝光' then is_passfirstelse 0end) as one_b_pf1,sum(casewhen situation = '二银行曝光' then is_passfirstelse 0end) as two_b_pf1,sum(casewhen situation = '三银行曝光' then is_passfirstelse 0end) as three_b_pf1,sum(casewhen situation = '四银行曝光' then is_passfirstelse 0end) as four_b_pf1,sum(casewhen situation in ('五银行曝光', '六银行曝光', '七银行曝光') then is_passfirstelse 0end) as fivemore_b_pf1,sum(casewhen situation = '一银行曝光' then is_passfirst2else 0end) as one_b_pf2,sum(casewhen situation = '二银行曝光' then is_passfirst2else 0end) as two_b_pf2,sum(casewhen situation = '三银行曝光' then is_passfirst2else 0end) as three_b_pf2,sum(casewhen situation = '四银行曝光' then is_passfirst2else 0end) as four_b_pf2,sum(casewhen situation in ('五银行曝光', '六银行曝光', '七银行曝光') then is_passfirst2else 0end) as fivemore_b_pf2,sum(GDYH_sw) as GDYH_sw,sum(GFYH_sw) as GFYH_sw,sum(PAYH_sw) as PAYH_sw,sum(NBYH_sw) as NBYH_sw,sum(QDYH_sw) as QDYH_sw,sum(PFYH_sw) as PFYH_sw,sum(qita_sw) as qita_sw,sum(GDYH_applyck) as GDYH_applyck,sum(GFYH_applyck) as GFYH_applyck,sum(PAYH_applyck) as PAYH_applyck,sum(NBYH_applyck) as NBYH_applyck,sum(QDYH_applyck) as QDYH_applyck,sum(PFYH_applyck) as PFYH_applyck,sum(qita_applyck) as qita_applyck,sum(GDYH_sw) / count(*) as GDYH_bgrate,sum(GFYH_sw) / count(*) as GFYH_bgrate,sum(PAYH_sw) / count(*) as PAYH_bgrate,sum(NBYH_sw) / count(*) as NBYH_bgrate,sum(QDYH_sw) / count(*) as QDYH_bgrate,sum(PFYH_sw) / count(*) as PFYH_bgrate,sum(qita_sw) / count(*) as qita_bgratefrom(selectdt,uid,city_name,is_passfirst,--case when pass_first_useris_apply_ck,case(GDYH_sw + NBYH_sw + PFYH_sw + GFYH_sw + PAYH_sw + QDYH_sw + qita_sw)when 1 then '一银行曝光'when 2 then '二银行曝光'when 3 then '三银行曝光'when 4 then '四银行曝光'when 5 then '五银行曝光'when 6 then '六银行曝光'when 7 then '七银行曝光'else 'unknown'end as situation,(casewhen (GDYH_sw + NBYH_sw + PFYH_sw + GFYH_sw + PAYH_sw + QDYH_sw + qita_sw) > 1and is_apply_ck = 1 then '多家曝光且点击申请'when (GDYH_sw + NBYH_sw + PFYH_sw + GFYH_sw + PAYH_sw + QDYH_sw + qita_sw) = 1and is_apply_ck = 1 then '单家曝光且点击申请'else '未点击申请'end) as tag,GDYH_sw,GFYH_sw,PAYH_sw,NBYH_sw,QDYH_sw,PFYH_sw,qita_sw,GDYH_applyck,GFYH_applyck,PAYH_applyck,NBYH_applyck,QDYH_applyck,PFYH_applyck,qita_applyck,(GDYH_pf + GFYH_pf + PAYH_pf + NBYH_pf + QDYH_pf + PFYH_pf + qita_pf) as is_passfirst2from(selectdt,uid,city_name,max(is_passfirst) as is_passfirst,max(is_apply_ck) as is_apply_ck,max(casewhen bank_name0 = 'GDYH'and is_sw = 1 then 1else 0end) as GDYH_sw,max(casewhen bank_name0 = 'NBYH'and is_sw = 1 then 1else 0end) as NBYH_sw,max(casewhen bank_name0 = 'PFYH'and is_sw = 1 then 1else 0end) as PFYH_sw,max(casewhen bank_name0 = 'GFYH'and is_sw = 1 then 1else 0end) as GFYH_sw,max(casewhen bank_name0 = 'PAYH'and is_sw = 1 then 1else 0end) as PAYH_sw,max(casewhen bank_name0 = 'QDYH'and is_sw = 1 then 1else 0end) as QDYH_sw,max(casewhen bank_name0 = '其他'and is_sw = 1 then 1else 0end) as qita_sw,max(casewhen bank_name0 = 'GDYH'and is_apply_ck = 1 then 1else 0end) as GDYH_applyck,max(casewhen bank_name0 = 'NBYH'and is_apply_ck = 1 then 1else 0end) as NBYH_applyck,max(casewhen bank_name0 = 'PFYH'and is_apply_ck = 1 then 1else 0end) as PFYH_applyck,max(casewhen bank_name0 = 'GFYH'and is_apply_ck = 1 then 1else 0end) as GFYH_applyck,max(casewhen bank_name0 = 'PAYH'and is_apply_ck = 1 then 1else 0end) as PAYH_applyck,max(casewhen bank_name0 = 'QDYH'and is_apply_ck = 1 then 1else 0end) as QDYH_applyck,max(casewhen bank_name0 = '其他'and is_apply_ck = 1 then 1else 0end) as qita_applyck,max(casewhen bank_name0 = 'GDYH'and is_passfirst = 1 then 1else 0end) as GDYH_pf,max(casewhen bank_name0 = 'NBYH'and is_passfirst = 1 then 1else 0end) as NBYH_pf,max(casewhen bank_name0 = 'PFYH'and is_passfirst = 1 then 1else 0end) as PFYH_pf,max(casewhen bank_name0 = 'GFYH'and is_passfirst = 1 then 1else 0end) as GFYH_pf,max(casewhen bank_name0 = 'PAYH'and is_passfirst = 1 then 1else 0end) as PAYH_pf,max(casewhen bank_name0 = 'QDYH'and is_passfirst = 1 then 1else 0end) as QDYH_pf,max(casewhen bank_name0 = '其他'and is_passfirst = 1 then 1else 0end) as qita_pffrom(selectdt,uid,bank_name0,city_name,max(casewhen eventid like '%sw%' then 1else 0end) as is_sw,max(casewhen eventid like '%ck%' then 1else 0end) as is_ck,max(casewhen eventid like '%ck%'and is_apply_ck = 1 then 1else 0end) as is_apply_ck,max (casewhen is_first_card = 1and is_pass = 1and (datediff(c_dt, dt) between 0and 3or c_dt is null) then 1else 0end) as is_passfirstfrom(selecta0.*,nvl(city_name, 'unknown') as city_name,casewhen a0.bank_name = ''or a0.bank_name = 'undefined'or a0.bank_name is null then 'unkown'when a0.bank_name like '%PAYH%' then 'PAYH'when a0.bank_name not in ('GDYH', 'PFYH', 'GFYH', 'QDYH', 'NBYH', 'PAYH') then '其他'else a0.bank_nameend as bank_name0,c.dt as c_dt,is_pass,is_first_cardfrom(selectdt,uid,eventid,channel_id,is_apply_ck,client_time,bank_name0 as bank_name,--explode(split(pub_bank_code, ',')) as bank_name,city_idfromfin_ds.dwd_credit_card_user_visit_event_di lateral view outer explode(split(pub_bank_code, ',')) t as bank_name0wheredt between '2023-05-01'and '2023-07-18'and uid > 0and attrs ['pub_page_location'] = 0) a0left join (selectnvl(city_id, 'unknown') as city_id,nvl(city_name, 'unknown') as city_namefromwhole_dw.dim_citywheredt = '$[YYYY-MM-DD - 1D]') a on a0.city_id = a.city_idleft join (----信用卡信息selectto_date(apply_time) as dt,uid as uid0,delivery_channel_id,bank_name,is_apply,is_pass,--is_activate,bank_code,--is_swipe,is_first_cardfrommanhattan_dw.dwd_cd_trd_credit_card_order_dfwheredt = '$[YYYY-MM-DD - 1D]'and is_apply = 1and uid > 0) c on a0.channel_id = c.delivery_channel_idand a0.uid = c.uid0and a0.bank_name = c.bank_code) a1group bydt,uid,city_name,bank_name0) bgroup bydt,uid,city_name) d) egroup bydt,city_name) fgroup by--rollup(city_name) 为了列转行不对cityname做上卷city_nameorder bytotal_uv desc
),
temp2 as (selectcity_name,total_uv,one_b,two_b,three_b,four_b,fivemore_b,one_b_ck,two_b_ck,three_b_ck,four_b_ck,fivemore_b_ck,one_b_pf1,two_b_pf1,three_b_pf1,four_b_pf1,fivemore_b_pf1,one_b_pf2,two_b_pf2,three_b_pf2,four_b_pf2,fivemore_b_pf2,(GDYH_bg + GFYH_bg + PAYH_bg + NBYH_bg + QDYH_bg + PFYH_bg + qita_bg) as bgnumfromtempwhereGDYH_bg + GFYH_bg + PAYH_bg + NBYH_bg + QDYH_bg + PFYH_bg + qita_bg > 1
),
temp3 as(--select city_name,bgnum,one_b as visit,one_b_ck as ck,one_b_pf1 as pf,'1' as tag from temp2--union allselectcity_name,bgnum,two_b as visit,two_b_ck as ck,two_b_pf1 as pf,'2' as tagfromtemp2union allselectcity_name,bgnum,three_b,three_b_ck,three_b_pf1,'3' as tagfromtemp2union allselectcity_name,bgnum,four_b,four_b_ck,four_b_pf1,'4' as tagfromtemp2union allselectcity_name,bgnum,fivemore_b,fivemore_b_ck,fivemore_b_pf1,'5' as tagfromtemp2
),
---,
--temp4 as(sel)
temp4 as(selectcity_name,bgnum,bgnum * 0.8 as bg_08,bgnum * 0.7 as bg_07,tag,visit,ck,pffromtemp3
)
selectsum(casewhen tag < bg_08 then visitelse 0end) as notwhole_v,sum(casewhen tag < bg_08 then ckelse 0end) as notwhole_ck,sum(casewhen tag < bg_08 then pfelse 0end) as notwhole_pf,sum(casewhen tag >= bg_08 then visitelse 0end) as whole_v,sum(casewhen tag >= bg_08 then ckelse 0end) as whole_ck,sum(casewhen tag >= bg_08 then pfelse 0end) as whole_pf,sum(casewhen tag < bg_07 then visitelse 0end) as notwhole_v2,sum(casewhen tag < bg_07 then ckelse 0end) as notwhole_ck2,sum(casewhen tag < bg_07 then pfelse 0end) as notwhole_pf2,sum(casewhen tag >= bg_07 then visitelse 0end) as whole_v,sum(casewhen tag >= bg_07 then ckelse 0end) as whole_ck2,sum(casewhen tag >= bg_07 then pfelse 0end) as whole_pf2
fromtemp4