Avatar billede mishrah Nybegynder
12. august 2012 - 17:46 Der er 5 kommentarer og
1 løsning

SQL sortering flere tabeller og tidsafgrænsning

Hej gutter :)

Jeg har to tabeller - Spillere og Registreringer der ser ud som følger:

Spillere:

- id (int)
- alias (varchar)
- spillerNr (int)

Registreringer:

- id (int)
- dato (unix timestamp)
- spillerId (int)

Jeg ønsker at lave et udtræk der viser en top 10 over hvilken spiller der har lavet flest registreringer indenfor den bestemt periode, og sorteret efter flest registreringer.

Hvis jeg ikke skal have tidsparametret med, er det "simpelt nok" - så gør jeg således:

SELECT s.id, s.alias, s.spillerNr, COUNT(*) as antalRegistreringer FROM Spillere s, Registreringer r WHERE s.id = r.spillerId GROUP BY s.id,s.alias,s.spillerNr ORDER by COUNT(*) DESC LIMIT 10

Problemet opstår når jeg skal lave samme top 10, men kun have registreringer indenfor en bestemt tidsramme.

SELECT s.id, s.alias, s.spillerNr, COUNT(*) as antalRegistreringer FROM Spillere s, Registreringer r WHERE s.id = r.spillerId AND r.dato > '1354320000' AND r.dato < '1356998399' GROUP BY s.id,s.alias,s.spillerNr ORDER by COUNT(*) DESC LIMIT 10

Det giver ingen resultater fordi antalRegistreringer = 0.
Jeg er bare lidt blank, for hvordan jeg får løst det.

Som sagt ønsker jeg en top 10 over spillere, sorteret efter flest registreringer indenfor en angiven periode - fx fra 1354320000 til 1356998399 (01.01.2012 - 31.12.2012).

Håber der er nogen der kan hjælpe.
På forhånd mange tak,
Martin
12. august 2012 - 21:24 #1
Hvilket database system bruger du?  Jeg har ikke kunnet finde en datatype 'unix timestamp' hverken i mysql eller i andre af de gængse database systemer.  Er dato i virkeligheden en tekststreng (VARCHAR)?
Avatar billede mishrah Nybegynder
12. august 2012 - 21:27 #2
Jeg bruger MySQL :)
Nej datofeltet er faktisk bare et integer felt - det skulle jeg nok lige have forklaret lidt bedre :)

Mit problem er dog ikke at jeg ikke kan sortere på datoer - jeg kan sagtens lave et udtræk for én enkelt spiller hvor jeg angiver en datoafgrænsning.

Problemet er når jeg vil have en liste over TOP 10 sorteret efter antal registreringer, hvor jeg kun vil have registreringer indenfor en bestemt periode skal tælle med.

Al hjælp værdsættes! :)
13. august 2012 - 08:19 #3
Ja, det er mystisk.  Jeg ser ingen fejl.  Du fortæller at denne forespørgsel giver det forventede resultat, jeg skal kalde den forespørgsel1:

SELECT s.id, s.alias, s.spillerNr, COUNT(*) as antalRegistreringer
FROM Spillere s, Registreringer r
WHERE s.id = r.spillerId GROUP BY s.id,s.alias,s.spillerNr ORDER by COUNT(*) DESC LIMIT 10

Så fortæller du, at du kan lave udtræk for en enkelt spiller med datoafgrænsning.  Kan du vise sådan en forespørgsel?  Hvis denne forespørgsel også giver det forventede resultat for spiller med id = 25, jeg skal kalde den forespørgsel2:

SELECT s.id, s.alias, s.spillerNr, COUNT(*) as antalRegistreringer
FROM Spillere s, Registreringer r
WHERE s.id = r.spillerId
AND r.dato > '1354320000'
AND r.dato < '1356998399'
AND s.id = 25
GROUP BY s.id,s.alias,s.spillerNr

så fald SKAL denne forespørgsel også give det korrekte resultat, jeg skal kalde den forespørgsel3:

SELECT s.id, s.alias, s.spillerNr, COUNT(*) as antalRegistreringer
FROM Spillere s, Registreringer r
WHERE s.id = r.spillerId
AND r.dato > '1354320000'
AND r.dato < '1356998399'
GROUP BY s.id,s.alias,s.spillerNr
ORDER by COUNT(*)
DESC LIMIT 10

