sql优化实战 把full join改为left join +union all(从5分钟降为10秒

select isnull (vv.customer_id,v.customer_id) as customer_id,

         isnull (vv.business_date, replace (v.business_date, '-' , '' )) as business_date,

         v.prod_id,

         v.sales,

         vv.visit_count,

     v.all_sales

from

(

   SELECT a.customer_id ,

         max ( month )+ '-01' as business_date,

       a.PROD_ID ,

       SUM ( CAST (VALUE AS NUMERIC (38, 3))) sales,

       sum ( SUM ( CAST (VALUE AS NUMERIC (38, 3)))) over(partition by a.customer_id) as all_sales

   FROM   TB_IMPORT_SALES a

   WHERE   a.customer_id IS NOT NULL

       AND a.PROD_ID IS NOT NULL

             and a. month = '2016-11'

   GROUP BY a.customer_id ,

       a.PROD_ID

)v

full join

(

   SELECT customer_id,

         max (a.business_date) as business_date,

       COUNT (*) AS VISIT_COUNT

     FROM TB_CALL_STORE a WITH (NOLOCK)

     inner join TB_TIME d

     on a.business_date = d.t_date

     where d. section = '2016-11'

     GROUP BY customer_id

)vv

on v.customer_id = vv.customer_id

select v.customer_id,

         replace ( max (v.business_date), '-' , '' ) as business_date,

         v.prod_id,

         max (v.sales_volume) sales_volume ,

         max (v.visit_count) visit_count,

         max (v.all_sales_volume) all_sales_volume

from

(

   SELECT a.customer_id ,

         max (biz_month)+ '-01' as business_date,

       a.PROD_ID ,

       SUM ( CAST (VALUE1 AS NUMERIC (38, 8))) sales_volume,

       sum ( SUM ( CAST (VALUE1 AS NUMERIC (38, 8)))) over(partition by a.customer_id) as all_sales_volume,

             null as visit_count

   FROM   TB_IMPORT_SALES a

   WHERE   a.customer_id IS NOT NULL

       AND a.PROD_ID IS NOT NULL

             and a. month = '2016-11'

   GROUP BY a.customer_id ,

        a.PROD_ID

   union all

   SELECT customer_id,

         max (a.business_date) as business_date,

           p.prod_id,

           null ,

           null ,

       COUNT (*) AS VISIT_COUNT

     FROM TB_CALL_STORE a WITH (NOLOCK)

     cross apply

     (

         select top 1 prod_id from TB_PRODUCT with (nolock)

     )p

     inner join TB_TIME d

     on a.business_date = d.t_date

     where d. section = '2016-11'

     GROUP BY customer_id,p.prod_id

)v

group by v.customer_id,

      v.prod_id