i am BEST

IBM i のいろいろ、についてです。(主にデータベースとプログラミングについて)  http://www.iforum.ne.jp/index.php?topic=blogger13 から引越してきました。

MySQL 独自の SUBSTRING_INDEX関数をビューにしてみよう!!

前回の「MySQL 独自の SPLIT_PART関数を標準SQL のユーザー定義関数にしてみよう!!」では、前々回の「MySQL 独自の SUBSTRING_INDEX関数を(あらためて)標準SQL で書いてみよう!!」で紹介したSUBSTRING_INDEX関数の標準SQL への書き直し例をユーザー定義関数として実装するやり方を紹介しました。

前回はふれませんでしたが、このユーザー定義関数という実装方法にはパフォーマンスの問題があります。

SPLIT_PART関数の場合と同様に、この SUBSTRING_INDEX関数のユーザー定義関数版でも「ユーザー定義関数と「ひとつの SQL」ではパフォーマンスが数百% も違う!?」で紹介したように、パフォーマンスから見ると「ひとつの SQL」とは大きな差がついてしまうんですね。データが増えれば増えるほど、その差は大きくなる傾向にあります。

いわば、ユーザー定義関数で実装すると結局「ぐるぐるSQL」になってしまう、ということなんですね。

「ぐるぐるSQL」については「SQL を使ったプログラムを 100倍以上遅くしてしまうコーディングとは!?(1)(SQL の達人たちがみな同意するダメなやり方とは)」や「「ぐるぐる SQL」を修正するのは大変だけど「ひとつの SQL」は対話型でカンタン!!」などでいろいろ書いてきた通り、とにかく何もいいことはありません。

後述しますが、前回紹介したユーザー定義関数の例でも残念ながら今までの例と同様なパフォーマンスの問題が起きてしまいます。

もちろん、解決策はあります。

この問題に対する解決策は、「ユーザー定義関数と「ひとつの SQL」ではパフォーマンスが数百% も違う!?」や「ユーザー定義関数(ストアド・ファンクション)のかわりにビューを使うとパフォーマンスもよくなる!?」で紹介したように、「ひとつの SQL」にまとめるか、ビューを使って共通部品化を行いつつ「ひとつの SQL」として実行するか、になります。

今回は、SUBSTRING_INDEX関数のように正負の区切り文字を使えるようにしたまま「ひとつの SQL」にするやり方と、そのビュー化の紹介を、パフォーマンスの観点も含めながら見ていきたいと思います。

「ぐるぐる SQL」はどのくらい遅いのか??

まず最初に、前回のユーザー定義関数と前々回の「ひとつの SQL」とのパフォーマンスがどのくらい違うのか、を見てみましょう。

それぞれを、1万件、10万件、100万件のデータに対して実行してみた結果が ↓ のようになります。

区切り位置が正数のもの

件数 処理時間(SQL) 処理時間(UDF) 性能比
10000 2870 ミリ秒 = 2.870 秒 4555 ミリ秒 = 4.555 秒 1.587
100000 28283 ミリ秒 = 28.283 秒 44695 ミリ秒 = 44.695 秒 1.580
1000000 291395 ミリ秒 = 4.86 分 447621 ミリ秒 = 7.46 分 1.536

区切り位置が負数のもの

件数 処理時間(SQL) 処理時間(UDF) 性能比
10000 2949 ミリ秒 = 2.949 秒 4337 ミリ秒 = 4.337 秒 1.470
100000 28284 ミリ秒 = 28.284 秒 43135 ミリ秒 = 43.135 秒 1.525
1000000 288558 ミリ秒 = 4.81 分 430802 ミリ秒 = 7.18 分 1.492

PostgresSQL 独自の SPLIT_PART関数を標準SQL のユーザー定義関数にしてみよう!!」の時と同様に、全件処理されていることを確認するために SELECT の前に INSERT をつけて実行しています。

グラフにしてみたものが ↓ になります。

区切り位置が正数のもの

image015

区切り位置が負数のもの

image017

区切り位置の正負にかかわらず、ユーザー定義関数(UDF)の方が「ひとつの SQL」で実行したものより大きく時間がかかっていることがわかります。

「ひとつの SQL」を 1 とした場合、ユーザー定義関数は 1.5 くらいになっています(正負ともに)。つまり 50% くらい遅いわけですね。

