Ik heb een tabel die eruitziet als deze beller ‘makerar’
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
En ik wil het maximale gemiddelde voor elke cname selecteren.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
maar ik krijg een foutmelding,
ERROR: column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
dus ik doe dit
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
dit geeft echter niet de beoogde resultaten en de onderstaande onjuiste uitvoer wordt weergegeven.
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Werkelijke resultaten zouden moeten zijn
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Hoe kan ik dit probleem oplossen?
Opmerking: deze tabel is een VIEW gemaakt op basis van een eerdere bewerking.
Antwoord 1, autoriteit 100%
Ja, dit is een veelvoorkomend aggregatieprobleem. Vóór SQL3 (1999)moeten de geselecteerde velden verschijnen in de GROUP BY
clausule[*].
Om dit probleem te omzeilen, moet u het aggregaat in een subquery berekenen en het vervolgens samenvoegen met zichzelf om de extra kolommen te krijgen die u moet weergeven:
SELECT m.cname, m.wmname, t.mx
FROM (
SELECT cname, MAX(avg) AS mx
FROM makerar
GROUP BY cname
) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Maar u kunt ook vensterfuncties gebruiken, die er eenvoudiger uitzien:
SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;
Het enige met deze methode is dat alle records worden getoond (vensterfuncties worden niet gegroepeerd). Maar het zal de juiste (d.w.z. maxed op cname
niveau) max
voor het land in elke rij tonen, dus het is aan jou:
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
De oplossing, aantoonbaar minder elegant, om de enige (cname, wmname)
-tupels weer te geven die overeenkomen met de maximale waarde, is:
SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
m.cname, m.wmname, t.avg AS mx
FROM (
SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn
FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
[*]: Interessant genoeg, hoewel de specificatie het mogelijk maakt om niet-gegroepeerde velden te selecteren, lijken grote zoekmachines het niet echt leuk te vinden. Oracle en SQLServer staan dit gewoon helemaal niet toe. Mysql stond het standaard toe, maar sinds 5.7 moet de beheerder deze optie (ONLY_FULL_GROUP_BY
) handmatig inschakelen in de serverconfiguratie om deze functie te ondersteunen…
Antwoord 2, autoriteit 50%
In Postgres kunt u ook de speciale DISTINCT ON (expression)
syntaxis:
SELECT DISTINCT ON (cname)
cname, wmname, avg
FROM
makerar
ORDER BY
cname, avg DESC ;
Antwoord 3, autoriteit 15%
Het probleem met het specificeren van niet-gegroepeerde en niet-geaggregeerde velden in group by
selecties is dat de engine niet weet welk recordveld het in dit geval moet retourneren. Is het eerst? Is het de laatste? Er is meestal geen record dat van nature overeenkomt met het geaggregeerde resultaat (min
en max
zijn uitzonderingen).
Er is echter een tijdelijke oplossing: maak het vereiste veld ook geaggregeerd.
In posgres zou dit moeten werken:
SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;
Merk op dat dit een array maakt van alle wnames, geordend op avg, en het eerste element retourneert (arrays in postgres zijn 1-gebaseerd).
Antwoord 4, autoriteit 11%
Voor mij gaat het niet om een “veelvoorkomend aggregatieprobleem”, maar gewoon om een onjuiste SQL-query. Het enige juiste antwoord voor “selecteer het maximale gemiddelde voor elke cname…” is
SELECT cname, MAX(avg) FROM makerar GROUP BY cname;
Het resultaat is:
cname | MAX(avg)
--------+---------------------
canada | 2.0000000000000000
spain | 5.0000000000000000
Dit resultaat beantwoordt in het algemeen de vraag “Wat is het beste resultaat voor elke groep?”. We zien dat het beste resultaat voor spanje 5 is en voor canada het beste resultaat 2. Het is waar, en er is geen fout.
Als we wmnameook moeten weergeven, moeten we de vraag beantwoorden: “Wat is de RULEom wmname uit de resulterende set te kiezen?” Laten we de invoergegevens een beetje wijzigen om de fout te verduidelijken:
cname | wmname | avg
--------+--------+-----------------------
spain | zoro | 1.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
Welk resultaat verwacht je bij het uitvoeren van deze query: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
? Moet het spain+luffy
of spain+usopp
zijn? Waarom? Het is niet bepaaldin de query hoe “beter” wmnamegekozen moet worden als meerdere geschikt zijn, dus het resultaat wordt ook niet bepaald. Dat is de reden waarom SQL-interpreter een fout retourneert – de query is niet correct.
Met andere woorden, er is geen juist antwoord op de vraag “Wie is de beste in de spain
-groep?”. Luffy is niet beter dan usopp, omdat usopp dezelfde “score” heeft.
Antwoord 5, autoriteit 7%
SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
SELECT cname, MAX(avg) max
FROM makerar
GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;
Gebruik rank()
vensterfunctie:
SELECT cname, wmname, avg
FROM (
SELECT cname, wmname, avg, rank()
OVER (PARTITION BY cname ORDER BY avg DESC)
FROM makerar) t
WHERE rank = 1;
Opmerking
Een van beide behoudt meerdere max-waarden per groep. Als u slechts één record per groep wilt, zelfs als er meer dan één record is met avg gelijk aan max, moet u het antwoord van @ypercube controleren.
Antwoord 6
Dit lijkt ook te werken
SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
FROM makerar m2
WHERE m1.cname = m2.cname
)
Antwoord 7
Ik kwam onlangs dit probleem tegen toen ik probeerde te tellen met behulp van case when
, en ontdekte dat het veranderen van de volgorde van de which
en count
verklaringen lost het probleem op:
SELECT date(dateday) as pick_day,
COUNT(CASE WHEN (apples = 'TRUE' OR oranges 'TRUE') THEN fruit END) AS fruit_counter
FROM pickings
GROUP BY 1
In plaats van – in het laatste geval, waar ik fouten kreeg dat appels en peren zouden moeten verschijnen in geaggregeerde functies
CASE WHEN ((apples = 'TRUE' OR oranges 'TRUE') THEN COUNT(*) END) END AS fruit_counter