因为接到这个需求的时候，一开始想写Sql语句直接实现，发现比较麻烦；后来想通过Mysql存储过程来处理，但以前对Oracle存储过程熟悉，MySql存储过程代码不熟悉，要使用起来有一定的学习成本；最后选择用Python控制实现。谨此以记，供以后类似需要做参考。
需求描述：
一般的统计对格式如：”dim1,dim2,index1,index2,date“，如果维度对应的指标不存在，则结果表中就没有这个记录。现在要求，指标为空的记录给补上，且将其指标置为0。
实现代码:
/Users/nisj/PycharmProjects/EsDataProc/result_null_proc.py
# -*- coding=utf-8 -*-import osimport re
def resultNullProc(): result_data = os.popen("""mysql -hMysqlHost -P6603 -uHadoop -pMysqlPass -N -e "use funnyai_data; \ select appkey,app_source,week_count \ from x_chushou_user_compare_week_stat \ where app_source <>'' \ group by appkey,app_source,week_count; \ " """ ).readlines();
nrpd_list = [] for nrp_list in result_data: nrp = re.split('\t', nrp_list.replace('\n', '')) nrpd_list.append(nrp) for nrpd in nrpd_list: appkey = nrpd[0] appsource = nrpd[1] week_count = nrpd[2]
result_data1 = os.popen("""mysql -hMysqlHost -P6603 -uhadoop -pMysqlPass -N -e "use funnyai_data; \ select pm,appkey,app_source,week_count,compare_week_count from \ (SELECT (SELECT COUNT(1) + 1 \ FROM (select distinct remain_week from bi_user_remain_pay_byweek ) A \ WHERE concat(substr(A.remain_week,1,4),lpad(substr(A.remain_week,6),2,0)) < concat(substr(B.remain_week,1,4),lpad(substr(B.remain_week,6),2,0))) pm \ FROM (select distinct remain_week from bi_user_remain_pay_byweek ) B \ ORDER BY PM) a1 \ left join ( \ select appkey,app_source,week_count,compare_week_count \ from x_chushou_user_compare_week_stat \ where appkey='%s' and app_source='%s' and week_count='%s' \ ) a2 on a1.pm=a2.compare_week_count \ where a1.pm>'%s' and a2.appkey is null \ order by a1.pm;" """ % (appkey, appsource, week_count, week_count)).readlines(); nrpd_list = [] for nrp_list in result_data1: nrp = re.split('\t', nrp_list.replace('\n', '')) nrpd_list.append(nrp) for nrpd in nrpd_list: pm = nrpd[0] # print pm,appkey,appsource,week_count os.system("""mysql -hMysqlHost -P6603 -uhadoop -pMysqlPass -N -e "use funnyai_data; \ insert into x_chushou_user_compare_week_stat(appkey,app_source,week_count,compare_week_count,compare_identify_count,compare_register_user_count,compare_user_count,compare_pay_amount,compare_pay_user_count) \ select '%s','%s','%s','%s','%d','%d','%d','%d','%d'; \ " """ % (appkey, appsource, week_count, pm, 0, 0, 0, 0, 0))
resultNullProc()
本文永久更新链接地址：http://www.xuebuyuan.com/Linux/2017-01/139751.htm
以上就上有关用Python将统计数据不存在的记录按维度对应指标补齐的全部内容，学步园全面介绍编程技术、操作系统、数据库、web前端技术等内容。