一次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

存在的问题有

1. 代码冗余，因为采用了union all的方法，所以每一个表的查询语句都必须存在完整的查询关键字，该表中不存在的字段必须用0占位，由此也带来了代码行数过多的问题。
2. 编写困难，编写每个表查询都必须根据该表条件，由开发的人按顺序决定哪些字段为0，哪些字段为统计。代码的复杂度太高，编写的效率太低。
3. 维护困难，如果发生表的变化，比如增加G表的统计，或者去掉E表的统计，需要对每一个表的统计字段做修改。

失败的优化尝试

尝试过的优化方式有

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

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

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

2. 尝试使用 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
                    ...

   该方法性能会受到影响。

3. 使用视图
   在数据库里建立A、B、C、D、E、等表的视图，能降低开发的复杂度，但是维护的复杂度没有降低，治标不治本。
4. 使用有比较好的代码缩进规则IDE，和使用视图类似，降低复杂度，也是治标不治本
5. 尝试使用程序生成语句，这个可以留到以后慢慢研究

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

最终的解决方法

改变统计的思路，统一每个表的统计维度，在子查询内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，还可以将语句进行拆分复用

<sql id="A_NUM_1">
    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
</sql>

select sum(<include refid="A_NUM_1"/>) A_NUM_1
  from ...

思考

最终解决问题，还是靠换了一种思路。用的是狼羊菜过河里把状态进行分类的思路： 00000 代表 A_NUM_1 , 00001 代表 A_NUM_2 。

如果还要进一步优化性能，可以建立静态表，每天定时的统计到该表中。