1DBD::SQLite::Cookbook(3U)ser Contributed Perl DocumentatiDoBnD::SQLite::Cookbook(3)
2
3
4

NAME

6       DBD::SQLite::Cookbook - The DBD::SQLite Cookbook
7

DESCRIPTION

9       This is the DBD::SQLite cookbook.
10
11       It is intended to provide a place to keep a variety of functions and
12       formals for use in callback APIs in DBD::SQLite.
13
14   Variance
15       This is a simple aggregate function which returns a variance. It is
16       adapted from an example implementation in pysqlite.
17
18         package variance;
19
20         sub new { bless [], shift; }
21
22         sub step {
23             my ( $self, $value ) = @_;
24
25             push @$self, $value;
26         }
27
28         sub finalize {
29             my $self = $_[0];
30
31             my $n = @$self;
32
33             # Variance is NULL unless there is more than one row
34             return undef unless $n || $n == 1;
35
36             my $mu = 0;
37             foreach my $v ( @$self ) {
38                 $mu += $v;
39             }
40             $mu /= $n;
41
42             my $sigma = 0;
43             foreach my $v ( @$self ) {
44                 $sigma += ($x - $mu)**2;
45             }
46             $sigma = $sigma / ($n - 1);
47
48             return $sigma;
49         }
50
51         # NOTE: If you use an older DBI (< 1.608),
52         # use $dbh->func(..., "create_aggregate") instead.
53         $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
54
55       The function can then be used as:
56
57         SELECT group_name, variance(score)
58         FROM results
59         GROUP BY group_name;
60
61   Variance (Memory Efficient)
62       A more efficient variance function, optimized for memory usage at the
63       expense of precision:
64
65         package variance2;
66
67         my $sum   = 0;
68         my $count = 0;
69         my %hash;
70
71         sub new { bless [], shift; }
72
73         sub step {
74             my ( $self, $value ) = @_;
75
76             # by truncating and hashing, we can comsume many more data points
77             $value = int($value); # change depending on need for precision
78                                   # use sprintf for arbitrary fp precision
79             if (defined $hash{$value}) {
80                 $hash{$value}++;
81             } else {
82                 $hash{$value} = 1;
83             }
84             $sum += $value;
85             $count++;
86         }
87
88         sub finalize {
89             my $self = $_[0];
90
91             # Variance is NULL unless there is more than one row
92             return undef unless $count > 1;
93
94             # calculate avg
95             my $mu = $sum / $count;
96
97             my $sigma = 0;
98             foreach my $h (keys %hash) {
99                 $sigma += (($h - $mu)**2) * $hash{$h};
100             }
101             $sigma = $sigma / ($count - 1);
102
103             return $sigma;
104         }
105
106       The function can then be used as:
107
108         SELECT group_name, variance2(score)
109         FROM results
110         GROUP BY group_name;
111
112   Variance (Highly Scalable)
113       A third variable implementation, designed for arbitrarily large data
114       sets:
115
116         package variance;
117
118         my $mu = 0;
119         my $count = 0;
120         my $S = 0
121
122         sub new { bless [], shift; }
123
124         sub step {
125             my ( $self, $value ) = @_;
126             $count++;
127             $delta = $value - $mu;
128             $mu = $mu + $delta/$count
129             $S = $S + $delta*($value - $mu);
130         }
131
132         sub finalize {
133             my $self = $_[0];
134             return $S / ($count - 1);
135         }
136
137       The function can then be used as:
138
139         SELECT group_name, variance3(score)
140         FROM results
141         GROUP BY group_name;
142

SUPPORT

144       Bugs should be reported via the CPAN bug tracker at
145
146       http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite
147       <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
148

TO DO

150       * Add more and varied cookbook recipes, until we have enough to turn
151       them into a seperate CPAN distribution.
152
153       * Create a series of tests scripts that validate the cookbook recipies.
154

AUTHOR

156       Adam Kennedy <adamk@cpan.org>
157

159       Copyright 2009 Adam Kennedy.
160
161       This program is free software; you can redistribute it and/or modify it
162       under the same terms as Perl itself.
163
164       The full text of the license can be found in the LICENSE file included
165       with this module.
166
167
168
169perl v5.12.1                      2010-01-08          DBD::SQLite::Cookbook(3)
Impressum