[Catalyst] Re: advise on data processing in Cat/DBIC/Model
bj at zuto.de
Mon Nov 26 21:53:28 GMT 2007
Matt S Trout wrote:
> On Mon, Nov 26, 2007 at 04:33:02PM +0100, Rainer Clasen wrote:
> > result_source as described in the DBIC cookbook under "arbitrary SQL".
> > example query for ->name():
> > SELECT
> > d.id,
> > steps AS day,
> > d.value,
> > COALESCE( d.other_value, $4 ) AS other_value
> > FROM
> > timeseries( $1, $2, $3) AS steps
> > LEFT JOIN ( SELECT * FROM data WHERE other_value = $4 ) d
> > ON ( d.day >= $2 AND d.day + $1 < $3;
> > $1 = time steps. eg. '1 day'
> > $2 = start date. eg. '2007-11-1'
> > $3 = end date. eg '2007-11-30'
> > $4 = other_value to filter on.
> > timeseries(step,start,end) = stored procedure that returns the
> > start-dates of the time-steps within the specified time-range.
> I tend to do -sort- of this.
> Except that instead of using a function like timeseries() I'll create a
> pivot table with a 'date' column that I prepopulated with all dates from
> now to say 2020 (and make sure one of my cron jobs extends this when we
> reach say 2019 or so). Then I put function indexes on the various DATE_PART
> or equivalent functions that I might use to pull the month, year etc.
> That way I can query the pivot as "just another DBIC class" and everything
> gets simpler.
Ah, that's similar to what I had before. Well, yours shows a bit more
experience ;) I haven't thought about indices, yet, and I auto-populated
the date table from a trigger (quite bad Idea as a user could fill my
disks by specifying a date that's waaaaaay in the future/past). Basically
SELECT date_trunc('week', day) AS day,
FROM dates s
LEFT JOIN data d
ON s.day = d.day
GROUP BY day;
But I found no easy way to date_trunc() to user-specified time-steps like
"4 weeks" so I switched to using the stored procedure directly.
Seems I'll better go back to the old aproach and think a bit about indices
and a custom date_trunc/date_part/whatever.
Thanks a lot and sorry for turning things complicated (as I quite often
tend to :( )
KeyID=759975BD fingerprint=887A 4BE3 6AB7 EE3C 4AE0 B0E1 0556 E25A 7599 75BD
More information about the Catalyst