elab_mvp/sql.py

class Sql:
    # 获取项目的城市信息
    sql_1 = """
        select brand_id, house_id, house_name, city from d_house a where status = 1
    """

    # 根据任务id获取推送客户信息
    sql_2 = """
        select a.task_key, a.customer_id, b.customer_type, b.name, b.mail, GROUP_CONCAT(c.house_or_brand_id) as ids
        from report_task_info a left join report_push_customer_info b on b.id = a.customer_id
        left join report_customer_authority_info c on b.id = c.customer_id
        where a.task_key = %s and a.status = b.status = c.status = 1
        group by a.task_key, a.customer_id, b.customer_type, b.name, b.mail
    """

    # 1:总浏览量
    #
    #   集团部分+项目部分!!!

    #  ----项目PV--权限项目范围内，求和
    # 参数：时间区间 和 项目列表
    sql_3 = """
            SELECT SUM(pv) AS pv1 FROM a_idfa_behavior_sum
            WHERE report_d >= %s and report_d <= %s AND house_id IN %s
    """

    # ----集团PV--权限项目范围内，求和
    # 参数：数据区间和项目列表
    sql_4 = """
        SELECT SUM(pv) AS pav2 FROM a_behavior_brand_mini_day
        WHERE report_d >= %s and report_d <= %s AND house_id IN %s
    """

    # 2:总浏览人数
    #
    # 参数，数据区间， 项目列表
    sql_5 = """
            SELECT
                count(
                    DISTINCT IFNULL(mobile, user_id)
                ) as people
            FROM
                (
                    SELECT
                        A.user_id,
                        B.mobile
                    FROM
                        a_idfa_behavior_sum A
                    LEFT JOIN d_user B ON A.user_id = B.user_id
                    WHERE
                        A.report_d >= %s and A.report_d <= %s
                    AND A.house_id IN %s
                    UNION
                        SELECT
                            A.brand_user_id AS user_id,
                            B.mobile
                        FROM
                            a_behavior_brand_mini_day A
                        LEFT JOIN a_brand_app_customer B ON A.brand_user_id = B.brand_customer_id
                        WHERE
                            A.report_d >= %s and A.report_d <= %s
                        AND A.house_id IN %s
                ) t1
    """

    # 3：新增获客
    sql_6 = """
                SELECT
                    count(
                        DISTINCT IFNULL(mobile, user_id)
                    )
                FROM
                    (
                        SELECT
                            user_id,
                            mobile,
                            created
                        FROM
                            d_user
                        WHERE
                            created >= %s
                        AND created <= %s
                        AND house_id IN %s
                        AND source ='3'
                        UNION
                            SELECT
                                brand_customer_id AS user_id,
                                mobile,
                                rlat_created
                            FROM
                                a_brand_app_customer_house_rlat
                            WHERE
                                rlat_created >= %s
                            AND rlat_created <= %s
                            AND rlat_house_id IN %s
                            AND cust_house_flag ='1'
                    ) t1
        """

    # 4:新增获电
    sql_7 = """
            SELECT
                COUNT(DISTINCT mobile)
            FROM
                (
                    SELECT
                        user_id,
                        mobile,
                        wx_phone_time AS created
                    FROM
                        d_user
                    WHERE
                        wx_phone_time >= %s
                    AND wx_phone_time <= %s
                    AND house_id IN %s
                    and source = '3'
                    UNION
                        SELECT
                            brand_customer_id,
                            mobile,
                            houdian_time AS created
                        FROM
                            (
                                SELECT
                                    *,
                                    CASE
                                WHEN rlat_created > shouquan_time THEN
                                    rlat_created
                                ELSE
                                    shouquan_time
                                END AS houdian_time
                                FROM
                                    a_brand_app_customer_house_rlat
                                WHERE
                                    mobile IS NOT NULL
                                AND rlat_house_id IN %s
                                AND cust_house_flag = '1'
                            ) t1
                        WHERE
                            houdian_time >= %s
                        AND houdian_time <= %s
                ) t1
        """

    # hosue_id, type, count
    sql_8 = """
    select house_id, type, COUNT(DISTINCT customer_mobile) from f_dm_jianye_allagent_day where report_d >= %s and report_d <= %s group by house_id, type
    """

    sql_9 = """
        SELECT house_id, visit_new_uv_dtd FROM a_brand_customer_share_dtd where brand_id ='13' and report_d >= %s and report_d <= %s  order by report_d
    """
    sql_10 = """
    select type, COUNT(DISTINCT customer_mobile) from f_dm_jianye_allagent_day where report_d >= %s and report_d <= group by type
    """
    sql_11 = """"
    select a.house_or_region, min(b.house_or_brand_id), sum(a.name) from report_push_customer_info a left join report_customer_authority_info b on a.id = b.customer_id group by house_or_region
    """
    pass