Pausas

Este gráfico muestra las ejecuciones a lo largo del tiempo. Observa que ha habido varios periodos prolongados sin ejecuciones. Nuestro objetivo es averiguar exactamente cuándo ocurrieron y estudiar sus causas.

Nuestra estrategia es preparar la tabla de modo que cada fila contenga también la fecha de la ejecución anterior. Luego podemos calcular la diferencia entre ambas fechas, ordenarlas en orden descendente y leer las pausas más largas.

Pensando en JOINs

Ninguna de las técnicas que hemos aprendido hasta ahora es suficiente aquí. Nuestra tabla deseada tiene la misma longitud que la tabla original executions, por lo que podemos descartar agregaciones que producen una tabla más pequeña. El capítulo Beazley solo enseñó operaciones a nivel de fila que limitan a trabajar con información ya en las filas. Sin embargo, la fecha de la ejecución previa está fuera de una fila, así que tenemos que usar JOIN para traer esa información adicional.

Supongamos que la información adicional que queremos existe en una tabla llamada previous que tiene dos columnas (ex_number, last_ex_date). Podríamos ejecutar la siguiente consulta para completar nuestra tarea:

SELECT
  last_ex_date AS start,
  ex_date AS end,
  ex_date - last_ex_date AS day_difference
FROM executions
JOIN previous
  ON executions.ex_number = previous.ex_number
ORDER BY day_difference DESC
LIMIT 10

El bloque JOIN es el foco de esta sección. En lugar de verlo como una línea aislada, a menudo es útil visualizarlo así: Esto enfatiza cómo JOIN crea una gran tabla combinada que luego se alimenta al bloque FROM como cualquier otra tabla.

Tipos de joins

El bloque JOIN tiene la forma <table1> JOIN <table2> ON <clause>. La cláusula funciona igual que en WHERE <clause>. Es decir, es una expresión que se evalúa como verdadera o falsa y cada vez que una fila de la primera tabla y otra de la segunda coinciden con la cláusula siendo verdadera, las dos se emparejan:

Pero, ¿qué sucede con las filas que no tienen coincidencias? En este caso, la tabla previous no tenía una fila para el número de ejecución 1 porque no hay ejecuciones anteriores.

El comando JOIN por defecto realiza lo que se llama un “inner join” en el que las filas no coincidentes se descartan.

Para preservar todas las filas de la tabla izquierda, usamos un LEFT JOIN en lugar del JOIN simple. Las partes vacías de la fila se dejan tal cual, lo que significa que se evalúan como NULL.

El RIGHT JOIN se puede usar para preservar filas no coincidentes en la tabla de la derecha, y el OUTER JOIN para preservar filas en ambas.

La sutileza final es el manejo de múltiples coincidencias. Si tenemos una tabla duplicated_previous con dos copias de cada fila de previous, cada fila de executions ahora coincide con dos filas en duplicated_previous. El join crea suficientes filas de executions para que cada fila coincidente de duplicated_previous tenga su propia pareja. Así, los joins pueden crear tablas más grandes que sus tablas de origen.

Fechas

Tomemos un descanso de los joins y veamos esta línea en nuestra consulta plantilla:

ex_date - last_ex_date AS day_difference

Hemos hecho una gran suposición al pedir que se resten fechas. Imagina que eres la computadora recibiendo una línea así: ¿devuelves el número de días entre las fechas? ¿Horas o segundos? Para empeorar las cosas, SQLite no tiene tipos de fecha y hora formales (a diferencia de otros dialectos), así que ex_date y last_ex_date se tratan como cadenas. Estaríamos intentando hacer 'hello' - 'world'. ¿Qué significa eso?

Afortunadamente, SQLite tiene funciones para indicar que ciertas cadenas contienen fechas y que deben tratarse como tales.

Self Joins

Con lo aprendido sobre fechas, podemos corregir nuestra consulta plantilla:

SELECT
  last_ex_date AS start,
  ex_date AS end,
  JULIANDAY(ex_date) - JULIANDAY(last_ex_date)
    AS day_difference
FROM executions
JOIN previous
  ON executions.ex_number = previous.ex_number
ORDER BY day_difference DESC
LIMIT 5

El siguiente paso es construir la tabla previous.

Ahora podemos anidar esta consulta en la plantilla anterior:

previous se deriva de executions, así que en efecto estamos juntando executions consigo misma. Esto se llama “self join” y es una técnica poderosa para que filas obtengan información de otras filas de la misma tabla.

Hemos creado previous para clarificar su propósito, pero podemos escribir la consulta de forma más elegante haciendo el join directamente con otra copia de executions.

Ahora podemos usar las fechas precisas de las pausas para investigar qué ocurrió en cada periodo. En los años inmediatamente posteriores a la restauración de la pena de muerte hubo largos periodos sin ejecuciones debido al bajo número de condenas y a desafíos legales. Nos centraremos en dos pausas principales desde 1993.

La Pausa 1 fue debida a desafíos legales a la Ley Antiterrorismo y Efectiva Pena de Muerte de 1996, aprobada tras los atentados de 1993 y 1995. La ley limitó procesos de apelación con el fin de hacer la pena de muerte más efectiva en ciertos casos.

La Pausa 2 fue causada por una suspensión decretada por la Corte Suprema mientras evaluaba Baze v. Rees, que examinó si la inyección letal violaba la Octava Enmienda. Esto afectó las ejecuciones en todo el país porque muchos estados usaban la misma combinación de fármacos. La Corte Suprema finalmente ratificó la decisión de Kentucky y las ejecuciones en Texas se reanudaron meses después.

Resumen

La idea central detrás de los JOINs ha sido crear una tabla aumentada porque la original no contenía la información que necesitábamos. Esto permite liberarnos de las limitaciones de una sola tabla y combinar múltiples tablas de maneras complejas. También hemos visto que con esta complejidad extra es importante llevar una contabilidad meticulosa: alias de tablas, renombrar columnas y definir buenas cláusulas JOIN ON ayudan a mantener el orden.