[Babase] Views and display of group membership
Niki Learn
nlearn at princeton.edu
Wed Oct 6 16:09:22 EDT 2010
Karl wrote:
mtd_cycles is going to need 3 group columns, one for the
Mdate, one for the Tdate, and one for the Ddate.
Niki writes:
Hmm... I guess during a split females could change group mid-cycle... But
normally that would not happen. Could we maybe just have group as of the
ddate and then if someone really needs to know the group during a different
part of the cycle - and is actually working in a time period where it could
be different (which doesn't seem to happen very often) - then they could use
members?
Btw, most of the missing ones from query 2 are actually just ones that were
conceived in 10a and not born by the end of June but ORI3 was never entered
into biograph in 2007 when his/her mother died. I'll need to fix that.
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
_______________________________________________
Babase mailing list
Babase at www.eco.princeton.edu
http://www.eco.princeton.edu/mailman/listinfo/babase
More information about the Babase
mailing list