一次SQL优化

jiezi

5 年前

有 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，还可以将语句进行拆分复用

<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。
如果还要进一步优化性能，可以建立静态表，每天定时的统计到该表中。

一次SQL优化

问题

失败的优化尝试

最终的解决方法

思考