select 2 `NO`,

   year(t1.complaintDate),

    sum(t1.数量)/sum(t2.总数量)*1000000 as 'PPM',

     sum(t1.数量),sum(t2.总数量),

     t1.日期维度,t1.base2

from

    (

select sum(countInPpmQty) as '数量',

     base2,

     case

        when '${baseway}' = '年' then  date_format(complaintDate, '%Y')

        when '${baseway}' = '季度' then concat(quarter(complaintDate),'季度')

        when '${baseway}' = '月' then concat(date_format(complaintDate, '%m'),'月')

        when '${baseway}' = '周' then concat(year(complaintDate),'年',week(complaintDate),'周')

        when '${baseway}' = '天' then  complaintDate 

        end as '日期维度'

from

   ( select *,  case when modulesite like '%上海%' then 'SH'

         when modulesite like '%深圳%' then 'SZ'  end as base2

         from ads_zxyclzl_kpi02_total_ppm_summary)ttt

    where complaintDate>='${startdate}'

    and complaintDate<='${enddate}'

group by 2,3,4

            )t1

left join 

(

    select

        sum(quantity) as 总数量,

        moduleorigin,

        case

        when '${baseway}' = '年' then  date_format(concat(shipment_date,'-01'), '%Y')

        when '${baseway}' = '季度' then concat(quarter(concat(shipment_date,'-01')),'季度')

        when '${baseway}' = '月' then concat(date_format(concat(shipment_date,'-01'), '%m'),'月')

        when '${baseway}' = '周' then concat(year(concat(shipment_date,'-01')),'年',week(concat(shipment_date,'-01')),'周')

        when '${baseway}' = '天' then  shipment_date 

        end as '日期维度'

    from

        ods_zxyclzl_totalshipments_fr

    where

        1 = 1

        and left(shipment_date,7)>= left('${startdate}',7)

            and left(shipment_date,7)<= left('${enddate}',7)

            group by 2,3,4

            )t2

                on t1.日期维度=t2.日期维度 

                and t1.base2=t2.moduleorigin