チューニングするにも、これをカバーするハードウェアを買うにも、けっこう大きな違いですよね。

ユーザー定義関数のメリットである処理の共通化はビューでも実現できる!?

「ユーザー定義関数だと処理が遅いのはわかった。が、区切り位置が正数でも負数でも同じもので処理できるところが便利なんだ」という意見があるかもしれません。

確かに「ひとつの SQL」では、区切り位置が正の数の場合と区切り位置が負の数の場合で別の SQL を使わなければなりません。

ユーザー定義関数であれば、区切り位置が正数の時でも負数の時でも同じものを使うことができました。

区切り位置の正負に応じて別の SQL を使いわけなければいけないのは面倒だ、と思われる方もいらっしゃるかもしれませんね。

では、この二つの SQL は「ひとつの SQL」にまとめてしまうことはできないのでしょうか??

実は、できるんです。

正のケースと負のケースはお互いに排他の状況になっていますので、それぞれの SQL を UNION してしまえばいいんですね。

UNION は遅い、って?!

やってみましょう!!

まったく異なる処理を統一的に扱いたいときは UNION を使えばいい!?

結果として返されるカラムの数とそれぞれの型が一致していれば、まったく異なる処理でも UNION でひとつの結果のデータ集合として統合することができます。

今回取り上げた SUBSTRING_INDEX関数は、入力となるパラメータ(区切り位置)とテーブルが同じ形で、結果も同じ形になるのに、パラメータの正負で SQL の内容が変わってしまいます。

手続き型プログラミングの考え方では、パラメータの正負に応じて条件分岐をさせ、それぞれ別のサブルーチンに処理させる、といったところでしょう。

実際、前回のユーザー定義関数の例はまさにそのようになっています。

気がついてみれば何でもないのですが、今回の例では正負のそれぞれの SQL の結果にはダブりがないんです。

今回の例では、正負それぞれの SQL があり、引数となる区切り位置を表す数には正の数、負の数、0 が考えられますよね。

正数に対応する SQL では、0 の時には結果が返らないようにしてありますし、負数が与えられても結果が返らないようにしてあります。

負数に対応する SQL では、0 の時には結果が返らないようにしてありますし、正数が与えられても結果が返らないようにしてあります。

つまり、この二つの SQL を UNION で組み合わせても、

  • 0 の時には結果が返らず、
  • 正数が与えられた場合は正数用の SQL が正数の場合の結果を返し、負数用の SQL は結果を返さず、
  • 負数が与えられた場合は負数用の SQL が負数の場合の結果を返し、正数用の SQL は結果を返さない、

というようになっているんですね。

UNION でひとつの SQL になっていても、

  • 正数の区切り位置であれば、負数用の SQL は”スルー”

され、

  • 負数の区切り位置であれば、正数用の SQL は”スルー”

されるんです。

まず「ひとつの SQL」で実行してみよう

この二つの SQL は ↓ のようにして UNION でひとつにすることができます。

WITH 

 LIST (val, deli) 
   AS (VALUES('www.mysql.com', '.')),

 SUBSTR_IX_P (D, V, P, L, R)                                                  
   AS (SELECT deli, 
              val,   
              CAST(1 AS INTEGER),  
              SUBSTRING(val, 1, POSITION(deli IN val) -1),  
              SUBSTRING(val, POSITION(deli IN val) +1, LENGTH(val) - POSITION(deli IN val) ) || deli
         FROM LIST  
        WHERE POSITION(deli IN val) <> 0 
        UNION ALL                                                           
       SELECT D,
              V,   
              P + 1,                                              
              SUBSTRING(V, 1,  length(l)  + POSITION(D IN R)),
              SUBSTRING(R, POSITION(D IN R) +1, Length(R) - POSITION(D IN R) ) 
         FROM SUBSTR_IX_p                                          
        WHERE POSITION(D IN R) <> 0 ),                                          
 
 SUBSTR_IX_M (D, R, P) 
   AS (SELECT deli, 
              val, 
              CAST(1 AS INTEGER)  
         FROM LIST 
        WHERE POSITION(deli IN val) <> 0  
        UNION ALL                                                           
       SELECT D,
              SUBSTRING(R, POSITION(D IN R) + 1, LENGTH(R) -  POSITION(D IN R)), 
              P + 1
         FROM SUBSTR_IX_M                                                          
        WHERE POSITION(D IN R) <> 0 ) ,

 SUBSTR_IX_RN (RN, D, R) 
   AS (SELECT (ROWNUMBER() OVER(ORDER BY P DESC) * -1) AS rn, D,
              R 
         FROM SUBSTR_IX_m)
  
