Wybierz losowy wiersz z tabeli sqlite


Odpowiedzi:


213

Spójrz na Wybieranie losowego wiersza z tabeli SQLite

SELECT * FROM table ORDER BY RANDOM() LIMIT 1;

1
Jak rozszerzyć to rozwiązanie o połączenie? Podczas używania SELECT a.foo FROM a JOIN b ON a.id = b.id WHERE b.bar = 2 ORDER BY RANDOM() LIMIT 1;zawsze otrzymuję ten sam wiersz.
Helmut Grohne

Czy można wysiać liczbę losową. np. Book of the day wysiewana z unix epoc na dziś w południe, więc pokazuje tę samą książkę przez cały dzień, nawet jeśli zapytanie jest uruchamiane wiele razy. Tak, wiem, że buforowanie jest bardziej wydajne w tym przypadku.
danielson317

FWIW na moje pytanie znajduje się tutaj odpowiedź. Odpowiedź brzmi: nie możesz wysiać losowej liczby. stackoverflow.com/questions/24256258/…
danielson317

31

Poniższe rozwiązania są znacznie szybsze niż anktastyczne (licznik (*) kosztuje dużo, ale jeśli możesz go buforować, to różnica nie powinna być aż tak duża), co samo w sobie jest znacznie szybsze niż „order by random ()” gdy masz dużą liczbę rzędów, chociaż mają one kilka niedogodności.

Jeśli twoje rowidy są raczej spakowane (tj. Kilka usunięć), możesz wykonać następujące czynności (użycie (select max(rowid) from foo)+1zamiast max(rowid)+1daje lepszą wydajność, jak wyjaśniono w komentarzach):

select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1));

Jeśli masz dziury, czasami będziesz próbował wybrać nieistniejący identyfikator wiersza, a funkcja select zwróci pusty zestaw wyników. Jeśli jest to nie do przyjęcia, możesz podać wartość domyślną w następujący sposób:

select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1)) or rowid = (select max(rowid) from node) order by rowid limit 1;

To drugie rozwiązanie nie jest doskonałe: rozkład prawdopodobieństwa jest wyższy w ostatnim wierszu (tym z najwyższym identyfikatorem wiersza), ale jeśli często dodajesz coś do tabeli, stanie się on ruchomym celem, a rozkład prawdopodobieństwa powinien być dużo lepiej.

Jeszcze inne rozwiązanie, jeśli często wybierasz losowe rzeczy z tabeli z wieloma dziurami, możesz utworzyć tabelę, która zawiera wiersze oryginalnej tabeli posortowane w losowej kolejności:

create table random_foo(foo_id);

Następnie okresowo uzupełniaj tabelę random_foo

delete from random_foo;
insert into random_foo select id from foo;

Aby wybrać losowy wiersz, możesz użyć mojej pierwszej metody (tutaj nie ma dziur). Oczywiście ta ostatnia metoda ma pewne problemy ze współbieżnością, ale ponowne budowanie random_foo jest operacją utrzymania, która prawdopodobnie nie będzie się zdarzać zbyt często.

Jednak jeszcze innym sposobem, który niedawno znalazłem na liście mailingowej , jest umieszczenie wyzwalacza usuwania, aby przenieść wiersz z największym identyfikatorem wiersza do aktualnie usuniętego wiersza, tak aby nie pozostały żadne dziury.

Na koniec zwróć uwagę, że zachowanie rowid i całkowitej automatycznej inkrementacji klucza podstawowego nie jest identyczne (z rowid, kiedy wstawiany jest nowy wiersz, wybierane jest max (rowid) +1, gdzie jest to najwyższa kiedykolwiek widziana wartość + 1 dla klucz podstawowy), więc ostatnie rozwiązanie nie będzie działać z autoincrement w random_foo, ale inne metody będą działać.


Tak jak właśnie widziałem na liście mailingowej, zamiast metody rezerwowej (metoda 2), możesz po prostu użyć rowid> = [random] zamiast =, ale w rzeczywistości jest to powolne w porównaniu z metodą 2.
Suzanne Dupéron

3
To świetna odpowiedź; jednak ma jeden problem. SELECT max(rowid) + 1będzie powolnym zapytaniem - wymaga pełnego skanowania tabeli. sqlite tylko optymalizuje zapytanie SELECT max(rowid). Tak więc ta odpowiedź byłaby poprawiona przez: select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1)); Zobacz to, aby uzyskać więcej informacji: sqlite.1065341.n5.nabble.com/…
dasl

19

Musisz położyć „order by LOSOWO ()” zapytaniu .

Przykład:

select * from quest order by RANDOM();

Zobaczmy pełny przykład

  1. Utwórz tabelę:
CREATE TABLE  quest  (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    quest TEXT NOT NULL,
    resp_id INTEGER NOT NULL
);

Wstawianie wartości:

insert into quest(quest, resp_id) values ('1024/4',6), ('256/2',12), ('128/1',24);

Domyślny wybór:

select * from quest;

| id |   quest  | resp_id |
   1     1024/4       6
   2     256/2       12
   3     128/1       24
--

Wybierz losowo:

select * from quest order by RANDOM();
| id |   quest  | resp_id |
   3     128/1       24
   1     1024/4       6
   2     256/2       12
--
* Za każdym razem, gdy wybierzesz, kolejność będzie inna.

Jeśli chcesz zwrócić tylko jeden wiersz

select * from quest order by RANDOM() LIMIT 1;
| id |   quest  | resp_id |
   2     256/2       12
--
* Za każdym razem, gdy wybierzesz, zwrot będzie inny.


