[Babase] POTENTIAL_DADS view can be tested-ish
Karl O. Pinc
kop at meme.com
Sun Oct 17 17:22:06 EDT 2010
On 10/17/2010 01:11:35 PM, Susan Alberts wrote:
> OK, it goes pretty fast when you query for a small subset. Very nice.
>
> We do want the Mom to be a column in there I realized. Just calling
> it
>
> Mom is fine.
Ok. Done.
> On Oct 17, 2010, at 2:09 PM, Susan Alberts wrote:
>
> >
> > On Oct 17, 2010, at 2:06 PM, Karl O. Pinc wrote:
> >
> >> On 10/17/2010 12:51:32 PM, Susan Alberts wrote:
> >>>> 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.
Note that there is no preset order to the results so, in theory,
browsing is dangerous. (As with any regular unordered table or
other unordered views data _could_ come back in any order.)
> >>
> >> 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 completely redid some of the structure. I will re-send the
query when I finish testing. It now will likely take a lot
longer to look at the entire view.
There was a serious bug -- the potential dad had to be there
on the zdate.
> >>
> >> 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.
> >
> > Ah I see. I think it is more important for us to be consistent
> > across different analyses, and I have always used 365.25 for years,
>
> > I think Jeanne uses months which can be calculated from the days
> > column. So my view is that we are OK on that column.
Ok. Good by me. Note that, I think, some of the database checks
use "real" calendar age. I.E. Born on the 31s? Then the 31st
is your birthday. I can see possibilities for age computations
to vary if dividing by 365.25 at the end of longer months, depending
on when birth happens in the leap year cycle. Maybe. Anyway,
a heads-up.
> >>
> >>>
> >>> 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?
> >
> > No
Ok. And we don't want MCE related counts then too.
But all kid/mom group membership and age and status is still
computed based on zdate. Right?
> >
> >>
> >>>
> >>> Thanks for sending the query, I have not looked through it in
> detail
> >>> but will if you want.
Eventually another pair of eyes would be good.
> >>
> >> 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.
> >
> > OK I will do some querying to test and see.
Is it typical you return the entire result set or use the
view as a table in another query? If not then it can
take twice as long and we don't really care.
> >
> > Susan
> >
> >>
> >>>
> >>> 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
> >>
> >>
> >> _______________________________________________
> >> 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
>
> ------------------------------------------------------------
> 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