SELECT P,
       D,
       V 
  FROM (SELECT RN AS P, 
               D, 
               R  AS V
          FROM SUBSTR_IX_RN
         UNION ALL 
        SELECT p, 
               d, 
               L AS V                                                                
          FROM SUBSTR_IX_P ) AS X 
 WHERE p = 2 ;

正の数でも負の数でも、どちらでも実行できることを確認してみましょう。

正の数のケースでの DB2 for IBM i 7.1 での実行例です。

image018

負の数のケースでの DB2 for IBM i 7.1 での実行例です。

image019

テーブルのカラムを指定して、複数行に対応するように変更したものが ↓ になります。

WITH 

 LIST (key, val, deli) 
   AS (SELECT id, STRING, '#' FROM STRIX1000) ,

 SUBSTR_IX_P (K, D, V, P, L, R)                                                  
   AS (SELECT key, 
              deli, 
              val,   
              CAST(1 AS INTEGER),  
              SUBSTRING(val, 1, POSITION(deli IN val) -1),  
              SUBSTRING(val, POSITION(deli IN val) +1, LENGTH(val) - POSITION(deli IN val) ) || deli
         FROM LIST  
        WHERE POSITION(deli IN val) <> 0 
        UNION ALL                                                           
       SELECT k,
              D,
              V,   
              P + 1,                                              
              SUBSTRING(V, 1,  length(l)  + POSITION(D IN R)),
              SUBSTRING(R, POSITION(D IN R) +1, Length(R) - POSITION(D IN R) ) 
         FROM SUBSTR_IX_p                                          
        WHERE POSITION(D IN R) <> 0 ),                                          
 
 SUBSTR_IX_M (K, D, R, P) 
   AS (SELECT key, 
              deli, 
              val, 
              CAST(1 AS INTEGER)  
         FROM LIST 
        WHERE POSITION(deli IN val) <> 0  
        UNION ALL                                                           
       SELECT k, 
              D,
              SUBSTRING(R, POSITION(D IN R) + 1, LENGTH(R) -  POSITION(D IN R)), 
              P + 1
         FROM SUBSTR_IX_M                                                          
        WHERE POSITION(D IN R) <> 0 ) ,

 SUBSTR_IX_RN (k, RN, D, R) 
      AS (SELECT k,
                 (DENSE_RANK() OVER(PARTITION BY k ORDER BY P DESC) * -1) AS RN,
                 D,
                 R  
         FROM SUBSTR_IX_m)
  
SELECT k, 
       P,
       D,
       V 
  FROM (SELECT k, 
               RN AS P, 
               D, 
               R  AS V
          FROM SUBSTR_IX_RN
         UNION ALL 
        SELECT k, 
               p, 
               d, 
               L AS V                                                                
          FROM SUBSTR_IX_P ) AS X 
 WHERE p = -2 ;

正の数のケースでの DB2 for IBM i 7.1 での実行例です。

image020

負の数のケースでの DB2 for IBM i 7.1 での実行例です。

image021

ビューに変更して実行してみよう

これを「SPLIT_PART関数をビューに変更する!?」で紹介した方法そのままでビューにすることができます。区切り位置の正負にかかわらずひとつのビューで実行させることができるわけです。

これを元にビューを作ってみましょう。

テーブルを変更する場合は、WITH句の最初にある LIST テーブル式の中の SELECT文の FROM句を変更すれば OK です。

