[Babase] POTENTIAL_DADS view can be tested-ish
Karl O. Pinc
kop at meme.com
Sun Oct 17 14:06:09 EDT 2010
On 10/17/2010 12:51:32 PM, Susan Alberts wrote:
> > Hi Susan, Lacey,
> >
> > You can try out the POTENTIAL_DADS view.
>
> I have done so. very nice. Quite slow browsing when I first brought
> it
>
> up, but that might have been the connectiivty problems you and Ryan
> mentioned. On the other hand, it is a big query to build to view.
It takes about a minute and a half to process. The rest
of the time is ppa and delivering the data to the browser.
> >
> > I am still not happy with the age
> > computation. We can do things with
> > calendar years and months....
>
> I don't understand what you don't like or what you are proposing. I
> like the age columns.
Well, postgres can compute age in years based on calendar date.
I have not thought through the implications but I imagine that it
has a lot to do with whether you were born in a leap year and how
the rounding all works out.
>
> One thing that should be fixed is the count of days the male was
> present in the female's five-day window. Currently we have 6 possible
>
> days because I think you counted Z-day as well as the five days
> prior.
>
> We only want the five days prior.
Ok. Do you want a male to be a potential dad if they were
present on the zdate but not any of the 5 prior days?
>
> Thanks for sending the query, I have not looked through it in detail
> but will if you want.
You could. It's a lot simpler if you base it on the maternities
and actor_actee views but that makes it run slower. At least 1/3rd
longer and probably more like twice as slow. It may be
a false optimization to tweak it like this, more trouble in the
long run to maintain, but I've not figured that out yet.
This is the sort of syntax-ie stuff I'm thinking about.
(Note that the whole first series of joins, up until you
get to the pdad stuff, is a re-creation of maternities.)
Any thoughts on performance? If you ask for a particular
kid then it runs fast enough no matter how it's formulated.
>
> Susan
>
> >
> > FYI, here's the query (more or less, I keep fussing):
> >
> > SELECT kids.bioid AS bioid
> > , kids.sname AS kid
> > , zdates.date AS zdate
> > , kidmembers.grp AS grp
> > , pdads.sname AS pdad
> > , CASE
> > WHEN rankdates.ranked <= pdadmembers.date
> > THEN 'A'
> > WHEN maturedates.matured <= pdadmembers.date
> > THEN 'S'
> > ELSE 'O'
> > END
> > AS status
> > , pdadmembers.date - pdads.birth AS age_days
> > , trunc((pdadmembers.date - pdads.birth) / 365.25,
> > 1) AS age_years
> > , (SELECT count(*)
> > FROM members as dadmembers
> > WHERE dadmembers.sname = pdads.sname
> > AND dadmembers.grp = kidmembers.grp
> > AND dadmembers.date <= zdates.date
> > AND dadmembers.date >= zdates.date - 5)
> > AS estrous_presence
> > , (SELECT count(*)
> > FROM interact_data, parts AS actor, parts AS actee
> > WHERE actor.sname = pdads.sname
> > AND actee.sname = cycles.sname
> > AND interact_data.iid = actor.iid
> > AND interact_data.iid = actee.iid
> > AND interact_data.date <= zdates.date
> > AND interact_data.date >= zdates.date - 5
> > AND (interact_data.act = 'M'
> > OR interact_data.act = 'E'))
> > AS estrous_me
> > , (SELECT count(*)
> > FROM interact_data, parts AS actor, parts AS actee
> > WHERE actor.sname = pdads.sname
> > AND actee.sname = cycles.sname
> > AND interact_data.iid = actor.iid
> > AND interact_data.iid = actee.iid
> > AND interact_data.date <= zdates.date
> > AND interact_data.date >= zdates.date - 5
> > AND interact_data.act = 'C')
> > AS estrous_c
> >
> > FROM biograph as kids
> > JOIN pregs
> > ON (pregs.pid = kids.pid)
> > JOIN cycpoints AS zdates
> > ON (zdates.cpid = pregs.conceive)
> > JOIN cycles
> > ON (cycles.cid = zdates.cid)
> > JOIN members AS kidmembers
> > ON (kidmembers.date = zdates.date
> > AND kidmembers.sname = cycles.sname)
> > JOIN members AS pdadmembers
> > ON (pdadmembers.date = zdates.date
> > AND pdadmembers.grp = kidmembers.grp)
> > JOIN biograph AS pdads
> > ON (pdadmembers.sname = pdads.sname)
> > LEFT OUTER JOIN rankdates
> > ON (rankdates.sname = pdads.sname)
> > LEFT OUTER JOIN maturedates
> > ON (maturedates.sname = pdads.sname)
> >
> > WHERE pdads.sex = 'M'
> > AND trunc((pdadmembers.date - pdads.birth) / 365.25, 1) >= 6
> > AND pdads.sname
> > IN (SELECT dadmembers.sname
> > FROM members AS dadmembers
> > WHERE dadmembers.sname = pdads.sname
> > AND dadmembers.grp = kidmembers.grp
> > AND dadmembers.date <= zdates.date
> > AND dadmembers.date >= zdates.date - 5);
> >
> >
> > Karl <kop at meme.com>
> > Free Software: "You don't pay back, you pay forward."
> > -- Robert A. Heinlein
> >
> >
> > _______________________________________________
> > Babase mailing list
> > Babase at www.eco.princeton.edu
> > http://www.eco.princeton.edu/mailman/listinfo/babase
>
> ------------------------------------------------------------
> Susan Alberts, Professor of Biology, Duke University, Box 90338,
> Durham NC 27708. Phone 919-660-7272, FAX 919-660-7293
>
>
>
>
> _______________________________________________
> Babase mailing list
> Babase at www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase
>
>
Karl <kop at meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
More information about the Babase
mailing list