Jak zażądać losowego wiersza w SQL?

Rozwiązania jak Jeremy:

SELECT * FROM table ORDER BY RAND() LIMIT 1

Działają, ale potrzebują sekwencyjnego skanowania całej tabeli (ponieważ należy obliczyć losową wartość związaną z każdym wierszem - tak, aby można było określić najmniejszy), co może być dość powolne nawet dla średnich tabel. Moim zaleceniem byłoby użycie jakiejś indeksowanej kolumny liczbowej( wiele tabel ma je jako klucze podstawowe), a następnie napisanie czegoś w stylu:

SELECT * FROM table WHERE num_value >= RAND() * 
    ( SELECT MAX (num_value ) FROM table ) 
ORDER BY num_value LIMIT 1

To działa w czasie logarytmicznym, niezależnie od rozmiar tabeli, jeśli num_value jest indeksowany. Jedno zastrzeżenie: zakłada się, że {[2] } jest równomiernie rozłożone w przedziale 0..MAX(num_value). Jeśli twój zbiór danych znacznie odbiega od tego założenia, otrzymasz wypaczone wyniki (niektóre wiersze pojawią się częściej niż inne).

Nie wiem jak to jest skuteczne, ale używałem go już wcześniej:

SELECT TOP 1 * FROM MyTable ORDER BY newid()

Ponieważ Guidy są dość losowe, kolejność oznacza, że otrzymujesz losowy wiersz.

ORDER BY NEWID()

Bierze 7.4 milliseconds

WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)

Bierze 0.0065 milliseconds!

Nie powiedziałeś, którego serwera używasz. W starszych wersjach SQL Server możesz użyć tego:

select top 1 * from mytable order by newid()

W SQL Server 2005 i nowszych, możesz użyć TABLESAMPLE, aby uzyskać losową próbkę, która jest powtarzalna:

SELECT FirstName, LastName
FROM Contact 
TABLESAMPLE (1 ROWS) ;

Dla SQL Server

Nevid () / order by będzie działać, ale będzie bardzo kosztowne dla dużych zestawów wyników, ponieważ musi wygenerować id dla każdego wiersza, a następnie je posortować.

TABLESAMPLE() jest dobre z punktu widzenia wydajności, ale otrzymasz grupowanie wyników(wszystkie wiersze na stronie zostaną zwrócone).

Aby uzyskać lepszą rzeczywistą próbkę losową, najlepszym sposobem jest losowe odfiltrowywanie wierszy. Znalazłem następujący przykład kodu w artykule SQL Server Books Online ograniczanie zestawów wyników za pomocą TABLESAMPLE:

Jeśli naprawdę chcesz losową próbkę poszczególnych wierszy, zmodyfikuj zapytanie do losowo filtruj wiersze, zamiast za pomocą TABLESAMPLE. Na przykład następujące zapytanie używa nevid funkcja zwracająca około jednego procent rzędów Sprzedaż.Tabela SalesOrderDetail:

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

