[Babase] Views and display of group membership
Niki Learn
nlearn at princeton.edu
Wed Oct 6 15:11:08 EDT 2010
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
More information about the Babase
mailing list