Interruzioni

Questo grafico mostra le esecuzioni nel tempo. Puoi notare che ci sono stati diversi lunghi periodi in cui non hanno avuto luogo esecuzioni. Il nostro obiettivo è capire esattamente quando si sono verificati e ricercarne le cause.

La nostra strategia è portare la tabella in uno stato in cui ogni riga contiene anche la data di esecuzione precedente. Possiamo quindi trovare la differenza tra le due date, ordinarle in ordine decrescente e leggere le pause più lunghe.

Pensando ai Join (unioni)

Nessuna delle tecniche che abbiamo imparato finora è adeguata in questo caso. La tabella desiderata ha la stessa lunghezza della tabella executions, quindi possiamo escludere aggregazioni che producono una tabella più piccola. Il capitolo Beazley ci ha insegnato solo le operazioni sulle righe che ci limitano a lavorare con le informazioni già presenti nelle righe. Poiché la data dell’esecuzione precedente si trova all’esterno di una determinata riga, dobbiamo utilizzare “JOIN” per inserire informazioni aggiuntive.

Supponiamo che le informazioni aggiuntive che vogliamo siano in una tabella chiamata previous che ha due colonne (ex_number, last_ex_date). Saremmo in grado di eseguire la seguente query per completare la nostra attività:

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

In questa sezione ci soffermeremo sul blocco JOIN. Invece di vederla come una riga di codice a sé stante, spesso è utile vederla in questo modo: Così si enfaticca come JOIN crea una grande tabella combinata che viene poi inserita nel blocco FROM esattamente come qualsiasi altra tabella.

Tipi di Join (unione)

Il blocco JOIN prende la forma di <tabella1> JOIN <tabella> ON <condizione>. La condizione funziona allo stesso modo che in WHERE <condizione>. Si tratta di una istruzione che dà come risultato vero o falso e ogni volta che una riga della prima tabella e un’altra della seconda rendono vera la condizione, le due righe vengono unite:

Ma cosa succede alle righe che non hanno corrispondenze? In questo caso, la tabella previous non ha una riga per l’esecuzione numero 1 perché non ci sono esecuzioni precedenti ad essa.

Il comando JOIN per impostazione predefinita esegue quello che viene chiamato “inner join”: eliminate le righe vengono eliminate.

Per preservare tutte le righe della tabella di sinistra, usiamo un LEFT JOIN al posto del comune JOIN. Le parti vuote della riga vengono lasciate intatte: saranno valutate come valori NULL.

Il RIGHT JOIN può essere usato per preservare le righe senza corrispondenza nella tabella di destra, e OUTER JOIN per preservare le righe senza corrispondenza di entrambe le tabelle.

L’ultima sottigliezza gestisce più casi. Pensiamo di avere una tabella duplicated_previous che contine due copie di ciascuna riga della tabella previous. Ciascuna riga di executions ora corrisponde a due righe in duplicated_previous. Il join crea abbastanza righe di executions in modo che ogni riga corrispondente di duplicated_previous abbia il proprio partner. In questo modo, i join possono creare tabelle che sono più grandi dei loro costituenti.

Date

Prendiamoci una pausa dai join e osserviamo questa riga nel nostro modello di query:

  ex_date - last_ex_date AS day_difference

Abbiamo dato per scontato che possiamo sottrarre le date l’una dall’altra. Ma immagina di essere il computer che riceve una riga come questa. Restituirai il numero di giorni tra le date? Perché non le ore o i secondi? A peggiorare le cose, SQLite in realtà non ha tipi di data o ora (a differenza della maggior parte degli altri dialetti SQL) quindi le colonne ex_date e last_ex_date ti appaiono come normali stringhe. È come se ti venisse chiesto di fare 'hello' - 'world'. Cosa significa?

Fortunatamente, SQLite contiene una serie di funzioni per dire al computer: “Ehi, queste stringhe che ti sto passando contengono effettivamente date o ore. Agisci su di loro come faresti con una data.”

Self Join

Con ciò che abbiamo imparato sulle date, possiamo correggere la nostra query modello:

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

Il prossimo passo è costruire la tabella previous.

Ora possiamo annidare questa query all’interno del modello sopra:

previous è derivata da executions, quindi stiamo eseguendo un join di executions su sé stessa. Questo viene chiamato “self join” ed è una potente tecnica per consentire alle righe di ottenere informazioni da altre parti della stessa tabella.

Abbiamo creato la tabella previous per chiarire a cosa serve. Tuttavia possiamo anche scrivere la query in modo più eleganteeseguendo il join della tabella executions direttamente su sé stessa.

Ora possiamo utilizzare le date precise delle interruzioni per ricercare cosa è successo in ciascun periodo. Negli anni immediatamente successivi alla revoca del divieto della pena capitale, si sono verificati lunghi periodi senza esecuzioni a causa del basso numero di condanne a morte, oltre alle sfide legali contro la nuova legge. Escludiamo quindi gli intervalli precedenti al 1993 e ci concentriamo sulle due principali interruzioni successive.

La prima pausa è dovuta a contestazioni legali al Antiterrorism and Effective Death Penalty Act del 1996 creato in risposta agli attentati del World Trade Center del 1993 e a Oklahoma City del 1995. La legge ha limitato il processo di appello per rendere la pena di morte più efficace soprattutto per i casi di terrorismo (Source).

La seconda pausa è stata causata da una sospensione emanata dalla Corte Suprema mentre valutava la sentenza Baze v. Rees che ha esaminato se l’iniezione letale viola l’ottavo emendamento che proibisce “punizioni crudeli e insolite”. Questo ha influenzato le esecuzioni in tutta l’America perché la maggior parte degli stati utilizzava lo stesso cocktail di farmaci del Kentucky. La Corte Suprema alla fine confermò la decisione del tribunale del Kentucky e le esecuzioni in Texas ripresero pochi mesi dopo.

Riepilogo

L’idea dietro i JOIN è stata quella di creare una tabella aumentata perché l’originale non conteneva le informazioni di cui avevamo bisogno. Questo concetto è potente perché ci libera dalle limitazioni di una singola tabella e ci consente di combinare più tabelle in modi potenzialmente complessi. Abbiamo anche visto che con questa complessità aggiuntiva, una verifica meticolosa diventa importante. Creare alias di tabelle, ridenominare le colonne e definire buone condizioni di JOIN ON sono tutte tecniche che ci aiutano a mantenere l’ordine.