[Babase] POTENTIAL_DADS view can be tested-ish

Susan Alberts alberts at duke.edu
Sun Oct 17 21:31:47 EDT 2010


thanks, i love it. probably the column age_days should be called  
pdad_age_days and the column age_years should be pdad_age_years, just  
to avoid confusion. I know the names are long but I think clarity is  
good here, as there are three animals in each row that age could refer  
to (in theory).
Susan

On Oct 17, 2010, at 5:22 PM, Karl O. Pinc wrote:

> 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
>
>
> _______________________________________________
> 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






More information about the Babase mailing list