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