一次SQL优化

摘要：维护困难，如果发生表的变化，比如增加表的统计，或者去掉表的统计，需要对每一个表的统计字段做修改。尝试使用的方式该方法性能会受到影响。第一降低了复杂度，第二效率提高了。如果还要进一步优化性能，可以建立静态表，每天定时的统计到该表中。

有A、B、C、D、E...多张表，分别存放的是不同的数据，因业务需要，需要将各表数据统计后合并到一起
原sql是这样写的

select T.DATE
     , sum(A_NUM_1) A_NUM_1, sum(A_AMT_1) A_AMT_1
     , sum(A_NUM_2) A_NUM_2, sum(A_AMT_2) A_AMT_2
     , sum(A_NUM_3) A_NUM_3, sum(A_AMT_3) A_AMT_3
     , sum(A_NUM_4) A_NUM_4, sum(A_AMT_4) A_AMT_4
     
     , sum(B_NUM_1) B_NUM_1, sum(B_AMT_1) B_AMT_1
     , sum(B_NUM_2) B_NUM_2, sum(B_AMT_2) B_AMT_2

     , sum(C_NUM_1) C_NUM_1, sum(C_AMT_1) C_AMT_1
     
     , sum(D_NUM_1) D_NUM_1, sum(D_AMT_1) D_AMT_1

     , sum(E_NUM_1) E_NUM_1, sum(E_AMT_1) E_AMT_1
     , sum(E_NUM_2) E_NUM_2, sum(E_AMT_2) E_AMT_2
     , sum(E_NUM_3) E_NUM_3, sum(E_AMT_3) E_AMT_3
     , sum(E_NUM_4) E_NUM_4, sum(E_AMT_4) E_AMT_4
     
  from (
         select A.DATE
              , sum(case when OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "1" and OPTION_4 = "0" then 1 else 0 end) A_NUM_1
              , sum(case when OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "1" and OPTION_4 = "0" then AMT else 0 end) A_AMT_1

              , sum(case when OPTION_1 = "0" and OPTION_2 = "2" then 1 else 0 end) A_NUM_2
              , sum(case when OPTION_1 = "0" and OPTION_2 = "2" then AMT else 0 end) A_AMT_2

              , sum(case when OPTION_1 = "0" and OPTION_2 = "3" and OPTION_3 = "1" and OPTION_4 = "4" then 1 else 0 end) A_NUM_3
              , sum(case when OPTION_1 = "0" and OPTION_2 = "3" and OPTION_3 = "1" and OPTION_4 = "4" then AMT else 0 end) A_AMT_3

              , sum(case when OPTION_1 = "1" then 1 else 0 end) A_NUM_4
              , sum(case when OPTION_1 = "1" then AMT else 0 end) A_AMT_4

              , 0 B_NUM_1, 0 B_AMT_1
              , 0 B_NUM_2, 0 B_AMT_2

              , 0 C_NUM_1, 0 C_AMT_1

              , 0 D_NUM_1, 0 D_AMT_1

              , 0 E_NUM_1, 0 E_AMT_1
              , 0 E_NUM_2, 0 E_AMT_2
              , 0 E_NUM_3, 0 E_AMT_3
              , 0 E_NUM_4, 0 E_AMT_4
               
           from A
          group by A.DATE
          
          union all
         select B.DATE
              , 0 A_NUM_1, 0 A_AMT_1
              , 0 A_NUM_2, 0 A_AMT_2
              , 0 A_NUM_3, 0 A_AMT_3
              , 0 A_NUM_4, 0 A_AMT_4

              , sum(case when OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "2" and OPTION_4 = "3" then 1 else 0 end) B_NUM_1
              , sum(case when OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "2" and OPTION_4 = "3" then AMT else 0 end) B_AMT_1
              , sum(case when OPTION_1 = "1" then 1 else 0 end) B_NUM_2
              , sum(case when OPTION_1 = "1" then AMT else 0 end) B_AMT_2
              
              , 0 C_NUM_1, 0 C_AMT_1

              , 0 D_NUM_1, 0 D_AMT_1

              , 0 E_NUM_1, 0 E_AMT_1
              , 0 E_NUM_2, 0 E_AMT_2
              , 0 E_NUM_3, 0 E_AMT_3
              , 0 E_NUM_4, 0 E_AMT_4

           from B
          group by B.DATE

          union all
          ...
       )T
 group by T.DATE

