[Babase] Views and display of group membership

Karl O. Pinc kop at meme.com
Wed Oct 6 15:33:01 EDT 2010


You want the group off of members for the given individual on the given 
date.


 select mtd_cycles.sname, mtd_cycles.ddate, extract(year from
 mtd_cycles.ddate) as year, hydroyear(mtd_cycles.ddate) as hydroyr,
 extract(month from mtd_cycles.ddate) as month, pregs.pid, 
 pregs.resume,
     members.grp
 from
 mtd_cycles, pregs,
    members
 where mtd_cycles.dcpid = pregs.conceive and
 mtd_cycles.ddate > '1999-10-31'

 and members.date = mtd_cycles.ddate and members.sname = 
mtd_cycles.sname

 order by ddate;

Of course this gives the group as of the ddate.  You may
want the group as of some other date, or the first of the
month, or....

mtd_cycles is going to need 3 group columns, one for the
Mdate, one for the Tdate, and one for the Ddate.

On 10/06/2010 02:11:08 PM, Niki Learn wrote:
> Karl,
> 
> I have been very happily using the extra group column in several
> tables.
> But now I am stuck with no extra group column in mtd_cycles.  Is that
> column
> coming soon?  I see there doesn't seem to be a group column for any 
> of
> the
> tables at the very bottom of this email but I remember when we
> discussed
> this, Susan pointed out just ahead of me that females do change 
> groups
> when
> there are splits.
> 
> See, I had this query (sorry, I'm not human readable):
> 
> select mtd_cycles.sname, mtd_cycles.ddate, extract(year from
> mtd_cycles.ddate) as year, hydroyear(mtd_cycles.ddate) as hydroyr,
> extract(month from mtd_cycles.ddate) as month, pregs.pid, 
> pregs.resume
> from
> mtd_cycles, pregs where mtd_cycles.dcpid = pregs.conceive and
> mtd_cycles.ddate > '1999-10-31' order by ddate;
> 
> and I wanted to add group to it so I tried this:
> 
> select mtd_cycles.sname, mtd_cycles.ddate, extract(year from
> mtd_cycles.ddate) as year, hydroyear(mtd_cycles.ddate), extract(month
> from
> mtd_cycles.ddate) as month, pregs.pid, pregs.resume, biograph.sname 
> as
> kidname, matgrp from mtd_cycles, pregs, biograph where
> mtd_cycles.dcpid =
> pregs.conceive and pregs.pid = biograph.pid and mtd_cycles.ddate >
> '1999-10-31' order by ddate;
> 
> but that gave me only 482 rows instead of 537, seemingly due to
> females who
> disappeared while pregnant whose infants were not given biograph
> entries (at
> least that's the discrepancy I've found so far), and I need to 
> include
> those
> missing females.
> 
> Yet, if I do this (and get all 537 rows):
> 
> select mtd_cycles.sname, mtd_cycles.ddate, extract(year from
> mtd_cycles.ddate) as year, hydroyear(mtd_cycles.ddate), extract(month
> from
> mtd_cycles.ddate) as month, pregs.pid, pregs.resume, matgrp from
> mtd_cycles,
> pregs, biograph where mtd_cycles.dcpid = pregs.conceive and
> mtd_cycles.sname
> = biograph.sname and mtd_cycles.ddate > '1999-10-31' order by ddate;
> 
> I end up with a bunch of females whose matgrp was one of the pre-
> split
> groups and there are way too many of them to want to correct them...  
> 
> Thanks,
> Niki
> 
> -----Original Message-----
> From: babase-bounces at eeblistserv.Princeton.EDU
> [mailto:babase-bounces at eeblistserv.Princeton.EDU] On Behalf Of Karl 
> O.
> Pinc
> Sent: Thursday, August 26, 2010 2:35 PM
> To: babase at eeblistserv.Princeton.EDU
> Subject: [Babase] Views and display of group membership
> 
> Hi,
> 
> Here's a quick document on my plans regards
> views and group membership so we can move things
> forward.
> 
> There are a number of tables that have Snames
> and dates for which a view can be made to
> make it easier to find the group of the individual
> on the given date.  There are two sorts of these
> tables, those with their own id and those that
> use Sname.  A good example of the former is the
> DARTINGS table.  (I've created the proposed
> views so you can try out the queries below
> as you read.)
> 
> I propose a view, DARTINGS_GRP, that contains
> the following 2 columns: Dartid and Grp.
> 
> 
> You would use this view in a query such as the following:
> 
> SELECT *
>  FROM dartings, dartings_grp
>  WHERE dartings_grp.dartid = dartings.dartid;
> 
> 
> The ER diagram for the view looks like:
> 
> http://papio.biology.duke.edu/diagrams/dartings_grp.png
> 
> 
> This would be the definition of the view:
> 
> SELECT dartings.dartid AS dartid
>      , members.grp AS grp
>   FROM members, dartings
>   WHERE members.sname = dartings.sname
>         AND members.date = CAST(dartings.dartdaytime AS DATE);
> 
> 
> The views for tables keyed by sname are similar,
> so similar that you don't really care about
> the difference.
> Here's a proposed view, STATDATE_GRP, that has
> the group as of the individual's Statdate.
> The columns are: Sname and Grp.
> 
> 
> You would use this view in a query such as the following:
> 
> SELECT *
>   FROM biograph, statdate_grp
>   WHERE statdate_grp.sname = biograph.sname;
> 
> 
> The ER diagram for the view looks like:
> 
> http://papio.biology.duke.edu/diagrams/statdate_grp.png
> 
> 
> This would be the definition of the view:
> 
> SELECT biograph.sname AS sname
>      , members.grp AS grp
>   FROM members, biograph
>   WHERE members.sname = biograph.sname
>         AND members.date = CAST(biograph.statdate AS DATE)
> 
> 
> The views I propose are:
> 
> birth_grp
> statdate_grp
> consortdates_grp
> cycgapdays_grp
> cycgaps_grp
> cycstats_grp
> dartings_grp
> dispersedates_grp
> maturedates_grp
> mdintervals_grp
> mmintervals_grp
> pcskins_grp
> rankdates_grp
> ranks_grp
> repstats_grp
> samples_grp
> 
> Of these birth.grp is included more for completeness than
> anything else, there already being a Matgrp column.
> The ranks.grp view may likewise be of limited utility
> given that the date is forced to the first of the month.
> Pcskins_grp is also may not belong or be useful.
> I have also cleverly excluded CENSUS from the list,
> although that may be too clever by half because
> you might be interested in querying census
> information where individuals were found to be absent.
> 
> The alert reader will have noticed that all the
> proposed new view names end in _GRP.  This is to
> facilitate automatic generation of the views and
> virtually all their documentation.  The code
> for this automatic generation is mostly complete.
> 
> The alternative to this sort of standardized approach
> is to create a view for each of the above tables that
> completely re-creates the table but adds a Grp column.
> This is less easy to automate and annoying to document
> and maintain over time.
> 
> There is one table left over that does not really show
> up in an existing view and does not have both
> an sname and a date in the same table so does not
> fit into the above model.  That's NEIGHBORS,
> which would need a new view to retrieve group.
> 
> The remaining question is what to do with the
> existing views that could display group membership.
> My thought was to, where possible, follow the
> approach taken above -- treat the existing view
> as "just another existing table" and make a new
> view ending in _GRP.  The advantage here is
> consistency with views proposed above.  Susan 
> has suggested that we just
> add a Grp column to the existing views, which
> has the advantage of being really easy to use.
> 
> I'm inclined to think that if you really always
> want to show the group there's little point in making
> a separate view and we may as well add a Grp column
> to the existing views.   It's a bit more work to
> set up but no harder to maintain over time.
> I think we can ignore performance issues, if any,
> unless and until we find there is a problem.
> 
> Here's a list of the existing views that could
> use one or more Grp columns:
> 
> ACTOR_ACTEES
> ACTOR_ACTEES_EX
> ACTOR_ACTEES_EX_SORTED
> ACTOR_ACTEES_SORTED
> CYCLES_SEXSKINS
> CYCLES_SEXSKINS_SORTED
> CYCPOINTS_CYCLES
> CYCPOINTS_CYCLES_SORTED
> MATERNATIES
> MPI_EVENTS
> MTD_CYCLES
> PCSKINS_SORTED
> SEXSKIN_CYCLES
> SEXSKIN_CYCLES_SORTED
> 
> Of these MATERNATIES, MTD_CYCLES PCSKINS_SORTED,
> SEXSKIN_CYCLES, and SEXSKIN_CYCLES_SORTED are all
> questionable because females don't change groups.
> Census related views are also excluded from the
> above.
> 
> 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
> 
> _______________________________________________
> 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