[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