需求描述：

一般的统计对格式如：”dim1,dim2,index1,index2,date“，如果维度对应的指标不存在，则结果表中就没有这个记录。现在要求，指标为空的记录给补上，且将其指标置为0。

实现代码:

/Users/nisj/PycharmProjects/EsDataProc/result_null_proc.py

# -*- coding=utf-8 -*-
import os
import re

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