Group By statistics columns

5 messages

Group By statistics columns

Reply Threaded

I have a table of logged data, which is too granular to browse row by
row. What I want to do is display a row per hour of data, and include
average/min/max for each field.

I can do this in SQL, using group by with a formatted timestamp field
to group per hour, along with AVG(), MIN(), MAX() on each field to get
the values I'm after. I can replicate the query in DBIx by defining
'select' and 'as'. However, I can't access these, I'm assuming because
I've given them names which don't match my DBIx::Class' defined column
names. A dump of the DBIx::Class shows the values in there as part of
_column_data.

So, for example, if my class has a column "foo", as does my DB table,
then what I want to access is foo_avg, foo_min and foo_max.

Is there an easy way to get a search resultset to auto-create the new columns?
Is there a better way to accomplish what I'm after?

I'm using DBIx::Class within Catalyst, with a MySQL DB, and Template
Toolkit for presentation.

Thanks.

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Андрей Костенко

Re: Group By statistics columns

Reply Threaded

You can create a view for this query. e.g.
CREATE VIEW stats AS SELECT MIN(a), MAX(a), AVG(a) FROM table1;

On Mon, Sep 21, 2009 at 10:42 AM, Trevor Phillips <[hidden email]> wrote:

I have a table of logged data, which is too granular to browse row by
row. What I want to do is display a row per hour of data, and include
average/min/max for each field.

I can do this in SQL, using group by with a formatted timestamp field
to group per hour, along with AVG(), MIN(), MAX() on each field to get
the values I'm after. I can replicate the query in DBIx by defining
'select' and 'as'. However, I can't access these, I'm assuming because
I've given them names which don't match my DBIx::Class' defined column
names. A dump of the DBIx::Class shows the values in there as part of
_column_data.

So, for example, if my class has a column "foo", as does my DB table,
then what I want to access is foo_avg, foo_min and foo_max.

Is there an easy way to get a search resultset to auto-create the new columns?
Is there a better way to accomplish what I'm after?

I'm using DBIx::Class within Catalyst, with a MySQL DB, and Template
Toolkit for presentation.

Thanks.

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Trevor Phillips

Re: Group By statistics columns

Reply Threaded

2009/9/21 Андрей Костенко <[hidden email]>:
> You can create a view for this query. e.g.
> CREATE VIEW stats AS SELECT MIN(a), MAX(a), AVG(a) FROM table1;

I could, but it's not the query I'm having a problem with - it's
mapping it to columns within the pre-existing DBIx::Class. I'd rather
not have to create a whole new class simply for this single query
against my data. I can see the data in the raw object - I just can't
access it through the normal methods.

Reading the manual, I should be able to get the value via get_column,
but Catalyst/TT uses the column methods instead.

Hmmm, I *can* use get_column within TT, but that seems to defeat the
purpose of abstracting data from presentation.

When getting the search results, I can map the rows with
$_->get_columns(). That seems to be the easiest solution for now.

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Андрей Костенко

Re: Group By statistics columns

Reply Threaded

You can call method ->get_column('some_value')->min from resultset.
Also you can create your own ResultSet and add method

sub get_min_malue {
shift->get_column('some_column')->min;
}

So you can make such query:
$c->model('DB::Table')->get_min_value;
$c->model('DB::Table')->search( {type => 1} )->get_min_value;

But you will make three requests for min, max and avg functions. Or you can create other method in your resultset object which returns hash with needed values.

sub get_stats_data{
return { min => 1, max => 2, avg => 3}
}

2009/9/22 Trevor Phillips <[hidden email]>

2009/9/21 Андрей Костенко <[hidden email]>:

> You can create a view for this query. e.g.
> CREATE VIEW stats AS SELECT MIN(a), MAX(a), AVG(a) FROM table1;

I could, but it's not the query I'm having a problem with - it's
mapping it to columns within the pre-existing DBIx::Class. I'd rather
not have to create a whole new class simply for this single query
against my data. I can see the data in the raw object - I just can't
access it through the normal methods.

Reading the manual, I should be able to get the value via get_column,
but Catalyst/TT uses the column methods instead.

Hmmm, I *can* use get_column within TT, but that seems to defeat the
purpose of abstracting data from presentation.

When getting the search results, I can map the rows with
$_->get_columns(). That seems to be the easiest solution for now.

--
Trevor Phillips - http://dortamur.livejournal.com/
"On nights such as this, evil deeds are done. And good deeds, of
course. But mostly evil, on the whole."
-- (Terry Pratchett, Wyrd Sisters)

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

Matt Whipple

Re: Group By statistics columns

Reply Threaded

In reply to this post by Trevor Phillips

Trevor Phillips wrote:

> 2009/9/21 Андрей Костенко <[hidden email]>:
>
>> You can create a view for this query. e.g.
>> CREATE VIEW stats AS SELECT MIN(a), MAX(a), AVG(a) FROM table1;
>>
>
> I could, but it's not the query I'm having a problem with - it's
> mapping it to columns within the pre-existing DBIx::Class. I'd rather
> not have to create a whole new class simply for this single query
> against my data. I can see the data in the raw object - I just can't
> access it through the normal methods.
>
> Reading the manual, I should be able to get the value via get_column,
> but Catalyst/TT uses the column methods instead.
>
> Hmmm, I *can* use get_column within TT, but that seems to defeat the
> purpose of abstracting data from presentation.
>

You could leave the accessor abstracted by creating the function within
the result class. Along the lines of
sub a_min { return shift->get_column('a_min'); };

> When getting the search results, I can map the rows with
> $_->get_columns(). That seems to be the easiest solution for now.
>
> --
> Trevor Phillips - http://dortamur.livejournal.com/
> "On nights such as this, evil deeds are done. And good deeds, of
> course. But mostly evil, on the whole."
> -- (Terry Pratchett, Wyrd Sisters)
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>