Wie kann man eine zufällige Zeile in SQL anfordern?

Question

Wie kann ich in reinem SQL eine zufällige Zeile anfordern (oder eine, die dem echten Zufall so nahe wie möglich kommt)?

Answer 1

Zu spät, aber ich bin über Google hierher gekommen, also werde ich der Nachwelt zuliebe eine alternative Lösung hinzufügen.

Ein anderer Ansatz besteht darin, TOP zweimal zu verwenden, und zwar in abwechselnder Reihenfolge. Ich weiß nicht, ob es sich dabei um "reines SQL" handelt, da es eine Variable im TOP verwendet, aber es funktioniert in SQL Server 2008. Hier ist ein Beispiel, das ich für eine Tabelle mit Wörterbuchwörtern verwende, wenn ich ein zufälliges Wort haben möchte.

SELECT TOP 1
  word
FROM (
  SELECT TOP(@idx)
    word 
  FROM
    dbo.DictionaryAbridged WITH(NOLOCK)
  ORDER BY
    word DESC
) AS D
ORDER BY
  word ASC

Natürlich ist @idx eine zufällig erzeugte ganze Zahl, die von 1 bis einschließlich COUNT(*) in der Zieltabelle reicht. Wenn Ihre Spalte indiziert ist, werden Sie auch davon profitieren. Ein weiterer Vorteil ist, dass Sie sie in einer Funktion verwenden können, da NEWID() nicht zulässig ist.

Schließlich wird die obige Abfrage in etwa 1/10 der Ausführungszeit einer NEWID()-Abfrage für dieselbe Tabelle ausgeführt. YYMV.

Answer 2

Für SQL Server 2005 und 2008, wenn wir eine Zufallsstichprobe von einzelnen Zeilen (aus Bücher Online ):

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)

Answer 3

Der beste Weg ist, einen Zufallswert in eine neue Spalte nur für diesen Zweck zu setzen und etwas wie dieses zu verwenden (Pseude-Code + SQL):

randomNo = random()
execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")

Dies ist die Lösung, die der MediaWiki-Code verwendet. Natürlich gibt es eine gewisse Voreingenommenheit gegenüber kleineren Werten, aber es wurde festgestellt, dass es ausreichend ist, den Zufallswert auf Null zu setzen, wenn keine Zeilen abgerufen werden.

newid()-Lösung erfordert möglicherweise einen vollständigen Tabellenscan, damit jeder Zeile eine neue guid zugewiesen werden kann, was wesentlich weniger performant ist.

rand()-Lösung möglicherweise überhaupt nicht funktioniert (z. B. mit MSSQL), weil die Funktion nur einmal ausgewertet wird und chaque Zeile wird die gleiche "Zufallszahl" zugewiesen.

Answer 4

Mit SQL Server 2012+ können Sie die OFFSET FETCH-Abfrage um dies für eine einzelne zufällige Zeile zu tun

select  * from MyTable ORDER BY id OFFSET n ROW FETCH NEXT 1 ROWS ONLY

wobei id eine Identitätsspalte und n die gewünschte Zeile ist - berechnet als Zufallszahl zwischen 0 und count()-1 der Tabelle (Offset 0 ist schließlich die erste Zeile)

Dies funktioniert mit Löchern in den Tabellendaten, solange Sie einen Index haben, mit dem Sie für die ORDER BY-Klausel arbeiten können. Es ist auch sehr gut für die Zufälligkeit - wie Sie arbeiten, dass sich selbst zu übergeben, aber die Probleme in anderen Methoden sind nicht vorhanden. Außerdem ist die Leistung ziemlich gut, auf einem kleineren Datensatz hält es gut, obwohl ich keine ernsthaften Leistungstests mit mehreren Millionen Zeilen durchgeführt habe.

Answer 5

Wie in @BillKarwins Kommentar zur Antwort von @cnu hervorgehoben...

Beim Kombinieren mit einem LIMIT habe ich festgestellt, dass es viel besser funktioniert (zumindest mit PostgreSQL 9.1), mit einer zufälligen Reihenfolge zu JOINen, als die tatsächlichen Zeilen direkt zu ordnen: z.B.

SELECT * FROM tbl_post AS t
JOIN ...
JOIN ( SELECT id, CAST(-2147483648 * RANDOM() AS integer) AS rand
       FROM tbl_post
       WHERE create_time >= 1349928000
     ) r ON r.id = t.id
WHERE create_time >= 1349928000 AND ...
ORDER BY r.rand
LIMIT 100

Stellen Sie einfach sicher, dass das "r" einen "rand"-Wert für jeden möglichen Schlüsselwert in der komplexen Abfrage erzeugt, die damit verbunden ist, aber begrenzen Sie trotzdem die Anzahl der "r"-Zeilen, wo es möglich ist.

CAST as Integer ist besonders hilfreich für PostgreSQL 9.2, das eine spezielle Sortieroptimierung für Integer- und Single Precision Floating-Typen hat.