on cust.customer_no=b.customer_no left outer join cust_worth_value_table c on cust.customer_no=c.customer_no left outer join hold_risk_level_table d on cust.customer_no=d.customer_no left outer join s_l_a_table e on cust.customer_no=e.customer_no left outer join industry_prefer_table f on cust.customer_no=f.customer_no ''') # 标签缺失补全 res_all = res_all.na.fill({'cycle_stable_level': u'炒股模式平稳度未知', 'contri_level': u'缴费贡献度未知', 'contri_stab_level': u'缴费稳定度未知', 'up_level': u'缴费上升占比未知', 'hold_risk_level': u'平仓风险未知', 'deal_model': u'炒股模式未知', 'control_loss_level': u'止损能力未知'}) res_all.registerTempTable("res_all") # 写入hive物理表 res_all.write.mode('overwrite').saveAsTable('tag_stat.invest_model') hqlContext.uncacheTable('stock_hold_date_table') hqlContext.uncacheTable('stock_prefer_detail_table') sc.stop()
when d.stock_concentrate=0.27 then '持股集中度中' else '持股集中度低' end) as stock_concentrate_level from (select distinct customer_no from ''' + db_name + '''.dim_customer) cust left outer join stock_hold_ratio_table a on cust.customer_no = a.customer_no left outer join industry_prefer_topN_table b on cust.customer_no = b.customer_no left outer join open_years_table c on cust.customer_no = c.customer_no left outer join stock_concentrate_table d on cust.customer_no = d.customer_no ''') # 标签缺失补全 res_all = res_all.na.fill({ 'stock_hold_ratio_level': u'持仓水平未知', 'stock_industry_n': u'行业偏好未知', 'open_years_level': u'开户年限未知', 'stock_concentrate_level': u'持股集中度未知' }) # res_all.registerTempTable("res_all") # 写入hive物理表 res_all.write.mode('overwrite').saveAsTable('tag_stat.invest_basic') hqlContext.uncacheTable('industry_prefer_detail_table') sc.stop()
from (select distinct customer_no from ''' + db_name[0] + '''.dim_customer) cust left outer join fact_temp a on cust.customer_no = a.customer_no left outer join model_temp b on cust.customer_no = b.customer_no ''') # 标签缺失补全 res_all = res_all.na.fill({ 'total_profit_level': u'盈利未知', 'cust_devo_level': u'客户贡献未知', 'close_rate_level': u'维保比例未知', 'crdt_close_balance_level': u'保证金可用余额未知', 'stock_corr': u'指数相似程度未知', 'margin_corr': u'市场敏感度未知', 'activity': u'活跃度未知' }) res_all.registerTempTable("res_all") hqlContext.sql('''drop table if exists ''' + db_name[1] + '''.margin_trading''') hqlContext.sql(''' create table ''' + db_name[1] + '''.margin_trading as select * from res_all ''') hqlContext.uncacheTable('cust_hy_days_table') sc.stop()