{
    "content": [
        {
            "type": "text",
            "text": "# DBD::SQLite::Cookbook (perldoc)\n\n## NAME\n\nDBD::SQLite::Cookbook - The DBD::SQLite Cookbook\n\n## DESCRIPTION\n\nThis is the DBD::SQLite cookbook.\n\n## Sections\n\n- **NAME**\n- **DESCRIPTION**\n- **AGGREGATE FUNCTIONS** (1 subsections)\n- **SUPPORT**\n- **TO DO**\n- **AUTHOR**\n- **COPYRIGHT**\n\nUse structuredContent.sections for detailed options, examples, and full documentation.\n"
        }
    ],
    "structuredContent": {
        "command": "DBD::SQLite::Cookbook",
        "section": "",
        "mode": "perldoc",
        "summary": "DBD::SQLite::Cookbook - The DBD::SQLite Cookbook",
        "synopsis": null,
        "tldr_summary": null,
        "tldr_examples": [],
        "tldr_source": null,
        "flags": [],
        "examples": [],
        "see_also": [],
        "section_outline": [
            {
                "name": "NAME",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "DESCRIPTION",
                "lines": 5,
                "subsections": []
            },
            {
                "name": "AGGREGATE FUNCTIONS",
                "lines": 1,
                "subsections": [
                    {
                        "name": "Variance",
                        "lines": 119
                    }
                ]
            },
            {
                "name": "SUPPORT",
                "lines": 4,
                "subsections": []
            },
            {
                "name": "TO DO",
                "lines": 5,
                "subsections": []
            },
            {
                "name": "AUTHOR",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "COPYRIGHT",
                "lines": 7,
                "subsections": []
            }
        ],
        "sections": {
            "NAME": {
                "content": "DBD::SQLite::Cookbook - The DBD::SQLite Cookbook\n",
                "subsections": []
            },
            "DESCRIPTION": {
                "content": "This is the DBD::SQLite cookbook.\n\nIt is intended to provide a place to keep a variety of functions and formals for use in callback\nAPIs in DBD::SQLite.\n",
                "subsections": []
            },
            "AGGREGATE FUNCTIONS": {
                "content": "",
                "subsections": [
                    {
                        "name": "Variance",
                        "content": "This is a simple aggregate function which returns a variance. It is adapted from an example\nimplementation in pysqlite.\n\npackage variance;\n\nsub new { bless [], shift; }\n\nsub step {\nmy ( $self, $value ) = @;\n\npush @$self, $value;\n}\n\nsub finalize {\nmy $self = $[0];\n\nmy $n = @$self;\n\n# Variance is NULL unless there is more than one row\nreturn undef unless $n || $n == 1;\n\nmy $mu = 0;\nforeach my $v ( @$self ) {\n$mu += $v;\n}\n$mu /= $n;\n\nmy $sigma = 0;\nforeach my $v ( @$self ) {\n$sigma += ($v - $mu)2;\n}\n$sigma = $sigma / ($n - 1);\n\nreturn $sigma;\n}\n\n# NOTE: If you use an older DBI (< 1.608),\n# use $dbh->func(..., \"createaggregate\") instead.\n$dbh->sqlitecreateaggregate( \"variance\", 1, 'variance' );\n\nThe function can then be used as:\n\nSELECT groupname, variance(score)\nFROM results\nGROUP BY groupname;\n\nVariance (Memory Efficient)\nA more efficient variance function, optimized for memory usage at the expense of precision:\n\npackage variance2;\n\nsub new { bless {sum => 0, count=>0, hash=> {} }, shift; }\n\nsub step {\nmy ( $self, $value ) = @;\nmy $hash = $self->{hash};\n\n# by truncating and hashing, we can comsume many more data points\n$value = int($value); # change depending on need for precision\n# use sprintf for arbitrary fp precision\nif (exists $hash->{$value}) {\n$hash->{$value}++;\n} else {\n$hash->{$value} = 1;\n}\n$self->{sum} += $value;\n$self->{count}++;\n}\n\nsub finalize {\nmy $self = $[0];\n\n# Variance is NULL unless there is more than one row\nreturn undef unless $self->{count} > 1;\n\n# calculate avg\nmy $mu = $self->{sum} / $self->{count};\n\nmy $sigma = 0;\nwhile (my ($h, $v) = each %{$self->{hash}}) {\n$sigma += (($h - $mu)2) * $v;\n}\n$sigma = $sigma / ($self->{count} - 1);\n\nreturn $sigma;\n}\n\nThe function can then be used as:\n\nSELECT groupname, variance2(score)\nFROM results\nGROUP BY groupname;\n\nVariance (Highly Scalable)\nA third variable implementation, designed for arbitrarily large data sets:\n\npackage variance3;\n\nsub new { bless {mu=>0, count=>0, S=>0}, shift; }\n\nsub step {\nmy ( $self, $value ) = @;\n$self->{count}++;\nmy $delta = $value - $self->{mu};\n$self->{mu} += $delta/$self->{count};\n$self->{S} += $delta*($value - $self->{mu});\n}\n\nsub finalize {\nmy $self = $[0];\nreturn $self->{S} / ($self->{count} - 1);\n}\n\nThe function can then be used as:\n\nSELECT groupname, variance3(score)\nFROM results\nGROUP BY groupname;\n"
                    }
                ]
            },
            "SUPPORT": {
                "content": "Bugs should be reported via the CPAN bug tracker at\n\n<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite>\n",
                "subsections": []
            },
            "TO DO": {
                "content": "*   Add more and varied cookbook recipes, until we have enough to turn them into a separate CPAN\ndistribution.\n\n*   Create a series of tests scripts that validate the cookbook recipes.\n",
                "subsections": []
            },
            "AUTHOR": {
                "content": "Adam Kennedy <adamk@cpan.org>\n",
                "subsections": []
            },
            "COPYRIGHT": {
                "content": "Copyright 2009 - 2012 Adam Kennedy.\n\nThis program is free software; you can redistribute it and/or modify it under the same terms as\nPerl itself.\n\nThe full text of the license can be found in the LICENSE file included with this module.\n",
                "subsections": []
            }
        }
    }
}