Posso fare un massimo (count (*)) in SQL?

Ecco il mio codice:

select yr,count(*) from movie join casting on casting.movieid=movie.id join actor on casting.actorid = actor.id where actor.name = 'John Travolta' group by yr 

Ecco la domanda

Quali erano gli anni più impegnativi per “John Travolta”. Mostra il numero di film che ha realizzato per ogni anno.

Ecco la struttura della tabella:

 movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) 

Questo è l’output che sto ottenendo:

 yr count(*) 1976 1 1977 1 1978 1 1981 1 1994 1 etcetc 

Ho bisogno di ottenere le righe per cui il count(*) è max.

Come faccio a fare questo?

Uso:

  SELECT m.yr, COUNT(*) AS num_movies FROM MOVIE m JOIN CASTING c ON c.movieid = m.id JOIN ACTOR a ON a.id = c.actorid AND a.name = 'John Travolta' GROUP BY m.yr ORDER BY num_movies DESC, m.yr DESC 

Ordinare con num_movies DESC metterà i valori più alti nella parte superiore del num_movies DESC di risultati. Se numerosi anni hanno lo stesso conteggio, il m.yr posizionerà l’anno più recente in alto … fino a quando il valore successivo di num_movies cambia.

Posso usare MAX (COUNT (*))?


No, non è ansible sovrapporre le funzioni di aggregazione l’una sull’altra nella stessa clausola SELECT. L’aggregato interno dovrebbe essere eseguito in una sottoquery. IE:

 SELECT MAX(y.num) FROM (SELECT COUNT(*) AS num FROM TABLE x) y 

Basta ordinare per count(*) desc e otterrai il massimo (se lo combini con il limit 1 )

 SELECT * from ( SELECT yr as YEAR, COUNT(title) as TCOUNT FROM actor JOIN casting ON actor.id = casting.actorid JOIN movie ON casting.movieid = movie.id WHERE name = 'John Travolta' GROUP BY yr order by TCOUNT desc ) res where rownum < 2 

è da questo sito – http://sqlzoo.net/3.htm 2 possibili soluzioni:

con TOP 1 a ORDINA PER … DESC:

 SELECT yr, COUNT(title) FROM actor JOIN casting ON actor.id=actorid JOIN movie ON movie.id=movieid WHERE name = 'John Travolta' GROUP BY yr HAVING count(title)=(SELECT TOP 1 COUNT(title) FROM casting JOIN movie ON movieid=movie.id JOIN actor ON actor.id=actorid WHERE name='John Travolta' GROUP BY yr ORDER BY count(title) desc) 

con MAX:

 SELECT yr, COUNT(title) FROM actor JOIN casting ON actor.id=actorid JOIN movie ON movie.id=movieid WHERE name = 'John Travolta' GROUP BY yr HAVING count(title)= (SELECT MAX(A.CNT) FROM (SELECT COUNT(title) AS CNT FROM actor JOIN casting ON actor.id=actorid JOIN movie ON movie.id=movieid WHERE name = 'John Travolta' GROUP BY (yr)) AS A) 

L’utilizzo di max con un limite ti darà solo la prima riga, ma se ci sono due o più righe con lo stesso numero di film massimi, ti mancheranno alcuni dati. Di seguito è riportato un modo per farlo se si dispone della funzione rank () disponibile.

 SELECT total_final.yr, total_final.num_movies FROM ( SELECT total.yr, total.num_movies, RANK() OVER (ORDER BY num_movies desc) rnk FROM ( SELECT m.yr, COUNT(*) AS num_movies FROM MOVIE m JOIN CASTING c ON c.movieid = m.id JOIN ACTOR a ON a.id = c.actorid WHERE a.name = 'John Travolta' GROUP BY m.yr ) AS total ) AS total_final WHERE rnk = 1 

Il seguente codice ti dà la risposta. Implementa essenzialmente MAX (COUNT (*)) usando ALL. Ha il vantaggio di utilizzare comandi e operazioni molto semplici.

 SELECT yr, COUNT(title) FROM actor JOIN casting ON actor.id = casting.actorid JOIN movie ON casting.movieid = movie.id WHERE name = 'John Travolta' GROUP BY yr HAVING COUNT(title) >= ALL (SELECT COUNT(title) FROM actor JOIN casting ON actor.id = casting.actorid JOIN movie ON casting.movieid = movie.id WHERE name = 'John Travolta' GROUP BY yr) 

A seconda del database che stai usando …

 select yr, count(*) num from ... order by num desc 

