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()
