ads_app_install.py

# -*- 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