La maggior parte della mia esperienza è in Sybase, che utilizza una syntax diversa rispetto ad altri DB. Ma in questo caso, stai nominando la colonna dei conti, quindi puoi ordinarla, in ordine decrescente. Puoi fare un ulteriore passo avanti e limitare i tuoi risultati alle prime 10 righe (per trovare i suoi 10 anni più trafficati).

  select top 1 yr,count(*) from movie join casting on casting.movieid=movie.id join actor on casting.actorid = actor.id where actor.name = 'John Travolta' group by yr order by 2 desc 

Questa domanda è vecchia, ma è stata referenziata in una nuova domanda su dba.SE. Sento che le migliori soluzioni non sono state ancora fornite, quindi ne aggiungo un’altra.

Innanzitutto, assumendo l’integrità referenziale (generalmente applicata con vincoli di chiave esterna) non è necessario partecipare al movie della tabella. Quella è la merce morta nella tua domanda. Tutte le risposte finora non riescono a farlo notare.


Posso fare un max(count(*)) in SQL?

Per rispondere alla domanda nel titolo: , in Postgres 8.4 (rilasciato nel 2009-07-01, prima che venisse chiesta la domanda) o successivamente è ansible ottenerlo raggruppando una funzione di aggregazione in una funzione di finestra :

 SELECT c.yr, count(*) AS ct, max(count(*)) OVER () AS max_ct FROM actor a JOIN casting c ON c.actorid = a.id WHERE a.name = 'John Travolta' GROUP BY c.yr; 

Considera la sequenza di eventi in una query SELECT :

  • Il modo migliore per ottenere il conteggio dei risultati prima dell’applicazione del LIMIT

Lo svantaggio (ansible): le funzioni della finestra non aggregano le righe. Ottieni tutte le righe rimaste dopo il passaggio aggregato. Utile in alcune domande, ma non ideale per questo.


Per ottenere una riga con il conteggio più alto, puoi usare ORDER BY ct LIMIT 1 come @wolph hinted :

 SELECT c.yr, count(*) AS ct FROM actor a JOIN casting c ON c.actorid = a.id WHERE a.name = 'John Travolta' GROUP BY c.yr ORDER BY ct DESC LIMIT 1; 

Utilizzando solo le funzionalità SQL di base disponibili in qualsiasi RDBMS a metà strada decente, l’implementazione LIMIT varia:

  • SQL seleziona elementi in cui la sum di campo è inferiore a N

Oppure puoi ottenere una riga per gruppo con il conteggio più alto con DISTINCT ON (solo Postgres):

  • Seleziona la prima riga in ogni gruppo GROUP BY?

Risposta

Ma tu hai chiesto:

… le righe per cui il conteggio (*) è max.

Forse più di uno. La soluzione più elegante è con la funzione window rank() in una sottoquery. Ryan ha fornito una query ma può essere più semplice (dettagli nella mia risposta sopra):

 SELECT yr, ct FROM ( SELECT c.yr, count(*) AS ct, rank() OVER (ORDER BY count(*) DESC) AS rnk FROM actor a JOIN casting c ON c.actorid = a.id WHERE a.name = 'John Travolta' GROUP BY c.yr ) sub WHERE rnk = 1; 

Tutte le principali funzioni della finestra di supporto RDBMS al giorno d’oggi. Tranne MySQL e fork ( MariaDB sembra averli implementati alla fine nella versione 10.2 ).

 create view sal as select yr,count(*) as ct from (select title,yr from movie m, actor a, casting c where a.name='JOHN' and a.id=c.actorid and c.movieid=m.id)group by yr 

—– VISUALIZZAZIONE CREATA —–

 select yr from sal where ct =(select max(ct) from sal) 

YR 2013

Grazie all’ultima risposta

 SELECT yr, COUNT(title) FROM actor JOIN casting ON actor.id = casting.actorid JOIN movie ON casting.movieid = movie.id WHERE name = 'John Travolta' GROUP BY yr HAVING COUNT(title) >= ALL (SELECT COUNT(title) FROM actor JOIN casting ON actor.id = casting.actorid JOIN movie ON casting.movieid = movie.id WHERE name = 'John Travolta' GROUP BY yr) 

Ho avuto lo stesso problema: avevo bisogno di conoscere solo i record che il loro conteggio corrisponde al conteggio massimo (potrebbe essere uno o più record).

Devo imparare di più su “ALL clausola”, e questo è esattamente il tipo di soluzione semplice che stavo cercando.