用Python将统计数据不存在的记录按维度对应指标补齐

因为接到这个需求的时候，一开始想写Sql语句直接实现，发现比较麻烦；后来想通过Mysql存储过程来处理，但以前对Oracle存储过程熟悉，MySql存储过程代码不熟悉，要使用起来有一定的学习成本；最后选择用Python控制实现。谨此以记，供以后类似需要做参考。需求描述：一般的统计对格式如：”dim1,dim2,index1,index2,date“，如果维度对应的指标不存在，则结果表中就没有这个记录。现在要求，指标为空的记录给补上，且将其指标置为0。实现代码:/Users/nisj/PycharmProjects/EsDataProc/result_null_proc.py# -*- coding=utf-8 -*-
import os
import redef 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（" ", nrp_list.replace（" ", ""））
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（" ", nrp_list.replace（" ", ""））
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.linuxidc.com/Linux/2017-01/139751.htm