Queries from the Twin Project
=============================

 Stockholm and the Section on Clinical Research in Aging and
Psychology at the University of Southern California.

 twins
who want to participate are visited by a doctor/nurse team. Some of the
examinations include physical and neuropsychological examination,
laboratory testing, neuroimaging, psychological status assessment, and
family history collection. In addition, data are collected on medical
and environmental risk factors.

More information about Twin studies can be found at:
`http://www.mep.ki.se/twinreg/index_en.html'

The latter part of the project is administered with a web interface
written using Perl and MySQL.

Each night all data from the interviews is moved into a MySQL database.

Find All Non-distributed Twins
------------------------------

The following query is used to determine who goes into the second part
of the project:

     SELECT
             CONCAT(p1.id, p1.tvab) + 0 AS tvid,
             CONCAT(p1.christian_name, " ", p1.surname) AS Name,
             p1.postal_code AS Code,
             p1.city AS City,
             pg.abrev AS Area,
             IF(td.participation = "Aborted", "A", " ") AS A,
             p1.dead AS dead1,
             l.event AS event1,
             td.suspect AS tsuspect1,
             id.suspect AS isuspect1,
             td.severe AS tsevere1,
             id.severe AS isevere1,
             p2.dead AS dead2,
             l2.event AS event2,
             h2.nurse AS nurse2,
             h2.doctor AS doctor2,
             td2.suspect AS tsuspect2,
             id2.suspect AS isuspect2,
             td2.severe AS tsevere2,
             id2.severe AS isevere2,
             l.finish_date
     FROM
             twin_project AS tp
             /* For Twin 1 */
             LEFT JOIN twin_data AS td ON tp.id = td.id
                       AND tp.tvab = td.tvab
             LEFT JOIN informant_data AS id ON tp.id = id.id
                       AND tp.tvab = id.tvab
             LEFT JOIN harmony AS h ON tp.id = h.id
                       AND tp.tvab = h.tvab
             LEFT JOIN lentus AS l ON tp.id = l.id
                       AND tp.tvab = l.tvab
             /* For Twin 2 */
             LEFT JOIN twin_data AS td2 ON p2.id = td2.id
                       AND p2.tvab = td2.tvab
             LEFT JOIN informant_data AS id2 ON p2.id = id2.id
                       AND p2.tvab = id2.tvab
             LEFT JOIN harmony AS h2 ON p2.id = h2.id
                       AND p2.tvab = h2.tvab
             LEFT JOIN lentus AS l2 ON p2.id = l2.id
                       AND p2.tvab = l2.tvab,
             person_data AS p1,
             person_data AS p2,
             postal_groups AS pg
     WHERE
             /* p1 gets main twin and p2 gets his/her twin. */
             /* ptvab is a field inverted from tvab */
             p1.id = tp.id AND p1.tvab = tp.tvab AND
             p2.id = p1.id AND p2.ptvab = p1.tvab AND
             /* Just the sceening survey */
             tp.survey_no = 5 AND
             /* Skip if partner died before 65 but allow emigration (dead=9) */
             (p2.dead = 0 OR p2.dead = 9 OR
              (p2.dead = 1 AND
               (p2.death_date = 0 OR
                (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
                 >
             (td.future_contact = 'Yes' AND td.suspect = 1
                                        AND id.suspect = 1) OR
             /* No twin - Informant is Blessed */
             (ISNULL(td.suspect) AND id.suspect = 1
                                 AND id.future_contact = 'Yes') OR
             /* Twin broken off - Informant is Blessed */
             (td.participation = 'Aborted'
              AND id.suspect = 1 AND id.future_contact = 'Yes') OR
             /* Twin broken off - No inform - Have partner */
             (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                           AND p2.dead = 0))
             AND
             l.event = 'Finished'
             /* Get at area code */
             AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
             /* Not already distributed */
             AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
             /* Has not refused or been aborted */
             AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
             OR h.status = 'Died' OR h.status = 'Other')
     ORDER BY
             tvid;

Some explanations:
`CONCAT(p1.id, p1.tvab) + 0 AS tvid'
     We want to sort on the concatenated `id' and `tvab' in numerical
     order. Adding `0' to the result causes MySQL to treat the result
     as a number.

column `id'
     This identifies a pair of twins. It is a key in all tables.

column `tvab'
     This identifies a twin in a pair. It has a value of `1' or `2'.

column `ptvab'
     This is an inverse of `tvab'. When `tvab' is `1' this is `2', and
     vice versa. It exists to save typing and to make it easier for
     MySQL to optimise the query.

This query demonstrates, among other things, how to do lookups on a
table from the same table with a join (`p1' and `p2'). In the example,
this is used to check whether a twin's partner died before the age of
65. If so, the row is not returned.

All of the above exist in all tables with twin-related information. We
have a key on both `id,tvab' (all tables), and `id,ptvab'
(`person_data') to make queries faster.

On our production machine (A 200MHz UltraSPARC), this query returns
about 150-200 rows and takes less than one second.

The current number of records in the tables used above:
*Table*            *Rows*
`person_data'      71074
`lentus'           5291
`twin_project'     5286
`twin_data'        2012
`informant_data'   663
`harmony'          381
`postal_groups'    100

Show a Table of Twin Pair Status
--------------------------------

 in how many
pairs one twin is finished and the other refused, and so on.

     SELECT
             t1.event,
             t2.event,
             COUNT(*)
     FROM
             lentus AS t1,
             lentus AS t2,
             twin_project AS tp
     WHERE
             /* We are looking at one pair at a time */
             t1.id = tp.id
             AND t1.tvab=tp.tvab
             AND t1.id = t2.id
             /* Just the sceening survey */
             AND tp.survey_no = 5
             /* This makes each pair only appear once */
             AND t1.tvab='1' AND t2.tvab='2'
     GROUP BY
             t1.event, t2.event;

[Назад] [Содержание] [Вперед]