NAME
DBIx::PivotQuery - create pivot tables from queries
SYNOPSIS
use DBIx::PivotQuery 'pivot_by';
my $rows = pivot_by(
dbh => $dbh,
columns => ['month'],
rows => ['region'],
aggregate => ['sum(amount) as amount'],
sql => <<'SQL');
select
month(date) as report_month
, region
, amount
from mytable
SQL
The above code returns a data structure roughly like
# [
# ['region','1','2',...,'11','12'],
# ['East', 0, 0 ,..., 10, 20 ],
# ['North', 0, 1 ,..., 10, 20 ],
# ['South', 0, 3 ,..., 10, 5 ],
# ['West', 0, 6 ,..., 8, 20 ],
# ]
FUNCTIONS
# This should maybe return a duck-type statement handle so that people
# can fetch row-by-row to their hearts content # row-by-row still means
we need to know all values for the column key :-/
pivot_by
my $l = pivot_by(
dbh => $test_dbh,
rows => ['region'],
columns => ['date'],
aggregate => ['sum(amount) as amount'],
placeholder_values => [],
subtotals => 1,
sql => <<'SQL',
select
region
, "date"
, amount
, customer
from mytable
SQL
);
Transforms the SQL given and returns an AoA pivot table according to
rows, columns and aggregate.
The last word (<c>\w+</c>) of each element of aggregate will be used as
the aggregate column name unless aggregate_columns is given.
Supplying undef for a column name in rows will create an empty cell in
that place. This is convenient when creating subtotals.
Options
headers
headers => 1,
Whether to include the headers as the first row
Subtotals are calculated by repeatedly running the query. For
optimization, you could first select the relevant (aggregated) rows
into a temporary table and then create the subtotals from that
temporary table if query performance is an issue:
select foo, sum(bar) as bar, baz
into #tmp_query
from mytable
where year = ?
select foo, bar, baz from #tmp_query
pivot_list
my $l = pivot_list(
list => @AoH,
columns => ['date'],
rows => ['region'],
aggregate => ['amount'],
);
The rows of @$l are then plain arrays not hashes. The first row of @$l
will contain the column titles.
The column titles are built from joining the pivot column values by $;
.
headers
headers => 1,
Whether to include the headers as the first row
pivot_sql
pivot_sql(
columns => ['date'],
rows => ['region'],
aggregate => ['sum(amount) as amount'],
sql => <<'SQL' );
select
"date"
, region
, amount
from mytable
SQL
Creates SQL around a subselect that aggregates the given columns.
The SQL created by the call above would be
select "region"
, "date"
, sum(amount) as amount
from (
select
"date"
, region
, amount
from mytable
) foo
group by "region, "date"
order by "region", "date"
Note that the values in the columns and rows options will be
automatically enclosed in double quotes.
This function is convenient if you want to ccreate ad-hoc pivot queries
instead of setting up the appropriate views in the database.
If you want to produce subtotals, this function can be called with the
elements removed successively from $options{rows} or $options{columns}
for computing row or column totals.
Unsupported features
Currently only one aggregate value is allowed.
Row aggregates ("totals") are not supported yet. Row aggregates will
mean heavy rewriting of the SQL to wrap the aggregate function over the
column names of the query.
SEE ALSO
DBI
REPOSITORY
The public repository of this module is
SUPPORT
BUG TRACKER
Please report bugs in this module via the RT CPAN bug queue at
via mail to dbix-pivotquery-Bugs@rt.cpan.org.
AUTHOR
Max Maischein corion@cpan.org
COPYRIGHT (c)
Copyright 2017 by Max Maischein corion@cpan.org.
LICENSE
This module is released under the same terms as Perl itself.