dwd_user_login_phone_mode.py 2.82 KB
# -*- coding: utf-8 -*-


'''
用户登录手机型号,定时任务,每2个小时运行一次
'''
import logging
import os
import sys

from common.mysql_uitl import fetch_all, save_result, save_etl_log
from common.time_util import now_str, YMDHMS_FORMAT

logging.basicConfig(format="%(asctime)s %(name)s:%(levelname)s:%(message)s", datefmt="%Y-%m-%d %H:%M:%S", level=logging.INFO)
# file_path = get_file_path()
# file_name = get_file_name()
file_name = sys.argv[0]
task_file = os.path.split(__file__)[-1].split(".")[0]


def dwd_user_login_phone_mode(data_dt):
    start_time = str(data_dt) + ' 00:00:00'
    end_time = str(data_dt) + ' 23:59:59'
    login_phone_dict = query_user_login_phone_mode(start_time, end_time)
    row = save_result('tamp_data_dwd', 'dwd_user_login_phone_mode', login_phone_dict, file_name)
    now_time = now_str(YMDHMS_FORMAT)
    save_etl_log('tamp_data_dwd', 'dwd_user_login_phone_mode', data_dt, row, 'done', task_file, now_time)


def query_user_login_phone_mode(start_time, end_time):
    logging.info(f'query_user_login_phone_mode start')
    sql = '''
            select   p.data_dt
                    ,p.user_id
                    ,coalesce(t.real_name, '') as real_name
                    ,coalesce(t.user_name, '') as user_name
                    ,coalesce(t.nickname, '') as nickname
                    ,t.team_id
                    ,p.phone_mode
                    ,coalesce(q.phone_name, '未知') as phone_name
                    ,coalesce(q.phone_brand, '未知') as phone_brand
                    ,p.start_time
                    ,p.end_time
            from     
            (
            select  date_format(server_time, '%%Y-%%m-%%d') as data_dt
                    ,uid as user_id
                    ,trim(md) as phone_mode
                    ,min(local_time) as start_time
                    ,max(local_time) as end_time 
            from    tamp_analysis.access_log
            where   server_time between %s and %s
            and     md <> ''
            and     uid <> ''
            and     md is not null 
            and     uid is not null
            and     local_time is not null
            group by  data_dt, uid, trim(md)
            ) p
            left    join  tamp_analysis.user_info_view t
            on      p.user_id = t.user_id
            left    join  tamp_data_dwd.dwd_phone_mode q
            on      p.phone_mode = q.phone_mode
    '''
    login_phone_dict = fetch_all(sql, (start_time, end_time))
    logging.info(f'query_user_login_phone success')
    return login_phone_dict


if __name__ == '__main__':
    import datetime
    begin = datetime.date(2021, 9, 22)
    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"))
        dwd_user_login_phone_mode(data_dt)
        data_dt += delta