JOIN, de club
Een beetje misleidende titel waarin de komma erg belangrijk is voor de interpunctie... Deze week een blog over strubbelingen met SQL query's.
We maken bij het CERT-WM periodiek rapportages over het gebruik van open standaarden zoals SPF, DMARC etc. De resultaten van de rapportages worden bewaard in een SQLite database, van waaruit we kunnen rapporteren.
Nu moet ik een manier hebben om procentueel aan te geven hoe de standaarden toegepast worden en dat met historisch verloop. Je krijgt dan ongeveer zo'n overzicht:
En dat allemaal op basis van deze data:
Dat worden dus minimaal twee query's die daarna samengevoegd moeten worden: 1x query met de som alle OK waarden en 1x met alle niet OK waarden. Die kunnen dan op basis van de gezamenlijke datum weer aan elkaar geknoopt worden.
Eitje toch? Aan de slag!
Ok. Eerst een basisquery om per datum alle goede waarden te verzamelen. Eerst maar alleen voor AFDO, om het goed te kunnen controleren:
select reportdate,count(type) as OK from reports where type='SPF' and orgkey='AFDO' and result='OK' group by reportdate
Nou, ziet er goed uit. Ook met die afwijking op de eerste scan (daar ging een test niet goed).
Gelijk maar door naar de NOTOK:
select reportdate,count(type) as NOTOK from reports where type='SPF' and orgkey='AFDO' and result!='OK' group by reportdate
En dan gelijk maar joinen die twee. Het lastige is, dat ik een full outer-join nodig heb, maar dat ondersteund SQLite niet. Enkel left-joins om mee te werken. [korte naslag break voor de lezers]
Gezien de output van de eerste twee query's is het het meest logisch om voor een left-join te kiezen op de OK waarden, de NOTOK komen daar dan wel bij:
Dat is een flinke query (Heb de tekst maar omgezet naar een plaatje, anders past het niet meer) waarbij beide bron query's als subquery gebruikt worden voor de join. Maar het resultaat is gelukkig conform verwachting. Nou eens kijken hoe het gaat als ik het op de andere organisatie ALDO loslaat...
Ow snap! da's nait te best. De data begint pas in juni en heeft maar 3 rijen. (AFDO heeft er 73, maar ik laat alleen de bovenste zien) Wat te doen...
Het probleem zit in de left-join op de OK subquery. Helaas heeft ALDO zijn zaakjes ten aanzien van SPF niet helemaal op orde en zitten er bijna alleen maar NOTOK waarden in de join, wat niet meer werkt met de left-join omdat er geen OK waarden zijn.
De hele opzet met de left-join op OK waarden is daarmee helaas een mislukking.
Hoe kan ik met de beperking van left-join nou beide reeksen (OK en NOTOK) joinen?
De data die ik wil hebben hebben allemaal een overeenkomst: De datum! Dus: aangezien ik geen full outer-join kan maken, dan maar een left-join bouwen op de datum met beide subqueries. Schematisch gezien:
Of anders gezegd: een dubbele join op drie subqueries. Kijken wat dat wordt.
En dit is 'm:
De eerste stap is een subquery met alle checks (allchecks), waarop de OK checks geleftjoint worden (Of hoe je dat ook moet noemen). Deze join wordt in zijn geheel dan weer als subquery gebruikt voor de left-join om de NOTOK erbij te plakken.
Grafisch:
Nu komen er bij ALDO ook keurig 73 rijen naar voren, ook mooi gevuld conform verwachting
Het enige dat nog mist ten opzichte van mijn gewenste tabel zijn de kolommen voor totaal en percentage. Maar die zijn op basis van de aanwezige velden redelijk gemakkelijk toe te voegen.
Al met al kom je dus best ver met left-query's als je maar eerst goed uitpluist hoe je de vork aan de steel steekt (bij voorkeur aan de voorkant).
Harm ter Veer