[mySQL] 1. Twee keer COUNT() 2. Positie in lijst bepalen

[BetuweKees]

Zou iemand me kunnen helpen met de twee onderstaanden probleempjes?
Voor de volledigheid: Ik gebruik mySQL 3.23..


VRAAG 1:

edit:
vraag 1 is inmiddels opgelost..

Ik wil graag van een tabel weten hoe vaak een veld voorkomt, en hoe vaak dit veld aan een bepaalde waarde voldoet. Momenteel gebruik ik hier twee losse query's voor die ik combineer door middel van een php script; kort komt het er op neer dat ik ik eerst kijk hoe vaak er aan mijn waarde voldaan wordt, het resultaat in een associate array opsla, kijk hoe vaak de waarde in totaal voorkomt, en vervolgens aan de hand van de assoc array het totaal aan een object hang. Aan de hand een verzameling objecten ga ik vervolgens verder met mijn programma.
Ik heb echter sterk het idee dat dit makkelijker moet kunnen, en waarschijnlijk zelfs wel in een query, ik heb alleeen geen idee hoe. De twee query's die ik momenteel gebruik zijn:

code:

SELECT 
   leden_id, 
   COUNT(*) AS pb_count 
FROM 
   overzicht 
WHERE 
   opmerking_id = '1' 
GROUP BY 
   leden_id 
ORDER BY 
   pb_count DESC;


SELECT 
   leden_id, 
   COUNT(*) AS total 
FROM 
   overzicht 
GROUP BY 
   leden_id 
ORDER BY 
   total DESC;

Hoe zou ik deze twee query's kunnen combineren?


VRAAG 2:


Hoe kan ik bepalen als hoeveelste een waarde voorkomt in een uitslag? Ik gebruik momenteel onderstaande query om een lijst te genereren, gesorteerd op tijd. Vervolgens sla ik wederom op in een assoc array en kijk ik voor een bepaald leden_id op welke plaats deze staat. Net als bij mijn eerste vraag, heb ik hier wederom het idee omslachtig bezig te zijn. Daarnaast is dit volgens mij ook behoorlijk traag als ik maar van een resultaat hoef te weten op welke plaats deze staat (voor meerdere resultaten kan ik de array iig nog een aantal keer gebruiken, dus dan zal het wel meevallen denk ik).
Mijn query is:

code:

SELECT 
   leden_id, 
   MIN(tijd) AS tijd 
FROM 
   overzicht 
GROUP BY 
   leden_id 
ORDER BY 
   tijd;

[ Voor 3% gewijzigd door BetuweKees op 19-11-2003 01:54 ]

[Verwijderd]

Kun je misschien ook een weergave van je data geven?

[SuperRembo]

Voor vraag 1 zou je zoiets kunnen doen:

code:

SELECT 
   leden_id, 
   SUM(IF(opmerking_id=1,1,0)) AS pb_count,
   COUNT(*) AS total 
FROM 
   overzicht 
GROUP BY 
   leden_id;

Bij vraag 2 snap ik niet echt wat je wil.

[marty]

vraag2: ik begrijp dat je alles in een array stopt? dan zou je daarbij toch heel eenvoudig de positie op kunnen slaan? (eerste keer dat je door het loopje loopt en de boel in de array propt sla je een 1 op, de 2e keer een 2, enz.)

[BetuweKees]

@SuperRembo: De oplossing die je aangeeft voor mijn probleem bij vraag is is echt helemaal top, en werkt super! Die ga ik ook zeker ergens mooi inlijsten, want ik weet zeker dat ik die truc vaker nodig ga hebben!!


Begrijp dat ik mijn tweede vraag weer eens niet zo duidelijk heb gesteld; lastig om abstracte dingen niet te abstract uit te leggen..
Gaat om volgende: Ik ben bezig aan een tijdendatabase systeem. Een van de functies die ik wil hebben is het weergegeven van persoonlijke en clubrecords. Dit is op zich niet ingewikkeld. Het probleem waar ik echter mee zit is dat ik wanneer ik een PR van iemand weer geef, ik er ook bij wil zetten als hoeveelste deze tijd op de clubrecord lijst voorkomt.
Omdat ik, zoals ik bij de topic start al vermelde, hier geen SQL query voor kon verzinnen, doe ik dit nu in twee stappen. Ik vraag eerst het PR van een persoon op, en daarna de lijst van clubrecords. Vervolgens loop ik de lijst van clubrecords van onder naar boven door, en als ik de persoon tegenkom waarvan ik het PR wil weten, return ik op welke plaats deze tijd gevonden is.
Naar mijn idee is dit echter een omslachtige methode, en moet dit op een slimmere manier kunnen. Mijn vraag aan jullie dus: weten jullie hoe ik dat aan kan pakken?

