MySQL – gewichtetes zufälliges Select

Ein gewichtetes zufälliges Select sieht sich auf den ersten nach Unfug aus. Das Wort „zufällig“  passt so gar nicht zu einer gewichteten Abfrage. Das es aber trotzdem funktioniert und recht konkrete Anwendungsfälle gibt zeige ich in diesem Beitrag. Alle Infos beziehen sich auf das frei nutzbare Datenbanksystem MySQL beziehungsweise dessen Fork MariaDB.

MySQL – gewichtetes zufälliges Select

Ein konkretes Beispiel veranschaulicht vermutlich am besten was ich meine: ich habe eine Datenbank mit über 3 Millionen Städten und Dörfern der ganzen Welt. Ich möchte nun mit einer Abfrage 10 zufällige davon für ein bestimmtes Land haben. Mit dem Statement:

SELECT * FROM cities WHERE country_id = 13 ORDER BY RAND() LIMIT 10

bekomme ich auch 10 zufällige. Ich möchte mir daraus aber 10 Fußball Klubs eines Landes (country_id wäre bei mir Österreich) bauen. Das sieht dann etwas unglaubwürdig aus, wenn der FC Hinteracker gegen den SV Oberdorf in der Bundesliga spielt. Glaubwürdiger wäre es, wenn in den 10 Zeilen mindestens 8 Fußballmannschaften von Städten mit mehreren Tausend Einwohnern vertreten sind. Eine Sortierung nach Einwohnern wäre aber nicht möglich, weil sonst immer nur die größten Städte vertreten sind. Wer Fußball kennt der weiß, dass hin und wieder ein kleines Dorf eine Sensation abliefert.

Suche verbessern

Nach relativ langer Recherche und viel Probieren bin ich auf folgendes Statement gekommen:

SELECT c.*
FROM city c
JOIN (
    SELECT *
    FROM city
    WHERE RAND() < (
        SELECT ((100 / COUNT(*)) * 10)
        FROM city 
        WHERE country_id = 13)
    AND country_id = 13
    ORDER BY RAND()
    LIMIT 100
) AS z ON z.id= c.id
ORDER BY c.population desc
LIMIT 10

Ok, 3 SELECT Statements und ein JOIN, das sieht kompliziert aus. Beginnen wir beim äußeren SELECT. Dort holen wir uns alle Städte die über das Join mit der ID zusammenpassen, sortieren diese Liste nach der Einwohnerzahl (population) und liefern dann nur die ersten 10 Ergebnisse zurück.

Das JOIN liefert 100 zufällig sortierte Ergebnisse der city Tabelle für ein bestimmtes Land (country_id). Das Spannende an dieser Abfrage ist die WHERE Klausel. Dabei werden nur jene Zeilen genommen deren Ergebnis des dritten Statements einen zufälligen Wert übersteigen.

Das dritte und innerste SELECT Statement liefert einen Wert aus einer Formel bei der die maximale Anzahl der Zeilen dieser Tabelle eine Rolle spielen.

Suche anpassen

Für meine Anwendung gibt es noch die Anforderung dieses Statements anzupassen. Das bedeutet folgendes:

• für die Bundesliga sollen mit einer höheren Chance große Städte zurück kommen
• jede weitere Ligastufe (2. Liga, Regionalliga, Landesliga, usw. ) soll mit einer höheren Wahrscheinlichkeit kleinere Ortschaften zurückgeben

Diese Anforderung ist recht einfach zu implementieren. Wir können bei der mittleren und letzten Abfrage die Anzahl an zufälligen Ergebnissen (vor der Sortierung nach Einwohnerzahl) bestimmen. In diesem Beispiel sind das 100 Zeilen. Je mehr Zeilen wir nehmen, desto höher ist die Wahrscheinlichkeit, dass unter diesen große Städte zu finden sind und das ORDER BY population und LIMIT 10 überstehen. Das heißt: für Bundesliga nehmen wir 10000, für die 2. Liga 1000 und für die dritte Liga 100. Und schon bekommen wir ganz ordentlich sortierte, aber trotzdem völlig zufällige Ergebnisse.

Eine zufällig erstellte Bundesliga und 2. Liga für Deutschland sieht bei mir dann so aus:

Perfekt ist das Ergebnis noch nicht. Große Städte wie Berlin oder München fehlen, würden aber vermutlich bei einer weiteren Erstellung vorhanden sein. Bei all dem generischen Ergebnis bleiben aber immer gewisse Unsicherheiten. Was bei einem Land und einer Liga ein perfektes Ergebnis ist, kann beim nächsten Land schon wieder gar nicht passen. Könntet ihr mit dem gezeigten Ergebnis leben?

Fazit

SQL bietet jede Menge toller Features. Bis zu meiner Recherche dachte ich nicht, dass man zufällig gewichtete Select Statements ohne PHP Code implementieren kann. Der Vorteil an der SQL Lösung ist ein deutlich schnelleres Ergebnis. Mit dem Verfahren konnte ich 450 Ligen mit fast 6000 Vereinen innerhalb weniger Minuten erstellen.

War der Artikel für euch hilfreich? Habt ihr noch Ideen wie man das Ergebnis weiter verbessern kann?