オラクルからMySQLへのSQL変換でのメモ

はじめに

オラクルからmysqlへの書き換えてに、理解に苦しむsqlに出くわし、理解に時間がかかったので繰り返さないよう記録として残した。

該当SQL

以下オラクルsql、見にくいが整形でき、整形したものを見れば理解できるなら以降見る必要なし。

該当SQL1

SELECT
DECODE(SIGN(SUM(DECODE(SIGN(ABS(TAN*AMT - KIN)/
        DECODE(TAN*AMT,0,1,TAN*AMT)*100-6),1,1,0))),1,1,0) 
AS CHK_TAN
FROM
 sample

該当SQL2

select decode(nvl(max(txt_id),-1),-1,
((to_number(to_char(sysdate,'YYYY'))-".$year." + 
DECODE(SIGN(to_number(to_char(sysdate,'MM'))-
".$month."),'-1',1,0))*10000 + 1),max(txt_id)+1) from sample

１つ目(該当SQL1)

関数が理解できなければ整形もできない。
まず該当１に関して以下の関数がわかればいいと思う。

DECODE関数

DECODE(列名, 値1, 結果1, 値2, 結果2, 結果3)

引数１(列名)が値１だと結果１を返す、引数１(列名)が値2だと結果2を返す、それ以外は結果３を返す。

SIGN

SIGN関数は、引数で指定された数値の符号を求める関数。引数で指定された数値が正ならば「1」、負ならば「-1」、0ならば「0」が返される。

ABS

絶対値です。

読みやすく整形

該当SQLを読みやすく整形したものです。(あのままではわかるはずがない)

SELECT 
  DECODE(
    SIGN(
      SUM(
        DECODE(
          SIGN(
            ABS(TAN*AMT - KIN)/
            DECODE(TAN*AMT,0,1,TAN*AMT)*100-6
          ),
          1, 1, 0
        )
      )
    ),
    1, 1, 0
  ) AS CHK_TAN
FROM sample;

内側から内容を記載
1.SINGの中を計算
(1).TAN * AMT - KINの絶対値を計算する： ABS(TAN*AMT - KIN)
(2).TAN * AMT をチェックし、0の場合は1を、それ以外の場合は TAN*AMT の値を使用する。DECODE(TAN*AMT,0,1,TAN*AMT)
(3).ステップ1の値をステップ2の値で割る
(4).その結果に100を掛ける。
(5).そして、6を引く。
2.SIGN値
評価される値(SIGNの中)が正なら1,0なら0、負なら-1を返す。
3.DECODE
評価される値(SING値)が1なら１、それ以外なら0を返す。
4.SUM
これらの値(SIGN値)を、全行にわたって合計する。
5.SIGN
SUMで合計したものが、正なら1,0なら0、負なら-1を返す。
6.DECODE
SIGNの値が、1なら１を返す、それ以外は0を返す。

要は、要件を満たす行が１つでもあれば１、なければ0を返すsqlである。

MYSQL

オラクルのDECODE関数は、MySQLではCASE文を使用して書き直す。
SUM関数はテーブルでセレクトされる合計の集計関数である、いざこんな書き方をされるとわかりにくかった、SIGN関数に惑わされないこと。
該当SQLをMYSQLに書き換えた例。

SQL
CASE
    WHEN SIGN(SUM(CASE
                    WHEN SIGN(ABS(TAN * AMT - KIN) /
                        CASE
                            WHEN TAN * AMT = 0 THEN 1
                            ELSE TAN * AMT
                        END * 100 - 6) = 1 THEN 1
                    ELSE 0
                  END)) = 1 THEN 1
    ELSE 0
END AS CHK_TAN
FROM sample

上記sqlでわかりくい場合、さらに整形すると以下になり、長くなるがわかるはず。
とにかくただ階層が深い。

SELECT 
  (
    CASE 
      WHEN 
        SIGN(
          SUM(
            CASE 
              WHEN 
                SIGN(
                  ABS(TAN * AMT - KIN_D) / 
                  (CASE WHEN TAN * AMT = 0 THEN 1 ELSE TAN * AMT END) * 100 - 6
                ) = 1 
              THEN 1 
              ELSE 0 
            END
          )
        ) = 1 
      THEN 1 
      ELSE 0 
    END
  ) AS CHK_TANKA
FROM sample;

２つ目(該当SQL2)

select decode(nvl(max(txt_id),-1),-1,
((to_number(to_char(sysdate,'YYYY'))-".$year." + 
DECODE(SIGN(to_number(to_char(sysdate,'MM'))-
".$month."),'-1',1,0))*10000 + 1),max(txt_id)+1) from sample

該当１のsqlが理解でれば整形も可能かと思う。
整形すると以下になる。

SELECT DECODE(
  NVL(MAX(txt_id), -1),
  -1,
  (
    (
      TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - ".$year."
      + DECODE(
          SIGN(TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) - ".$month."),
          '-1',
          1,
          0
        )
    ) * 10000 + 1
  ),
  MAX(ext_id) + 1
)
FROM sample;

関数の理解

以下のように分解
・NVL(MAX(ext_id), -1)
MAX(ext_id)は、ext_idというカラムの最大値を返す集約関数。

NVLは、NULL値を別の値に置き換える関数。この場合、NVL(MAX(ext_id), -1)は、MAX(ext_id)の結果がNULLであれば-1という値に置き換える。

・TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))
SYSDATEは、現在のシステム日付と時刻を返す関数。
TO_CHARは、数値や日付型のデータを文字列に変換する関数。
つまり、TO_CHAR(SYSDATE, ‘YYYY’)は、SYSDATEの値を’YYYY’という書式で文字列に変換している。
TO_NUMBERは、文字列の数値への変換を行う関数。
つまりTO_NUMBER(TO_CHAR(SYSDATE, ‘YYYY’))は、TO_CHAR(SYSDATE, ‘YYYY’)の結果を数値に変換している。
つまり、TO_NUMBER(TO_CHAR(SYSDATE, ‘YYYY’))は、現在のシステム日付の年部分を数値として返す式である。

・DECODE、SIGNに関しては該当１参考

このSQL文の目的は、新しいtxt_idを生成すること。もしtxt_idが存在しない、またはその最大値が-1の場合、新しいtxt_idは特定の計算に基づいて生成される。

MySQLへの置き換え

DECODEやNVL関数は、同等の機能を提供する IFNULLやCASE などの関数を使用する。文字列、数値への変換対応は、特に必要なく、例えばYEAR(CURDATE())で数値となる。

SELECT 
  CASE
    WHEN IFNULL(MAX(ext_id), -1) = -1 THEN
      (
        (
          YEAR(CURDATE()) - ".$year."
          + CASE
              WHEN MONTH(CURDATE()) - ".$month." < 0 THEN 1
              ELSE 0
            END
        ) * 10000 + 1
      )
    ELSE MAX(ext_id) + 1
  END
FROM
 SAMPLE

おわりに

sql理解に苦しんだのだが、DECODE関数、SIGN関数が何かわからず、しかもネストが深いため思考が止まってしまったと思う。オラクルになれた人なら問題ないsqlかと思う。DECODE関数、SIGN関数がわかれば整形はできた。集計関数はテーブルを読む上で重要だが、なぜか本来の理解ができなかった。
個人的なブログになったかもしれないが、今後のsqlのステップとして残した。