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

AGGREGATE FUNCTIONS

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

SUPPORT

138       Bugs should be reported via the CPAN bug tracker at
139
140       <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>
141

TO DO

143       •   Add more and varied cookbook recipes, until we have enough to turn
144           them into a separate CPAN distribution.
145
146       •   Create a series of tests scripts that validate the cookbook
147           recipes.
148

AUTHOR

150       Adam Kennedy <adamk@cpan.org>
151

153       Copyright 2009 - 2012 Adam Kennedy.
154
155       This program is free software; you can redistribute it and/or modify it
156       under the same terms as Perl itself.
157
158       The full text of the license can be found in the LICENSE file included
159       with this module.
160
161
162
163perl v5.34.0                      2021-08-02          DBD::SQLite::Cookbook(3)
Impressum