[SQL] GROUP BY niet correct? - Softwareontwikkeling

maandag 28 november 2005 16:36

Acties:

Topicstarter

Ik probeer voor het welbekende phpBB forumsysteem een query te schrijven die in één keer per subforum de namen geeft die per forum de meeste topics geplaatst hebben, met het desbetreffende aantal erbij. Ik verwacht dus een resultaat zoals dit:

code:

nickname | user_id | topics | forum
=====================================
piet     | 23      | 102    | auto's
henk     | 345     | 340    | muziek
klaas    | 5       | 84     | boeken

Maar in plaats daarvan, krijg ik iets als dit, met dus teveel rijen en niet uitgesplitst per forum:

code:

nickname | user_id | topics | forum
=====================================
piet     | 23      | 102    | auto's
henk     | 345     | 76     | auto's
marie    | 8       | 74     | auto's
henk     | 345     | 70     | muziek
joop     | 12      | 64     | auto's
klaas    | 5       | 52     | boeken
piet     | 23      | 30     | muziek

Mijn query is als volgt, ik denk dat de kolom- en tabelnamen wel voor zich spreken;

code:

SELECT u.username AS nickname, u.user_id, COUNT(u.user_id) AS topics, f.forum_name AS forum
FROM phpbb_topics t, phpbb_users u, phpbb_forums f
WHERE t.topic_poster = u.user_id
AND t.forum_id = f.forum_id
AND u.user_id <> -1
GROUP BY u.user_id, f.forum_id
ORDER BY topics DESC, forum_order ASC;

Het heeft dus alles te maken met het groeperen van de rijen. Ik heb veel varianten geprobeert, maar het lukt niet zoals ik wil.

Een GROUP BY op "f.forum_id" alleen geeft wel het goede áántal rijen, maar de opgetelde topics zijn daarbij niet uitgesplitst per lid (wat dus betekent dat de gegeven namen willekeurig zijn) en als ik alleen groepeer op "u.user_id" krijg ik een uitdraai van alle gemaakte topics per lid, wat me dus evenveel rijen oplevert als dat er topics zijn geplaatst door verschillende leden in verschillende forums.

Waar doe ik iets fout? Hetgeen ik wil moet toch mogelijk zijn? Mijn server draait MySQL versie v3.23.58, als er meer details nodig zijn wat betreft de tabelstructuur geef ik die graag.

maandag 28 november 2005 16:37

Acties:

whoami

Die group by is idd niet correct.

Check dit:
P&W FAQ - SQL

[ Voor 42% gewijzigd door whoami op 28-11-2005 16:37 ]

https://fgheysels.github.io/

maandag 28 november 2005 16:41

Acties:

kenneth

achter de duinen

Je groepeert nu per gebruikersnaam, maar je wil de gebruikersnaam waarvoor geldt dat hij bovenaan de lijst van aantal topics per forum staat.

Look, runners deal in discomfort. After you get past a certain point, that’s all there really is. There is no finesse here.

maandag 28 november 2005 16:47

Acties:

mosymuis

Topicstarter

Ik heb de FAQ pagina nog eens doorgelezen, en begrijp uit de reactie van kenneth dat ik toch alleen moet groeperen op forum_id, en op de gebruiker een aggregate functie moet toepassen om de hoogste topics poster te kiezen. Maar hoe doe ik dat? Een MAX() op user_id of username slaat nergens op, het heeft betrekking tot de score topics uit de COUNT() functie. Ik zou dus zoiets verwachten, maar dat geeft een MySQL error: "#1111 - Invalid use of group function"

code:

SELECT u.username AS nickname, u.user_id, MAX(COUNT(u.user_id)) AS topics, f.forum_name AS forum
FROM phpbb_topics t, phpbb_users u, phpbb_forums f
WHERE t.topic_poster = u.user_id
AND t.forum_id = f.forum_id
AND u.user_id <> -1
GROUP BY f.forum_id
ORDER BY topics DESC, forum_order ASC;

maandag 28 november 2005 17:00

Acties:

whoami

Als je enkel groepeert op forum_id, dan groepeer je verkeerd.

't Is niet omdat mySql een dergelijke constructie toelaat, dat het daarom correct is.
Groeperen doe je op de velden die in je select lijst staan, en geen resultaat van een aggregate functie zijn.

