[Babase] query for marie
Karl O. Pinc
kop at meme.com
Fri May 11 10:26:31 EDT 2007
Note that I think you want to reverse the month/year grouping.
See below. Or, maybe not, if Marie wants to look at Ddates
by season.
The hardest part is figuring out what question you're asking.
On 05/11/2007 09:00:12 AM, Karl O. Pinc wrote:
>
> On 05/11/2007 08:02:23 AM, Susan Alberts wrote:
Optimization A:
>>>> Secondary problem: we selected on members.date instead of
>>>> cycpoints.date. I am not sure whether this was a problem or not
>>>> but on thinking about it I realized that the date in cycpoints was
>>>> the relevant date (it was the D date) and not the date in members.
Optimization B:
> What _is_ worth thinking about is the kind of optimization you made,
> where you did members.date = cycpoints.date and so cut the number
> of rows down by a couple of factors of 10, at least, rather than
> doing what I did and later eliminating the rows using DISTINCT.
The other important difference between optimization A and optimization
B is that optimization B will always be an optimization whereas
optimization A depends on the stupidness of the internal database query
planner. So, as the database planner gets smarter optimization A
can become unnecessary, or even slow things down in pathological cases.
It makes the most sense to write queries that are clear to
the person reading the query, and let the computer worry
about the implimentation details. To that end it can
make sense to comment (and indent) the query.
The comments here reflect how I think of the query, YMMV.
select count(cycles.cid) -- Total Ddates in month
, members.grp
, date_part('month', cycpoints.date) as month
, date_part('year', cycpoints.date) as year
from cycles -- Lets us connect cycpoints with members
, cycpoints -- Has the Ddates in it
, members -- Says what group individual is in on Ddate
where cycles.cid = cycpoints.cid -- Gives Ddate an Sname
and cycles.sname = members.sname
and cycpoints.date = members.date -- Gets group on Ddate
-- Try a sample time period to see if query works
-- remove this later to get complete results.
and cycpoints.date between '2000-01-01' and '2005-12-31'
and cycpoints.code = 'D' -- only look at Ddates
group by members.grp -- Within group
, year -- summarize first by year
, month -- and within year by month.
-- (Note that the grouping order is not the same
-- as the display order.)
; -- (end of query)
Karl <kop at meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
P.S. Susan, you're writing email in HTML again.
More information about the Babase
mailing list