Mogelijke onschuld
Tegenstanders van de doodstraf hebben geargumenteerd dat het risico van het per ongeluk executeren van een onschuldig persoon een te grote kostenpost is om te dragen. In dit hoofdstuk proberen we bij benadering vast te stellen hoeveel onschuldige mensen er geëxecuteerd zijn.
Het belangrijkste voorbehoud is dat een bewering van onschuld, zelfs als deze op iemands sterfbed wordt gedaan, geen onschuld inhoudt. Bovendien, zelfs als de gevangene de waarheid spreekt, zijn er vele interpretaties van onschuld: De gevangene kan beschuldigd zijn van de moord op twee mensen maar is slechts onschuldig aan de moord op één; of hij kan de omstander hebben gedood maar niet de agent. Dit zijn echter niet alleen maar spitsvondigheden: in Texas rechtvaardigt moord alleen niet de doodstraf. De gevangene moet een halsmisdaad begaan hebben, zoals het doden van een veiligheidsagent of meerdere mensen. De gevangene kan dus onschuldig zijn in strikte juridische zin, maar misschien niet volgens de gangbare morele normen.
Geaggregeerde functies
We hebben twee getallen nodig om de verhouding te berekenen:
Numerator: Number of executions with claims of innocence.
Denominator: Number of executions in total.
Tot nu toe is elke rij in de uitvoer afkomstig van een enkele rij invoer. Hier hebben we echter voor zowel de teller als de noemer informatie nodig uit meerdere invoerrijen. Dit vertelt ons dat we een aggregaatfunctie moeten gebruiken. “Samenvoegen” betekent meerdere elementen combineren tot een geheel. Op dezelfde manier nemen aggregatiefuncties meerdere rijen gegevens en combineren deze tot één getal.
De COUNT-functie
COUNT is waarschijnlijk de meest gebruikte aggregatiefunctie. Zoals de naam al zegt, telt deze functie dingen! COUNT(<kolom>) geeft bijvoorbeeld het aantal niet-nul rijen in de kolom.
Zoals je kunt zien, is de COUNT-functie intrinsiek verbonden met het concept van NULLs. Laten we een kleine uitweiding maken om meer te leren over NULLs.
Nullen
In SQL is NULL de waarde van een lege invoer. Dit is anders dan de lege tekenreeks '' en het gehele getal 0, die beide niet als NULL worden beschouwd. Om te controleren of een invoer NULL is, gebruik je IS en IS NOT in plaats van = en !=.
Hiermee kunnen we de noemer voor onze verhouding vinden:
Variaties op COUNT
Tot zover alles goed. Maar wat als we niet weten welke kolommen NULL-vrij zijn? Erger nog, wat als geen van de kolommen NULL-vrij is? Er moet toch een manier zijn om de lengte van de tabel te vinden!
The solution is COUNT(*). This is reminiscent of SELECT * where the * represents all columns. In practice COUNT(*) counts rows as long as any one of their columns is non-null. This helps us find table lengths because a table shouldn’t have rows that are completely null.
Another common variation is to count a subset of the table. For instance, counting Harris county executions. We could run SELECT COUNT(*) FROM executions WHERE county='Harris' which filters down to a smaller dataset consisting of Harris executions and then counts all the rows. But what if we want to simultaneously find the number of Bexar county executions?
The solution is to apply a CASE WHEN block which acts as a big if-else statement. It has two formats and the one I like is:
CASE
WHEN <clause> THEN <result>
WHEN <clause> THEN <result>
...
ELSE <result>
END
This is admittedly one of the clunkier parts of SQL. A common mistake is to miss out the END command and the ELSE condition which is a catchall in case all the prior clauses are false. Also recall from the previous chapter that clauses are expressions that can be evaluated to be true or false. This makes it important to think about the boolean value of whatever you stuff in there.
Praktijk
It is worthwhile to step back and think about the different ways the computer handled these three queries. The WHERE version had it filter down to a small table first before aggregating while in the other two, it had to look through the full table. In the COUNT + CASE WHEN version, it only had to go through once, while the double COUNT version made it go through twice. So even though the output was identical, the performance was probably best in the first and worst in the third version.
Documentatie opzoeken
Dit boek is nooit bedoeld als een allesomvattende referentie voor de SQL taal. Daarvoor zult u andere online bronnen moeten opzoeken. Dit is een vaardigheid op zich, die het waard is om onder de knie te krijgen omdat u jaren nadat u bekend bent met de taal nog documentatie zult opzoeken.
Het goede nieuws is dat met de mentale modellen die je in dit boek leert, opzoekingen snel en pijnloos zouden moeten zijn, omdat je alleen details controleert zoals of de functie AVERAGE of AVG heet in plaats van uit te zoeken welke aanpak je moet kiezen.
Voor opzoekingen gebruik ik vaak W3 Schools, Stack Overflow en de officiële SQLite documentatie.
SELECT DISTINCT isn’t really an aggregate function because it doesn’t return a single number and because it operates on the output of the query rather than the underlying table. Nevertheless, I’ve included it here because it shares a common characteristic of operating on multiple rows.
Een vreemde query
Laten we, voordat we afsluiten, eens kijken naar deze query:
SELECT first_name, COUNT(*) FROM executions.
Doesn’t it look strange? If you have a good mental model of aggregations, it should! COUNT(*) is trying to return a single entry consisting the length of the execution table. first_name is trying to return one entry for each row. Should the computer return one or multiple rows? If it returns one, which first_name should it pick? If it returns multiple, is it supposed to replicate the COUNT(*) result across all the rows? The shapes of the output just don’t match!
In practice, databases try to return something sensible even though you pass in nonsense. In this case, our database picks the first name from the last entry in our table. Since our table is in reverse chronological order, the last entry is Charlie Brooks Jr., the first person executed since the Supreme Court lifted the ban on the death penalty. Different databases will handle this case differently so it’s best not to count on their default behavior. If you know you want the last entry, you should explicitly find it. Many SQL dialects have a LAST aggregate function which makes this trivial. Unfortunately SQLite doesn’t, so a workaround is necessary.
SQL Dialecten en Databases
Hoewel we dit een boek over SQL hebben genoemd, is het eigenlijk een boek over SQLite. Dit komt omdat SQL een denkbeeldig concept is, een platonisch ideaal. In werkelijkheid zijn er alleen dialecten die proberen te voldoen aan de SQL specificaties.
SQL is ook ondergespecificeerd, wat betekent dat sommige functionaliteit niet gespecificeerd wordt door de standaarden. De standaarden zeggen bijvoorbeeld niet of de functie voor het vinden van de lengte van een string LEN (SQL Server) of LENGTH (SQLite) moet heten; of hoe identifiers zoals tabel- of kolomnamen moeten worden aangehaald(` in MySQL, " in SQLite).
Om het nog erger te maken, zelfs een enkele query in een enkel dialect kan verschillend verwerkt worden omdat de onderliggende databases verschillende architecturen kunnen hebben. Het PostgreSQL dialect kan bijvoorbeeld gebruikt worden op databases die verdeeld zijn over veel verschillende fysieke machines, en databases die bestaan uit een enkel bestand. Dit betekent dat de mentale modellen die we hier ontwikkelen slechts een hulpmiddel zijn. Ze weerspiegelen misschien niet precies wat de database doet.
We hebben SQLite gekozen, wat zowel een dialect als een implementatie is, omdat het een van de meest gebruikte databases is. We hebben ook geprobeerd ons te richten op de kernfunctionaliteit en het mentale model van SQL in plaats van op onderscheidende onderdelen van SQLite. Met een robuust mentaal model is het eenvoudig om te wisselen tussen SQL-dialecten en -databases.
Conclusie en samenvatting
Laten we wat we tot nu toe geleerd hebben gebruiken om onze taak te voltooien:
Deze methode voor het vinden van onschuldclaims is weliswaar nogal onnauwkeurig omdat onschuld kan worden uitgedrukt in andere termen zoals “niet schuldig”. Desondanks vermoed ik dat het echte aantal onderschat wordt en waarschijnlijk van de juiste orde van grootte is. De vraag waar we dan nog mee zitten is of we bereid zijn om de mogelijkheid te accepteren dat tot 5% procent van de mensen die we executeren daadwerkelijk onschuldig is. (Paul Graham is dat niet.)
To recap, we’ve moved from row-level operations in the previous section, to using aggregate functions on multiple rows in the dataset. This has opened up an avenue to study system-level behavior. In the next section, we’ll learn to apply aggregate functions on multiple subgroups of the dataset using the GROUP BY block.