CREATE OR REPLACE VIEW SUBSTR_IX_BY_PD AS 
WITH 

 LIST (key, val, deli) 
   AS (SELECT id, STRING, '#' FROM STRIX1000) ,

 SUBSTR_IX_P (K, D, V, P, L, R)                                                  
   AS (SELECT key, 
              deli, 
              val,   
              CAST(1 AS INTEGER),  
              SUBSTRING(val, 1, POSITION(deli IN val) -1),  
              SUBSTRING(val, POSITION(deli IN val) +1, LENGTH(val) - POSITION(deli IN val) ) || deli
         FROM LIST 
        WHERE POSITION(deli IN val) <> 0 
        UNION ALL                                                           
       SELECT K, 
              D,
              V,   
              P + 1,                                              
              SUBSTRING(V, 1,  length(l)  + POSITION(D IN R)),
              SUBSTRING(R, POSITION(D IN R) +1, Length(R) - POSITION(D IN R) ) 
         FROM SUBSTR_IX_p                                          
        WHERE POSITION(D IN R) <> 0 ),                                          
 
 SUBSTR_IX_M (K, D, R, P) 
      AS (SELECT key, 
                 deli, 
                 val, 
                 CAST(1 AS INTEGER)  
            FROM LIST  
           WHERE POSITION(deli IN val) <> 0 
           UNION ALL                                                           
          SELECT K, 
                 D,
                 SUBSTRING(R, POSITION(D IN R) + 1, LENGTH(R) -  POSITION(D IN R)), 
                 P + 1
            FROM SUBSTR_IX_M                                                        
           WHERE POSITION(D IN R) <> 0) ,

 SUBSTR_IX_RN (k, RN, D, R) 
      AS (SELECT k,
                 (DENSE_RANK() OVER(PARTITION BY k ORDER BY P DESC) * -1) AS RN,
                 D,
                 R  
            FROM SUBSTR_IX_M)
  
SELECT K,  
       P,
       D,
       V 
  FROM (SELECT K, 
               RN AS P, 
               D, 
               R  AS V
          FROM SUBSTR_IX_RN
         UNION ALL 
        SELECT k, 
               p, 
               d, 
               L AS V                                                                
          FROM SUBSTR_IX_P ) AS X ;

DB2 for IBM i 7.1 での作成例です。

image022

区切り文字と区切り対象になるカラムとテーブルを指定したビューになります。

↓ のように WHERE句で区切り位置を指定するようになっています。

SELECT k, V 
  FROM SUBSTR_IX_BY_PD
 WHERE p = 3;

DB2 for IBM i 7.1 での作成例です。

image023

SELECT k, V 
  FROM SUBSTR_IX_BY_PD
 WHERE p = -3;

DB2 for IBM i 7.1 での作成例です。

image024

正数でも負数でも使えることがわかります。

区切り位置を指定しないと、↓ のように右からと左からをすべて区切った結果が出てきます。

SELECT * 
  FROM SUBSTR_IX_BY_PD;

DB2 for IBM i 7.1 での作成例です。

image025

この集合から、区切り位置を指定して取り出す、といったイメージですね。

UNION にしても遅くならない!?

それではここで、UNION にした「ひとつの SQL」とそれをビュー化したもの、それぞれのパフォーマンスを見てみましょう。

ユーザー定義関数と「ひとつの SQL」、そして UNION を利用して正負の SQL をまとめた「ひとつの SQL」、UNION を利用してまとめた SQL をビュー化したもの、

この四つそれぞれを、1万件、10万件、100万件のデータに対して実行してみた結果が ↓ のようになります。

区切り位置が正数のもの

件数 処理時間(SQL) 処理時間(UDF) 処理時間(UNION) 処理時間(View)
10000 2870 ミリ秒 = 2.870 秒 4555 ミリ秒 = 4.555 秒 3167 ミリ秒 = 3.167 秒 3229 ミリ秒 = 3.229 秒
100000 28283 ミリ秒 = 28.283 秒 44695 ミリ秒 = 44.695 秒 30998 ミリ秒 = 30.998 秒 31045 ミリ秒 = 31.045 秒
1000000 291395 ミリ秒 = 4.86 分 447621 ミリ秒 = 7.46 分 308974 ミリ秒 = 5.15 分 309153 ミリ秒 = 5.15 分

区切り位置が負数のもの

