Google+ Followers

woensdag 4 september 2013

Series tellen.

Een van de mooiste aspecten van SQL vind ik dat je regelmatig oplossingen tegenkomt die zo simpel lijken dat je jezelf afvraagt waarom je er zelf niet op gekomen bent.

Het volgende probleem deed zich voor; een statisticus heeft een serie lotto uitslagen. Elke uitslag bestaat uit zes cijfers van 1-9 en zijn in de database opgeslagen als een tabel met zes kolommen, waarin de waarden voor het gemak zijn opgeslagen in oplopende volgorde, dus de eerste kolom bevat altijd het laagste getal van de trekking en de laatste kolom het hoogste getal.

Vraag: hoe kun je achterhalen hoevaak elke serie van drie getallen voorkomt in de totale set?

Dus niet "hoe vaak komen alle mogelijke combinaties van drie getallen voor", maar "1,3,5,6,8,9" bevat "3,5,6", en "3,5,6,7,8,9" bevat ook "3,5,6", en "1,2,3,5,6,7" bevat ook "3,5,6" dus "3,5,6" komt in deze set trekkingen drie keer voor.

Het feit dat de getallen direct op elkaar moeten volgen lijkt in eerste instantie wellicht een extra moeilijkheid maar het maakt het juist eenvoudiger omdat er in een combinatie van zes getallen maar vier mogelijkheden zijn waarop zo'n combinatie kan voorkomen; immers alleen de eerste vier getallen kunnen het begin van een serie van drie zijn, daarna zijn er gewoon niet meer genoeg getallen.

Het is dus zeer goed mogelijk om alle combinaties van kolommen uit te schrijven, de getallen te combineren tot een string die uniek is per combinatie, en vervolgens te tellen hoeveel van die strings er zijn.

Voor het combineren van de kolommen gebruik ik hier domweg stringconcatenatie met een spatie. Om alle gevonden combinaties tegelijk te krijgen worden alle combinatiequeries aan elkaar geplakt met een UNION ALL, immer; UNION filtert dubbelen weg en dan tel je alleen het aantal unieke combinaties in plaats van het aantal keer dat elke combinatie voorkomt. Aan het eind wordt er op de combinaties gegroepeerd en een simpele COUNT(*) vertelt hoevaak elke combinatie in de set zat.


CREATE TABLE trekkingen (trekkingid SERIAL, bal1 int, bal2 int, bal3 int, bal4 int, bal5 int ,bal6 int);
INSERT INTO trekkingen VALUES (1, 2,3,5,6,7,8), (2, 1,4,6,7,8,9), (3, 1,5,6,7,8,9),(4,1,4,6,7,8,9);

SELECT combinatie, COUNT(*) AS aantal
FROM
(
SELECT (bal1 || ' ' ||bal2 || ' ' || bal3) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal2 || ' ' ||bal3 || ' ' || bal4) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal3 || ' ' ||bal4 || ' ' || bal5) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal4 || ' ' ||bal5 || ' ' || bal6) AS combinatie FROM trekkingen
) AS combinaties
GROUP BY combinatie;