So wählen Sie zufällige Zeilen in PostgreSQL aus

Verwenden Sie ORDER BY RANDOM() LIMIT n, wenn Sie bis zu einer festen Anzahl zufällig angeordneter Zeilen aus einer PostgreSQL-Tabelle erhalten möchten. Zum Beispiel gibt SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2; höchstens zwei Zeilen aus pets zurück (oder weniger, wenn die Tabelle weniger Zeilen hat). Verwenden Sie stattdessen WHERE RANDOM() < probability, wenn Sie eine grobe prozentuale Stichprobe möchten, deren Größe variieren kann. Diese Muster verwenden die pseudozufällige Funktion von PostgreSQL für gewöhnliches Sampling; sie eignen sich nicht für kryptografische Auswahl.

Wenn Sie die einfache Anweisung aus einem kleinen ausführbaren Beispiel kopieren möchten, gibt dieses Python-Snippet die SQL-Anweisung aus, die Sie in Ihrer PostgreSQL-Sitzung ausführen. Es stellt keine Verbindung zu einer Datenbank her und führt die Abfrage nicht aus.

Führen Sie dieses Beispiel aus:

query = "SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;"
print(query)

SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;

Zufällige Zeilen mit RANDOM() auswählen

RANDOM() gibt einen pseudozufälligen double precision-Wert von 0 einschließlich bis 1 ausschließlich zurück. Eine Bedingung wie SELECT id, name FROM pets WHERE RANDOM() < 0.05; betrachtet jede Zeile unabhängig und behält jede mit einer Wahrscheinlichkeit von ungefähr 5 %. Die Anzahl der Ergebnisse variiert: Sie kann kleiner oder größer als eine Zielanzahl sein, einschließlich gar keiner Zeilen, insbesondere bei einer kleinen Tabelle. Jeder Aufruf liefert einen Wert für die gerade ausgewertete Zeile; diese Werte sind nicht garantiert eindeutig.

Verwenden Sie für eine begrenzte Anzahl von Zeilen SELECT id, name FROM pets ORDER BY RANDOM() LIMIT 2;. PostgreSQL weist Sortierschlüssel zu und gibt die ersten beiden Zeilen in dieser Reihenfolge zurück, sofern vorhanden. Das ist bequem und unkompliziert, erfordert aber in der Regel, Kandidatenzeilen auszuwerten und zu sortieren. Es ist nicht automatisch schneller als die Filterung nach Wahrscheinlichkeit und kann bei einer großen Tabelle teuer sein. Fügen Sie die benötigten Spalten in die Auswahlliste ein, statt * zu verwenden, wenn das Ergebnis dadurch leichter zu verwenden ist.

Zufällige Zeilen aus einer großen Tabelle auswählen

Wählen Sie bei einer großen Relation zwischen der einfachen Ordnungsabfrage und einer Stichprobe, basierend auf der Menge und Art der Zufälligkeit, die Ihre Aufgabe benötigt. TABLESAMPLE BERNOULLI (5) fordert eine ungefähre 5-%-Stichprobe an, indem Zeilen einzeln betrachtet werden. TABLESAMPLE SYSTEM (5) zieht Stichproben aus Tabellenblöcken, sodass Zeilen, die auf der Festplatte nahe beieinanderliegen, gemeinsam im Ergebnis erscheinen können. Beide Prozentangaben sind Näherungen, und eine kleine Tabelle kann deutlich weniger oder mehr Zeilen zurückgeben als erwartet. TABLESAMPLE wird vor einer späteren WHERE-Bedingung ausgeführt, sodass Filter die Stichprobe weiter verkleinern können.

Zum Beispiel liefert SELECT id, name FROM pets TABLESAMPLE BERNOULLI (5); eine Stichprobe variabler Größe; sie verspricht weder genau fünf Zeilen noch bei jeder Ausführung fünf Prozent. SYSTEM kann den Aufwand zur Auswahl einer Stichprobe bei großen Tabellen verringern, aber die Block-Stichprobe kann weniger repräsentativ sein, wenn die Speicherreihenfolge einer sinnvollen Gruppierung folgt. Vergleichen Sie die Ergebnisse mit Ihren tatsächlichen Daten und Anforderungen, statt anzunehmen, dass eine der Methoden universell besser ist.