件数 処理時間(SQL) 処理時間(UDF) 処理時間(UNION) 処理時間(View)
10000 2949 ミリ秒 = 2.949 秒 4337 ミリ秒 = 4.337 秒 3105 ミリ秒 = 3.105 秒 2964 ミリ秒 = 2.964 秒
100000 28284 ミリ秒 = 28.284 秒 43135 ミリ秒 = 43.135 秒 30966 ミリ秒 = 30.966 秒 29392 ミリ秒 = 29.392 秒
1000000 288558 ミリ秒 = 4.81 分 430802 ミリ秒 = 7.18 分 307434 ミリ秒 = 5.12 分 297016 ミリ秒 = 4.95 分

PostgresSQL 独自の SPLIT_PART関数を標準SQL のユーザー定義関数にしてみよう!!」の時と同様に、全件処理されていることを確認するために SELECT の前に INSERT をつけて実行しています。

グラフにしてみたものが ↓ になります。

区切り位置が正数のもの

image027

区切り位置が負数のもの

image029

区切り位置の正負にかかわらず、ユーザー定義関数(UDF)が他を圧倒して遅いことがわかります。

「ひとつの SQL」の方は、UNION にしたからといっても 1~2秒遅くなっているだけです。

ビューにしたものも 1秒と差がありません。

「ひとつの SQL」を 1 とした場合、ユーザー定義関数は 1.5 くらいで 50% くらい遅いのですが、UNION もビューも 1.1 行くか行かないくらいです。

正数の場合の性能比

件数 性能比(SQL) 性能比(UDF) 性能比(UNION) 性能比(View)
10000 1 1.58 1.10 1.12
100000 1 1.58 1.09 1.09
1000000 1 1.53 1.06 1.06

負数の場合の性能比

件数 性能比(SQL) 性能比(UDF) 性能比(UNION) 性能比(View)
10000 1 1.47 1.05 1.00
100000 1 1.52 1.09 1.03
1000000 1 1.49 1.06 1.02

UNION にしたからといって遅くなるわけでも、ビューにしたからといって遅くなるわけでもないんです。

SPLIT_PART関数をビューに変更する!?」で紹介したように、SQL を共通化・部品化するためにはユーザー定義関数ではなくビューの方が効率がいい、ということがここでも言えると思います。

区切り位置より前に全部区切れてしまった文字列も含めるようにする

MySQL 独自の SUBSTRING_INDEX関数を(あらためて)標準SQL で書いてみよう!!」の最後でちょっとふれた、指定された区切り位置が対象文字列の最大区切り位置より大きい(負の場合は小さい)ケースで、対象文字列全体を返すようにする仕様ですが、このビューを用いても実現することができます。

正数の場合と負数の場合で SQL は異なります(正数の場合は”大きい”で負数の場合は”小さい”になるため)が、アクセスするビューは共通で使用できます。

正数の場合の例です。

SELECT x.k, p, d, v
  FROM SUBSTR_IX_BY_PD X 
       JOIN (SELECT k, 
               MAX(p) P_max                     
          FROM SUBSTR_IX_BY_PD 
         GROUP BY k) X2 
         ON X.k = X2.k
 WHERE (p = 5) OR (p_max < 5 AND p = p_max) ;

DB2 for IBM i 7.1 での実行例です。

image030

負数の場合の例です。

SELECT x.k, p, d, v  
  FROM SUBSTR_IX_BY_PD X 
       JOIN  (SELECT k, 
               MIN(p) P_min                     
          FROM SUBSTR_IX_BY_PD 
         GROUP BY k) X2 
         ON X.k = X2.k
 WHERE (p = -5) OR (p_min > -5 AND p = p_min);

DB2 for IBM i 7.1 での実行例です。

image031

100000件のデータに対して実行してみましたが、「ひとつの SQL」などとくらべてもあまり変わらない処理時間で実行できました。DB2 for IBM i 7.1 での実行例です。

image032

ただ、このクエリは再帰クエリを UNION で結合したビューを集約した上に自己結合するようなクエリなので、正直あまり効率がいいものではありません。

データベース製品の性能次第ですが、処理データが増加していくどこかの地点で時間がかかりすぎるようなことになるかもしれません。大きなデータに対して実行する場合は、テストしてちゃんと動いた件数(たとえば ↑ のケースで言えば10万件)ずつ分割して並行で実行させるとか、いろいろ工夫した方がいいケースがあるでしょう。

MySQL 独自の SPLIT_PART関数を標準SQL のユーザー定義関数にしてみよう!!

