| Subject: | Sample programs in the cookbock is not good examples |
Hello ADAMK and others,
DBD::SQLite::Cookbook does not seem to be good examples. SQLite
aggrigates are designed to be object-oriented, but these examples use no
benefits of OOP.
Take the highly-scalable version of variance() for example. It uses some
global variabes ($mu, $count, $S), so it can be called only once in a
SQL statement. Why do you use object properties, instead of global
variables?
The attached file "Variance.pm" is a new example, which implements
simple variance (Variance::Simple) and highly-scalable variance
(Variance::Fast). I think it is a little better. "variance.pl" is a demo
file for "Variance.pm".
Regards,
--
Goro Fuji (gfx) GFUJI at CPAN.org
| Subject: | Variance.pm |
package Variance::Simple;
use strict;
use warnings;
sub new {
my($class) = @_;
return bless {
sum => 0,
data => [],
} => $class;
}
sub step {
my($self, $value) = @_;
$self->{sum} += $value;
push @{$self->{data}}, $value;
return;
}
sub finalize {
my($self) = @_;
my $n = scalar @{$self->{data}};
my $mu = $self->{sum} / $n;
my $s = 0;
foreach my $datum(@{$self->{data}}){
$s += ($datum - $mu) ** 2;
}
return $s / ($n-1);
}
package Variance::Fast;
sub new {
my($class) = @_;
return bless {
n => 0,
sum => 0,
mu => 0,
s => 0,
} => $class;
}
sub step {
my($self, $value) = @_;
$self->{n}++;
my $delta = $value - $self->{mu};
$self->{mu} += $delta / $self->{n};
$self->{S} += $delta * ($value - $self->{mu});
return;
}
sub finalize {
my($self) = @_;
return $self->{S} / ($self->{n}-1);
}
1;
| Subject: | variance.pl |
#!perl -w
use strict;
use DBI;
use Data::Dumper;
use FindBin qw($Bin);
use lib $Bin;
use Variance;
my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:', undef, undef, { PrintError => 0, RaiseError => 1});
$dbh->do(<<'SQL');
CREATE TABLE foo(
value INTEGER NOT NULL
);
SQL
{
my $sth = $dbh->prepare('INSERT INTO foo VALUES(?)');
foreach my $value(10, 12, 11, 10, 8, 9, 10, 11, 9){
$sth->execute($value);
}
$sth->finish();
}
foreach my $vclass (qw(Variance::Simple Variance::Fast)){
$dbh->sqlite_create_aggregate(variance => 1, $vclass);
my $sth = $dbh->prepare('SELECT variance(value) FROM foo');
$sth->execute();
print Data::Dumper->new([$sth->fetchrow_hashref()], [$vclass])->Indent(1)->Dump;
}