Als voorbeeld hieronder een stukje van mijn database. Gaan we uit van gebruiker 3 dan zou als snelste tijd 39.55 genoemd moeten worden, met een positie 3 op de clubranglijst.

code:

+----------+---------+-------+
| leden_id | tijd_id | tijd  |
+----------+---------+-------+
|        1 |       1 | 39.05 |
|        1 |      19 | 38.55 |
|        2 |       2 | 39.14 |
|        2 |      20 | 39.18 |
|        3 |       3 | 39.79 |
|        3 |      21 | 39.55 |
|        4 |       4 | 39.84 |
|        4 |      22 | 39.93 |
|        5 |       5 | 40.15 |
|        5 |      23 | 39.94 |
+----------+---------+-------+

[BetuweKees]

niemand?

[marty]

als je je wil weten op welke plaats iemand komt te staan dan zul je zowiezo een selectie moeten doen op alle rows in je tabel waarbij je een ordening aangeeft, anders weet je niet wat de positie tov de andere is.
Wat mij dan de snelste manier lijkt mij is om iedere keer na het invoeren van de tijden zoiets te doen:

MySQL:

DELETE FROM ranglijst;

INSERT INTO ranglijst (
        leden_id,
        tijd
        )
    SELECT
        leden_id,
        tijd
    FROM clubranglijst ORDER BY tijd DESC;

waarbij die ranglijst naast die twee kolommen ook nog een auto_increment kolom heeft.

Dit is enigzins een trage procedure, maar als je die tijden niet echt vaak invult brengt het toch z'n waarde op denk ik. Want dan kun je rechtstreeks uit die ranglijst tabel de huidige positie halen. (= waarde van de auto_increment kolom)

[BetuweKees]

