乐趣区

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

退出移动版