Perché non è ansible combinare valori aggregati e valori non aggregati in un singolo SELECT?

So che se si dispone di una funzione di aggregazione in un’istruzione SELECT, tutti gli altri valori nell’istruzione devono essere funzioni aggregate o elencate in una clausola GROUP BY. Non capisco perché sia così.

Se lo faccio:

SELECT Name, 'Jones' AS Surname FROM People 

Ottengo:

 NAME SURNAME Dave Jones Susan Jones Amy Jones 

Quindi, il DBMS ha preso un valore da ogni riga e ne ha aggiunto un singolo valore nel set di risultati. Va bene. Ma se funziona, perché non posso farlo:

 SELECT Name, COUNT(Name) AS Surname FROM People 

Sembra la stessa idea, prendere un valore da ogni riga e aggiungere un singolo valore. Ma invece di:

 NAME SURNAME Dave 3 Susan 3 Amy 3 

Ottengo:

Hai provato a eseguire una query che non include l’espressione specificata “ContactName” come parte di una funzione di aggregazione.

So che non è permesso, ma le due circostanze sembrano così simili che non capisco perché. È per rendere più facile l’implementazione del DBMS? Se qualcuno può spiegarmi perché non funziona come penso che dovrebbe, sarei molto grato.

Aggregati non funziona su un risultato completo, funzionano solo su un gruppo in un risultato.

Considera una tabella contenente:

 Person Pet -------- -------- Amy Cat Amy Dog Amy Canary Dave Dog Susan Snake Susan Spider 

Se si utilizza una query che raggruppa su Persona, suddividerà i dati in questi gruppi:

 Amy: Amy Cat Amy Dog Amy Canary Dave: Dave Dog Susan: Susan Snake Susan Spider 

Se si utilizza un aggreage, per esempio il conteggio aggregato, produrrà un risultato per ogni gruppo:

 Amy: Amy Cat Amy Dog Amy Canary count(*) = 3 Dave: Dave Dog count(*) = 1 Susan: Susan Snake Susan Spider count(*) = 2 

Quindi, la query select Person, count(*) from People group by Person ti dà un record per ogni gruppo:

 Amy 3 Dave 1 Susan 2 

Se si tenta di ottenere anche il campo Pet nel risultato, ciò non funziona perché potrebbero esserci più valori per quel campo in ciascun gruppo.

(Alcuni database, come MySQL, lo consentono comunque e restituiscono qualsiasi valore casuale all’interno del gruppo, ed è tua responsabilità sapere se il risultato è ragionevole o meno).

Se si utilizza un aggregato, ma non si specifica alcun raggruppamento, la query verrà comunque raggruppata e l’intero risultato sarà un singolo gruppo. Quindi la query select count(*) from Person creerà un singolo gruppo contenente tutti i record e l’aggregato può contare i record in quel gruppo. Il risultato contiene una riga per ogni gruppo e poiché esiste un solo gruppo, ci sarà una riga nel risultato.

Pensaci in questo modo: quando chiami COUNT senza raggruppamento, “collassa” la tabella in un singolo gruppo rendendo imansible accedere ai singoli elementi all’interno di un gruppo in una clausola select.

Puoi ancora ottenere i risultati utilizzando una subquery o un cross join:

  SELECT p1.Name, COUNT(p2.Name) AS Surname FROM People p1 CROSS JOIN People p2 GROUP BY p1.Name SELECT Name, (SELECT COUNT(Name) FROM People) AS Surname FROM People 

Come altri hanno spiegato, quando hai un GROUP BY o stai utilizzando una funzione aggregata come COUNT() nell’elenco SELECT , stai facendo un raggruppamento di righe e quindi comprimi le righe corrispondenti in uno per ogni gruppo.

Quando si utilizzano le funzioni di aggregazione solo nell’elenco SELECT , senza GROUP BY , pensarlo come se fosse un GROUP BY 1 , quindi tutte le righe sono raggruppate e raggruppate in una sola. Quindi, se hai un centinaio di righe, il database non può mostrarti un nome perché ce ne sono cento.

Tuttavia, per gli RDBMS con funzioni di “windowing”, ciò che si desidera è fattibile. Ad esempio, utilizzare le funzioni di aggregazione senza GROUP BY .

Esempio per SQL-Server, in cui vengono conteggiate tutte le righe (nomi) della tabella:

 SELECT Name , COUNT(*) OVER() AS cnt FROM People 

Come funziona il sopra?

  • Mostra il Name come il COUNT(*) OVER() AS cnt non esisteva e

  • Mostra il COUNT(*) come se stesse facendo un raggruppamento totale del tavolo.


Un altro esempio. Se hai un campo Surname sul tavolo, puoi avere qualcosa come questo per mostrare tutte le righe raggruppate per Cognome e contare quante persone hanno lo stesso Cognome:

 SELECT Name , Surname , COUNT(*) OVER(PARTITION BY Surname) AS cnt FROM People 

La query richiede implicitamente diversi tipi di righe nel set di risultati e ciò non è consentito. Tutte le righe restituite devono essere dello stesso tipo e avere lo stesso tipo di colonne.

‘SELEZIONA nome, cognome’ vuole restituire una riga per ogni riga nella tabella.

‘SELECT COUNT (*)’ vuole restituire una singola riga combinando i risultati di tutte le righe nella tabella.

Penso che tu abbia ragione che in questo caso il database potrebbe plausibilmente fare entrambe le query e quindi copiare il risultato di “SELECT COUNT (*)” in ogni risultato. Una delle ragioni per non farlo è che sarebbe un successo in termini di prestazioni invisibili: si farebbe effettivamente un extra self-join senza dichiararlo da nessuna parte.

Altre risposte hanno spiegato come scrivere una versione funzionante di questa query, quindi non entrerò in quella.

La funzione di aggregazione e la clausola group by non sono elementi separati, sono parti della stessa cosa che appaiono in diversi punti della query. Se desideri aggregare su una colonna, devi dire quale funzione utilizzare per l’aggregazione; se desideri avere una funzione di aggregazione, deve essere applicata su alcune colonne.

La funzione di aggregazione prende i valori da più righe con una condizione specifica e li combina in un unico valore. Questa condizione è definita da GROUP BY nella tua dichiarazione. Quindi non è ansible utilizzare una funzione di aggregazione senza GROUP BY

Con

 SELECT Name, 'Jones' AS Surname FROM People 

basta selezionare una colonna aggiuntiva con un valore fisso … ma con

 SELECT Name, COUNT(Name) AS Surname FROM People GROUP BY Name 

dici al DBMS di selezionare i nomi, ricorda quanto spesso ogni nome si è verificato nella tabella e li collassa in una riga. Quindi se ometti il GROUP BY il DBMS non può dirlo, come comprimere i record