Encontrando Todos Gêmeos Não-distribuídos

A seguinte consulta é usada para determinar quem vai na segunda parte do projeto:

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)
 >= 65))))
 AND
 (
 /* Twin is suspect */
 (td.future_contact = 'Yes' AND td.suspect = 2) OR
 /* Twin is suspect - Informant is Blessed */
 (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;

Algumas explicações:

CONCAT(p1.id, p1.tvab) + 0 AS tvid
N queremos ordenar o id e o tvab concatenados na ordem numérica. Adicionando 0 ao resultado faz o MariaDB tratar o resultado como um número.
coluna id
Esta identifica um par de gêmeos. Ela é uma chave em todas as tabelas.
column tvab
Esta identifica um gêmeo em um par. Ela pode ter um valor de 1 ou 2.
column ptvab
Esta é o inverso de tvab. Quando tvab é 1 este campo é 2 e vice versa. Ela existe para poupar digitação e tornar mais fácil para o MariaDB otimizar a query.

Esta consulta demonstra, entre outras coisas, como fazer buscas em uma tabela a partir da mesma tabela com uma uniao (p1 e p2). No exemplo, isto é usado para conferir se um par de um gêmeo morreu antes de 65 anos. Se for verdade, a linha não é retornada.

Tudo acima existe em todas as tabelas com informações relacionada aos gêmeos. Nós temos uma chave em ambos id,tvab (todas as tabelas) e id,ptvab (person_data) para tornar as consultas mais rápidas.

Na nossa máquina de produção (Um UltraSPARC 200MHz), esta consulta retorna entre 150-200 linhas e gasta menos que um segundo.

O número atual de registros nas tabelas usadas acima:

Encontrando Todos Gêmeos Não-distribuídos

Retornar