# -*- coding: utf-8 -*- ''' app安装量,定时任务,每天运行一次 ''' import json import logging import os import sys import datetime import requests from common.mysql_uitl import save_result, fetch_all, save_etl_log from common.time_util import YMDHMS_FORMAT, now_str logging.basicConfig(format="%(asctime)s %(name)s:%(levelname)s:%(message)s", datefmt="%Y-%m-%d %H:%M:%S", level=logging.INFO) file_name = sys.argv[0] task_file = os.path.split(__file__)[-1].split(".")[0] def ads_app_install(data_dt): install_num_dict = query_app_install(data_dt) row = save_result('tamp_data_ads', 'ads_app_install', install_num_dict, file_name) now_time = now_str(YMDHMS_FORMAT) save_etl_log('tamp_data_ads', 'ads_app_install', data_dt, row, 'done', task_file, now_time) def query_app_install(data_dt): data_dt = (datetime.datetime.strptime(str(data_dt), "%Y-%m-%d") - datetime.timedelta(days=1)).strftime("%Y-%m-%d") function_name = sys._getframe().f_code.co_name logging.info(f'{function_name} start') sql = ''' select p.data_dt ,case when weekday(p.data_dt) + 1 = '1' then '星期一' when weekday(p.data_dt) + 1 = '2' then '星期二' when weekday(p.data_dt) + 1 = '3' then '星期三' when weekday(p.data_dt) + 1 = '4' then '星期四' when weekday(p.data_dt) + 1 = '5' then '星期五' when weekday(p.data_dt) + 1 = '6' then '星期六' when weekday(p.data_dt) + 1 = '7' then '星期日' else '计算错误' end as weekday ,sum(p.pc_num) as pc_num ,sum(p.ios_num) as ios_num ,sum(p.android_num) as android_num ,sum(p.total_app_num) as total_app_num from ( select data_dt ,count(1) as pc_num ,0 as ios_num ,0 as android_num ,0 as total_app_num from tamp_data_dwd.dwd_user_login_pc_record where data_dt = %s and team_id <> '0' group by data_dt union all select data_dt ,0 as pc_num ,ios_num ,android_num ,total_app_num from tamp_data_dwd.dwd_app_install where data_dt = %s ) p group by p.data_dt, weekday ''' install_num_dict = fetch_all(sql, (data_dt, data_dt)) logging.info(f'{function_name} success') return install_num_dict if __name__ == '__main__': begin = datetime.date(2021, 9, 19) end = datetime.date(2021, 9, 22) data_dt = begin delta = datetime.timedelta(days=1) while data_dt <= end: print(data_dt.strftime("%Y-%m-%d")) ads_app_install(data_dt) data_dt += delta