elab_mvp/report_push.py

class ReportPush(object):
    """
        报表推送功能实现类
    """
    pass

    # 数据一，周数据概览
    sql_1 = """
        待定
    """

    #  1.默认值/001_大麦/项目排行榜/小程序排行榜TOP_N
    sql_2_1 = """
        SELECT
            a.*, 
            b.house_name,
            c.interested_num,
            d.wx_num,
            e.new_cust_num
        FROM
            (
                SELECT
                    house_id,
                    count(
                        DISTINCT ifnull(user_id, idfa)
                    ) uv,
                    sum(session_times) session_times,
                    sum(sum_session_time) sum_session_time,
                    sum(pv) pv,
                    sum(page_num) page_num
                FROM
                    a_idfa_behavior_sum
                WHERE
                    report_d >= '2020-03-01'
                AND report_d < '2020-03-05'
                GROUP BY
                    house_id
            ) a
        JOIN d_house b ON a.house_id = b.house_id
        LEFT JOIN (
            SELECT
                house_id,
                count(*) interested_num
            FROM
                f_interested_custlist
            WHERE
                report_d >= '2020-03-01'
            AND report_d <'2020-03-05'
            GROUP BY
                house_id
        ) c ON a.house_id = c.house_id
        LEFT JOIN (
            SELECT
                house_id,
                count(*) wx_num
            FROM
                f_customer_dynamic
            WHERE
                dynamic = 1
            AND report_d >= '2020-03-01'
            AND report_d <= '2020-03-05'
            GROUP BY
                house_id
        ) d ON a.house_id = d.house_id
        LEFT JOIN (
            SELECT
                house_id,
                count(*) new_cust_num
            FROM
                d_user
            WHERE
                created >= '2020-03-01'
            AND created < '2020-03-05' # 时间需要加一天！！！！
            GROUP BY
                house_id
        ) e ON a.house_id = e.house_id
    """

    # 2.默认值/006_大麦（集团）/集团项目排行榜v1.3/集团排行榜
    sql_2_2 = """
    待定
    """

    # 默认值/001_大麦/场景_用户来源渠道/用户来源渠道—明细
    sql_3_1 = """
            SELECT
                *
            FROM
                d_user_attr a
            LEFT JOIN d_scene b ON a.scene = b. CODE
            WHERE
                a.source IN (1, 2, 3, 4, 10)
            AND a.report_d >= '2020-03-01'
            AND a.report_d <= '2020-03-04'
    """

    # 默认值/006_大麦（集团）/场景(集团)_用户来源渠道_v1.1/用户来源渠道—明细
    sql_3_2 = """
            SELECT
                a.scene,
                a.brand_id,
                b.*, a.share_brand_customer_id,
                '2' adviser_agent,
                a.house_id house_id,
                c.house_name house_name,
                c.brand_name
            FROM
                (
                    SELECT
                        scene,
                        brand_id,
                        share_brand_customer_id,
                        house_id
                    FROM
                        d_brand_app_customer
                    WHERE
                        created >='2020-03-01'
                    AND created < DATE_ADD(
                        '2020-03-04', INTERVAL 1 DAY
                    )
                    UNION ALL
                        SELECT
                            scene,
                            brand_id,
                            share_brand_customer_id,
                            brand_id house_id
                        FROM
                            d_brand_app_customer
                        WHERE
                            created >= '2020-03-01'
                        AND created < DATE_ADD(
                            '2020-03-04', INTERVAL 1 DAY
                        )
                ) a
            LEFT JOIN d_scene b ON a.scene = b. CODE
            JOIN d_house c ON a.house_id = c.house_id
            AND a.brand_id = c.brand_id
    """