Udtræk x tilfældige records
Denne artikel gennemgår hvordan man kan udtrække et antal tilfældige records fra en tabel i MS Access.
Der tages udgangspunkt i en hjemmeside i ASP, men da det hele (næsten) foregår i en SQL-streng kan det også bruges i andre sammenhænge.
Vi vil her forudsætte af tabellen, der skal udtrækkes fra indeholder en kolonne af tal, hvori der ikke optræder dubletter. Dette kunne f.eks. være et autonummereringsfelt.
Vi kalder vores tabel 'tabel' og den kunne se således ud:
id felt
1 tekst 1
2 tekst 2
3 tekst 3
6 tekst 6
8 tekst 8
Problemet med MS Access er, at man ikke kan skrive SELECT felt FROM tabel ORDER BY Rnd og så forvente at den laver et nyt tilfældigt tal til hver record, som man så sorterer efter. Der vil kun blive dannet ét tilfældigt tal, som gives til alle records.
Ydermere, så vil det 'tilfældige' tal være det samme hver gang man spørger databasen. F.eks. giver SELECT Rnd altid det samme.
Følgende kode vil udtrække kolonnerne felt og id fra 3 tilfældige records i tabellen tabel:
Randomize
Rnds = ""
For i = 1 To 100
Rnds = Rnds & Int(Rnd*100)
Next
sql = "SELECT TOP 3 tabel.felt, tabel.id " & _
" FROM ( " & _
" tabel " & _
" LEFT JOIN " & _
" ( " & _
" SELECT x.id, COUNT(1) - 1 AS mindre " & _
" FROM tabel x, tabel y " & _
" WHERE x.id >= y.id GROUP BY x.id " & _
" ) AS mindre " & _
" ON tabel.id = mindre.id " & _
" ) " & _
" ORDER BY " & _
" Mid('" & Rnds & "', 2*mindre.mindre+1, 2) "
Set rs = conn.Execute(sql)
Vi går nu igennem hvert trin for sig og forklarer hvad der sker:
FROM (
tabel
LEFT JOIN
(
SELECT x.id, COUNT(1) - 1 AS mindre
FROM tabel x, tabel y
WHERE x.id >= y.id GROUP BY x.id
) AS mindre
ON tabel.id = mindre.id
Her tager vi tabellen tabel og tilføjer en kolonne, der hedder mindre.
mindre indeholder antallet af records i tabellen, der har et id, der er mindre end rækkens id.
Denne tabel ser altså således ud:
id felt mindre
1 tekst 1 0
2 tekst 2 1
3 tekst 3 2
6 tekst 6 3
8 tekst 8 4
Bemærk at da der ikke er dubletter id, så er der det heller ikke i mindre.
mindre er i øvrigt en fortløbende nummerering af records'ne startende med 0 i den første record.
Randomize
Rnds = ""
For i = 1 To 100
Rnds = Rnds & Int(Rnd*10)
Next
Her laves en sekvens af tilfældige tal mellem 0 og 9 (begge inkl). Tallene sættes sammen som cifrene i et 100-cifret tal.
Dette gøres da databasen ikke selv kan generere de tilfældige tal, så laver vi en hel masse undenfor databasen og overfører dem i SQL-strengen.
Husk Randomize som initialiserer tilfældighedsgeneratoren.
Lad os antage at Rnds er 1234567890...890.
ORDER BY
Mid('1234567890...890', 2*mindre.mindre+1, 2)
Når mindre er 0, så udtages de første 2 cifre. Her 12.
Når mindre er 1, så udtages de næste 2 cifre. Her 34.
Når mindre er 2, så udtages de næste 2 cifre. Her 56.
På denne måde tildeles hver række et tilfældigt tal, som der så kan ordnes efter.
Læg mærke til at det 100 cifrede tal behandles som en streng, idet tal ikke kan have så mange betydende cifre i databasen. Så ville tallet bare laves om til 1.2345E100.
At vi her at valgt at lave et 100-cifret tal er bare et eksempel, men der skal bare være tal nok til alle records i tabellen, så man skal lave 2 * (det maksimale antal records i tabellen) tilfældige tal mellem 0 og 9.
SELECT TOP 3 tabel.felt, tabel.id
Her vælges at der skal udtrækkes 3 records, og det er felterne felt og id, der skal trækkes ud.
Hvorfor laver vi den ekstra kolonne mindre, når vi alligevel har kolonnen id?
Hvis man har en tabel, hvor der ofte bliver tilføjet og slettet records, så vil et autonummereringsfelt få relativt høje værdier. En værdi på 10 vil kræve et 20-cifret tal i variablen Rnds. Længden af det tilfældige tal vil så skulle blive længere og længere.
I dette tilfælde skal det tilfældige tal bare være længere end antallet af records i tabellen, og ikke antallet af records er på et eller andet tilspunkt har været i tabellen.
Denne artikel er skrevet af Torben Brandt, torben@actuar.dk, 11/4-2003