ads_user_browse_fund.py

# -*- coding: utf-8 -*-
import logging
import sys

from common.mysql_uitl import fetch_all, save_result, insert_batch, insert

'''
用户浏览产品明细数据统计和汇总数据统计,定时任务，每2个小时运行一次
先上线，如果要用访问线索，需要调整这里的逻辑，包括(dws_user_share_event, dws_user_visitor_clues)
一张明细表和一张汇总表
p2107	产品详情（公募）
p2108	产品详情（理财师添加）
p2060	探普产品详情
'''
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]


def ads_user_browse_fund(data_dt):
    browse_fund_details_dict = query_user_browse_fund_details(data_dt)
    browse_fund_summary_dict = query_user_browse_fund_summary()
    save_result('tamp_data_ads', 'ads_user_browse_fund_details', browse_fund_details_dict, file_name)
    save_result('tamp_data_ads', 'ads_user_browse_fund_summary', browse_fund_summary_dict, file_name)


def query_user_browse_fund_details(data_dt):
    function_name = sys._getframe().f_code.co_name
    logging.info(f'{function_name} start')
    sql = '''
       select   p.data_dt
                ,p.user_id
                ,p.real_name 
                ,p.user_name
                ,p.nickname
                ,p.team_id
                ,p.level_grade
                ,p.res_id
                ,p.res_type
                ,p.res_name
                ,p.res_short_name
                ,p.browse_dur
                ,p.browse_num
                ,p.share_num
                ,coalesce(t.invite_num, 0) as invite_num
                ,p.share_num
                ,p.start_time
                ,p.end_time
        from    tamp_data_dws.dws_user_browse_fund p
        left    join    
        (
            select  data_dt
                    ,source_user_id
                    ,res_id
                    ,count(distinct user_id) as invite_num
            from    tamp_data_dwd.dwd_user_visit_clues
            where   data_dt = %s
            and     current_page in ('p2107', 'p2108', 'p2060')
            group   by data_dt,source_user_id,res_id
        ) t 
        on      p.user_id = t.source_user_id
        and     p.res_id = t.res_id 
        where   p.data_dt = %s
    '''
    browse_fund_details_dict = fetch_all(sql, (data_dt, data_dt))
    logging.info(f'{function_name} success')
    return browse_fund_details_dict


def query_user_browse_fund_summary():
    function_name = sys._getframe().f_code.co_name
    logging.info(f'{function_name} start')
    sql = '''
        select   p.data_dt
                ,p.user_id
                ,q.real_name
                ,q.user_name 
                ,q.nickname
                ,q.team_id
                ,q.level_grade
                ,p.res_id
                ,p.res_name
                ,p.res_short_name
                ,p.browse_dur
                ,p.browse_num
                ,coalesce(p.share_num, 0) as share_num
                ,coalesce(t.invite_num, 0) as invite_num
        from 
        (
            select   user_id
                    ,max(data_dt) as data_dt
                    ,res_id
                    ,res_name
                    ,res_short_name 
                    ,sum(browse_dur) as browse_dur
                    ,sum(browse_num) as browse_num
                    ,sum(share_num) as share_num
            from    tamp_data_dws.dws_user_browse_fund    
            group   by user_id,res_id,res_name,res_short_name
        ) p
        left    join    
        (
            select source_user_id
                    ,res_id
                    ,count(distinct user_id) as invite_num
            from    tamp_data_dwd.dwd_user_visit_clues
            where   current_page in ('p2107', 'p2108', 'p2060')
            group   by source_user_id,res_id
        ) t 
        on      p.user_id = t.source_user_id
        and     p.res_id = t.res_id 
        left    join tamp_analysis.user_info_view q 
        on      p.user_id = q.user_id
        order   by p.user_id, p.data_dt desc, p.res_id
    '''
    browse_fund_summary_dict = fetch_all(sql, None)
    logging.info(f'{function_name} success')
    return browse_fund_summary_dict


if __name__ == '__main__':
    import datetime
    begin = datetime.date(2021, 9, 14)
    end = datetime.date(2021, 9, 14)
    data_dt = begin
    delta = datetime.timedelta(days=1)
    while data_dt <= end:
        print(data_dt.strftime("%Y-%m-%d"))
        ads_user_browse_fund(data_dt)
        data_dt += delta