[SQL|Postgres] Random rij selecteren

[Verwijderd]

Ik wil dus een random rij uit een tabel krijgen, maar wel een beetje efficienter dan

SQL:

SELECT cat, dog FROM (SELECT cat, dog, RANDOM() FROM foo ORDER BY 3) bar LIMIT 1;

Dus ik heb het volgende bedacht, maar nu komen er af en toe twee rijen uit! Dat kan toch helemaal niet:

SQL:

linkfind=> SELECT linkno, domain, uri FROM links WHERE 
linkno=trunc(random()*(SELECT max(linkno) FROM links));
 linkno |        domain        |                     uri                      
--------+----------------------+----------------------------------------------
   3867 | disney.com      | http://www.disney.com/katrien.html
   9343 | donald-duck.com | http://www.donald-duck.com
(2 rows)

[ Voor 7% gewijzigd door gorgi_19 op 08-11-2004 19:08 ]

[gorgi_19]

klein momentje even...

edit:

Je hebt vast geen probleem als ik, preuts als ik ben, de links even heb veranderd naar mijn favoriete sites

[ Voor 78% gewijzigd door gorgi_19 op 08-11-2004 19:08 ]

[Verwijderd]

Een dubbele regel kan toch wel. Random is opnieuw random voor elk record. Het aantal elementen in je recordset is dus variabel. Kan ook 0 zijn als er gaten in de linkno-nummering zit. Vervelend als je denkt de bovenste te kunnen pakken .

Random is nu eenmaal niet zo efficient, maar da's random eigen.
Je kan het ook nog op deze manier doen, recht-toe recht-aan, zonder subquery:

code:

SELECT cat, dog
FROM foo
ORDER BY RAND()
LIMIT 1;

@gorgi Laat die jongen toch als'ie naar www.pimfortuyn.nu wil linken .
Bij puur natuur geen censuur.

[Verwijderd]

Ok, ik dacht dat random() maar een keer aangeroepen werd.

Je kan het ook nog op deze manier doen, recht-toe recht-aan, zonder subquery:

code:

SELECT cat, dog
FROM foo
ORDER BY RAND()
LIMIT 1;

Dit is ook niet efficient, omdat hij voor elke rij RAND() aanroept, en er ook nog op gaat sorteren. Is er geen manier om RAND() slechts een keer aan te roepen?

@gorgi Laat die jongen toch als'ie naar www.pimfortuyn.nu wil linken .
Bij puur natuur geen censuur.

Het was ongetwijfeld niet helemaal kuis wat ik gepost had. Maar dat heb je als je een index van pornosites aan het maken bent.

[Verwijderd]

Random moet je buiten de selectie query houden. Je kan een locale variabele met random vullen als de DBMS dat ondersteunt. Je kan ook de random waarde van buiten aangeven. In beide gevallen moet je wel een sluitende unieke nummering aanbrengen op je records. Op die manier kan je de random waarde aan het maximum relateren.
Deze aanpak vereist wel weer extra onderhoud bij verwijderen.

[Verwijderd]

Veel sneller:

SQL:

UPDATE random SET random=trunc(random()*(SELECT max(linkno) from links));
SELECT linkno, domain, uri FROM links WHERE linkno=(SELECT random FROM random);

[Verwijderd]

Verwijderd schreef op 08 november 2004 @ 21:33:
Veel sneller:

SQL:

UPDATE random SET random=trunc(random()*(SELECT max(linkno) from links));
SELECT linkno, domain, uri FROM links WHERE linkno=(SELECT random FROM random);

0 rows returned.

Upes, dat ging inderdaad snel .
Dit werkt alleen als linkno een niet onderbroken nummering is.

[Thekk]

Er zijn ook al anderen geweest met jouw probleem. Hier staat een, volgens de auteur, redelijk snelle oplossing.
Die bestaat uit het maken van een speciale random kolom, waarbij de getallen binnen een redelijk grote range liggen. In je select query kun je dan een random getal laten kiezen (met dezelfde range), en het de eerste rij teruggeven die een hoger getal heeft dan het getal in de query.

PS: oplossing netjes vertaald van:
http://www.mail-archive.c...tgresql.org/msg52108.html

[pjotrk]

Kan zoiets niet? Ik weet ook niet zeker hoe hij dat zou uitvoeren, maar het lijkt me iig sneller dan een hele tabel sorteren (als het uberhaubt mogelijk is).

SELECT trunc(count(*) * random()) FROM links INTO :rowCounts;
SELECT linkno, domain, uri FROM links OFFSET :rowCounts LIMIT 1;

[ACM]

Verwijderd schreef op 08 november 2004 @ 22:08:
0 rows returned.

Upes, dat ging inderdaad snel .
Dit werkt alleen als linkno een niet onderbroken nummering is.

Dan pas je hem toch aan, zodat je een <= ipv een < hebt en een order by linkno desc limit 1 erachter doet?

Verwijderd schreef op 08 november 2004 @ 21:33:
Veel sneller:

SQL:

UPDATE random SET random=trunc(random()*(SELECT max(linkno) from links));
SELECT linkno, domain, uri FROM links WHERE linkno=
(SELECT random FROM random);

Stop dat dan gelijk in 1 query

SQL:

SELECT linkno, domain, uri FROM links WHERE linkno <= trunc(
random()*(SELECT max(linkno) from links)) ORDER BY linkno DESC LIMIT 1;

Waarschijnlijk is het sneller trouwens om:

SQL:

SELECT linkno FROM links ORDER BY linkno DESC LIMIT 1

Te doen, als je een relatief grote tabel met indices op linkno hebt, ipv die MAX-query.

[ Voor 40% gewijzigd door ACM op 08-11-2004 23:31 ]

[Verwijderd]

ACM schreef op 08 november 2004 @ 23:28:

SQL:

SELECT linkno, domain, uri FROM links WHERE linkno <= 
trunc(random()*(SELECT max(linkno) from links)) ORDER BY linkno DESC LIMIT 1;

Heel veel linkno's zijn kleiner of gelijk het random getal. Je pakt hier de hoogste, waardoor de lage linkno's bijna nooit gekozen worden. Verder voer je weer voor elke rij een random() uit, en je sorteert, wat beide niet echt efficient is.

SQL:

SELECT linkno FROM links ORDER BY linkno DESC LIMIT 1

Te doen, als je een relatief grote tabel met indices op linkno hebt, ipv die MAX-query.

Hiermee pak je altijd het hoogste linkno, wat natuurlijk niet random is.

[ACM]

Verwijderd schreef op 09 november 2004 @ 00:09:
Heel veel linkno's zijn kleiner of gelijk het random getal. Je pakt hier de hoogste, waardoor de lage linkno's bijna nooit gekozen worden.

Dan lees je niet goed... Want ik pak de hoogste onder het random getal, als het random getal dus 5 is, dan wordt linkno 5 gepakt.

Verder voer je weer voor elke rij een random() uit, en je sorteert, wat beide niet echt efficient is.

Ik zie dat ik vergeten was de random bij de max te plaatsen, hij hoeft inderdaad maar 1x uitgevoerd te worden en dat gebeurt als je hem bij de MAX zet, ipv voor de subquery. Sorteren kan hartstikke efficient als je een index kan gebruiken en ik mag hopen dat je een index op linkno hebt.

Anders gezegd, bij een grote dataset is deze query meestal sneller als er een index beschikbaar is:

SQL:

SELECT linkno FROM links ORDER BY linkno DESC LIMIT 1

Dan deze query:

SQL:

SELECT MAX(linkno) FROM links

De tweede gaat alle records af, de eerste sorteert omgekeerd op de index en stopt als ie er 1 heeft gevonden. Tel uit je winst

Hiermee pak je altijd het hoogste linkno, wat natuurlijk niet random is.

Lees je weer niet goed, want dat was als alternatief voor de max-query, niet voor de hele query.

Maar ik zal speciaal voor jou de query nog es posten dan, zoals ik het in gedachten had:

SQL:

SELECT linkno, domain, uri FROM links WHERE linkno <= ((SELECT 
CEIL(RANDOM()* MAX(linkno)) from links)) ORDER BY linkno DESC LIMIT 1;

En dus eventueel:

SQL:

SELECT linkno, domain, uri FROM links WHERE linkno <= ((SELECT 
CEIL(RANDOM()* linkno) from links ORDER BY linkno DESC LIMIT 1)) ORDER BY linkno DESC LIMIT 1;

En dan dus de random bij de subquery, zodat ie als het goed is maar 1x wordt uitgevoerd. Hoe vaak die subquery uitgevoerd wordt kan je met het EXPLAIN commando bekijken. Overigens kan je daarmee ook zien of je index op linkno wel bij beide queries (en de subqueries) gebruikt wordt.

Eventueel kan het dan nog zo:

SQL:

SELECT linkno, domain, uri FROM links,
((SELECT CEIL(RANDOM()* linkno) as val from links ORDER BY linkno DESC LIMIT 1)) as rand
 WHERE linkno <= rand.val ORDER BY linkno DESC LIMIT 1;

[Thekk]

Zelfs met de query's uit de vorige post, blijft er nogsteeds een probleem. Stel dat je de volgende verzameling links hebt (ik geef hier alleen de linkno's)

code:

linkno |  uri  | ...
  1    | ...
  2    | ...
  3    | ...
  4    | ...

Stel dat ik nu de 3e link verwijder uit de database, zouden de queries in de vorige post gemiddeld genomen de tweede link twee maal zo vaak tevoorschijn toveren...

Om nog maar niet te spreken over het probleem wanneer de 1e rij verwijderd zou worden, want dan zal de query af en toe geen resultaten opleveren (maar dat valt nog wel simpel op te lossen).

[ACM]

Je hebt wel een enorme corner-case bedacht. Als er maar 4 (of iig weinig) records zouden zijn, dan kan je gewoon de correcte maar niet enorm snelle ORDER BY random() doen...

Met duizenden records is het al veel minder tergend en desnoods neem je random ipv de grootste onder het random getal, de kleinste erboven om het wat correcter te maken. Of je selecteert er 10 onder en pakt daar weer een willekeurige van.

Vaak gaat optimaliseren ten koste van de opslagruimte of de correctheid, in mijn voorbeeld dus ten koste van de correctheid maar de corner case die jij hier noemt vind ik nou niet sterk genoeg om het niet te gebruiken