Um das Sampling für eine bestimmte Abfrage-Konfiguration wiederholbar zu machen, fügen Sie REPEATABLE (42) hinzu, wie in SELECT id, name FROM pets TABLESAMPLE BERNOULLI (5) REPEATABLE (42);. Der Seed kann eine Stichprobe reproduzieren, wenn Tabelle und relevante Bedingungen geeignet und unverändert bleiben; er ist kein Versprechen, dass die Ergebnisse nach Tabellenänderungen oder in jeder Umgebung identisch bleiben.

Die pg_class.reltuples von PostgreSQL kann nach dem Sammeln von Statistiken durch ANALYZE eine geschätzte Zeilenanzahl liefern. Zum Beispiel sucht SELECT reltuples FROM pg_class WHERE oid = 'public.pets'::regclass; die Schätzung für diese Relation nach. Sie kann veraltet oder ungenau sein, verwenden Sie sie also nicht als exakte Anzahl. COUNT(*) liefert die tatsächliche Anzahl für den Snapshot der Abfrage, muss dafür aber möglicherweise viele Zeilen prüfen; vermeiden Sie feste Aussagen zur Dauer, da die Kosten von den Daten und dem System abhängen.

Eine zufällige Zeile mit OFFSET auswählen

Wenn Sie die aktuelle Zeilenanzahl kennen und die Kosten des Überspringens von Zeilen akzeptieren können, kann ein zufälliger Offset eine Zeile anhand ihrer Position auswählen: SELECT id, name FROM pets ORDER BY id OFFSET floor(random() * row_count) LIMIT 1;. Ersetzen Sie row_count durch eine Anzahl, die für denselben Abfragekontext ermittelt wurde. Ohne ORDER BY sind Zeilenpositionen nicht festgelegt; mit einer Ordnung können gleichzeitige Einfügungen oder Löschungen ändern, welche Zeile eine Position einnimmt. Zählen und Überspringen kann ebenfalls teuer sein, daher ist dieses Muster nicht von sich aus eine effiziente Suche nach einer einzelnen Zeile. Wenn die Tabelle keine Zeilen hat, gibt die Abfrage keine Zeile zurück; wenn sich ihr Inhalt zwischen Zählen und Auswahl ändert, beschreibt ein Offset auf Basis der früheren Anzahl die aktuelle Menge möglicherweise nicht mehr.

Eine Zeilenschätzung verwenden, um eine zufällige Zeile auszuwählen

Eine Schätzung kann helfen, eine Schwelle für einen Schlüssel zu wählen, sie zieht aber selbst keine Zeile als Stichprobe. Die Umwandlung ::regclass in der Statistikabfrage identifiziert public.pets, damit PostgreSQL ihren pg_class-Eintrag finden kann. Ein Schwellenmuster wie WHERE id >= floor(random() * estimated_count) ORDER BY id LIMIT 1 ist nur sinnvoll, wenn id geeignete numerische Werte und einen passenden Index hat. Lückenhafte oder nicht zusammenhängende Schlüssel können Lücken lassen, dazu führen, dass die Schwelle keine Zeile trifft, oder manche Zeilen wahrscheinlicher machen als andere. Schlüsselbereiche, die nicht nahe null beginnen, verletzen ebenfalls die Annahmen des Musters. Prüfen Sie die Schlüsselverteilung und behandeln Sie einen fehlenden Treffer, bevor Sie sich auf diesen Ansatz verlassen. Für eine allgemeine Abfrage ist ORDER BY RANDOM() LIMIT 1 leichter nachzuvollziehen, kann bei einer großen Tabelle aber mehr Arbeit verursachen.