Chociaż odpowiedzi w postaci samego kodu nie są zabronione, proszę zrozumieć, że jest to społeczność pytań i odpowiedzi, a nie społeczność pozyskiwana z tłumu, i że zwykle, jeśli OP zrozumiałby wysłany kod jako odpowiedź, podszedłby z podobnym rozwiązaniem na własną rękę i nie wysłałby pytania w pierwszej kolejności. W związku z tym proszę podać kontekst swojej odpowiedzi i / lub kodu, wyjaśniając, jak i / lub dlaczego to działa.
XenoRo

2
Wolę to rozwiązanie, ponieważ pozwala mi szukać n wierszy. W moim przypadku potrzebowałem 100 losowych próbek z bazy danych - ORDER BY RANDOM () w połączeniu z LIMIT 100 robi dokładnie to.
mnr

17

Co powiesz na:

SELECT COUNT(*) AS n FROM foo;

następnie wybierz losową liczbę mw [0, n) i

SELECT * FROM foo LIMIT 1 OFFSET m;

Możesz nawet zapisać gdzieś pierwszą liczbę ( n ) i zaktualizować ją tylko wtedy, gdy zmieni się liczba w bazie danych. W ten sposób nie musisz za każdym razem wykonywać WYBORU LICZNIKA.


1
To dobra, szybka metoda. Nie generalizuje zbyt dobrze, aby wybrać więcej niż 1 wiersz, ale OP poprosił tylko o 1, więc myślę, że to w porządku.
Ken Williams

Ciekawostką jest to, że czas potrzebny do znalezienia OFFSETwydaje się rosnąć w zależności od rozmiaru przesunięcia - wiersz 2 jest szybki, wiersz 2 miliony zajmuje trochę czasu, nawet jeśli wszystkie dane w pliku mają stałą wielkość i powinien móc szukać bezpośrednio do niej. Przynajmniej tak to wygląda w SQLite 3.7.13.
Ken Williams

@KenWilliams Prawie wszystkie bazy danych mają ten sam problem z OFFSETEM. Jest to bardzo nieefektywny sposób wykonywania zapytań w bazie danych, ponieważ musi odczytać tyle wierszy, mimo że zwróci tylko 1.
Jonathan Allen

1
Zwróć uwagę, że mówiłem o / ustalonym rozmiarze / rekordach - powinno być łatwe do skanowania bezpośrednio do właściwego bajtu w danych ( bez czytania tak wielu wierszy), ale musieliby jawnie zaimplementować optymalizację.
Ken Williams

@KenWilliams: w SQLite nie ma rekordów o stałej wielkości, jest on wpisywany dynamicznie, a dane nie muszą pasować do zadeklarowanych podobieństw ( sqlite.org/fileformat2.html#section_2_1 ). Wszystko jest przechowywane na stronach b-tree, więc tak czy inaczej, musi przynajmniej przeszukać b-tree w kierunku liścia. Aby to osiągnąć efektywnie, musiałby przechowywać rozmiar poddrzewa wraz z każdym wskaźnikiem potomnym. Byłby to zbyt duży narzut dla niewielkich korzyści, ponieważ nadal nie będziesz w stanie zoptymalizować PRZESUNIĘCIA pod kątem łączenia, porządkowania według itp. (A bez ZAMÓWIENIA według kolejności nie jest ona zdefiniowana)
Yakov Galka

13
SELECT   bar
FROM     foo
ORDER BY Random()
LIMIT    1

11
Ponieważ najpierw wybierze całą zawartość tabeli, czy nie byłoby to bardzo czasochłonne w przypadku dużych tabel?
Alex_coder,

1
Czy nie możesz po prostu ograniczyć zakresu za pomocą warunków „GDZIE”?
jldupont,

11

Oto modyfikacja rozwiązania @ ank:

SELECT * 
FROM table
LIMIT 1 
OFFSET ABS(RANDOM()) % MAX((SELECT COUNT(*) FROM table), 1)

To rozwiązanie działa również w przypadku indeksów z przerwami, ponieważ losujemy przesunięcie w zakresie [0, count). MAXsłuży do obsługi przypadku z pustą tabelą.

Oto proste wyniki testu na tabeli z 16 tys. Wierszy:

sqlite> .timer on
sqlite> select count(*) from payment;
16049
Run Time: real 0.000 user 0.000140 sys 0.000117

sqlite> select payment_id from payment limit 1 offset abs(random()) % (select count(*) from payment);
14746
Run Time: real 0.002 user 0.000899 sys 0.000132
sqlite> select payment_id from payment limit 1 offset abs(random()) % (select count(*) from payment);
12486
Run Time: real 0.001 user 0.000952 sys 0.000103

sqlite> select payment_id from payment order by random() limit 1;
3134
Run Time: real 0.015 user 0.014022 sys 0.000309
sqlite> select payment_id from payment order by random() limit 1;
9407
Run Time: real 0.018 user 0.013757 sys 0.000208

4

Wymyśliłem następujące rozwiązanie dla dużych baz danych sqlite3 :

SELECT * FROM foo WHERE rowid = abs(random()) % (SELECT max(rowid) FROM foo) + 1; 

Funkcja abs (X) zwraca wartość bezwzględną argumentu liczbowego X.

Funkcja random () zwraca pseudolosową liczbę całkowitą z przedziału od -9223372036854775808 do +9223372036854775807.

Operator% wyprowadza wartość całkowitą swojego lewego operandu modulo jego prawy operand.

Na koniec dodajesz +1, aby zapobiec rowid równemu 0.


1
Dobra próba, ale nie sądzę, żeby to zadziałało. Co się stanie, jeśli wiersz z rowId = 5 został usunięty, ale rowIds 1,2,3,4,6,7,8,9,10 nadal istnieje? Następnie, jeśli wybrany losowy rowId wynosi 5, to zapytanie nie zwróci nic.
Calicoder
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.