Proste losowe próbki z bazy danych Sql
Jak pobrać efektywną prostą próbkę losową w SQL? Baza danych o której mowa działa MySQL; moja tabela ma co najmniej 200 000 wierszy, a ja chcę prostą próbkę losową około 10 000.
"oczywista" odpowiedź brzmi:
SELECT * FROM table ORDER BY RAND() LIMIT 10000
Dla dużych tabel jest to zbyt wolne: wywołuje RAND() dla każdego wiersza (który już umieszcza go na O(n)) i sortuje je, co czyni go O(N lg n) w najlepszym razie. Czy jest sposób, aby zrobić to szybciej niż O (n)?
Uwaga : jak wskazuje Andrew Mao w komentarze, jeśli używasz tego podejścia na serwerze SQL, powinieneś użyć funkcji T-SQL nevid (), ponieważ RAND () może zwrócić tę samą wartość dla wszystkich wierszy .
EDIT: 5 YEARS LATER
Wpadłem na ten problem ponownie z większą tabelą, a skończyło się na użyciu wersji @ignorant ' s solution, Z dwoma poprawkami:
- próbki wierszy do 2-5x mój pożądany rozmiar próbki, aby tanio zamówić przez RAND ()
- Zapisz wynik RAND() do zindeksowanej kolumny na każda wstawka/aktualizacja. (Jeśli zestaw danych nie jest zbyt aktualizowany, może być konieczne znalezienie innego sposobu na utrzymanie świeżości tej kolumny.)
Aby pobrać 1000-elementową próbkę tabeli, liczę wiersze i przykładam wynik do średnio 10 000 wierszy z kolumną frozen_rand:
SELECT COUNT(*) FROM table; -- Use this to determine rand_low and rand_high
SELECT *
FROM table
WHERE frozen_rand BETWEEN %(rand_low)s AND %(rand_high)s
ORDER BY RAND() LIMIT 1000
(Moja rzeczywista implementacja wymaga więcej pracy, aby upewnić się, że nie zaniżam próbki i ręcznie zawijam rand_high, ale podstawową ideą jest " losowo przyciąć N do kilku tysiąc.")
Chociaż wymaga to pewnych poświęceń, pozwala mi na pobranie próbki bazy danych za pomocą skanowania indeksowego, dopóki nie będzie wystarczająco mała, aby ponownie zamówić przez RAND ().
9 answers
Jest tu bardzo ciekawa dyskusja na ten temat: http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/
Myślę, że bez absolutnie żadnych założeń co do tabeli, że Twoje rozwiązanie O (n lg n) jest najlepsze. Chociaż w rzeczywistości przy dobrym optymalizatorze lub nieco innej technice zapytanie na liście może być nieco lepsze, o (m*n), gdzie M to liczba pożądanych losowych wierszy, ponieważ nie musiałoby to być konieczne posortuj całą dużą tablicę, może po prostu wyszukać najmniejsze m razy. Ale dla tych liczb, które napisałeś, m jest większe niż lg n i tak.
Trzy jak możemy wypróbować:
-
W tabeli znajduje się unikalny, zindeksowany klucz podstawowy
Liczba losowych wierszy, które chcesz wybrać (m) jest znacznie mniejsza niż liczba wierszy w tabeli (n)
-
Unikalny klucz podstawowy to liczba całkowita, która waha się od 1 do n bez luki
Tylko przy założeniach 1 i 2 myślę, że można to zrobić w O (n), chociaż musisz napisać cały indeks do tabeli, aby dopasować założenie 3, więc nie jest to konieczne szybko O(n). Jeśli możemy dodatkowo założyć coś jeszcze ładnego w tabeli, możemy wykonać zadanie w O (M log m). Założenie 3 byłoby łatwą, przyjemną dodatkową właściwością do pracy. Z ładnym generatorem liczb losowych, który gwarantuje brak duplikatów podczas generowania liczb m z rzędu, rozwiązanie O (m) byłoby to możliwe.
Biorąc pod uwagę trzy założenia, podstawową ideą jest generowanie m unikalnych liczb losowych z zakresu od 1 do n, a następnie wybieranie wierszy z tymi kluczami z tabeli. Nie mam teraz mysql ' a ani nic przed sobą, więc w nieco pseudokodach wyglądałoby to coś w stylu:
create table RandomKeys (RandomKey int)
create table RandomKeysAttempt (RandomKey int)
-- generate m random keys between 1 and n
for i = 1 to m
insert RandomKeysAttempt select rand()*n + 1
-- eliminate duplicates
insert RandomKeys select distinct RandomKey from RandomKeysAttempt
-- as long as we don't have enough, keep generating new keys,
-- with luck (and m much less than n), this won't be necessary
while count(RandomKeys) < m
NextAttempt = rand()*n + 1
if not exists (select * from RandomKeys where RandomKey = NextAttempt)
insert RandomKeys select NextAttempt
-- get our random rows
select *
from RandomKeys r
join table t ON r.RandomKey = t.UniqueKey
Jeśli naprawdę martwisz się o wydajność, możesz rozważyć generowanie losowych kluczy w jakimś języku proceduralnym i wstawianie wyników w bazy danych, jak prawie wszystko inne niż SQL byłoby prawdopodobnie lepsze w rodzaju pętli i generowania liczb losowych wymagane.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-10-31 04:12:38
Myślę, że najszybszym rozwiązaniem jest
select * from table where rand() <= .3
Oto dlaczego myślę, że to powinno zadziałać.
- utworzy losową liczbę dla każdego wiersza. Liczba wynosi od 0 do 1
- ocenia, czy wyświetlać ten wiersz, jeśli liczba wygenerowana jest w przedziale od 0 do .3 (30%).
Zakłada to, że rand() generuje liczby w równomiernym rozkładzie. Jest to najszybszy sposób, aby to zrobić.
Widziałem, że ktoś polecił takie rozwiązanie i dostał zestrzelony bez dowodu.. oto, co bym na to powiedział -
- jest to O(n), ale nie jest wymagane sortowanie, więc jest szybsze niż O (n)
-
Mysql jest bardzo zdolny do generowania liczb losowych dla każdego wiersza. Spróbuj tego -
Select rand() from INFORMATION_SCHEMA.TABLES limit 10;
Ponieważ bazą danych jest mySQL, jest to właściwe rozwiązanie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-11-24 16:11:59
Szybciej niż ORDER BY RAND ()
Przetestowałem tę metodę, aby była znacznie szybsza niż ORDER BY RAND()
, stąd działa w czasie O (n) i robi to imponująco szybko.
Z http://technet.microsoft.com/en-us/library/ms189108%28v=sql.105%29.aspx :
Nie-MSSQL wersja -- nie testowałem tego
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= RAND()
MSSQL version:
SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
Spowoduje wybranie ~1% rekordów. Więc jeśli potrzebujesz dokładnego # procent lub rekordów do wyboru, oszacuj swoje procent z pewnym marginesem bezpieczeństwa, a następnie losowo wyrywa nadmiar rekordów z wynikowego zestawu, używając droższej metody ORDER BY RAND()
.
Jeszcze Szybciej
Udało mi się poprawić tę metodę jeszcze bardziej, ponieważ miałem dobrze znany indeksowany zakres wartości kolumn.
Na przykład, jeśli masz indeksowaną kolumnę z równomiernie rozłożonymi liczbami całkowitymi [0..max], możesz użyć tego, aby losowo wybrać N małych interwałów. Zrób to dynamicznie w programie, aby uzyskać inny zestaw dla każdego Uruchom zapytanie. Ten podzbiór będzie O (N) , który może być o wiele rzędów wielkości mniejszy niż pełny zestaw danych.
W moim teście skróciłem czas potrzebny na uzyskanie 20 (z 20 milionów) przykładowych rekordów z 3 min używając ORDER BY RAND () do 0.0 sekund!
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-09-10 20:29:05
Najwyraźniej w niektórych wersjach SQL istnieje polecenie TABLESAMPLE
, ale nie we wszystkich implementacjach SQL (zwłaszcza Redshift).
Http://technet.microsoft.com/en-us/library/ms189108(v=sql. 105). aspx
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-05-01 00:24:10
Po prostu użyj
WHERE RAND() < 0.1
Aby uzyskać 10% rekordów lub
WHERE RAND() < 0.01
Aby uzyskać 1% rekordów itp.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-04-29 06:03:44
Zaczynając od obserwacji, że możemy pobrać identyfikatory tabeli (np. liczba 5) na podstawie zbioru:
select *
from table_name
where _id in (4, 1, 2, 5, 3)
Możemy dojść do wyniku, że gdybyśmy mogli wygenerować łańcuch "(4, 1, 2, 5, 3)"
, to mielibyśmy bardziej efektywny sposób niż RAND()
.
Na przykład w języku Java:
ArrayList<Integer> indices = new ArrayList<Integer>(rowsCount);
for (int i = 0; i < rowsCount; i++) {
indices.add(i);
}
Collections.shuffle(indices);
String inClause = indices.toString().replace('[', '(').replace(']', ')');
Jeśli identyfikatory mają luki, to początkowa arraylist {[4] } jest wynikiem zapytania sql o identyfikatory.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-09-07 07:53:52
Chcę podkreślić, że wszystkie te rozwiązania wydają się próbkować bez wymiany. Wybranie górnych wierszy K z losowego sortowania lub połączenie z tabelą zawierającą unikalne klucze w losowej kolejności spowoduje wygenerowanie losowej próbki bez wymiany.
Jeśli chcesz, aby Twoja próbka była niezależna, musisz pobrać próbkę z wymianą. Zobacz pytanie 25451034 Aby uzyskać przykład jak to zrobić używając JOIN w sposób podobny do rozwiązania user12861. Rozwiązaniem jest napisany dla T-SQL, ale koncepcja działa w każdym SQL db.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:32:21
Jeśli potrzebujesz dokładnie m
wierszy, realistycznie wygenerujesz swój podzbiór ID poza SQL. Większość metod wymaga w pewnym momencie wybrania pozycji "nth" , a tabele SQL naprawdę nie są tablicami. Założenie, że klucze są kolejno po sobie, aby po prostu połączyć losowe wejścia między 1 A liczbą, jest również trudne do spełnienia - MySQL na przykład nie obsługuje go natywnie, a warunki blokady są... tricky.
Oto O(max(n, m lg n))
- Czas, O(n)
- przestrzeń rozwiązanie przy założeniu zwykłego klucza BTREE:
- pobieranie wszystkich wartości kolumny key tabeli danych w dowolnej kolejności do tablicy w ulubionym języku skryptowym w
O(n)
- wykonajFisher-Yates shuffle , zatrzymując się po
m
swapach i wyodrębnij subarray[0:m-1]
wϴ(m)
- "Join" the subarray with the original dataset (np.
SELECT ... WHERE id IN (<subarray>)
) inO(m lg n)
Każda metoda, która generuje losowy podzbiór poza SQL musi mieć co najmniej to złożoność. Połączenie nie może być szybsze niż O(m lg n)
z BTREE (więc O(m)
twierdzenia są fantazją dla większości silników), a shuffle jest ograniczone poniżej n
i m lg n
i nie wpływa na zachowanie asymptotyczne.
W Pseudokodzie Pythonicznym:
ids = sql.query('SELECT id FROM t')
for i in range(m):
r = int(random() * (len(ids) - i))
ids[i], ids[i + r] = ids[i + r], ids[i]
results = sql.query('SELECT * FROM t WHERE id IN (%s)' % ', '.join(ids[0:m-1])
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-22 17:39:40
Maybe you could do
SELECT * FROM table LIMIT 10000 OFFSET FLOOR(RAND() * 190000)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-10-30 05:29:34