Am un tabel care arata ca acest apelant 'makerar'
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Și vreau pentru a selecta maxim avg pentru fiecare cname.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
dar eu va primi o eroare,
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;
așa că am face acest lucru
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
cu toate acestea, acest lucru nu va da finalitate rezultate, și la ieșire incorect de mai jos este prezentat.
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Rezultatele reale ar trebui să fie
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Cum pot repara aceasta problema?
Notă: Acest tabel este o VEDERE a creat de la o operație anterioară.
Da, aceasta este o comună de agregare problema. Înainte de SQL3 (1999), câmpurile selectate trebuie să apară în anii `GROUP BY clauza de[*].
Pentru a soluționa această problemă, trebuie să calculeze agregate într-o sub-interogare și apoi se alăture cu el însuși pentru a obține suplimentare de coloane pe care le'd trebuie sa arate:
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
Dar puteți folosi, de asemenea, fereastra de funcții, care pare mai simplu:
SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;
Singurul lucru cu această metodă este că acesta va afișa toate înregistrările (fereastra de funcții nu de grup). Dar se va arăta corect (adică maxed la cname nivel a) "MAX." pentru țară, în fiecare rând, astfel încât acesta's până la tine:
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
Soluția, fără îndoială, mai puțin elegant, pentru a arăta doar (cname, wmname)
tupluri potrivire valoarea maximă, este:
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
[*]: Destul de interesant, chiar dacă spec fel de vă permite să selectați non-grupate pe domenii, principalele motoare par să nu place. Oracle și SQLServer doar don't permite acest lucru la toate. Mysql folosit pentru a permite în mod implicit, dar acum, de când 5.7 administratorul trebuie să activați această opțiune (ONLY_FULL_GROUP_BY
) manual în configurarea serverului pentru ca această caracteristică să fie acceptată...
În Postgres, puteți utiliza, de asemenea, speciale DISTINCT PE (expresia)
sintaxa:
SELECT DISTINCT ON (cname)
cname, wmname, avg
FROM
makerar
ORDER BY
cname, avg DESC ;
Problema cu specificarea non-grupate și non-agregat domenii în grupa de` selectează este ca motorul nu are nici o modalitate de a ști care înregistrează's teren ar trebui să se întoarcă în acest caz. Este prima? Este ultima? Există, de obicei, nici o înregistrare care în mod natural corespunde rezultat agregat ("min" și "max." sunt și excepții).
Cu toate acestea, există o soluție: asigurați-câmp obligatoriu agregate la fel de bine. În posgres, acest lucru ar trebui să funcționeze:
SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;
Rețineți că acest lucru creează o matrice de toate wnames, comandat de către avg, și returnează primul element (tablouri în postgres sunt 1-based).
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;
Folosind rang()
fereastra function:
SELECT cname, wmname, avg
FROM (
SELECT cname, wmname, avg, rank()
OVER (PARTITION BY cname ORDER BY avg DESC)
FROM makerar) t
WHERE rank = 1;
Notă
Unul se va păstra mai multe valori max per grup. Dacă vrei numai o singură înregistrare pentru fiecare grup, chiar dacă există mai mult de o înregistrare cu avg egală cu max ar trebui să verificați @ypercube's a răspunde.
Pentru mine, este vorba despre un "frecvente agregare problema", dar doar despre un incorectă de interogare SQL. Singur răspuns corect pentru "selectați maxim avg pentru fiecare cname..." este
SELECT cname, MAX(avg) FROM makerar GROUP BY cname;
Rezultatul va fi:
cname | MAX(avg)
--------+---------------------
canada | 2.0000000000000000
spain | 5.0000000000000000
Acest rezultat, în general, răspunsurile la întrebarea "ceea Ce este cel mai bun rezultat pentru fiecare grup?". Vom vedea că cel mai bun rezultat pentru spania este 5 și pentru canada cel mai bun rezultat este de 2. Este adevărat, și nu există nici o eroare. Dacă avem nevoie pentru a afișa wmname de asemenea, trebuie să se răspundă la întrebarea: "Ce este REGULI pentru a alege wmname din care rezultă set?" Sa's a modifica datele de intrare un pic pentru a clarifica o greseala:
cname | wmname | avg
--------+--------+-----------------------
spain | zoro | 1.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
Ce rezultat te aștepți pe runnig această interogare: SELECTAȚI cname, wmname, MAX(avg) DIN makerar GRUP DE cname;
? Ar trebui să fie spania+luffy " sau " spania+usopp
? De ce? Nu este determinat în interogare cum de a alege "bine" wmname dacă mai multe sunt potrivite, astfel încât rezultatul este, de asemenea, determinată. Ca's de ce SQL interpret returnează o eroare - interogarea nu este corect.
În alte cuvinte, nu există nici un răspuns corect la întrebarea "Care este cel mai bun din "spania" grup?". Luffy nu este mai bun decât usopp, pentru că usopp are aceeași "scor".
Recent am rula în această problemă, atunci când încearcă să numere folosind cazul
, și a constatat că schimbarea ordinii de "care" și "numărul" declarații rezolvă problema:
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
În loc de a folosi - în cele din urmă, de unde am luat erorile care mere și portocale ar trebui să apară în funcții agregate
CASE WHEN ((apples = 'TRUE' OR oranges 'TRUE') THEN COUNT(*) END) END AS fruit_counter