[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