[Catalyst] Re: advise on data processing in Cat/DBIC/Model

[Rainer Clasen]

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
I did:

 SELECT date_trunc('week', day) AS day, 
	avg(value) 
 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 :( )

Rainer

-- 
KeyID=759975BD fingerprint=887A 4BE3 6AB7 EE3C 4AE0  B0E1 0556 E25A 7599 75BD