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

TO DO

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

AUTHOR

155       Adam Kennedy <adamk@cpan.org>
156

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