moet voorkomen in de GROUP BY-clausule of worden gebruikt in een aggregatiefunctie

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 BYclausule[*].

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 cnameniveau) maxvoor 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 byselecties 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 (minen maxzijn 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+luffyof spain+usoppzijn? 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 whichen countverklaringen 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