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

Für SQL Server

newid()/order by funktioniert, ist aber bei großen Ergebnismengen sehr teuer, da für jede Zeile eine ID generiert und dann sortiert werden muss.

TABLESAMPLE() ist vom Standpunkt der Leistung aus gesehen gut, aber es kommt zu einer Verklumpung der Ergebnisse (alle Zeilen auf einer Seite werden zurückgegeben).

Für eine leistungsfähigere echte Zufallsstichprobe ist es am besten, Zeilen nach dem Zufallsprinzip herauszufiltern. Das folgende Codebeispiel habe ich im Artikel SQL Server Books Online gefunden Begrenzung der Ergebnismengen durch Verwendung von TABLESAMPLE :

Wenn Sie wirklich eine Stichprobe von einzelnen Zeilen wünschen, ändern Sie Ihre Abfrage, um Zeilen nach dem Zufallsprinzip herauszufiltern, anstatt TABLESAMPLE zu verwenden. Zum Beispiel, die folgende Abfrage verwendet die NEWID Funktion, um ungefähr ein Prozent der Zeilen der Tabelle Sales.SalesOrderDetail Tabelle:

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

Die Spalte SalesOrderID ist enthalten in dem CHECKSUM-Ausdruck enthalten, so dass NEWID() einmal pro Zeile ausgewertet wird, um um eine Stichprobenbildung auf Zeilenbasis zu erreichen. Der Ausdruck CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float / CAST (0x7fffffff AS int) wird ausgewertet zu einen zufälligen Float-Wert zwischen 0 und 1.

Wenn ich eine Tabelle mit 1.000.000 Zeilen ausführe, sehe ich folgende Ergebnisse:

SET STATISTICS TIME ON
SET STATISTICS IO ON

/* newid()
   rows returned: 10000
   logical reads: 3359
   CPU time: 3312 ms
   elapsed time = 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()

/* TABLESAMPLE
   rows returned: 9269 (varies)
   logical reads: 32
   CPU time: 0 ms
   elapsed time: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)

/* Filter
   rows returned: 9994 (varies)
   logical reads: 3359
   CPU time: 641 ms
   elapsed time: 627 ms
*/    
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float) 
              / CAST (0x7fffffff AS int)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Wenn Sie mit der Verwendung von TABLESAMPLE auskommen können, erhalten Sie damit die beste Leistung. Andernfalls verwenden Sie die newid()/Filter-Methode. newid()/Ordnen nach sollte der letzte Ausweg sein, wenn Sie eine große Ergebnismenge haben.

Answer 2

Eine Zufallsfunktion aus der Sql könnte helfen. Auch wenn Sie sich auf eine Zeile beschränken möchten, fügen Sie dies einfach am Ende hinzu.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Answer 3

Verwenden Sie nach Möglichkeit gespeicherte Anweisungen, um die Ineffizienz sowohl von Indizes auf RND() als auch der Erstellung eines Datensatznummernfeldes zu vermeiden.

PREPARE RandomRecord FROM "SELECT \* FROM table LIMIT ?,1";
SET @n=FLOOR(RAND()\*(SELECT COUNT(\*) FROM table));
EXECUTE RandomRecord USING @n;

Answer 4

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 5

Für SQL Server und den Bedarf an "einer einzelnen zufälligen Zeile"

Wenn keine echte Stichprobe benötigt wird, einen Zufallswert erzeugen [0, max_rows) und verwenden Sie die ORDER BY..OFFSET..FETCH von SQL Server 2012+ .

Dies ist sehr schnell wenn die COUNT y ORDER BY über geeignete Indizes erfolgen - so dass die Daten bereits entlang der Abfragezeilen "sortiert" sind. Wenn diese Operationen abgedeckt sind, ist es eine schnelle Anfrage und leidet nicht unter dem schreckliche Skalierbarkeit der Verwendung ORDER BY NEWID() oder ähnlich. Offensichtlich ist dieser Ansatz bei einer nicht indizierten HEAP-Tabelle nicht gut skalierbar.

declare @rows int
select @rows = count(1) from t

-- Other issues if row counts in the bigint range..
-- This is also not 'true random', although such is likely not required.
declare @skip int = convert(int, @rows * rand())

select t.*
from t
order by t.id -- Make sure this is clustered PK or IX/UCL axis!
offset (@skip) rows
fetch first 1 row only

Vergewissern Sie sich, dass die entsprechenden Transaktionsisolierungsebenen verwendet werden und/oder dass 0 Ergebnisse berücksichtigt werden.

---