存在的问题有

代码冗余，因为采用了union all的方法，所以每一个表的查询语句都必须存在完整的查询关键字，该表中不存在的字段必须用0占位，由此也带来了代码行数过多的问题。

编写困难，编写每个表查询都必须根据该表条件，由开发的人按顺序决定哪些字段为0，哪些字段为统计。代码的复杂度太高，编写的效率太低。

维护困难，如果发生表的变化，比如增加G表的统计，或者去掉E表的统计，需要对每一个表的统计字段做修改。

尝试过的优化方式有

尝试从oracle的语法里发现更简便的写法

   select A.DATE
          ...
        , B_NUM_1, B_AMT_1 -- A中不存在的列
     from A

避免0 B_NUM_1这样的写法，但是不存在这样的语法，即使存在也没降低多少复杂度。

尝试使用left join的方式

   select t.DATE
        , A_NUM_1, A_AMT_1
        , B_NUM_1, B_NUM_2
     from (select "20190603" DATE from dual) t
     left join (select A.DATE
                     , sum(case when OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "1" and OPTION_4 = "0" then 1 else 0 end) A_NUM_1
                     , sum(case when OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "1" and OPTION_4 = "0" then 1 else AMT end) A_AMT_1)
                     ...
                  from A
                 group by A.DATE) A on A.DATE = t.DATE
     left join (select B.DATE
                     , sum(case when OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "2" and OPTION_4 = "3" then 1 else 0 end) B_NUM_1
                     , sum(case when OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "2" and OPTION_4 = "3" then 1 else AMT end) B_AMT_1
                     ...
                  from B
                 group by B.DATE) B on B.DATE = t.DATE
                 ...

该方法性能会受到影响。

使用视图
在数据库里建立A、B、C、D、E、等表的视图，能降低开发的复杂度，但是维护的复杂度没有降低，治标不治本。

使用有比较好的代码缩进规则IDE，和使用视图类似，降低复杂度，也是治标不治本

尝试使用程序生成语句，这个可以留到以后慢慢研究

以上方式都不是治本的方法

改变统计的思路，统一每个表的统计维度，在子查询内union数据，然后在外面进行统一分类。第一降低了复杂度，第二效率提高了20%。

select T.DATE
     , sum(case when TAB_TYPE = "A" and OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "1" and OPTION_4 = "0" then NUM else 0 end) A_NUM_1
     , sum(case when TAB_TYPE = "A" and OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "1" and OPTION_4 = "0" then AMT else 0 end) A_AMT_1
       ...
  from (
         select A.DATE
              , OPTION_1, OPTION_2, OPTION_3, OPTION_4, OPTION_5
              , "A" TAB_TYPE
              , sum(NUM) NUM, sum(AMT) AMT
           from A
          group by A.DATE, OPTION_1, OPTION_2, OPTION_3, OPTION_4, OPTION_5

          union all
         select B.DATE
              , OPTION_1, OPTION_2, OPTION_3, OPTION_4, OPTION_5
              , sum(NUM) NUM, sum(AMT) AMT
           from B
          group by B.DATE, OPTION_1, OPTION_2, OPTION_3, OPTION_4, OPTION_5

          union all
          ...
       )T
 group by T.DATE

如果使用mybatis，还可以将语句进行拆分复用

    case when TAB_TYPE = "A" and OPTION_1 = "0" and OPTION_2 = "1" and OPTION_3 = "1" and OPTION_4 = "0" then NUM else 0 end


select sum() A_NUM_1
  from ...

最终解决问题，还是靠换了一种思路。用的是狼羊菜过河里把状态进行分类的思路：00000代表A_NUM_1,00001代表A_NUM_2。
如果还要进一步优化性能，可以建立静态表，每天定时的统计到该表中。