# -*- coding: utf-8 -*- import datetime import logging from phone import Phone from common.file_uitil import get_file_name, get_file_path from common.mysql_uitl import fetch_all, insert, insert_batch ''' 用户观看直播数据 ''' 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() # def ods_user_watch_live(): # pass def ods_user_watch_live(data_dt): logging.info(f"ods_user_watch_live start") start_time = str(data_dt) + ' 00:00:00' end_time = str(data_dt) + ' 23:59:59' del_live_data(data_dt) get_live_data(start_time, end_time) # 先删除当天的数据,在插入新的数据 def del_live_data(data_dt): sql = 'delete from tamp_data_ods.ods_user_watch_live where data_dt = %s' fetch_all(sql, data_dt) def get_live_data(start_time, end_time): sql = ''' insert into tamp_data_ods.ods_user_watch_live ( data_dt ,user_id ,province ,city ,res_id ,dur ,start_time ,end_time ,event_type ,is_share ,env ) select date_format(server_time, '%%Y-%%m-%%d') as data_dt ,uid as user_id ,province ,city ,res_id ,dur ,start_time ,end_time ,event_type ,is_share ,env from tamp_analysis.access_log where server_time between %s and %s and event_type in ('1014', '1015') and res_id <> '' and res_id is not null and uid <> '' and uid is not null ''' fetch_all(sql, (start_time, end_time)) if __name__ == '__main__': # ods_user_watch_live() # each_platform_user_active() import datetime begin = datetime.date(2021, 8, 26) end = datetime.date(2021, 8, 26) data_dt = begin delta = datetime.timedelta(days=1) while data_dt <= end: print(data_dt.strftime("%Y-%m-%d")) ods_user_watch_live(data_dt) data_dt += delta