hmm.. dat is opzich een optie natuurlijk.. enige nadeel wat ik daar in zie is dat je een hele berg redundantie gaat opleveren.. daarnaast zal er waarschijnlijk minimaal een keer in de week een serie nieuwe tijden worden ingevoerd; als er dan na elke keer invoeren op deze manier een hele nieuwe tabel moet worden aangemaakt, van alle tijden voor iedereen dan wordt het toch wel een zooitje denk ik..
ik zal er eens goed over nadenken, maar ik denk dat de oplossing die ik momenteel gebruik (twee query's en vergelijken) dan toch iets netter is..

[marty]

BetuweKees schreef op 19 november 2003 @ 19:35:
hmm.. dat is opzich een optie natuurlijk.. enige nadeel wat ik daar in zie is dat je een hele berg redundantie gaat opleveren..

dat lijkt inderdaad zo, maar dat hangt er vanaf hoe het gebruikt wordt. Als de posities op de ranglijst vrij vaak worden opgevraagd dan doe je veel meer dubbelwerk door het iedere keer uit te rekenen en is dat in termnen van redundantie dus veel groter dan deze oplossing.

daarnaast zal er waarschijnlijk minimaal een keer in de week een serie nieuwe tijden worden ingevoerd; als er dan na elke keer invoeren op deze manier een hele nieuwe tabel moet worden aangemaakt, van alle tijden voor iedereen dan wordt het toch wel een zooitje denk ik..

de tabel maak je uiteraard maar 1x aan. En dan is het alleen een kwestie van een DELETE en een INSERT. Dat duurt hooguit 2sec bij elkaar denk ik en door die DELETE begin je iedere keer met een schone lei, dus dat zootje valt ook wel mee.
(en dit doe je uiteraard met een scriptje wat je daar voor schrijft )

ik zal er eens goed over nadenken, maar ik denk dat de oplossing die ik momenteel gebruik (twee query's en vergelijken) dan toch iets netter is..

Dat hangt er dus, zoals ik al zei, maar net vanaf in welke mate wat gebeurt

[Verwijderd]

Ik denk dat je een heel eind in de richting kunt komen met de volgende query, hij is niet helemaal correct maar dat moet je zelf maar ff bekijken het idee is er in ieder geval.

SELECT * FROM tijden RIGHT JOIN Leden ON tijden.ledenid=leden.ledenid WHERE tijden.ledenid=n ORDER BY tijden.Tijd


Deze query haalt dus de beste tijd uit de club tabel voor een bepaald lid, en zoekt daarbij de persoonsgegevens op. Als er geen clubrecord is voor die persoon dan werkt het toch omdat het een RIGHT join is. Je kunt er ook nog een LIMIT op lost laaten of een andere record begrenser zodat je maar 1 record terug krijgt.

[Verwijderd]

hmm, dat klopt voor geen meter... Eerst maar ff een bakkie koffie ...

[Knutselsmurf]

Zo, ook weer druk bezig met je ranglijsten

Je kunt het volgens mij redelijk snel oplossen met 2 queries.
Met je eerste query zoek je de snelste tijd van persoon X. Deze tijd noemen we T.
Vervolgens kun je met een

code:

select count (*)+1 from tijdenlijst where eindtijd < T group by persoon_id

de bijbehorende positie op de ranglijst bepalen.

Als je dit een aantal keer op 1 pagina doet, kan het op een gegeven moment uit om de hele lijst binnen te halen en te sorteren.

[marty]

als je dit maar 1x nodig heb, dan is het wel de beste manier denk ik ja. Maar zodra je meer gaat doen met die plekken op de ranglijst (bijv. een overzichtje van alle leden die eigenschap X hebben), dan is een extra tabel veel efficienter
maar dat moet de TS dus zelf maar beslissen

[BetuweKees]

Knutselsmurf schreef op 19 november 2003 @ 21:40:
een slimme sql query

tuurlijk da's waar ook.. kan je nagaan hoeveel ik gefixeerd was om alles in een keer uit te vragen; heb hier niet eens aan gedacht..

denk dat dat dan ook meteen de oplossing is voor individuele scores (pr lijst per persoon bv) is dit natuurlijk de ideale query..
voor die groepsquery (bv adelskalender ed (waarschijnlijk brandt nu alleen bij knutselsmurf een lampje )) moet ik nog eens goed nadenken of ik dat een extra tabel waard vind, ofdat ik het gewoon bij de huidige oplossing houd..

[jvdmeer]

Getest onder MS SQL 2000, dus weet niet of het onder MySQL werkt...

SQL:

SELECT Lid, Snelste, Regel as Plaats FROM
  (
    SELECT ledenid AS Lid, MIN(tijd) AS Snelste 
      FROM ranglijst 
      GROUP BY Ledenid) T1
  ,
  (
    SELECT DISTINCT Tijd, 
      (SELECT COUNT(*)+1 FROM Ranglijst R WHERE R.tijd < R2.Tijd) AS Regel
      FROM Ranglijst R2
  ) T2
  WHERE Tijd=Snelste

PS: Wat een verschrikkelijke querie's zijn dit weer... en ik heb de tijd*100 opgeslagen, om ff een voorbeeldje in elkaar te flanzen met allemaal int's.

Het resultaat:

code:

Lid         Snelste     Plaats      
----------- ----------- ----------- 
1           3855        1
2           3914        3
3           3955        5
4           3984        7
5           3994        9

[edit:gedachtensprong]

Misschien nog een andere handige query:

SQL:

SELECT LedenID, TijdID, T2.Tijd, Plaats FROM Ranglijst,
  (SELECT DISTINCT Tijd, 
      (SELECT COUNT(*)+1 FROM Ranglijst R WHERE R.tijd < R2.Tijd) AS Plaats
      FROM Ranglijst R2
  ) T2
  WHERE T2.Tijd=Ranglijst.Tijd

om de complete lijst te geven incl. Plaats:

code:

LedenID     TijdID      Tijd        Plaats      
----------- ----------- ----------- ----------- 
1           1           3905        2
1           19          3855        1
2           2           3914        3
2           20          3918        5
3           3           3979        7
3           21          3955        6
4           4           3984        8
4           22          3993        9
5           5           4015        11
5           23          3994        10
4           5           3914        3

Let op: Onderste record toegevoegd om dubbele tijd te testen:

Dit geeft de mogelijkheid om mijn eerste SQL-Query te verkorten naar:

SQL:

SELECT LedenID, MIN(T2.Tijd) AS Tijd, MIN(Plaats) AS Plaats FROM Ranglijst,
  (SELECT DISTINCT Tijd, 
      (SELECT COUNT(*)+1 FROM Ranglijst R WHERE R.tijd < R2.Tijd) AS Plaats
      FROM Ranglijst R2
  ) T2
  WHERE T2.Tijd=Ranglijst.Tijd
  GROUP BY LedenID

Geeft als resultaat:

code:

LedenID     Tijd        Plaats      
----------- ----------- ----------- 
1           3855        1
2           3914        3
3           3955        6
4           3914        3
5           3994        10

Let op Leden 3,4 & 5 zijn van plaats veranderd doordat ik dat extra record heb toegevoegd.

[/edit]

[ Voor 55% gewijzigd door jvdmeer op 20-11-2003 19:32 . Reden: Gedachtensprong toegevoegd. ]

[SuperRembo]

jvdmeer schreef op 20 november 2003 @ 19:16:
Getest onder MS SQL 2000, dus weet niet of het onder MySQL werkt...

Ja, met sub-selects lukt het natuurlijk wel in een keer. Maar met MySQL 3.23 werkt dat dus niet.

[BetuweKees]

SuperRembo schreef op 20 november 2003 @ 21:00:
Ja, met sub-selects lukt het natuurlijk wel in een keer. Maar met MySQL 3.23 werkt dat dus niet.

dat klopt, maar ik ga deze iig wel even ergens opslaan, voor het geval mijn hosting nog eens besluit mySQL 4.1 te installeren

dank @ jvdmeer