配列処理の比較

FORCIA, Inc. 板垣 貴裕

 

前回の「文字列処理の比較」に引き続き、SQL から利用できる配列処理を比較していきます。 今回も、比較対象のプログラミング言語は Java と JavaScript です。

配列は PostgreSQL 特有の機能のように誤解されていることも多いのですが、実際には標準SQLで規定された機能です。 ただ、配列をサポートしている RDBMS 製品は他にはあまり無いようなので(※)、仕方ないところかもしれません。

※ テーブルに格納できる配列型をサポートしているのは、PostgreSQL の他には InterBase, Firebird くらいでしょうか? ストアド･プロシージャ内のみであれば、もう少し増えるようですが。

配列操作の比較

全般的な注意としては、配列のインデックス (添字, 文字の位置) の表現方法があります。 文字列と同様、SQL関数では、最初の文字のインデックスは 1 です。 一方、Java, Java Script では、0 から始まります。

その他、Java, JavaScript では「インスタンスのメソッド」がありますが、SQL関数にはありません。 すべてスタティック関数のような扱いになります。 また、Java の場合は単純な配列以外にも、ArrayList や LinkedList といったコレクション・クラスがありますが、それらとの比較は割愛しています。

配列処理の比較
操作 SQL Java JavaScript
リテラル ARRAY[ 1, 2, 3 ]
'{1, 2, 3}'::integer[]		 new int[] { 1, 2, 3 } [ 1, 2, 3 ]
長さ array_length( array, dim ) -- 8.4
array_upper( array, dim )		 array . length array . length
配列要素 array [ idx ] 同左 同左
部分配列 array [ from : to ] ArrayUtils.subarray( array, from, to ) array . slice( from, to )
全要素のループ SELECT elem FROM unnest( array ) -- 8.4 for (int i = 0; i < array.length; i++) { array [ i ] }
for (Object elem : array) { elem }		 for (var i = 0; i < array.length; i++) { array [ i ] }
for (var key in array) { array [ key ] }
連結 arr1 || arr2
array_cat( arr1, arr2)
array_agg( setof_elem ) -- 8.4		 ArrayUtils.addAll( arr1, arr2 ) arr1 . concat( arr2 )
末尾へ追加 array || elem
array_append( array, elem )		 ArrayUtils.add( array, elem ) array . push( elem ) -- 破壊的
先頭へ追加 elem || array
array_prepend( elem, array )		 ArrayUtils.add( array, 0, elem ) array . unshift( elem ) -- 破壊的
文字列への連結 array_to_string( array, sep ) StringUtils.join( array, sep ) array . join( sep )
要素の削除 a [array_lower(a, 1):n - 1] || a [n + 1:array_upper(a, 1)] ArrayUtils.remove( array, n )
ArrayUtils.removeElement( array, elem )		 array . splice( from, len ) -- 破壊的
要素の置換 UPDATE ... SET array [ n ] = value array [ n ] = value -- 破壊的 array [ n ] = value -- 破壊的
array . splice( from, len, rep, ... ) -- 破壊的
前後反転 N/A (※) ArrayUtils.reverse( array ) N/A
ソート N/A (※) Array.sort( array [, cmp ] ) -- 破壊的 array.sort( [ cmp ] ) -- 破壊的

※ 関数を自作することは可能です：

=# CREATE FUNCTION reverse(anyarray) RETURNS anyarray AS $$
  SELECT array_agg($1[i]) FROM generate_subscripts($1, 1, true) AS t(i)
$$ LANGUAGE sql IMMUTABLE STRICT;
=# SELECT reverse( ARRAY[1, 2, 3] );
 reverse
---------
 {3,2,1}
(1 row)
=# CREATE FUNCTION sort(anyarray) RETURNS anyarray AS $$
  SELECT array_agg(i ORDER BY i) FROM unnest($1) AS t(i)
$$ LANGUAGE sql IMMUTABLE STRICT;
=# SELECT sort( ARRAY[3, 1, 2] );
  sort
---------
 {1,2,3}
(1 row)

配列要素比較の比較

配列の全体または部分を比較する関数や演算子もあります。 WHERE 句で使われることが想定されているためか、PostgreSQL では数多くの比較処理がサポートされています。 プログラミング言語のほうは不足する機能はループを使って計算する処理を自分で作成することになるでしょう。

配列要素比較の比較
操作 SQL Java JavaScript
全要素の一致 arr1 = arr2 Arrays.equals( arr1, arr2 ) N/A
一部でも重なる arr1 && arr2 N/A N/A
抱合する/される array @> sub
sub <@ array		 N/A N/A
いずれかの要素が条件を満たす elem op ANY ( array ) N/A N/A
すべての要素が条件を満たす elem op ALL ( array ) N/A N/A

多次元配列 vs. 配列の配列

単純な配列は、値が一次元に並んだ状態とイメージしてください。 これが多次元になると、言語ごとに、多次元配列 (Multidimensional Arrays) と配列の配列 (Jagged Arrays) という違いがあります。

PostgreSQL では「多次元配列」です。 これは、各要素の配列の長さが同じで、格子状の矩形や直方体をイメージしてください。 ただし、配列型の次元や長さを型定義の際に制限することができません。 例えば integer[] と列の型を定義すると、どんな長さやどんな次元であっても、integer 配列ならば格納できてしまいます。 以下のように integer[3][4] と指定しても、実際の定義は integer[] と解釈されていることが分かります。

=# CREATE TABLE tbl (i integer[3][4]);
CREATE TABLE
=# \d tbl
       Table "public.t"
 Column |   Type    | Modifiers
--------+-----------+-----------
 i      | integer[] |

入力をチェックする必要がある場合は、DOMAIN や CHECK 制約を組み合わせる必要があります。

=# CREATE DOMAIN ia3x3 AS integer[]
   CHECK (array_dims(VALUE) = '[1:3][1:3]'); -- 3×3の整数行列
=# SELECT ARRAY[[1,2,3],[4,5,6],[7,8,9]]::ia3x3;
           array
---------------------------
 {{1,2,3},{4,5,6},{7,8,9}}
(1 row)

一方、Java や JavaScript は「配列の配列」です。 各要素の配列は別個に初期化され、それぞれ長さが違う場合もあります。

破壊的 vs. 非破壊的

言語ごとに、メソッドが破壊的か否かが異なるものがあります。 「破壊的」とは、引数で渡した変数が直接変更されることです。 例えば JavaScript の push(), unshift(), splice() は入力した配列そのものを変更します。 一方、SQL, Java では、「要素の置換」を除く上記すべての配列操作では、入力した配列は変更されません。

まとめ

配列をリレーショナル・データベースに格納することは、一般的にはスキーマの正規化により回避することが多いかもしれません。 しかし、PostgreSQL では配列型がサポートされており、また配列内を検索するためのGINインデックスもあります。 ちょっとした構造であれば、配列を使うことでテーブルの結合 (JOIN) を減らして、性能向上に役立つかもしれません。 プログラミング言語で配列を使っているならば、データベースでもそのまま配列として格納することも検討してみてはいかがでしょうか？

関連リンク

(2010年11月8日 公開)

ブックナビゲーション