L’innocence possible
Opponents of the death penalty have argued that the risk of mistakenly executing an innocent person is too great a cost to bear. In this chapter, we attempt to approximate how many innocent people may have been executed.
Les opposants à la peine de mort ont fait valoir que le risque d’exécution par erreur d’une personne innocente est trop pour être supporté. Dans ce chapitre, nous essayons de déterminer approximativement le nombre d’innocents qui ont été exécutés. La principale mise en garde est qu’une affirmation d’innocence, même si elle est faite au seuil de mort, ne constitue pas l’innocence. De plus, même si le détenu est véridique, il existe de nombreuses interprétations de l’innocence : on aurait accusé le détenu d’avoir tué deux personnes mais n’en a tué qu’une seule; ou il aurait pu tuer le spectateur mais pas le policier. Ce ne sont pas seulement des caprices : au Texas, le meurtre seul ne justifie pas la peine de mort. Le détenu doit avoir commis un capital crime comme le meurtre d’un agent de la sécurité publique ou de plusieurs personnes. Par conséquent, le détenu peut être innocent au sens strict du droit, mais peut-être pas selon les normes morales courantes.
Néanmoins, il y a encore quelque chose d’inquiétant à propos des allégations d’innocence persistant jusqu’au seuil de l’exécution alors qu’il reste peu à gagner. Notre tâche ici est de déterminer la fréquence de cette situation en calculant la proportion des dernières paroles pour lesquelles il y a une allégation d’innocence.
Les fonctions d’agrégation
Il y a deux chiffres dont nous avons besoin pour calculer la proportion :
Numérateur : Nombre d’exécutions avec allégation d’innocence.
Dénominateur : Nombre d’exécutions au total.
Jusqu’à présent, chaque ligne de la sortie provenait d’une seule ligne d’entrée. Cependant, nous avons ici le numérateur et le dénominateur qui nécessitent des informations provenant de plusieurs lignes d’entrée. Cela nous indique que nous devons utiliser une fonction d’agrégation. À « agréger » signifie combiner plusieurs éléments en un tout. De même, les fonctions d’agrégation prennent plusieurs lignes de données et les combinent en un seul nombre.
La fonction COUNT
COUNT (litt. « compter ») est probablement la fonction d’agrégation la plus utilisée. Comme son nom l’indique, elle compte les choses ! Par exemple, COUNT(<column>) renvoie le nombre de lignes non nulles dans la colonne.
Comme vous pouvez le constater, la fonction COUNT est intrinsèquement liée au concept de NULL. Faisons une petite digression pour en apprendre davantage sur les NULLs.
Les NULLs
En SQL, NULL est la valeur d’une entrée vide. Ceci est différent de la chaîne vide '' et du nombre 0, qui ne sont pas considérés comme NULL. Pour vérifier si une entrée est NULL, utilisez IS (litt. « est ») et IS NOT (litt. « n'est pas ») à la place de = et !=.
Avec cela, nous pouvons trouver le dénominateur de notre proportion :
Les variations de COUNT
Jusque là tout va bien. Mais que se passe-t-il si nous ne savons pas quelles colonnes sont sans des NULLs? Pire encore, qu’arrive-t-il si aucune des colonnes n’est sans NULL? Il doit bien y avoir un moyen de trouver la longueur de la table !
La solution est COUNT(*). Cela rappelle SELECT * où le * représente toutes les colonnes. En pratique, COUNT(*) compte les lignes tant qu’une de ses colonnes est non-null. Cela nous aide à trouver les longueurs de table car une table ne devrait pas avoir de lignes qui sont remplies des nulls.
Une autre variation courante consiste à compter un sous-ensemble du table. Par exemple, le comptage des exécutions dans le comté de Harris. Nous pourrions exécuter SELECT COUNT(*) FROM executions WHERE county='Harris' qui filtre vers le bas pour un plus petit ensemble de données composé d’exécutions Harris et compte ensuite toutes les lignes. Mais si nous voulons trouver simultanément le nombre d’exécutions dans le comté de Bexar ?
La solution est d’appliquer un bloc CASE WHEN qui agit comme une grande instruction if-else. Il a deux formats et celui que j’aime est :
CASE
WHEN <clause> THEN <result>
WHEN <clause> THEN <result>
...
ELSE <result>
END
C’est certes l’une des parties les plus maladroites de SQL. Une erreur courante est de rater la commande END (litt. « finir ») et la condition ELSE (litt. « autrement ») qui sont les fourre-tout dans le cas où toutes les clauses précédentes sont fausses. Rappelez-vous également du chapitre précédent que les clauses sont des expressions qui peuvent être évaluées comme étant vraies ou fausses. Cela rend important de penser à la valeur booléenne de ce que vous y mettez.
Pratiquer
Il est utile de prendre du recul et de réfléchir aux différentes façons dont l’ordinateur a traité ces trois requêtes. La version WHERE a filtré vers une petite table avant de l’agréger, tandis que dans les deux autres instructions, elle devait regarder la table complète. Dans la version COUNT + CASE WHEN, il n’a dû passer qu’une seule fois, alors que la version double COUNT l’a fait passer deux fois. Donc même si la sortie était identique, les performances étaient probablement meilleures dans la première version et pires dans la troisième.
Recherche de documentation
On n’a pas conçu ce livre pour être une référence complète du langage SQL. Pour cela, vous devrez chercher d’autres ressources en ligne. C’est une compétence en soi, et qui vaut la peine d’être maîtrisée parce que vous allez chercher de la documentation des années après avoir acquis une familiarité avec la langue.
La bonne nouvelle est qu’avec les modèles mentaux que vous apprendrez dans ce livre, les recherches devraient être rapides et sans peine car vous ne ferez que vérifier des détails comme si la fonction s’appelle AVERAGE ou AVG au lieu de déterminer quelle approche prendre.
Pour les recherches, j’utilise souvent W3 Schools, Stack Overflow et la documentation officielle SQLite.
SELECT DISTINCT n’est pas vraiment une fonction d’agrégation car elle ne renvoie pas un seul nombre et parce qu’elle fonctionne sur la sortie de la requête plutôt que sur la table sous-jacente. Néanmoins, je l’ai inclus ici parce qu’il partage une caractéristique commune de fonctionnement sur plusieurs lignes.
Une requête bizarre
Avant de conclure, jetons un coup d’œil à cette requête :
SELECT first_name, COUNT(*) FROM executions.
Ça fait bizarre n’est-ce pas ? Si vous avez un bon modèle mental d’agrégation, ça devrait ! COUNT(*) tente de renvoyer une seule entrée correspondant à la longueur de la table d’exécution. La colonne first_name essaie de renvoyer une entrée pour chaque ligne. Est-ce que l’ordinateur doit renvoyer une ou plusieurs lignes? S’il en renvoie une, quel first_name doit‐il choisir? Si elle renvoie plusieurs, est-ce qu’elle doit répliquer le résultat COUNT(*) sur toutes les lignes? Les formes de la sortie ne correspondent pas !
En pratique, les bases de données essaient de renvoyer quelque chose de sensé même si vous passez dans le non-sens. Dans ce cas, notre base de données choisit le premier prénom (first name) à partir de la dernière entrée de notre table. Puisque notre tableau est en ordre chronologique inverse, la dernière entrée est Charlie Brooks Jr., la première personne exécutée depuis que la Cour suprême a levé l’interdiction de la peine de mort. Différentes bases de données traiteront ce cas différemment, il est donc préférable de ne pas compter sur leur comportement par défaut. Si vous savez que vous voulez la dernière entrée, il faut que vous la trouviez explicite. Beaucoup de dialectes SQL ont une fonction d’agrégation LAST (litt. « dernière ») qui rend ceci trivial. Malheureusement SQLite ne le fait pas, donc une solution de contournement est nécessaire.
Les dialectes SQL et les bases de données
Bien que nous ayons appelé cela un livre sur SQL, si nous voulons être pédants c’est vraiment un livre sur SQLite. En effet, le SQL est un concept imaginaire : un idéal platonique. En réalité, il n’existe que des dialectes qui tentent de se conformer aux spécifications du SQL.
SQL est également sous-spécifié, c’est-à-dire que certaines fonctionnalités ne sont pas spécifiées par les normes. Par exemple, les normes ne disent pas si la fonction de recherche de longueur de chaîne doit être appelée LEN (SQL Server) ou LENGTH (SQLite); ni comment des identificateurs comme les noms de table ou de colonne doivent être mis en guillemets (` dans MySQL, " dans SQLite)
Pour aggraver les choses, même une seule requête dans un seul dialecte peut être traitée différemment parce que les bases de données sous-jacentes peuvent avoir des architectures différentes. Par exemple, le dialecte PostgreSQL peut être utilisé sur des bases de données distribuées sur plusieurs machines physiques différentes, et celles qui ne sont constituées que d’un seul fichier. Cela signifie que les modèles mentaux que nous développons ici ne sont qu’une béquille. Ils peuvent ne pas refléter exactement ce que fait la base de données.
Nous avons choisi SQLite, qui est à la fois un dialecte et une implémentation, car c’est l’une des bases de données les plus courantes. Nous avons également essayé de nous concentrer sur les fonctionnalités de base et le modèle mental de SQL plutôt que sur des parties distinctes de SQLite. Avec un modèle mental robuste en place, il est facile de basculer entre les dialectes SQL et les bases de données.
Les conclusions et le résumé
Utilisons ce que nous avons appris jusqu’à présent pour accomplir notre tâche :
Cette méthode de détermination des allégations d’innocence est certes plutôt inexacte, car l’innocence peut être exprimée en d’autres termes comme « non coupable ». Néanmoins, je soupçonne qu’il sous-estime le nombre réel et est probablement de l’ordre de grandeur. La question qui nous reste alors est de savoir si nous sommes prêts à accepter la possibilité que jusqu’à 5 % des personnes que nous exécutons soient en fait innocentes. (Paul Graham refuse de le faire.)
Pour résumer, nous sommes passés des opérations au niveau de la ligne dans la section précédente à l’utilisation de fonctions d’agrégation sur plusieurs lignes dans le jeu de données. Cela a ouvert une voie pour étudier le comportement au niveau du système. Dans la section suivante, nous allons apprendre à appliquer des fonctions d’agrégation sur plusieurs sous-groupes de l’ensemble de données en utilisant le bloc GROUP BY.