Hvis forespørgsel2 ikke virker, så kunne det måske være fordi værdierne af dato er INT som ikke skal quotes.  Altså r.dato : 135432000 o.s.v.  Men min egen version af mysql tolererer '135432000' selv om det strengt taget ikke er korrekt.

Kan du fortælle mere?

To sidebemærkninger: 

(1) Jeg finder det mere oversigteligt at bruge BETWEEN i stedet for 'større end' og 'mindre end', såsom:

WHERE s.id = r.spillerId AND r.dato BETWEEN '1354320000' AND '1356998399'

(2)  Jeg finder det også mere oversigteligt at bruge JOIN til at joine tabeller.  Altså i stedet for

SELECT ...
FROM Spillere s, Registreringer r
WHERE s.id = r.spillerId



SELECT ...
FROM Spillere s
JOIN Registreringer r ON s.id = r.spillerId
Avatar billede mishrah Nybegynder
13. august 2012 - 08:41 #4
Først og fremmest tak fordi du giver bruge tid på at hjælpe mig :)

Altså vi kan lige hurtigt omformulere og kalde udtrækkene følgende:

1. Udtræk der giver top 10 over spillere med flest registreringer (ikke datoafgrænset)
2. Udtræk fra en enkelt spiller (med datoafgrænsing)
3. Udtræk der giver top 10 over spillere med flest registreringer afgrænset af dato

1.
SELECT s.id, s.alias, s.spillerNr, COUNT(*) as antalRegistreringer FROM Spillere s, Registreringer r WHERE s.id = r.spillerId GROUP BY s.id,s.alias,s.spillerNr ORDER by COUNT(*) DESC LIMIT 10

2.
SELECT COUNT(*) AS antalRegistreringer FROM Registreringer WHERE spillerId = 1 AND dato > '1354320000 ' AND dato < '1356998399'

3.
Her kommer problemet, fordi jeg hvis jeg bruger løsning 1 som udgangspunkt og blot tilføjer datoafgrænsing, så bliver antalRegistreringer 0 for alle poster og det er ikke top 10 der kommet med i udtrækket :(
Mit forsøg ser således ud, men det VIRKER IKKE :(

SELECT s.id, s.alias, s.spillerNr, COUNT(*) as antalRegistreringer FROM Spillere s, Registreringer r WHERE s.id = r.spillerId AND r.dato > '1354320000' AND r.dato < '1356998399' GROUP BY s.id,s.alias,s.spillerNr ORDER by COUNT(*) DESC LIMIT 10

Jeg har ikke nogen løsning på hvordan SQL'en kan skrues sammen så den virker, hvorfor jeg spørger om hjælp :)
Avatar billede mishrah Nybegynder
13. august 2012 - 14:40 #5
Argh!

Det var en fejl 40 :( :( :(
Jeg var kommet til at generere forkerte timestamps og derfor virkede  gav antalRegistreringer altid 0.

SELECT s.id, s.alias, s.spillerNr, COUNT(*) as antalRegistreringer FROM Spillere s, Registreringer r WHERE s.id = r.spillerId AND r.dato > '1354320000' AND r.dato < '1356998399' GROUP BY s.id,s.alias,s.spillerNr ORDER by COUNT(*) DESC LIMIT 10

virker derfor fint, hvis man bruger de rigtige timestamps. Dem jeg har brugt i eksemplet er fra 1/12 2012 - 31/12 2012 istedet for 1/1 2012 - 31/12 2012. Ups!

Og tak for din tid Christian_Belgien.
Jeg fik dog list du af din hjælp alligevel, da BETWEEN da er lidt mere elegant at bruge. Så smid bare et svar, så får du point :)
Jeg vil have det dårlig med selv at tage point, når fejlen lå hos mig selv :)
13. august 2012 - 19:50 #6
Det var nok det jeg tænkte, at det ikke den måde sql'en var skruet sammen der var problemet, men de data du puttede i queryen.  Derfor bad jeg dig om at bekræfte at du kunne søge for en enkelt spiller med tidsbegrænsning, for så skulle du også kunne søge efter top 10.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester