[Babase] POTENTIAL_DADS view can be tested-ish

Susan Alberts alberts at duke.edu
Sun Oct 17 14:09:07 EDT 2010


On Oct 17, 2010, at 2:06 PM, Karl O. Pinc wrote:

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

OK thanks
>
>>>
>>> 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.

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

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

OK I will do some querying to test and see.

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






More information about the Babase mailing list