构建画像系统体系
数据架构
标签体系
用户属性标签体系
用户行为标签体系
用户风险指标体系
用户消费维度指标体系
集团会员基础标签体系
支付会员基础标签体系
保险会员基础标签体系
信贷会员基础标签体系
会员标签体系
理财会员基础标签体系
消费金融会员基础标签体系
标签表模型设计
横表
以 Hive 为例，我们最常用的就是横表，也就是一个 key，跟上它的所有标签。
横表有什么问题？
1：标签的数量会随时增加，横表需要不断新增字段并重刷数据
2：不同的标签计算频率可能不一致
3：不同标签的生成时间可能不一致
4：大量空缺的标签会导致存储稀疏竖表
竖表其实就是将标签都拆开，一个用户有多少标签，那么在这里面就会有几条数据。竖表有什么问题？
不利于查询。
比如：
“我们想看年龄在 23-30 之间，月薪在 10-20k 之间，喜欢听古典音乐的女性”，这种多标签查询条件组合情况在竖表中就不太容易支持。两者结合，标签生成阶段使用竖表，最终生成一个标签横表，对外提供标签查询服务。
标签开发流程
需要开发的表梳理
竖表加工示例
-----------------------------------------------step01建表：用户消费属性标签表
CREATE TABLE dw.dw_member_trade_action_d(
user_id string comment '用户id'
,tag_id string comment '标签id'
,tag_name string comment '标签名称'
,cnt string comment '标签值'
,tag_type_id string comment '标签类型'
,reserve1 string comment '预留字段1'
,reserve2 string comment '预留字段2'
)
comment '用户消费属性标签表'
PARTITIONED BY (data_date STRING COMMENT '数据日期',business_cd STRING COMMENT '业务线');
---------------------------------------------step02标签计算(这几标签计算只列举部分标签的计算逻辑，其他标签相同)
---------------------------------------------计算会员近1个月的成功支付订单数
insert overwrite table dw.dw.dw_member_trade_action_d partition(data_date= "${hivevar:statis_date}",business_cd='jt')
select
t.acct_no as user_id --用户id
,'030000122' as tag_id --自定义的标签id
,'pay_cnt_1m' as tag_name --标签名称
,,sum(t2.pay_cnt_1m) as pay_cnt_1m as cnt --近1个月的成功支付订单数
,'01' as tag_type_id --标签类型
,null as reserve1
,null as reserve2
FROM dws.mls_member_visit_base_info_all
WHERE PV_DATE>FINANCE.AddMonth('${hivevar:statis_date}',-3)
AND PV_DATE<=${hivevar:yesterday}
GROUP BY acct_no
;
---------------------------------------------基金历史购买总金额
insert overwrite table dw.dw.dw_member_trade_action_d partition(data_date= "${hivevar:statis_date}",business_cd='fin')
select
t.acct_no as user_id --用户id
,'030000123' as tag_id --自定义的标签id
,'fnd_prch_amt' as tag_name --标签名称
,SUM(T1.TOT_AMT) as cnt ----基金历史购买总金额
,'01' as tag_type_id --标签类型
,null as reserve1
,null as reserve2
FROM dwd.sor_txn_fnd_prch_order T1
WHERE t1.fund_id not in('1','2','106100','105450','105451') ----剔除天天利财
and t1.SYS_ST='14' --申购成功
and T1.PAY_DATE IS NOT NULL
GROUP BY acct_no
;
---------------------------------------------近7天理财次数
insert overwrite table dw.dw.dw_member_base_info_d partition(data_date= "${hivevar:statis_date}",business_cd='fin')
select
t.acct_no as user_id --用户id
,'030000223' as tag_id --自定义的标签id
,'fin_prch_cnt_7days' as tag_name --标签名称
, count(CASE WHEN t.order_date>=${hivevar:before7day} and t.order_date<=${hivevar:yesterday} THEN t.order_id END) as cnt --近7天理财次数
,'01' as tag_type_id --标签类型
,null as reserve1
,null as reserve2
from dwd.dpa_fin_order_collection t
GROUP BY acct_no
;
---------------------------------------------保险理财收益金额-12个月内
insert overwrite table dw.dw.dw_member_base_info_d partition(data_date= "${hivevar:statis_date}",business_cd='ins')
select
t.acct_no as user_id --用户id
,'030000423' as tag_id --自定义的标签id
,'ifs_pft_amt_12m' as tag_name --标签名称
,round(sum(case when income_date>=${hivevar:month12day} and income_date<=${hivevar:yesterday} then t2.account_value end)/100,2) as cnt --保险理财收益金额-12个月内
,'01' as tag_type_id --标签类型
,null as reserve1
,null as reserve2
from fdm_sor.SOR_TXN_INS_DAILY_INCOME t2
where t2.income_date is not null
and t2.income_date>=${hivevar:month12day}
and t2.income_date<=${hivevar:yesterday}
GROUP BY acct_no
;
---------------------------------------------信贷累计贷款-6个月内
insert overwrite table dw.dw.dw_member_base_info_d partition(data_date= "${hivevar:statis_date}",business_cd='pls')
select
t.acct_no as user_id --用户id
,'030000428' as tag_id --自定义的标签id
,'tb_loan_amt_1m' as tag_name --标签名称
round(sum(case when substr(t.loan_time,1,10)>=${hivevar:month1day} and substr(t.loan_time,1,10)<=${hivevar:yesterday} then t.loan_amount/100 end),2) as cnt --信贷累计贷款-1个月内
,'01' as tag_type_id --标签类型
,null as reserve1
,null as reserve2
from fdm_sor.SOR_TXN_INS_DAILY_INCOME t2
where t2.income_date is not null
and t2.income_date>=${hivevar:month12day}
and t2.income_date<=${hivevar:yesterday}
GROUP BY acct_no
;先计算竖表中的每个标签，然后写进竖表不同分区中。
最后将竖表合并成横表，对外提供标签查询服务。
横表加工示例
-----------------------------------------------step01建表：理财会员标签表
CREATE TABLE `dw.dw_member_userprofile_fin`(
acct_no string COMMENT '用户id'
,fin_mem_sts string comment '理财开户日期'
,fin_is_fzn string comment '基金会员用户类型'
,fin_is_bind_card string comment '是否设置零钱宝自动转入'
,fin_is_cur_bind_card string comment '零钱宝非定期理财大客户'
,fin_is_epp_bag_user string comment '理财资产大额变动用户'
,fin_is_scan string comment '定期理财沉睡用户'
,fin_is_by_scan string comment '定期理财到期还款'
,fin_is_quick_fin string comment '理财AUM（资产管理规模）分级'
,fin_is_fin_user string comment '理财用户整体生命周期'
,fin_is_bill_fin_user string comment '理财会员活跃度等级'
,fin_is_fnd_user string comment '零钱宝会员活跃度等级'
,fin_is_bof_prch string comment '基金会员活跃度等级'
,fin_is_chp_user string comment '定期理财会员活跃度等级'
,fin_is_ins_user string comment '理财会员价值度等级'
,fin_is_cfc_user string comment '基金会员价值度等级'
,fin_is_ccr_user string comment '定期理财会员价值等级'
,fin_lifecycle string comment '是否零钱宝潜逃用户'
,fin_actv_lvl_tm string comment '是否零钱宝流失用户'
,fin_actv_lvl string comment '是否零钱宝僵尸用户'
,fin_value_lvl string comment '是否基金潜逃用户'
,fin_loyal_lvl string comment '是否基金流失用户'
,fin_cosm_drive string comment '是否基金僵尸用户'
,fin_disposition string comment '是否基金0元用户'
,fin_gds_cgy_love string comment '是否定期理财变现用户'
,fin_work_dt_vst_lvl string comment '定期理财变现频次'
,fin_weekend_vst_lvl string comment '定期理财持有类型'
,fin_work_tm_vst_lvl string comment '定期理财用户-年化收益偏好'
,fin_rest_tm_vst_lvl string comment '定期理财用户-投资期限偏好'
,fin_night_vst_lvl string comment '会员访问终端偏好'
,fin_commute_vst_lvl string comment '会员访问深度_页面数'
,fin_rgst_from string comment '会员访问深度_次数'
,fin_rgst_tml string comment '会员访问深度_跳出率'
,fin_tml_love string comment '会员访问深度_访问停留时间'
,fin_chn_love string comment '会员访问深度_页面停留时间'
,fin_vst_pg_lvl string comment '首次购买时间'
,fin_vst_cnt_lvl string comment '浏览票据/定期理财产品列表页，且不包含已经理财成功（含pc&客户端）'
,fin_vst_bounce_lvl string comment '浏览基金理财详情页，且不包含已经理财成功（含pc&客户端）'
,fin_vst_staytm_lvl string comment '浏览零钱宝首页，未转入（含pc&客户端）'
,fin_pg_staytm_lvl string comment '是否定期理财未转入用户'
,fin_epp_lifecycle string comment '定期理财-到期剩余时间'
,fin_epp_bag_actv_lvl string comment '最近被动扫码时间'
,fin_epp_scan_actv_lvl string comment '是否金融APP交易用户'
,fin_epp_byscan_actv_lvl string comment '是否交易用户'
,fin_is_epp_tran string comment '最近一次交易使用终端'
,fin_epp_tran_lvl string comment '最近一次交易类型'
,fin_is_bank_tran string comment '最近一次交易理财渠道'
,fin_bank_tran_lvl string comment '余额不足交易次数'
,fin_is_crdt_crd_tran string comment '最新登陆时间'
,fin_crdt_crd_tran_lvl string comment '首笔消费-理财方式'
,fin_is_mbl_fill string comment '最近消费-理财方式'
,fin_mbl_fill_lvl string comment '最近一笔消费时间'
,fin_is_sch_crd_user string comment '消费笔数'
,fin_sch_crd_lvl string comment '消费金额'
,fin_is_water_chrg string comment '消费笔数-理财'
,fin_water_chrg_lvl string comment '消费金额-理财'
,fin_is_ele_chrg string comment '理财交易笔数占比'
,fin_ele_chrg_lvl string comment '理财交易金额占比'
,fin_is_gas_chrg string comment '连续两次未使用理财用户'
,fin_gas_chrg_lvl string comment '线上消费用户'
,fin_is_tel_chrg string comment '线上消费用户'
,fin_tel_chrg_lvl string comment '微信上绑定的银行卡'
,fin_is_adsl_chrg string comment '花呗理财用户'
,fin_adsl_chrg_lvl string comment '信用卡分期理财用户'
,fin_is_mbl_bill_user string comment 'PP视频付费会员包用户'
,fin_mbl_bill_lvl string comment 'PP体育付费会员包用户'
,fin_is_tv_chrg string comment '付费会员'
,fin_tv_chrg_lvl string comment '首次消费时间'
,fin_is_gas_crd_user string comment '最近一次消费时间'
,fin_gas_crd_lvl string comment '最近一次理财方式'
,fin_epp_vst_pg_lvl string comment '首次使用理财时间'
,fin_epp_vst_cnt_lvl string comment '消费笔数'
,fin_epp_vst_bounce_lvl string comment '消费金额'
,fin_epp_vst_staytm_lvl string comment '消费笔数-理财'
,fin_epp_pg_staytm_lvl string comment '消费金额-理财'
,fin_fin_rgst_dt string comment '理财交易笔数占比'
,fin_fin_rgst_days string comment '理财交易金额占比'
,fin_fin_rgst_drtn string comment '消费笔数-微信理财'
,fin_is_auto_bof_tran string comment '消费金额-微信理财'
,fin_fin_aum_lvl string comment '消费笔数-理财宝'
,fin_fin_hold_amt string comment '消费金额-理财宝'
,fin_fin_lifecycle string comment '连续两次未使用理财用户'
,fin_fin_actv_lvl string comment '消费时间段偏好（不限理财方式）'
,fin_bof_actv_lvl string comment '理财渠道选择偏好（近180天）'
,fin_fnd_actv_lvl string comment '用户交易城市（不限理财方式，近180天）'
,fin_bill_actv_lvl string comment '用户交易城市（限理财，近180天）'
,fin_fin_value_lvl string comment '是否随时会员'
,fin_bof_value_lvl string comment '购买随时会员次数（近180天）'
,fin_fnd_value_lvl string comment '随时会员线下消费笔数（不限理财方式，近180天）'
,fin_bill_value_lvl string comment '随时会员线下消费笔数（限理财，近180天）'
,fin_is_fnd_prch string comment '促销类订单数-1个月内（限理财）'
,fin_is_bof_zero_hold string comment '促销类订单数-1个月内（限理财）'
,fin_is_bill_cash_user string comment '促销类订单数-6个月内（限理财）'
,fin_inv_prd_love string comment '促销交易占比（限理财，近180天）'
,fin_inv_risk_love string comment '最近7天优惠券领取次数'
,fin_inv_term_love string comment '最近15天优惠券领取次数'
,fin_fin_tml_love string comment '最近30天优惠券领取次数'
,fin_fin_vst_pg_lvl string comment '最近15天优惠券使用率'
,fin_fin_vst_cnt_lvl string comment '最近30天优惠券使用率'
,fin_fin_vst_bounce_lvl string comment '最近60天优惠券使用率'
,fin_fin_vst_staytm_lvl string comment '理财成功页奖励领取偏好'
,fin_fin_pg_staytm_lvl string comment '银联码开通与解约'
,fin_fin_fin_tm_1st string comment '银联码使用'
,fin_fin_fin_days_1st string comment '银联码-交易笔数'
,fin_fin_fin_1st_drtn string comment '银联码-交易金额'
,fin_bof_fin_1st_drtn string comment '最近一次使用银联码时间'
,fin_bof_fin_last_drtn string comment '银联码理财渠道选择偏好'
,fin_bof_hold_drtn string comment '交易商户类型偏好'
,fin_is_7d_fnd_fail string comment '交易商户城市'
,fin_is_7d_bof_fail string comment '银联码优惠券领取次数'
,fin_is_7d_bil_fail string comment '银联码优惠券使用次数'
,fin_is_bil_vip string comment '银联码优惠券使用率'
,fin_is_fnd_vip string comment '促销订单占比'
,fin_is_bof_vip string comment '用户累计补贴金额-月度'
,fin_bof_new_prch_7d string comment '用户累计补贴金额-年度'
,fin_fin_new_non_prch_7d string comment '银联码理财成功页权益领取偏好'
,fin_fin_new_prch_7d string comment '是否参加过分享领红包活动'
,fin_bof_new_prch_30d string comment '分享领红包参与频次-月度（近30天）'
,fin_fin_new_non_prch_30d string comment '分享领红包参与频次-月度（近60天）'
,fin_fin_new_prch_30d string comment '自助贩卖机-交易笔数'
,fin_non_2nd_prch string comment '自助贩卖机-交易金额'
,fin_non_prch_af_refin string comment '最近一次在自助贩卖机理财时间'
,fin_bil_cash_cnt_1m string comment '自助贩卖机场景理财方式偏好'
,fin_bil_cash_amt_1m string comment '自助贩卖机购买时间段偏好'
,fin_is_chp_focus_user string comment '自助贩卖机是否促销偏好用户'
,fin_chp_foucs_attr string comment '是否开通乘车码'
,fin_chp_focus_lvl string comment '是否解约乘车码'
,fin_is_chp_fin_user string comment '乘车开通城市'
,fin_is_chp_lotto_user string comment '是否使用乘车码'
,fin_chp_fin_tm_1st string comment '是否开通NFC卡'
,fin_chp_fin_days_1st string comment '是否解约NFC卡'
,fin_chp_fin_1st_drtn string comment 'NFC开通城市'
,fin_chp_first_gz_type string comment '是否使用NFC卡（现在拿不到数据）'
,fin_chp_lifecycle string comment '公交与地铁偏好'
,fin_chp_actv_lvl string comment '交易笔数'
,fin_chp_value_lvl string comment '消费金额'
,fin_chp_tml_love string comment '最近一次乘车时间'
,fin_chp_vst_pg_lvl string comment '是否有过高速扫码消费'
,fin_chp_vst_cnt_lvl string comment '高速-最近一次消费时间'
,fin_chp_vst_bounce_lvl string comment '是否有过停车消费'
,fin_chp_vst_staytm_lvl string comment '停车-最近一次消费时间'
,fin_chp_pg_staytm_lvl string comment '工作日乘车偏好'
,fin_chp_is_click_farm string comment '否通勤时间乘车偏好度'
,fin_chp_lucky_draw_amt string comment '促销类订单数'
,fin_chp_last_buy_cyc string comment '促销偏好用户'
,fin_chp_buy_gz_types string comment '优惠券领取次数'
,fin_chp_buy_cnt string comment '优惠券使用次数'
,fin_chp_reward_type_amt string comment '理财成功页奖励领取偏好'
,fin_chp_income_type_amt string comment '高速-是否促销偏好用户'
,fin_chp_pv_cyc string comment '停车-是否促销偏好用户'
,fin_chp_pv_types string comment '停车-理财成功页奖励领取偏好'
,fin_chp_pv_cnt string comment '停车-理财方式偏好-30天内'
,fin_chp_7days_nobuys string comment '停车-最近15天随机立减使用频次'
,fin_chp_15days_nobuys string comment '交易笔数'
,fin_chp_is_1yuan string comment '消费金额'
,fin_chp_is_use_coupon string comment '是否是便民场景用户'
,fin_chp_coupon_use_cat string comment '话费-手机号码充值个数-近30天'
,fin_chp_coupon_use_cyc string comment '是否设置话费定期充值'
,fin_chp_coupon_use_cnt string comment '生活缴费是否为本人缴费'
,fin_chp_coupon_use_amt string comment '电费平均客单价-近30天'
,fin_chp_coupon_ord_amt string comment '缴费时间段统计-近180天'
,fin_chp_buy_cat string comment '优惠券领取次数'
,fin_chp_buy_dept string comment '优惠券使用次数'
,fin_cfc_rgst_dt string comment '理财方式偏好-30天内'
,fin_cfc_rgst_days string comment '话费-用户充值面值偏好-近30天'
,fin_cfc_rgst_drtn string comment '乘坐火车票日期偏好-近60天'
,fin_cfc_open_dt string comment '是否购买出行保险'
,fin_cfc_open_drtn string comment '加油卡-用户充值面值偏好-近30天'
,fin_cfc_fin_dt_1st string comment '转账到银行卡-对象偏好（本人卡/他人卡）-近30天'
,fin_cfc_fin_1st_drtn string comment '转账到银行卡-是否理财手续费-近30天'
,fin_cfc_user_src string comment '转账到卡-出款银行名称分布'
,fin_cfc_amt string comment '还款-对象偏好-近30天'
,fin_cfc_bal string comment '还款-是否理财手续费-近30天'
,fin_is_cfc_stages string comment '还款金额累计-近3个月'
,fin_is_cfc_flex_use string comment '还款金额累计-近6个月'
,fin_is_cfc_overdue string comment '信用卡还款张数'
,fin_cfc_overdue_days string comment '信用卡还款名称'
,fin_cfc_tml_love string comment '是否开通理财'
,fin_is_nonfnc_mem string comment '是否开通付款码'
,fin_pupsec_status string comment '是否使用付款码'
,fin_chnel_lvl string comment '最近一次交易记录'
,fin_chnel_cnt string comment '政务-消费笔数-周度（近7天）'
,fin_mbl_auth string comment '政务-消费笔数-周度（近15天）'
,fin_actv_type string comment '政务-消费笔数-月度（近30天）'
,fin_app_hold string comment '政务-是否促销偏好用户'
,fin_cfc_black_list string comment '是否关注理财公众号'
,fin_malicious_rgst string comment '大转盘参与次数—周度（7天）'
,fin_is_scalper string comment '消息提醒点击次数'
,fin_fin_black_list string comment '发文点击次数'
)
COMMENT '理财会员标签表'
PARTITIONED BY (data_date STRING COMMENT '数据日期')
;
-----------------------------------------------step01建表：集团会员基础信息标签表
--------1小时10分钟
insert overwrite table dw.dw_member_userprofile_fin
partition(data_date= "${hivevar:statis_date}")
select
acct_no
,max(if(t1.tag_name='fin_mem_sts' ,cnt,null)) as fin_mem_sts
,max(if(t1.tag_name='fin_is_fzn' ,cnt,null)) as fin_is_fzn
,max(if(t1.tag_name='fin_is_bind_card' ,cnt,null)) as fin_is_bind_card
,max(if(t1.tag_name='fin_is_cur_bind_card' ,cnt,null)) as fin_is_cur_bind_card
,max(if(t1.tag_name='fin_is_epp_bag_user' ,cnt,null)) as fin_is_epp_bag_user
,max(if(t1.tag_name='fin_is_scan' ,cnt,null)) as fin_is_scan
,max(if(t2.tag_name='fin_is_by_scan' ,cnt,null)) as fin_is_by_scan
,max(if(t2.tag_name='fin_is_quick_fin' ,cnt,null)) as fin_is_quick_fin
,max(if(t2.tag_name='fin_is_fin_user' ,cnt,null)) as fin_is_fin_user
,max(if(t2.tag_name='fin_is_bill_fin_user' ,cnt,null)) as fin_is_bill_fin_user
,max(if(t2.tag_name='fin_is_fnd_user' ,cnt,null)) as fin_is_fnd_user
,max(if(t2.tag_name='fin_is_bof_prch' ,cnt,null)) as fin_is_bof_prch
,max(if(t2.tag_name='fin_is_chp_user' ,cnt,null)) as fin_is_chp_user
,max(if(t2.tag_name='fin_is_ins_user' ,cnt,null)) as fin_is_ins_user
,max(if(t2.tag_name='fin_is_cfc_user' ,cnt,null)) as fin_is_cfc_user
,max(if(t2.tag_name='fin_is_ccr_user' ,cnt,null)) as fin_is_ccr_user
,max(if(t2.tag_name='fin_lifecycle' ,cnt,null)) as fin_lifecycle
,max(if(t2.tag_name='fin_actv_lvl_tm' ,cnt,null)) as fin_actv_lvl_tm
,max(if(t2.tag_name='fin_actv_lvl' ,cnt,null)) as fin_actv_lvl
,max(if(t2.tag_name='fin_value_lvl' ,cnt,null)) as fin_value_lvl
,max(if(t2.tag_name='fin_loyal_lvl' ,cnt,null)) as fin_loyal_lvl
,max(if(t2.tag_name='fin_cosm_drive' ,cnt,null)) as fin_cosm_drive
,max(if(t2.tag_name='fin_disposition' ,cnt,null)) as fin_disposition
,max(if(t2.tag_name='fin_gds_cgy_love' ,cnt,null)) as fin_gds_cgy_love
,max(if(t2.tag_name='fin_work_dt_vst_lvl' ,cnt,null)) as fin_work_dt_vst_lvl
,max(if(t2.tag_name='fin_weekend_vst_lvl' ,cnt,null)) as fin_weekend_vst_lvl
,max(if(t2.tag_name='fin_work_tm_vst_lvl' ,cnt,null)) as fin_work_tm_vst_lvl
,max(if(t2.tag_name='fin_rest_tm_vst_lvl' ,cnt,null)) as fin_rest_tm_vst_lvl
,max(if(t2.tag_name='fin_night_vst_lvl' ,cnt,null)) as fin_night_vst_lvl
,max(if(t2.tag_name='fin_commute_vst_lvl' ,cnt,null)) as fin_commute_vst_lvl
,max(if(t2.tag_name='fin_rgst_from' ,cnt,null)) as fin_rgst_from
,max(if(t2.tag_name='fin_rgst_tml' ,cnt,null)) as fin_rgst_tml
,max(if(t2.tag_name='fin_tml_love' ,cnt,null)) as fin_tml_love
,max(if(t1.tag_name='fin_chn_love' ,cnt,null)) as fin_chn_love
,max(if(t1.tag_name='fin_vst_pg_lvl' ,cnt,null)) as fin_vst_pg_lvl
,max(if(t1.tag_name='fin_vst_cnt_lvl' ,cnt,null)) as fin_vst_cnt_lvl
,max(if(t1.tag_name='fin_vst_bounce_lvl' ,cnt,null)) as fin_vst_bounce_lvl
,max(if(t1.tag_name='fin_vst_staytm_lvl' ,cnt,null)) as fin_vst_staytm_lvl
,max(if(t1.tag_name='fin_pg_staytm_lvl' ,cnt,null)) as fin_pg_staytm_lvl
,max(if(t1.tag_name='fin_epp_lifecycle' ,cnt,null)) as fin_epp_lifecycle
,max(if(t1.tag_name='fin_epp_bag_actv_lvl' ,cnt,null)) as fin_epp_bag_actv_lvl
,max(if(t1.tag_name='fin_epp_scan_actv_lvl' ,cnt,null)) as fin_epp_scan_actv_lvl
,max(if(t1.tag_name='fin_epp_byscan_actv_lvl' ,cnt,null)) as fin_epp_byscan_actv_lvl
,max(if(t1.tag_name='fin_is_epp_tran' ,cnt,null)) as fin_is_epp_tran
,max(if(t1.tag_name='fin_epp_tran_lvl' ,cnt,null)) as fin_epp_tran_lvl
,max(if(t1.tag_name='fin_is_bank_tran' ,cnt,null)) as fin_is_bank_tran
,max(if(t1.tag_name='fin_bank_tran_lvl' ,cnt,null)) as fin_bank_tran_lvl
,max(if(t1.tag_name='fin_is_crdt_crd_tran' ,cnt,null)) as fin_is_crdt_crd_tran
,max(if(t2.tag_name='fin_crdt_crd_tran_lvl' ,cnt,null)) as fin_crdt_crd_tran_lvl
,max(if(t2.tag_name='fin_is_mbl_fill' ,cnt,null)) as fin_is_mbl_fill
,max(if(t2.tag_name='fin_mbl_fill_lvl' ,cnt,null)) as fin_mbl_fill_lvl
,max(if(t2.tag_name='fin_is_sch_crd_user' ,cnt,null)) as fin_is_sch_crd_user
,max(if(t2.tag_name='fin_sch_crd_lvl' ,cnt,null)) as fin_sch_crd_lvl
,max(if(t2.tag_name='fin_is_water_chrg' ,cnt,null)) as fin_is_water_chrg
,max(if(t2.tag_name='fin_water_chrg_lvl' ,cnt,null)) as fin_water_chrg_lvl
,max(if(t2.tag_name='fin_is_ele_chrg' ,cnt,null)) as fin_is_ele_chrg
,max(if(t2.tag_name='fin_ele_chrg_lvl' ,cnt,null)) as fin_ele_chrg_lvl
,max(if(t2.tag_name='fin_is_gas_chrg' ,cnt,null)) as fin_is_gas_chrg
,max(if(t2.tag_name='fin_gas_chrg_lvl' ,cnt,null)) as fin_gas_chrg_lvl
,max(if(t2.tag_name='fin_is_tel_chrg' ,cnt,null)) as fin_is_tel_chrg
,max(if(t2.tag_name='fin_tel_chrg_lvl' ,cnt,null)) as fin_tel_chrg_lvl
,max(if(t2.tag_name='fin_is_adsl_chrg' ,cnt,null)) as fin_is_adsl_chrg
,max(if(t2.tag_name='fin_adsl_chrg_lvl' ,cnt,null)) as fin_adsl_chrg_lvl
,max(if(t2.tag_name='fin_is_mbl_bill_user' ,cnt,null)) as fin_is_mbl_bill_user
,max(if(t2.tag_name='fin_mbl_bill_lvl' ,cnt,null)) as fin_mbl_bill_lvl
,max(if(t2.tag_name='fin_is_tv_chrg' ,cnt,null)) as fin_is_tv_chrg
,max(if(t2.tag_name='fin_tv_chrg_lvl' ,cnt,null)) as fin_tv_chrg_lvl
,max(if(t2.tag_name='fin_is_gas_crd_user' ,cnt,null)) as fin_is_gas_crd_user
,max(if(t2.tag_name='fin_gas_crd_lvl' ,cnt,null)) as fin_gas_crd_lvl
,max(if(t2.tag_name='fin_epp_vst_pg_lvl' ,cnt,null)) as fin_epp_vst_pg_lvl
,max(if(t2.tag_name='fin_epp_vst_cnt_lvl' ,cnt,null)) as fin_epp_vst_cnt_lvl
,max(if(t2.tag_name='fin_epp_vst_bounce_lvl' ,cnt,null)) as fin_epp_vst_bounce_lvl
,max(if(t2.tag_name='fin_epp_vst_staytm_lvl' ,cnt,null)) as fin_epp_vst_staytm_lvl
,max(if(t1.tag_name='fin_epp_pg_staytm_lvl' ,cnt,null)) as fin_epp_pg_staytm_lvl
,max(if(t1.tag_name='fin_fin_rgst_dt' ,cnt,null)) as fin_fin_rgst_dt
,max(if(t1.tag_name='fin_fin_rgst_days' ,cnt,null)) as fin_fin_rgst_days
,max(if(t1.tag_name='fin_fin_rgst_drtn' ,cnt,null)) as fin_fin_rgst_drtn
,max(if(t1.tag_name='fin_is_auto_bof_tran' ,cnt,null)) as fin_is_auto_bof_tran
,max(if(t1.tag_name='fin_fin_aum_lvl' ,cnt,null)) as fin_fin_aum_lvl
,max(if(t1.tag_name='fin_fin_hold_amt' ,cnt,null)) as fin_fin_hold_amt
,max(if(t1.tag_name='fin_fin_lifecycle' ,cnt,null)) as fin_fin_lifecycle
,max(if(t1.tag_name='fin_fin_actv_lvl' ,cnt,null)) as fin_fin_actv_lvl
,max(if(t1.tag_name='fin_bof_actv_lvl' ,cnt,null)) as fin_bof_actv_lvl
,max(if(t1.tag_name='fin_fnd_actv_lvl' ,cnt,null)) as fin_fnd_actv_lvl
,max(if(t1.tag_name='fin_bill_actv_lvl' ,cnt,null)) as fin_bill_actv_lvl
,max(if(t1.tag_name='fin_fin_value_lvl' ,cnt,null)) as fin_fin_value_lvl
,max(if(t1.tag_name='fin_bof_value_lvl' ,cnt,null)) as fin_bof_value_lvl
,max(if(t1.tag_name='fin_fnd_value_lvl' ,cnt,null)) as fin_fnd_value_lvl
,max(if(t1.tag_name='fin_bill_value_lvl' ,cnt,null)) as fin_bill_value_lvl
,max(if(t1.tag_name='fin_is_fnd_prch' ,cnt,null)) as fin_is_fnd_prch
,max(if(t1.tag_name='fin_is_bof_zero_hold' ,cnt,null)) as fin_is_bof_zero_hold
,max(if(t1.tag_name='fin_is_bill_cash_user' ,cnt,null)) as fin_is_bill_cash_user
,max(if(t1.tag_name='fin_inv_prd_love' ,cnt,null)) as fin_inv_prd_love
,max(if(t1.tag_name='fin_inv_risk_love' ,cnt,null)) as fin_inv_risk_love
,max(if(t1.tag_name='fin_inv_term_love' ,cnt,null)) as fin_inv_term_love
,max(if(t1.tag_name='fin_fin_tml_love' ,cnt,null)) as fin_fin_tml_love
,max(if(t1.tag_name='fin_fin_vst_pg_lvl' ,cnt,null)) as fin_fin_vst_pg_lvl
,max(if(t1.tag_name='fin_fin_vst_cnt_lvl' ,cnt,null)) as fin_fin_vst_cnt_lvl
,max(if(t1.tag_name='fin_fin_vst_bounce_lvl' ,cnt,null)) as fin_fin_vst_bounce_lvl
,max(if(t1.tag_name='fin_fin_vst_staytm_lvl' ,cnt,null)) as fin_fin_vst_staytm_lvl
,max(if(t1.tag_name='fin_fin_pg_staytm_lvl' ,cnt,null)) as fin_fin_pg_staytm_lvl
,max(if(t1.tag_name='fin_fin_fin_tm_1st' ,cnt,null)) as fin_fin_fin_tm_1st
,max(if(t1.tag_name='fin_fin_fin_days_1st' ,cnt,null)) as fin_fin_fin_days_1st
,max(if(t1.tag_name='fin_fin_fin_1st_drtn' ,cnt,null)) as fin_fin_fin_1st_drtn
,max(if(t1.tag_name='fin_bof_fin_1st_drtn' ,cnt,null)) as fin_bof_fin_1st_drtn
,max(if(t1.tag_name='fin_bof_fin_last_drtn' ,cnt,null)) as fin_bof_fin_last_drtn
,max(if(t1.tag_name='fin_bof_hold_drtn' ,cnt,null)) as fin_bof_hold_drtn
,max(if(t1.tag_name='fin_is_7d_fnd_fail' ,cnt,null)) as fin_is_7d_fnd_fail
,max(if(t1.tag_name='fin_is_7d_bof_fail' ,cnt,null)) as fin_is_7d_bof_fail
,max(if(t1.tag_name='fin_is_7d_bil_fail' ,cnt,null)) as fin_is_7d_bil_fail
,max(if(t1.tag_name='fin_is_bil_vip' ,cnt,null)) as fin_is_bil_vip
,max(if(t1.tag_name='fin_is_fnd_vip' ,cnt,null)) as fin_is_fnd_vip
,max(if(t1.tag_name='fin_is_bof_vip' ,cnt,null)) as fin_is_bof_vip
,max(if(t1.tag_name='fin_bof_new_prch_7d' ,cnt,null)) as fin_bof_new_prch_7d
,max(if(t1.tag_name='fin_fin_new_non_prch_7d' ,cnt,null)) as fin_fin_new_non_prch_7d
,max(if(t1.tag_name='fin_fin_new_prch_7d' ,cnt,null)) as fin_fin_new_prch_7d
,max(if(t1.tag_name='fin_bof_new_prch_30d' ,cnt,null)) as fin_bof_new_prch_30d
,max(if(t1.tag_name='fin_fin_new_non_prch_30d' ,cnt,null)) as fin_fin_new_non_prch_30d
,max(if(t1.tag_name='fin_fin_new_prch_30d' ,cnt,null)) as fin_fin_new_prch_30d
,max(if(t1.tag_name='fin_non_2nd_prch' ,cnt,null)) as fin_non_2nd_prch
,max(if(t1.tag_name='fin_non_prch_af_refin' ,cnt,null)) as fin_non_prch_af_refin
,max(if(t1.tag_name='fin_bil_cash_cnt_1m' ,cnt,null)) as fin_bil_cash_cnt_1m
,max(if(t1.tag_name='fin_bil_cash_amt_1m' ,cnt,null)) as fin_bil_cash_amt_1m
,max(if(t1.tag_name='fin_is_chp_focus_user' ,cnt,null)) as fin_is_chp_focus_user
,max(if(t1.tag_name='fin_chp_foucs_attr' ,cnt,null)) as fin_chp_foucs_attr
,max(if(t1.tag_name='fin_chp_focus_lvl' ,cnt,null)) as fin_chp_focus_lvl
,max(if(t1.tag_name='fin_is_chp_fin_user' ,cnt,null)) as fin_is_chp_fin_user
,max(if(t1.tag_name='fin_is_chp_lotto_user' ,cnt,null)) as fin_is_chp_lotto_user
,max(if(t1.tag_name='fin_chp_fin_tm_1st' ,cnt,null)) as fin_chp_fin_tm_1st
,max(if(t3.tag_name='fin_chp_fin_days_1st' ,cnt,null)) as fin_chp_fin_days_1st
,max(if(t3.tag_name='fin_chp_fin_1st_drtn' ,cnt,null)) as fin_chp_fin_1st_drtn
,max(if(t3.tag_name='fin_chp_first_gz_type' ,cnt,null)) as fin_chp_first_gz_type
,max(if(t3.tag_name='fin_chp_lifecycle' ,cnt,null)) as fin_chp_lifecycle
,max(if(t3.tag_name='fin_chp_actv_lvl' ,cnt,null)) as fin_chp_actv_lvl
,max(if(t3.tag_name='fin_chp_value_lvl' ,cnt,null)) as fin_chp_value_lvl
,max(if(t3.tag_name='fin_chp_tml_love' ,cnt,null)) as fin_chp_tml_love
,max(if(t3.tag_name='fin_chp_vst_pg_lvl' ,cnt,null)) as fin_chp_vst_pg_lvl
,max(if(t3.tag_name='fin_chp_vst_cnt_lvl' ,cnt,null)) as fin_chp_vst_cnt_lvl
,max(if(t3.tag_name='fin_chp_vst_bounce_lvl' ,cnt,null)) as fin_chp_vst_bounce_lvl
,max(if(t3.tag_name='fin_chp_vst_staytm_lvl' ,cnt,null)) as fin_chp_vst_staytm_lvl
,max(if(t3.tag_name='fin_chp_pg_staytm_lvl' ,cnt,null)) as fin_chp_pg_staytm_lvl
,max(if(t3.tag_name='fin_chp_is_click_farm' ,cnt,null)) as fin_chp_is_click_farm
,max(if(t3.tag_name='fin_chp_lucky_draw_amt' ,cnt,null)) as fin_chp_lucky_draw_amt
,max(if(t3.tag_name='fin_chp_last_buy_cyc' ,cnt,null)) as fin_chp_last_buy_cyc
,max(if(t3.tag_name='fin_chp_buy_gz_types' ,cnt,null)) as fin_chp_buy_gz_types
,max(if(t3.tag_name='fin_chp_buy_cnt' ,cnt,null)) as fin_chp_buy_cnt
,max(if(t3.tag_name='fin_chp_reward_type_amt' ,cnt,null)) as fin_chp_reward_type_amt
,max(if(t3.tag_name='fin_chp_income_type_amt' ,cnt,null)) as fin_chp_income_type_amt
,max(if(t3.tag_name='fin_chp_pv_cyc' ,cnt,null)) as fin_chp_pv_cyc
,max(if(t3.tag_name='fin_chp_pv_types' ,cnt,null)) as fin_chp_pv_types
,max(if(t3.tag_name='fin_chp_pv_cnt' ,cnt,null)) as fin_chp_pv_cnt
,max(if(t3.tag_name='fin_chp_7days_nobuys' ,cnt,null)) as fin_chp_7days_nobuys
,max(if(t3.tag_name='fin_chp_15days_nobuys' ,cnt,null)) as fin_chp_15days_nobuys
,max(if(t3.tag_name='fin_chp_is_1yuan' ,cnt,null)) as fin_chp_is_1yuan
,max(if(t3.tag_name='fin_chp_is_use_coupon' ,cnt,null)) as fin_chp_is_use_coupon
,max(if(t3.tag_name='fin_chp_coupon_use_cat' ,cnt,null)) as fin_chp_coupon_use_cat
,max(if(t3.tag_name='fin_chp_coupon_use_cyc' ,cnt,null)) as fin_chp_coupon_use_cyc
,max(if(t3.tag_name='fin_chp_coupon_use_cnt' ,cnt,null)) as fin_chp_coupon_use_cnt
,max(if(t3.tag_name='fin_chp_coupon_use_amt' ,cnt,null)) as fin_chp_coupon_use_amt
,max(if(t3.tag_name='fin_chp_coupon_ord_amt' ,cnt,null)) as fin_chp_coupon_ord_amt
,max(if(t3.tag_name='fin_chp_buy_cat' ,cnt,null)) as fin_chp_buy_cat
,max(if(t3.tag_name='fin_chp_buy_dept' ,cnt,null)) as fin_chp_buy_dept
,max(if(t3.tag_name='fin_cfc_rgst_dt' ,cnt,null)) as fin_cfc_rgst_dt
,max(if(t1.tag_name='fin_cfc_rgst_days' ,cnt,null)) as fin_cfc_rgst_days
,max(if(t1.tag_name='fin_cfc_rgst_drtn' ,cnt,null)) as fin_cfc_rgst_drtn
,max(if(t4.tag_name='fin_cfc_open_dt' ,cnt,null)) as fin_cfc_open_dt
,max(if(t4.tag_name='fin_cfc_open_drtn' ,cnt,null)) as fin_cfc_open_drtn
,max(if(t4.tag_name='fin_cfc_fin_dt_1st' ,cnt,null)) as fin_cfc_fin_dt_1st
,max(if(t4.tag_name='fin_cfc_fin_1st_drtn' ,cnt,null)) as fin_cfc_fin_1st_drtn
,max(if(t4.tag_name='fin_cfc_user_src' ,cnt,null)) as fin_cfc_user_src
,max(if(t4.tag_name='fin_cfc_amt' ,cnt,null)) as fin_cfc_amt
,max(if(t4.tag_name='fin_cfc_bal' ,cnt,null)) as fin_cfc_bal
,max(if(t4.tag_name='fin_is_cfc_stages' ,cnt,null)) as fin_is_cfc_stages
,max(if(t4.tag_name='fin_is_cfc_flex_use' ,cnt,null)) as fin_is_cfc_flex_use
,max(if(t4.tag_name='fin_is_cfc_overdue' ,cnt,null)) as fin_is_cfc_overdue
,max(if(t4.tag_name='fin_cfc_overdue_days' ,cnt,null)) as fin_cfc_overdue_days
,max(if(t4.tag_name='fin_cfc_tml_love' ,cnt,null)) as fin_cfc_tml_love
,max(if(t4.tag_name='fin_is_nonfnc_mem' ,cnt,null)) as fin_is_nonfnc_mem
,max(if(t4.tag_name='fin_pupsec_status' ,cnt,null)) as fin_pupsec_status
,max(if(t4.tag_name='fin_chnel_lvl' ,cnt,null)) as fin_chnel_lvl
,max(if(t4.tag_name='fin_chnel_cnt' ,cnt,null)) as fin_chnel_cnt
,max(if(t4.tag_name='fin_mbl_auth' ,cnt,null)) as fin_mbl_auth
,max(if(t4.tag_name='fin_actv_type' ,cnt,null)) as fin_actv_type
,max(if(t4.tag_name='fin_app_hold' ,cnt,null)) as fin_app_hold
,max(if(t4.tag_name='fin_cfc_black_list' ,cnt,null)) as fin_cfc_black_list
,max(if(t4.tag_name='fin_malicious_rgst' ,cnt,null)) as fin_malicious_rgst
,max(if(t4.tag_name='fin_is_scalper' ,cnt,null)) as fin_is_scalper
,max(if(t4.tag_name='fin_fin_black_list' ,cnt,null)) as fin_fin_black_list
from dw.dw_member_base_info_d t1
left join(select * from dw.dw_member_action_attr_d where data_date='${hivevar:statis_date}' and business_cd ='jt') t2
on t1.user_id=t2.user_id
left join(select * from dw.dw_member_action_attr_d where data_date='${hivevar:statis_date}' and business_cd ='jt') t3
on t1.user_id=t3.user_id
left join(select * from dw.dw_member_action_attr_d where data_date='${hivevar:statis_date}' and business_cd ='jt') t4
on t1.user_id=t4.user_id
where t1.data_date='${hivevar:statis_date}' and t1.business_cd ='jt'
group by t1.user_id
;涉及到的表梳理
个人会员标签体系
