2007-01-04  SQLiteで集約関数を追加する

PerlのDBD::SQLiteモジュール環境で，中央値（メディアン＜英＞Median）関数を使えるようにしてみます。「SQLiteのヘルプを読んだら，関数は追加できるって書いてあったような」と記憶に残っている人もいるはず。どうすれば関数を追加できるのでしょうか。

集約関数とは

SQLiteで使える関数の種類には，単純関数と集約関数の二種類があります。今回，実装するのは集約関数（avg，sumのようにSELECTで抽出した要素に対して作用する関数）です。単純関数の実装方法は説明しませんので注意してください。

中央値とは

念のため「中央値」とは何か，整理しておきます。

＜条件＞要素は昇順（小から大）に並んでいるものとする。

＜定義＞要素の個数をnとする。

• nが奇数のとき，ちょうど真ん中の値
• nが偶数のとき，真ん中のふたつの数の平均値

＜例＞中央値を返す関数Medianがあったとき，関数Medianが返す値は以下のようになる。

Median( 1,2,3,4,5 ) = 3
Median( 1,2,3,4,5,6 ) = 3.5
Median( 1,1,1,1,1,6 ) = 1

Perlによる実装

ようやく実装の話になります。以下，手順です。Perlのオブジェクト指向プログラミングを知らないと解読するのが面倒かもしれませんが，やっていることはさほど複雑ではありません。

1. package宣言にて名前空間を指定する。名前は集約関数と同じにするのがよい *1
2. SQLiteから呼ばれるコールバック関数を実装する。関数はnew，step，finalizeのみっつ
3. new関数の実装。コンストラクタである。サンプルを書き写すだけで問題ないはず *2
4. step関数の実装。この関数はSELECTにてレコードが抽出されるたびに呼ばれる。受け取った引数をblessした配列に追加する *3
5. finalize関数の実装。この関数の戻り値が集約関数の結果となる。stepにて配列に追加した要素を元に，値を算出し返す *4
6. データベースハンドラのfunc関数を呼び出し，SQLiteに集約関数を追加する *5
7. SELECTの例 *6

以下，コード例です。コード例はあくまで断片です。変数$dbhにDBハンドラの参照を代入するコードは省略しています。準備としてDBにはresultsテーブルが作成されており，テーブルにはgroup_nameとscoreという列が定義されているものとします。さらには6件レコードが事前に挿入されているものとします。

package median ; # *1

sub new { bless [], shift; } # *2

sub step { # *3
  my $self = shift ;
  my $value = shift ;
  push @$self, $value ;
}

sub finalize { # *4
  my $self = shift ;
  my $n = @$self;

  @$self = sort { $a <=> $b } @$self ;

  my $median ;

  if( $n % 2 )
  {
    $median = @$self[ ($n - 1) / 2 ] ;
  }
  else
  {
    $median = ( @$self[ ($n - 1) / 2 ] + @$self[ $n / 2 ] ) / 2 ;
  }
  return $median;
}

# *5
$dbh->func( "median", 1, 'median', "create_aggregate" ) ;

# *6
my $cur ;
$cur = $dbh->prepare( qq!
  SELECT group_name, median(score) FROM results
  GROUP BY group_name
!) ;

$cur->execute() ;

my $group ;
my $var ;

$cur->bind_columns( undef, \( $group, $var ) ) ;

while( $cur->fetch() )
{
  print $group ." " . $var ."\n" ;
}

まとめ

＜画像の説明＞SQLと内部シーケンスのようなものを図にしました（シーケンスは一部想像です）。stepとfinalizeをだれがいつ呼ぶものなのか分からない人も，図を見れば解決するでしょう。（Microsoft Visioで作成。）

引数がふたつ以上の集約関数

「引数がひとつの集約関数の書き方は分かったけれど，ふたつ以上のときはどうするの？」と思った人がいるかもしれません。3引数の例を以下に書きます。

＜step関数＞

sub step {
  my $self = shift ;
  my( $value1, $value2, $value3 ) = @_ ;
  push @$self, [ $value1, $value2, $value3 ] ;
}

または

sub step {
  my $self = shift ;
  push @$self, \@_ ;
}

＜finalize関数＞

sub finalize {
  my $self = shift ;
  foreach( @$self )
  {
    my( $value1, $value2, $value3 ) = ( @$_[ 0 ], @$_[ 1 ], @$_[ 2 ] ) ;
  }
  ...
}

関連リンク

• DBD::SQLite ― 本家の情報です