一次SQL最適化

6867 ワード

に質問
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データを統一し、外で統一的に分類します.第1は複雑さを低減し,第2の効率は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を表しています.パフォーマンスをさらに最適化する場合は、静的テーブルを作成し、毎日のタイミングの統計をテーブルに設定できます.

zzuli OJ 1050:階乗の累積和

iosは深く勉強する(一).m和hファイル