Kolumna SalesOrderID znajduje się w wyrażenie sumy kontrolnej tak, że Nevid () ocenia raz na wiersz do uzyskaj pobieranie próbek na podstawie rzędu. Wyrażenie CAST(CHECKSUM (NEWID(), SalesOrderID) & 0x7fffffff jako float / CAST (0X7FFFFFFF AS int) losowa wartość zmiennoprzecinkowa między 0 a 1.

Kiedy uruchamiamy tabelę z 1,000,000 wierszy, oto moje wyniki:

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

Jeśli ujdzie ci to na sucho za pomocą TABLESAMPLE, zapewni Ci to najlepszą wydajność. W przeciwnym razie użyj metody nevid () / filter. nevid () / order by powinno być ostatecznością, jeśli masz duży wynik gotowi.

Jeśli to możliwe, użyj przechowywanych instrukcji, aby uniknąć nieefektywności obu indeksów w RND() i tworzenia pola numer rekordu.

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

Najlepszym sposobem jest umieszczenie losowej wartości w nowej kolumnie właśnie w tym celu i użycie czegoś takiego (kod pseude + SQL):

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

Jest to rozwiązanie zastosowane w kodzie MediaWiki. Oczywiście istnieje pewne odchylenie od mniejszych wartości, ale okazało się, że wystarczy zawinąć losową wartość wokół zera, gdy nie są pobierane wiersze.

Rozwiązanie Rand() może w ogóle nie działać (np. z MSSQL), ponieważ funkcja będzie oceniana tylko raz, a każdy wiersz zostanie przypisany tej samej "losowej" liczbie.

Dla SQL Server 2005 i 2008, jeśli chcemy losową próbkę poszczególnych wierszy (z książek Online):

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

Input of using RAND (), as it is not encounted, you may simply get max ID (=Max):

SELECT MAX(ID) FROM TABLE;

Uzyskaj losowe między 1..Max (=My_Generated_Random)

My_Generated_Random = rand_in_your_programming_lang_function(1..Max);

A następnie uruchom ten SQL:

SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1

Zauważ, że sprawdza wiersze, których identyfikatory są równe lub wyższe od wybranej wartości. Można również polować na wiersz w tabeli i uzyskać równy lub niższy identyfikator niż My_Generated_Random, a następnie zmodyfikować zapytanie w następujący sposób:

SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1

Jak zaznaczono w komentarzu @ BillKarwin do odpowiedzi @ cnu...

Podczas łączenia z limitem, odkryłem, że dużo lepiej (przynajmniej w PostgreSQL 9.1) łączyć się z losowym porządkiem, a nie bezpośrednio porządkować rzeczywiste wiersze: np. {[0]}

Upewnij się tylko, że " r "generuje wartość" rand "dla każdej możliwej wartości klucza w złożonym zapytaniu, które jest z nim połączone, ale nadal ogranicza liczbę wierszy "r", jeśli to możliwe.

Obsada jako liczba całkowita jest szczególnie przydatne dla PostgreSQL 9.2, który ma specyficzną optymalizację sortowania dla typów całkowitych i pojedynczych precyzyjnych.

Większość rozwiązań ma na celu uniknięcie sortowania, ale nadal muszą wykonać sekwencyjne skanowanie tabeli.

Istnieje również sposób, aby uniknąć skanowania sekwencyjnego, przechodząc na skanowanie indeksowe. Jeśli znasz wartość indeksu losowego wiersza, możesz uzyskać wynik niemal natychmiastowo. Problem w tym - jak odgadnąć wartość indeksu.

Następujące rozwiązanie działa na PostgreSQL 8.4:

explain analyze select * from cms_refs where rec_id in 
  (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
   from generate_series(1,10))
  limit 1;

I powyżej rozwiązanie można odgadnąć 10 różnych losowych wartości indeksu z zakresu 0 .. [ostatnia wartość id].

Liczba 10 jest dowolna - możesz użyć 100 lub 1000, ponieważ (o dziwo) nie ma dużego wpływu na czas odpowiedzi.

Jest też jeden problem - jeśli masz mało identyfikatorów możesz przegapić. Rozwiązaniem jest mieć plan zapasowy :) w tym przypadku czysta stara kolejność przez zapytanie random (). Po połączeniu id wygląda tak:

explain analyze select * from cms_refs where rec_id in 
    (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
     from generate_series(1,10))
    union all (select * from cms_refs order by random() limit 1)
    limit 1;

Nie Unia wszystkie klauzula. W tym przypadku, jeśli pierwsza część zwróci jakiekolwiek dane, drugi nigdy nie zostanie stracony!

Późno, ale dotarłem tutaj przez Google, więc dla dobra potomności dodam alternatywne rozwiązanie.

Innym podejściem jest użycie TOP dwa razy, z przemiennymi rozkazami. Nie wiem czy to jest "pure SQL" , bo używa zmiennej w górze, ale działa w SQL Server 2008. Oto przykład, którego używam przeciwko tabeli słów słownikowych, jeśli chcę przypadkowego słowa.

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

Oczywiście @idx jest losowo generowaną liczbą całkowitą, która waha się od 1 do COUNT (*) w tabeli docelowej, inkluzywnie. Jeśli twoja kolumna jest indeksowana, również na niej skorzystasz. Kolejną zaletą jest to, że można go używać w funkcji, ponieważ funkcja nevid () jest wyłączona.

Na koniec, powyższe zapytanie działa w około 1/10 czasu exec zapytania typu nevid () w tej samej tabeli. YYMV.

Możesz również spróbować użyć funkcji new id().

Po prostu napisz zapytanie i użyj funkcji order by new id(). To całkiem przypadkowe.

Dla MySQL, aby uzyskać losowy rekord

 SELECT name
  FROM random AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

Więcej Szczegółów http://jan.kneschke.de/projects/mysql/order-by-rand/

Nie widziałem jeszcze tej odmiany w odpowiedziach. Miałem dodatkowe ograniczenie tam, gdzie potrzebowałem, biorąc pod uwagę początkowe ziarno, aby wybrać ten sam zestaw wierszy za każdym razem.

Dla MS SQL:

Przykład minimalny:

select top 10 percent *
from table_name
order by rand(checksum(*))

Znormalizowany czas wykonania: 1.00

Nevid () przykład:

select top 10 percent *
from table_name
order by newid()

Znormalizowany czas wykonania: 1.02

NewId() jest nieznacznie wolniejszy niż rand(checksum(*)), więc możesz nie chcieć go używać przeciwko dużym zestawom rekordów.

Wybór z inicjałem Seed:

declare @seed int
set @seed = Year(getdate()) * month(getdate()) /* any other initial seed here */

select top 10 percent *
from table_name
order by rand(checksum(*) % seed) /* any other math function here */

