[SQL] Una query mySQL che non funge

[zanardi84]

Ho queste due relazioni:

musei(ID_museo, nome, città, numero_sale, nazione)

quadri(ID_quadro, titolo, periodo, autore, id_museo)

in cui ho evidenziato in grassetto la chiave primaria e in corsivo quella esterna.

Ho una query da risolvere che dice di trovare quali musei hanno un numero di quadri superiore alla media complessiva per quella nazione.

Questo è il mio codice che coincide con quello della soluzione, ma non funziona.

SELECT nome,COUNT(*) as tot
FROM Musei M INNER JOIN Quadri Q
ON Q.id_museo=M.ID_museo
GROUP BY M.ID_museo
HAVING tot >
  (
  (SELECT COUNT(*) 
   FROM Quadri
   WHERE Quadri.id_museo=M.ID_museo)
  /
  (SELECT COUNT(*) 
   FROM Quadri
   WHERE Quadri.id_museo=M.ID_museo
   GROUP BY M.nazione)
  )

mysql mi dice che è sconosciuto l'attributo M.nazione nel raggruppamento.

Sapreste indicarmi anche una strategia per risolvere le query annidate molto complesse?

Per quanto riguarda l'uso delle variabili mi potreste chiarire quando è opportuno inserirle nei casi di query annidate?

Grazie.

[Dânêl]

Nelle due relazioni che hai riportato non esiste alcun attributo nazione, quindi è normale che tu ottenga quell'errore. Se non ci sono altri problemi (non ho provato la query) ti basta sostituire nazione con città, l'unico attributo che gli si avvicina concettualmente.

Forse si tratta di un refuso nella traccia/tabella o, in alternativa, dovrebbe esistere una terza relazione, o un semplice attributo aggiuntivo, che associa ciascuna città ad una nazione

[Ashgan83]

Originariamente inviato da zanardi84

Ho queste due relazioni:

musei(ID_museo, nome, città, numero_sale, nazioni)

quadri(ID_quadro, titolo, periodo, autore, id_museo)

in cui ho evidenziato in grassetto la chiave primaria e in corsivo quella esterna.

Ho una query da risolvere che dice di trovare quali musei hanno un numero di quadri superiore alla media complessiva per quella nazione.

Questo è il mio codice che coincide con quello della soluzione, ma non funziona.

SELECT nome,COUNT(*) as tot
FROM Musei M INNER JOIN Quadri Q
ON Q.id_museo=M.ID_museo
GROUP BY M.ID_museo
HAVING tot >
  (
  (SELECT COUNT(*) 
   FROM Quadri
   WHERE Quadri.id_museo=M.ID_museo)
  /
  (SELECT COUNT(*) 
   FROM Quadri
   WHERE Quadri.id_museo=M.ID_museo
   GROUP BY M.nazione)
  )

mysql mi dice che è sconosciuto l'attributo M.nazione nel raggruppamento.

Sapreste indicarmi anche una strategia per risolvere le query annidate molto complesse?

Per quanto riguarda l'uso delle variabili mi potreste chiarire quando è opportuno inserirle nei casi di query annidate?

Grazie.

Scusa ma non l'hai chiamato "nazioni" nella tabella? Nella query usi "M.nazione".

[zanardi84]

Chiedo scusa, è nazione, non nazioni. Correggo anche nel thread iniziale.

[demos88]

Originariamente inviato da zanardi84

  (SELECT COUNT(*) 
   FROM Quadri
   WHERE Quadri.id_museo=M.ID_museo
   GROUP BY M.nazione)
  )

mysql mi dice che è sconosciuto l'attributo M.nazione nel raggruppamento.

Mi sembra una query molto "creativa" nel suo complesso, ma questa particolare annidata mi lascia perplesso...
Fai un select del conteggio dei quadri raggruppati secondo un attributo che appartiene a un'altra tabella... secondo me l'errore sta in questo... prova a controllare.

ps: per quanto riguarda lo stile, ti consiglio: tutto in minuscolo (ID_museo -> id_museo), e il nome dei campi del tipo "id_xxxx" usarli solo per gli id usati come chiavi primarie. Per esempio nella tabella quadri, avrei messo "museo" e non "id_museo", in quanto quell'attributo è si l'id del museo, ma rappresenta l'intero museo.

[gugoXX]

Quando scrivi

Having tot> (bla bla)

oppure anche quando scrivi

Where qualcosa > (bla bla)

dove quel blabla vuoi che sia una sottoquery, allora questa sottoquery deve restituire uno scalare.