Für SQL Server und den Bedarf an einem "allgemeinen Zeilenmuster" Ansatz

Anmerkung: Dies ist eine Anpassung der Antwort, wie sie gefunden wurde zu einer SQL Server-spezifischen Frage über das Abrufen einer Stichprobe von Zeilen . Sie wurde auf den Kontext zugeschnitten.

Ein allgemeiner Stichprobenansatz ist hier zwar mit Vorsicht zu genießen, aber im Zusammenhang mit anderen Antworten (und den sich wiederholenden Vorschlägen für nicht skalierende und/oder fragwürdige Implementierungen) sind dies dennoch potenziell nützliche Informationen. Ein solcher Stichprobenansatz ist weniger effizient als der erste gezeigte Code und fehleranfällig, wenn das Ziel darin besteht, eine "einzelne Zufallszeile" zu finden.

---

Hier ist eine aktualisierte und verbesserte Form von Probenahme eines Prozentsatzes von Zeilen . Sie basiert auf demselben Konzept wie einige andere Antworten, die CHECKSUM / BINARY_CHECKSUM und Modulus verwenden.

• Sie ist relativ schnell über große Datenmengen y kann effizient in/mit abgeleiteten Abfragen verwendet werden . Millionen von vorgefilterten Zeilen können in Sekundenschnelle abgerufen werden ohne Verwendung der tempdb und wenn sie auf den Rest der Abfrage abgestimmt sind, ist der Overhead oft minimal.

• *_Leidet nicht unter `CHECKSUM()/BINARYCHECKSUM(*)` Probleme mit Datenläufen. Bei Verwendung des CHECKSUM(*) können die Zeilen in "Stücken" und nicht "zufällig" ausgewählt werden! Der Grund dafür ist CHECKSUM bevorzugt Geschwindigkeit vor Verteilung .**

• Ergibt eine stabil/wiederholbar Zeilenauswahl und kann trivialerweise geändert werden, um bei nachfolgenden Abfrageausführungen andere Zeilen zu erzeugen. Ansätze, die Folgendes verwenden NEWID() kann niemals stabil/wiederholbar sein.

• Verwendet nicht ORDER BY NEWID() der gesamten Eingabemenge als die Bestellung kann zu einem erheblichen Engpass werden mit großen Eingabesätzen. Vermeidung von unnötig Sortierung auch reduziert den Speicher- und Tempdb-Verbrauch .

• Verwendet nicht TABLESAMPLE und arbeitet somit mit einer WHERE Vorfilter.

Das ist das Wesentliche. Siehe diese Antwort für zusätzliche Details und Hinweise .

Ein naiver Versuch:

declare @sample_percent decimal(7, 4)
-- Looking at this value should be an indicator of why a
-- general sampling approach can be error-prone to select 1 row.
select @sample_percent = 100.0 / count(1) from t

-- BAD!
-- When choosing appropriate sample percent of "approximately 1 row"
-- it is very reasonable to expect 0 rows, which definitely fails the ask!
-- If choosing a larger sample size the distribution is heavily skewed forward,
-- and is very much NOT 'true random'.
select top 1
    t.*
from t
where 1=1
    and ( -- sample
        @sample_percent = 100
        or abs(
            convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
        ) % (1000 * 100) < (1000 * @sample_percent)
    )

Dies kann weitgehend durch eine hybride Abfrage behoben werden, bei der Stichproben und ORDER BY Auswahl aus dem viel kleinere Stichprobenmenge . Dadurch wird der Sortiervorgang auf die Stichprobengröße und nicht auf die Größe der Originaltabelle beschränkt.

-- Sample "approximately 1000 rows" from the table,
-- dealing with some edge-cases.
declare @rows int
select @rows = count(1) from t

declare @sample_size int = 1000
declare @sample_percent decimal(7, 4) = case
    when @rows <= 1000 then 100                              -- not enough rows
    when (100.0 * @sample_size / @rows) < 0.0001 then 0.0001 -- min sample percent
    else 100.0 * @sample_size / @rows                        -- everything else
    end

-- There is a statistical "guarantee" of having sampled a limited-yet-non-zero number of rows.
-- The limited rows are then sorted randomly before the first is selected.
select top 1
    t.*
from t
where 1=1
    and ( -- sample
        @sample_percent = 100
        or abs(
            convert(bigint, hashbytes('SHA1', convert(varbinary(32), t.rowguid)))
        ) % (1000 * 100) < (1000 * @sample_percent)
    )
-- ONLY the sampled rows are ordered, which improves scalability.
order by newid()