前回の「MySQL 独自の SUBSTRING_INDEX関数を(あらためて)標準SQL で書いてみよう!!」で紹介した MySQL 独自の SUBSTRING_INDEX関数の標準SQL への書き換え例も、「PostgresSQL 独自の SPLIT_PART関数を標準SQL のユーザー定義関数にしてみよう!!」で紹介したやり方でユーザー定義関数にすることができます。

今回は、その書き換え例の紹介です。

ユーザー定義関数化で MySQL の SUBSTRING_INDEX関数と見た目・使い方を同じにできる

今回は、前回見た SUBSTRING_INDEX関数と同等の動きをする標準SQL への書き換え例を、ユーザー定義関数にしたててみた例の紹介です。

前回紹介した「ひとつの SQL」では区切り位置の正負で別の SQL にする必要がありました。

ユーザー定義関数は、パラメータを取ったり、IF を使ったりすることができるので、この二つの SQL をひとつのユーザー定義関数にまとめてしまうことができます。

つまり、見た目、使い方はもともとの MySQL の SUBSTRING_INDEX関数と同じにすることができます。

ユーザー定義関数にするやり方は「PostgresSQL 独自の SPLIT_PART関数を標準SQL のユーザー定義関数にしてみよう!!」の時の SPLIT_PART関数でのやり方と同じです。

CREATE OR REPLACE FUNCTION で作成する方法ですね。

ユーザー定義関数で二つの SQL をまとめることができる

↑ で述べたように、SUBSTRING_INDEX関数の場合は区切り位置が正数のケースと負数のケースそれぞれに固有の SQL になっていましたが、ユーザー定義関数では IF文が使えるので、最初に区切り位置を示す数が正負のどちらかを判断して、それぞれの SQL文を実行させることができます。

区切り位置が正数なのか負数なのかで、別の SQL を使い分ける、というようなことをしないでいいわけですね。

ユーザー定義関数では値を返さない、ということはできない

ユーザー定義関数は、各行毎に呼び出されます。返した値は、その行の中のひとつのカラム(の値)になるわけです。

前回見た「ひとつの SQL」で処理する場合は、「”区切られる対象の文字”が”対象となる文字列”が存在しなかった場合にその値を持つカラムの存在する行を出力しない」という処理を行うことができましたね。

ユーザー定義関数ではこういうわけにはいきません。

ユーザー定義関数が呼ばれる時点でその行はもう処理対象になってしまっていますので、その行を”なかったことにする”ことはできないんですね。

ユーザー定義関数自体が値を返さなくても、呼び出し元の SQL で他のカラムを選択してしまっていればその行は存在してしまいます。

値の返らなかったカラムは、結果として null になるか、null 不可のカラムの値になるものとして呼び出されたケースであればエラーになってしまうか、のどちらかになるわけです。

null 不可のカラムに対して呼び出されることを考えて、今回は値が存在しないケースに対しては 'n/a' といった値の不在を示す文字を返すことにしました。

呼び出す側の SQL で、この 'n/a' という値を WHERE で選択しないようにしてもらえば、「”区切られる対象の文字”が”対象となる文字列”が存在しなかった場合にその値を持つカラムの存在する行を出力しない」ようにすることもできます。

ユーザー定義関数では、区切り文字が対象文字列に存在しなかった場合はいったん 'n/a' などといった値を出しておいて、ユーザー定義関数を呼び出している SQL の WHERE句で 'n/a' となっている値を非選択とする、といったイメージですね。

前回の二つの SQL をまとめてユーザー定義関数にしてみよう

こちら ↓ がユーザー定義関数の作成例です。

CREATE OR REPLACE FUNCTION SUBSTRING_INDEX (value VARCHAR(50), delim VARCHAR(5), posit INTEGER)
RETURNS VARCHAR(50) 
LANGUAGE SQL

BEGIN

DECLARE return_val varchar(50);

IF posit = 0 THEN

  SET return_val = value ;

END IF ; 

