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

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

# -*- coding=utf-8 -*-import osimport 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('\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()