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