IF posit < 0 THEN 

  SET return_val = (
    /* - の場合 */
    WITH SUBSTR_IX (D, R, P)          
      AS (SELECT deli, 
                 val, 
                 CAST(1 AS INTEGER)  
            FROM (VALUES(value, delim)) AS List (val, deli) 
           WHERE POSITION(deli IN val) <> 0   
           UNION ALL                                                           
          SELECT D,    
                 SUBSTRING(R, POSITION(D IN R) + 1, LENGTH(R) -  POSITION(D IN R)), 
                 P + 1
            FROM SUBSTR_IX                                                          
           WHERE POSITION(D IN R) <> 0 ) ,

         SUBSTR_IX_RN (RN, R, P) 
      AS (SELECT (ROWNUMBER() OVER(ORDER BY P DESC) * -1) AS RN, 
                 R,  
                 P 
            FROM SUBSTR_IX)
 
    SELECT R 
      FROM SUBSTR_IX_RN 
     WHERE RN = posit) ;

END IF;

IF posit > 0 THEN 

  SET return_val = (
    /* + の場合 */
    WITH SUBSTR_IX (D, V, P, L, R)                                                  
      AS (SELECT deli, 
                 val,   
                 CAST(1 AS INTEGER), 
                 SUBSTRING(val, 1, POSITION(deli IN val) -1),  
                 SUBSTRING(val, POSITION(deli IN val) +1, LENGTH(val) - POSITION(deli IN val) ) || deli
            FROM (VALUES(value, delim)) AS List (val, deli)  
           WHERE POSITION(deli IN val) <> 0
           UNION ALL                                                           
          SELECT D,
                 V,   
                 P + 1,                                              
                 SUBSTRING(V, 1,  length(l)  + POSITION(D IN R)),
                 SUBSTRING(R, POSITION(D IN R) +1, Length(R) - POSITION(D IN R) ) 
            FROM SUBSTR_IX                                          
           WHERE POSITION(D IN R) <> 0 )                                          
    SELECT L                                                                    
      FROM SUBSTR_IX  
     WHERE P = posit AND L IS NOT NULL) ;

END IF;

RETURN COALESCE(return_val, 'N/A') ;

END ;

DB2 for IBM i 7.1 での作成例です。

image007

ひとつのユーザー定義関数で区切り位置が正の場合も負の場合も対応できることを確認してみよう

MySQL 独自の SUBSTRING_INDEX関数を標準SQL で書いてみよう!!」と同様の結果が出ることを、「もう少し実行例を見てみよう」という節の例を確認してみました。

第三引数が正の例

VALUES( SUBSTRING_INDEX ('Linux#Apache#MySQL#PHP', '#', 3) ) ;

DB2 for IBM i 7.1 での実行例です。

image008

第三引数が正で、区切り文字の長さが混在している場合

VALUES( SUBSTRING_INDEX ('Linux#Apache###MySQL#PHP', '###', 1) ) ;

DB2 for IBM i 7.1 での実行例です。

image009

第三引数が負の例

VALUES( SUBSTRING_INDEX ('Linux#Apache#MySQL#PHP', '#', -2) ) ;

DB2 for IBM i 7.1 での実行例です。

image010

ちなみに、区切り位置として 0 が入力された場合は対象文字列をそのまま出力するようにしてあります。

VALUES( SUBSTRING_INDEX ('Linux#Apache#MySQL#PHP', '#', 0) ) ;

DB2 for IBM i 7.1 での実行例です。

image011

テーブル内のカラムを連続して処理することももちろん可能

テーブル内のカラムを連続して処理する場合については ↓ のように確認できます。

SELECT id, SUBSTRING_INDEX(string, '#', -2) 
  FROM strix1000;

DB2 for IBM i 7.1 での実行例です。

image011

↓ のようにすれば正しく全件が処理されているかどうかのチェックもできますね。

SELECT COUNT(*) 
  FROM (SELECT id, 
               SUBSTRING_INDEX(string, '#', -2) 
          FROM strix1000) X;

DB2 for IBM i 7.1 での実行例です。

image012

次回は、「ひとつの SQL」の形で二つの SQL を選択的に実行させるやり方はあるか、ということと、以前「ユーザー定義関数(ストアド・ファンクション)のかわりにビューを使うとパフォーマンスもよくなる!?」で紹介したような、ユーザー定義関数のかわりにビューを作って対応する、ということが今回のようなケースでもできるのか、ということを見てみたいと思います。

記事検索
プロフィール

i_am_best

タグクラウド
QRコード
QRコード
  • ライブドアブログ