Jeśli musisz wybrać ten sam zestaw, biorąc pod uwagę ziarno, to wydaje się działać.

W MSSQL (testowane na 11.0.5569) przy użyciu

SELECT TOP 100 * FROM employee ORDER BY CRYPT_GEN_RANDOM(10)

Jest znacznie szybszy niż

SELECT TOP 100 * FROM employee ORDER BY NEWID()

W SQL Server możesz połączyć TABLESAMPLE z nevid (), aby uzyskać całkiem dobrą losowość i nadal mieć szybkość. Jest to szczególnie przydatne, jeśli naprawdę chcesz tylko 1 lub niewielką liczbę wierszy.

SELECT TOP 1 * FROM [table] 
TABLESAMPLE (500 ROWS) 
ORDER BY NEWID()

 SELECT * FROM table ORDER BY RAND() LIMIT 1

Muszę się zgodzić z CD-MaN: użycie" ORDER BY RAND () " będzie dobrze działać dla małych tabel lub gdy wykonasz SELECT tylko kilka razy.

Używam również "num_value > = RAND ()*..."technika, a jeśli naprawdę chcę mieć losowe wyniki, mam specjalną "losową" kolumnę w tabeli, którą aktualizuję raz dziennie lub tak. Ta pojedyncza aktualizacja zajmie trochę czasu( zwłaszcza, że będziesz musiał mieć indeks w tej kolumnie), ale jest to znacznie szybsze niż tworzenie losowych liczb dla każdego wiersza za każdym razem, gdy select jest uruchamiany.

Bądź ostrożny, ponieważ TableSample nie zwraca losowej próbki wierszy. Kieruje Twoje zapytanie do losowej próbki stron 8KB, które składają się na twój wiersz. Następnie Twoje zapytanie zostanie wykonane na podstawie danych zawartych na tych stronach. Ze względu na sposób grupowania danych na tych stronach (kolejność wstawiania itp.) może to prowadzić do danych, które w rzeczywistości nie są próbką losową.

Zobacz: http://www.mssqltips.com/tip.asp?tip=1308

Ta strona MSDN dla TableSample zawiera przykład jak wygenerować faktycznie losową próbkę danych.

Http://msdn.microsoft.com/en-us/library/ms189108.aspx

Wydaje się, że wiele z wymienionych pomysłów nadal korzysta z zamawiania

Jeśli jednak używasz tabeli tymczasowej, możesz przypisać indeks losowy (jak wiele rozwiązań sugerowało), a następnie pobrać pierwszy, który jest większy niż dowolna liczba z zakresu od 0 do 1.

Na przykład (dla DB2):

WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY

Prosty i skuteczny sposób z http://akinas.com/pages/en/blog/mysql_random_row/

SET @i = (SELECT FLOOR(RAND() * COUNT(*)) FROM table); PREPARE get_stmt FROM 'SELECT * FROM table LIMIT ?, 1'; EXECUTE get_stmt USING @i;

Istnieje lepsze rozwiązanie dla Oracle zamiast używania dbms_random.wartość, podczas gdy wymaga pełnego skanowania, aby zamówić wiersze przez dbms_random.wartość i jest dość powolny dla dużych tabel.

Użyj tego zamiast:

SELECT *
FROM employee sample(1)
WHERE rownum=1

Dla Firebird:

Select FIRST 1 column from table ORDER BY RAND()

Z SQL Server 2012+ możesz użyć OFFSET FETCH query aby to zrobić dla jednego losowego wiersza

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

Gdzie id jest kolumną tożsamości, a n jest żądanym wierszem-obliczonym jako liczba losowa między 0 a count () -1 tabeli (offset 0 jest pierwszym wierszem)

Działa to z dziurami w danych tabeli, o ile masz indeks do pracy z klauzulą ORDER BY. Jego również bardzo dobre dla przypadkowości - jak wypracować, że się przejść, ale w innych metodach nie występują. Ponadto wydajność jest całkiem dobra, na mniejszym zbiorze danych trzyma się dobrze, chociaż nie próbowałem poważnych testów wydajności w stosunku do kilku milionów wierszy.

Dla SQL Server 2005 i nowszych, rozszerzenie odpowiedzi @GreyPanther na przypadki, gdy num_value nie ma ciągłych wartości. Działa to również w przypadkach, gdy nie mamy równomiernie rozłożonych zbiorów danych i gdy num_value nie jest liczbą, ale unikalnym identyfikatorem.

WITH CTE_Table (SelRow, num_value) 
AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS SelRow, num_value FROM table
) 

SELECT * FROM table Where num_value = ( 
    SELECT TOP 1 num_value FROM CTE_Table  WHERE SelRow >= RAND() * (SELECT MAX(SelRow) FROM CTE_Table)
)

Funkcja losowa z sql może pomóc. Również jeśli chcesz ograniczyć się do jednego wiersza, po prostu dodaj to na końcu.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1