2022-10-08

select a.*,b.收入金额,b.外部收入业务条线
from (
        SELECT
        t2.dt AS "月份",
        t2.b2_type AS "是否2B",
        t2.prod_lv1 AS "一级产品",
        t2.prod_lv2 AS "二级产品",
        t2.prod_lv3 AS "三级产品",
        t2.is_self AS "自营外单",
        t2.product_value AS "产品标",
        t2.waybill_code ,
        company_code AS "客户编码",
        customer_name AS "客户名称",
        CASE
        WHEN t2.company_type = '商家客户'
        THEN '商家'
        WHEN t2.company_type = '商业客户'
        THEN '商业'
        ELSE '自营'
        END "商家商业类型",
        CASE
        WHEN t2.vender_type_sec = '集团KA'
        THEN '集团KA'
        WHEN t2.vender_type_sec LIKE '__KA'
        THEN '区域KA'
        ELSE '非KA'
        END "KA7类型",
        IF(COALESCE(region_name7, '') = '', '集团', region_name7) AS '区域',
        - SUM(cost_new) AS '成本'
        FROM
        (
        SELECT
        t1.*,
        cost_total AS cost_new
        FROM
        (
            SELECT
            a.waybill_code,
            COALESCE(a.is_self, '外单') AS is_self,
            a.sendpay,
            a.prod_lv1,
            a.prod_lv2,
            a.prod_lv3,
            a.cost_total,
            a.product_value,
            a.region_name7,
            a.dt,
            CASE
            WHEN store_type = '纯配'
            AND b2_type <> 1
            AND SUBSTR(sendpay, 17, 1) = 1
            THEN '2B'
            ELSE IF(b2_type = 1, '2B', '2C')
            END AS b2_type,
            b.customer_id AS company_code,
            b.vender_type_sec,
            b.trader_mold AS company_type,
            b.customer_name
            FROM
            (
            SELECT
            t.*,
            COALESCE(waybill.b2_type, '未知') AS b2_type,
            waybill.sendpay
            FROM
            (
                SELECT * FROM app.app_ea_las_cost_detail_all WHERE dt >= '2022-08-01'
            )
            t
            LEFT JOIN
            (
                SELECT
                *
                FROM
                app.app_ea_las_waybill_info
                WHERE
                (
                dt = '2021-08-01'
                AND dt <= '2022-08-01'
                OR dt = '4712-12-01'
                )
                AND COALESCE(waybill_send_date, '') <> ''
            )
            waybill
            ON
            t.waybill_code = waybill.waybill_code
            )
            a
            LEFT JOIN app_ea_dim_vender_info_map b
            ON
            a.dt = b.dt
            AND b.customer_id = a.company_code
        )
        t1
        WHERE
        is_self = '外单'
        AND b2_type = '2C'
        AND prod_lv2 = '快递'
        AND company_code <> 'EBU4418046515507'
        AND SUBSTR(sendpay, 64, 1) <> '1')
        t2
        GROUP BY
        t2.dt,
        t2.b2_type,
        t2.prod_lv1,
        t2.prod_lv2,
        t2.prod_lv3,
        t2.is_self,
        t2.product_value,
        t2.waybill_code,
        company_code,
        customer_name,
        CASE
        WHEN t2.company_type = '商家客户'
        THEN '商家'
        WHEN t2.company_type = '商业客户'
        THEN '商业'
        ELSE '自营'
        END,
        CASE
        WHEN t2.vender_type_sec = '集团KA'
        THEN '集团KA'
        WHEN t2.vender_type_sec LIKE '__KA'
        THEN '区域KA'
        ELSE '非KA'
        END,
        IF(COALESCE(region_name7, '') = '', '集团', region_name7)
) a
left join (
        SELECT
        shipping_bill_id,
        SUM(total_amount_no_tax) AS "收入金额",
        egr_business_type AS "外部收入业务条线"
        
        FROM
        app.app_ea_egr_all_m
        WHERE
        dt = '2022-09-01'
        AND
        (
        (
        egr_business_type LIKE '%快运%'
        AND operation_type NOT LIKE '%仓配%'
        AND cold_chain_flag NOT LIKE '%冷链%'
        AND cold_chain_flag NOT LIKE '%医药%'
        )
        OR egr_business_type LIKE '%大件%'
        )
        group by shipping_bill_id,egr_business_type


)b
on b.shipping_bill_id = a.waybill_code