Dat je dan een 'verkeerd' resultaat krijgt (als in: teveel rijen), dan is dat best logisch.
Ik geloof niet dat je hetgeen jij wilt in één query kunt doen. (Of toch alleszins niet in één zo'n simpele query).

[ Voor 30% gewijzigd door whoami op 28-11-2005 17:02 ]

https://fgheysels.github.io/

maandag 28 november 2005 17:04

Acties:

mosymuis

Topicstarter

whoami schreef op maandag 28 november 2005 @ 17:00:
Ik geloof niet dat je hetgeen jij wilt in één query kunt doen. (Of toch alleszins niet in één zo'n simpele query).

Ik vind dit dan wel aannemelijk, want met de gegeven tips kom ik er niet meer uit. Ik heb inderdaad gelezen over hoe MySQL tolerant is t.o.v. foutieve queries.

maandag 28 november 2005 17:30

Acties:

kenneth

achter de duinen

Gebruikersnaam moet je wel opnemen in je query, maar er moet een WHERE clausule bij waar je alleen de gewenste gebruiker kiest. Normaliter doe ik dat in een subselect, maar ik weet niet of jouw versie van MySQL dat ondersteunt.

Look, runners deal in discomfort. After you get past a certain point, that’s all there really is. There is no finesse here.

maandag 28 november 2005 18:01

Acties:

mosymuis

Topicstarter

kenneth schreef op maandag 28 november 2005 @ 17:30:
Gebruikersnaam moet je wel opnemen in je query, maar er moet een WHERE clausule bij waar je alleen de gewenste gebruiker kiest.

Wat bedoel je? Ik weet niet wat de best postende gebruiker is in een forum, dat is juist aan de database om te bepalen.

Normaliter doe ik dat in een subselect, maar ik weet niet of jouw versie van MySQL dat ondersteunt.

Nee, dat wordt niet ondersteund in MySQL < 4.1

maandag 28 november 2005 18:30

Acties:

NMe

Quia Ego Sic Dico.

In een GROUP BY moet je àlle velden die je selecteert ook opnemen, met uitzondering van de velden die je selecteert met behulp van een aggregate function (zoals COUNT). Dat staat toch ook in de FAQ?

SQL:

SELECT 
    u.username AS nickname,
    u.user_id, COUNT(u.user_id) AS topics,
    f.forum_name AS forum
FROM
    phpbb_topics t,
    phpbb_users u,
    phpbb_forums f
WHERE
    t.topic_poster = u.user_id
    AND t.forum_id = f.forum_id
    AND u.user_id <> -1
GROUP BY
    f.forum_name, u.user_name
ORDER BY
    topics DESC,
    forum_order ASC

Dat moet toch gewoon doen wat je wil?

[ Voor 51% gewijzigd door NMe op 28-11-2005 18:34 ]

'E's fighting in there!' he stuttered, grabbing the captain's arm.
'All by himself?' said the captain.
'No, with everyone!' shouted Nobby, hopping from one foot to the other.

maandag 28 november 2005 18:57

Acties:

ACM

Software Architect

Werkt hier

-NMe- schreef op maandag 28 november 2005 @ 18:30:
Dat moet toch gewoon doen wat je wil?

Waar filter jij het op "enkel die user per forum met de maximale topiccount" dan?

Maar zoals gezegd kan dit niet in een query zonder subselects. In mysql kan je dat overigens meestal wel vrij aardig afvangen door de resultaten van "wat eigenlijk de subselect is" in een temporary table te stoppen. Selecteer dan ongeveer wat je nu al hebt, selecteer daaruit per forum dan ook de maximale waarde, neem dan die records van je eerdere resultaat die die maximale waarde van dat forum hebben.
Met 2 temporary tables moet het dus lukken.

[ Voor 3% gewijzigd door ACM op 28-11-2005 18:58 ]

maandag 28 november 2005 21:47

Acties:

Verwijderd

-NMe-, in je SELECT gedeelte zit ook nog u.user_id (niet geaggregeerd), terwijl die niet in je GROUP BY voorkomt...
Bovendien groepeer je op velden die niet uniek hoeven te zijn, tenminste, daar ga ik van uit: ik neem aan dat de *_id velden uniek moeten zijn, en dat dit niet verplicht is voor de *_name velden.

Ik gebruik zelf geen MySQL, maar met deze query zou 't toch moeten werken?

SQL:

SELECT 
  t.topic_poster, 
  MAX(u.username) AS nickname, 
  MAX(f.forum_name) AS forum,
  COUNT(*) AS topics
FROM 
  phpbb_topics t, 
  phpbb_users u, 
  phpbb_forums f
WHERE 
  t.topic_poster = u.user_id
  AND t.forum_id = f.forum_id
  AND u.user_id <> -1
GROUP BY 
  t.topic_poster, 
  t.forum_id
ORDER BY 
  forum_order ASC,
  topics DESC;

Die MAX() functies lijken overbodig, maar aangezien die per *_id toch identiek zijn maakt dat niks uit. En omdat 't nu wel aggregate columns zijn, is GROUP BY ook weer blij.

maandag 28 november 2005 23:45

Acties:

mosymuis

Topicstarter

Bedankt voor jullie recties tot zover!

Ik krijg toch het idee dat het kan werken. Afterlife, kan je me uitleggen wat MAX() voor nut heeft op niet-nummerieke waarden? Het maakt namelijk geen verschil op de uitkomst als ik de functies weglaat.

Zowel de queries van -NME- als Afterlife gaven ongeveer dezelfde resultaten (screenshots, resp. 1, 2) als het voorbeeld in mijn openingspost. De gegevens in de query van -NME- kloppen, de telling in Afterlife's query gaat scheef. Bij beide pogingen krijg ik teveel rijen.

Zou ik voor elk subforum één resultaat krijgen (namelijk de poster met de meeste topics), dan is de query geslaagd. Als dit niet kan in één query, zoals ACM sugereerde, dan geef ik het op...

dinsdag 29 november 2005 06:46

Acties:

P_de_B

ACM heeft gelijk, je hebt een subquery nodig in de HAVING clausule. In jouw geval moet je dus met een temporary tabel werken omdat je MySQL versie dit nog niet ondersteunt.

Oops! Google Chrome could not find www.rijks%20museum.nl

dinsdag 29 november 2005 07:23

Acties:

ACM

Software Architect

Werkt hier

Verwijderd schreef op maandag 28 november 2005 @ 21:47:
Die MAX() functies lijken overbodig, maar aangezien die per *_id toch identiek zijn maakt dat niks uit. En omdat 't nu wel aggregate columns zijn, is GROUP BY ook weer blij.

Jouw query doet iets heel anders, die kijkt voor alle gebruikers hoeveel topics ie gepost heeft, met zijn maximale username (hij heeft er maar 1) en maximale forumnaam (erg zinloze informatie

).

mosymuis schreef op maandag 28 november 2005 @ 23:45:
Zou ik voor elk subforum één resultaat krijgen (namelijk de poster met de meeste topics), dan is de query geslaagd. Als dit niet kan in één query, zoals ACM sugereerde, dan geef ik het op...

Maar waarom zou je niet 3 queries uitvoeren, waarbij je de resultaten in een temporary table opslaat? Als je die gegevens nodig hebt, zal je er wat voor moeten doen

[ Voor 33% gewijzigd door ACM op 29-11-2005 07:24 ]

dinsdag 29 november 2005 09:24

Acties:

mosymuis

Topicstarter

Ik zou het zoiezo kunnen realiseren met mijn query in de TS, en een verdere verwerking in PHP. Het gaat er echter om dat ik de query wil plaatsen in een automatisch statestieken script waar ik aan werk, en waar ik voor elke lijst één query plaats. Daarom zocht ik dus naar een oplossing in SQL. Ik begrijp dat dat niet gaat, toch bedankt voor de moeite, ik ben er weer wat wijzer van geworden.

dinsdag 29 november 2005 09:32

Acties:

P_de_B

mosymuis schreef op dinsdag 29 november 2005 @ 09:24:
Ik zou het zoiezo kunnen realiseren met mijn query in de TS, en een verdere verwerking in PHP. Het gaat er echter om dat ik de query wil plaatsen in een automatisch statestieken script waar ik aan werk, en waar ik voor elke lijst één query plaats. Daarom zocht ik dus naar een oplossing in SQL. Ik begrijp dat dat niet gaat, toch bedankt voor de moeite, ik ben er weer wat wijzer van geworden.

Het gaat wel, maar dan met een temporary table zoals ACM al 2 keer heeft gezegd. Je hoeft denk ik niet echt rekening houden met performance omdat de query niet vaak uitgevoerd wordt?

Oops! Google Chrome could not find www.rijks%20museum.nl

dinsdag 29 november 2005 10:50

Acties:

mosymuis

Topicstarter

P_de_B schreef op dinsdag 29 november 2005 @ 09:32:
Het gaat wel, maar dan met een temporary table zoals ACM al 2 keer heeft gezegd.

Ja, maar dan heb je het dus al over meerde queries.

dinsdag 29 november 2005 10:52

Acties:

P_de_B

Inderdaad, of je MySQL versie upgraden, maar meerdere queries is niet perse een slecht ding, zeker als het om queries gaat die slechts incidenteel gerund worden.

Oops! Google Chrome could not find www.rijks%20museum.nl

dinsdag 29 november 2005 21:34

Acties:

Verwijderd

ACM schreef op dinsdag 29 november 2005 @ 07:23:
Jouw query doet iets heel anders, die kijkt voor alle gebruikers hoeveel topics ie gepost heeft, met zijn maximale username (hij heeft er maar 1) en maximale forumnaam (erg zinloze informatie ).

Had ik al gezegd dat ik geen ervaring met MySQL heb? Dacht 't wel...
Die MAX() functies had ik erin gezet omdat een hoop (de meeste?) database systemen niet houden van niet-aggregate kolommen in de SELECT clause die niet in de GROUP BY terugkomen. Zoals u.user_id in -NMe-'s voorbeeld. Maar blijkbaar is MySQL hier wat vergevingsgezinder c.q. slimmer in dan de SQL 89 standaard...

Die maximale forumnaam is overigens de max. forumnaam per GROUP BY groep, oftewel ook weer uniek. Althans, dat was de bedoeling, maar gezien de screenshot van mijn query resultaat denkt MySQL daar ietsje anders over.

/me kruipt maar weer terug onder z'n MSSQL/Interbase steen...

woensdag 30 november 2005 08:43

Acties:

P_de_B

Verwijderd schreef op dinsdag 29 november 2005 @ 21:34:
[...]

Had ik al gezegd dat ik geen ervaring met MySQL heb? Dacht 't wel...
Die MAX() functies had ik erin gezet omdat een hoop (de meeste?) database systemen niet houden van niet-aggregate kolommen in de SELECT clause die niet in de GROUP BY terugkomen. Zoals u.user_id in -NMe-'s voorbeeld. Maar blijkbaar is MySQL hier wat vergevingsgezinder c.q. slimmer in dan de SQL 89 standaard...

Die maximale forumnaam is overigens de max. forumnaam per GROUP BY groep, oftewel ook weer uniek. Althans, dat was de bedoeling, maar gezien de screenshot van mijn query resultaat denkt MySQL daar ietsje anders over.

/me kruipt maar weer terug onder z'n MSSQL/Interbase steen...

MySQL is niet vergevingsgezinder, het is gewoon achterlijk dat MySQL het wel goed keurt dat er een kolom in de select staat die niet in aggregate of de group by zit. Daarnaast zal ook MS SQL niet het gewenste resultaat geven met je query. Om het in MS SQL goed te doen (iets vereenvoudigd)

SQL:

SELECT userid, forumid, count(*) as NoPosts
FROM topics t
GROUP BY userid, forumid
HAVING COUNT(*) =  (SELECT TOP 1  COUNT(*) FROM Topics 
            WHERE forumid = t.forumid GROUP BY userid ORDER BY COUNT(*) DESC )

of als er gebruikers zijn die hetzelfde aantal posts in een forum hebben en je die ook wilt zien:

SQL:

SELECT userid, forumid, count(*) as NoPosts
FROM topics t
GROUP BY userid, forumid
HAVING COUNT(*) IN  (SELECT TOP 1 WITH TIES COUNT(*) FROM Topics 
            WHERE forumid = t.forumid GROUP BY userid ORDER BY COUNT(*) DESC )

In MySQL kun je dit ook doen vanaf 4.1

Oops! Google Chrome could not find www.rijks%20museum.nl