Ovvero nel tuo caso devi far restituire la media dei quadri della nazione del record che stai processando sulla riga master
La Join non la devi fare per museo, ma per nazione.
E ho idea che non ci sara' nessuna GROUP BY

[demos88]

supponendo che le relazioni siano (ho apportato qualche modifica):

musei(id_museo, nome, città, numero_sale, nazione)
quadri(id_quadro, titolo, periodo, autore, museo)

questa query dovrebbe funzionare:

SELECT y.museo, x.nazione, x.medianaz, y.nqm  FROM (
  SELECT a.nazione, nqn, nmn, nqn/nmn AS medianaz FROM 
    ( SELECT nazione, COUNT(*) AS nqn FROM musei JOIN quadri ON id_museo = museo GROUP BY nazione ) a 
  JOIN
    ( SELECT nazione, COUNT(*) AS nmn FROM musei GROUP BY nazione) b
  ON a.nazione = b.nazione ) x
JOIN (
  SELECT nazione, museo, COUNT(*) AS nqm FROM musei JOIN quadri ON id_museo = museo GROUP BY museo ) y
ON x.nazione = y.nazione
WHERE y.nqm > x.medianaz

ho poca fantasia, e gli alias a,b,x,y ne sono la conseguenza, ovviamente mettici quello che ti pare
nqn = numero quadri nazione
nmn = numero musei nazione
nqm = numero quadri museo
medianaz = media nazionale di quadri per museo

la relazione a conta i quadri per nazione, la relazione b conta il numero di musei per nazione. x è il join di a con b e l'attributo x.medianaz è il rapporto nqn/nmn. x è joinato a y che conta il numero di quadri per ogni museo, dal join x*y tengo solo le tuple in cui nqm > medianaz.

Provato in locale e mi pare che funzioni

ps: non escludo l'esistenza di soluzioni più eleganti con l'uso di funzioni SQL, questa mi è venuta in mente così... e mi sa che esiste più di un modo di scrivere la stessa query che ho scritto sopra.

[zanardi84]

Quando dovete formulare una query quali sono i ragionamenti che fate?
Cosa vi fa optare per una subquery?
Quando usare il self join?

Alla fine conosco la sintassi e le definizioni degli operatori, ma non riesco a formulare efficaci interrogazioni.

[demos88]

Originariamente inviato da zanardi84

Quando dovete formulare una query quali sono i ragionamenti che fate?
Cosa vi fa optare per una subquery?
Quando usare il self join?

Alla fine conosco la sintassi e le definizioni degli operatori, ma non riesco a formulare efficaci interrogazioni.

Quello che cerchi si studia nella teoria dell'algebra relazionale, ma fondamentalmente non è nulla di così arcano e la vera tecnica si sviluppa col tempo facendo pratica. Comunque già il fatto di renderti conto che l'algebra relazione così come l'SQL hanno molte analogie con il calcolo insiemistico (il join è di fatto un prodotto cartesiano fra gli insiemi che sono le tabelle, è possibile lavorare su interesezioni, unioni, raggruppamenti...) ti permette di capire come usare le interrogazioni.
Non c'e' una regola che ti dice quando usare una sottoquery o un selfjoin, lì sul momento vedi che ti serve... e spesso esistono più strade per raggiungere lo stesso risultato

[yuri.f_87]

Ciao, di seguito trovi una delle tante soluzioni possibili.

declare @t table (	IdMuseo	int,	
	              Nazione	varchar(50),
		NumQuadMuseo	int
	  )
declare @t2 table ( Nazione varchar(50), 		
               	  AVGQuadNazione	decimal(18,2)
		)
				
insert @t (	IdMuseo ,	
		Nazione	,
		NumQuadMuseo
	)
select	M.ID_museo, 
	M.nazione, 
	COUNT(*) as QuadriMuseo
from	@musei M
	inner join @quadri Q on M.ID_museo = Q.id_museo
group by M.ID_museo, M.nazione

insert into @t2 (	Nazione,
		AVGQuadNazione
	)
select	T.nazione, AVG(convert(decimal(18,2), T.NumQuadMuseo))
from	@t T
group by T.nazione

select	*
from	@t T
where	T.NumQuadMuseo >= (	select	T2.AVGQuadNazione
				from	@t2 T2
				where	T2.nazione = T.nazione
			)
order by T.IdMuseo

Di certo non è la soluzione più veloce (si potrebbe abbastanza facilmente eliminare le tab temporanee), ma secondo me è abbastanza lineare scritta in questo modo.
Ciao,
Yuri.