query mysql per estrarre classifica da db calcistico

[-demianlz-]

Ciao a tutti
sono alle prese con una query che mi sta facendo diventare matto, spero riusciate ad aiutarmi

Ho un database che gestisce le stagioni, i campionati, le squadre, le giornate e le partite dei campionati calcistici italiani (varie stagioni, diversi campionati)

Il mio compito ora è estrapolare la classifica di uno specifico campionato

Quello che fino ad ora sono riuscito a fare è estrapolare in una tabella tutte le partite che si sono svolte in quel preciso campionato in quella precisa stagione

ecco la query

SELECT 
squadracasa.nomesquadra AS squadracasa, squadrafuori.nomesquadra AS squadrafuori, partita.casa AS idsquadracasa, partita.fuori AS idsquadrafuori, partita.golcasa, partita.golfuori FROM  
partite AS partita  
INNER JOIN giornate AS giornata  ON giornata.id = partita.idgiornata AND giornata.idstagione = 2 AND giornata.idcampionato = 13 AND partita.golcasa >= 0  
INNER JOIN squadre AS squadracasa ON partita.casa = squadracasa.id  
INNER JOIN squadre AS squadrafuori ON partita.fuori = squadrafuori.id

Questa query crea il seguente output

http://www.simentesempre.net/tuttocalcio/output.jpg

Che rappresenta tutte le partite giocate in quel campionato in quella stagione (ho inserito solo due giornate per ora, con dieci squadre inserite fanno dieci partite).
Ovviamente la struttura è
nome squadra che gioca in casa, nome squadra che gioca fuori casa, id della squadra di casa, id della squadra fuori casa, gol fatti dalla squadra di casa, gol fatti dalla squadra ospite

Quello che ora a me serivrebbe è creare una query (di cui la prima diventerebbe una sottoquery) che estrapoli per ogni squadra i seguenti dati:
nome squadra, punti, partite in casa(giocate, vinte, pareggiate, perse), giocate fuori casa (giocate, vinte, pareggiate, perse), partite in totale(giocate, vinte, pareggiate, perse), reti in casa(fatte, subite), reti fuori casa(fatte, subite), reti totali(fatte, subite)
ovviamente il tutto GROUP BY nomesquadra e ORDER BY punti DESC

La prima domanda è:si può fare?
La seconda domanda è: se si come faccio? Non sono molto esperto di query, ma sono disposto ad imparare quindi anche solo dei consigli su come fare sarebbero graditi.
Se potete darmi qualche dritta o aiutarmi a scrivere la query ve ne sarei molto grato.

[nicola75ss]

Avevo scritto una query tempo fa per quest'esigenza.
Ti posto il dump recuperato dal mio repository.

-- Dump e query classifica calcio
create table `campionato` (
  `id` int(11) not null auto_increment,
  `sq_casa` varchar(50) default null,
  `sq_trasferta` varchar(50) default null,
  `gol_casa` tinyint(4) default null,
  `gol_trasferta` tinyint(4) default null,
  primary key  (`id`)
) engine=innodb auto_increment=11 default charset=latin1;
 
/*Data for the table `campionato` */
 
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (1,'Juve','Milan',3,1);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (2,'Inter','Juve',1,1);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (3,'Roma','Lazio',4,3);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (4,'Fiorentina','Milan',2,2);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (5,'Juve','Fiorentina',1,1);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (6,'Cagliari','Inter',1,2);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (7,'Genoa','Cagliari',0,2);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (8,'Napoli','Juve',2,3);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (9,'Milan','Livorno',2,1);
insert into `campionato` (`id`,`sq_casa`,`sq_trasferta`,`gol_casa`,`gol_trasferta`) values (10,'Inter','Milan',2,1);

 
 
 
select squadra,
count(squadra) as partite,
sum(if(punteggio=3,1,0)) as vittorie,
sum(if(punteggio=1,1,0)) as pareggi,
sum(if(punteggio=0,1,0)) as sconfitte,
sum(punteggio) as punteggio,
sum(fatti) as fatti,
sum(subiti) as subiti,
sum(fatti)-sum(subiti) as diff_reti,
sum(if(punteggio=3 and dove = "C",1,0)) as vittorie_casa,
sum(if(punteggio=1 and dove = "C",1,0)) as pareggi_casa,
sum(if(punteggio=0 and dove = "C",1,0)) as sconfitte_casa,
sum(if(punteggio=3 and dove = "T",1,0)) as vittorie_trasferta,
sum(if(punteggio=1 and dove = "T",1,0)) as pareggi_trasferta,
sum(if(punteggio=0 and dove = "T",1,0)) as sconfitte_trasferta
from
(
select sq_casa as squadra,gol_casa as fatti,gol_trasferta as subiti,"C" as dove, 
case
when gol_casa > gol_trasferta then 3
when gol_casa = gol_trasferta then 1
else 0
end as punteggio
from campionato
union all
select sq_trasferta as squadra,gol_trasferta as fatti,gol_casa as subiti,"T",
case
when gol_trasferta > gol_casa then 3
when gol_trasferta = gol_casa then 1
else 0
end as punteggio
from campionato
) as tab
group by squadra
order by punteggio desc

Probabile ci siano più dati di quelli che ti occorrono. Togli il superfluo.

[-demianlz-]

Sono riuscito ad adattarlo e funziona bene (allego la query in seguito così puoi vederla).
Ti ringrazio molto, ora mi metto a cercare di capire come funziona, anche perchè devo anche estrarre gol fatti e subiti in casa e fuori, e le partite totali giocate in casa e fuori
Intanto ti chiedo: esiste un libro o un sito di tutorial che aiutano ad imparare questo genere di query avanzate?
Grazie ancora.

select squadra, 
sum(punteggio) as punteggio, 
count(squadra) as partite,
sum(if(punteggio=3,1,0)) as vittorie,
sum(if(punteggio=1,1,0)) as pareggi,
sum(if(punteggio=0,1,0)) as sconfitte,
sum(if(punteggio=3 and dove = "C",1,0)) as vittorie_casa,
sum(if(punteggio=1 and dove = "C",1,0)) as pareggi_casa,
sum(if(punteggio=0 and dove = "C",1,0)) as sconfitte_casa,
sum(if(punteggio=3 and dove = "T",1,0)) as vittorie_trasferta,
sum(if(punteggio=1 and dove = "T",1,0)) as pareggi_trasferta,
sum(if(punteggio=0 and dove = "T",1,0)) as sconfitte_trasferta, 
sum(fatti) as fatti,
sum(subiti) as subiti 
from
(
select squadracasa as squadra,golcasa as fatti,golfuori as subiti,"C" as dove, 
case
when golcasa > golfuori then 3 
when golcasa = golfuori then 1
else 0
end as punteggio
from (
SELECT squadracasa.nomesquadra AS squadracasa, squadrafuori.nomesquadra AS squadrafuori, partita.casa AS idsquadracasa, partita.fuori AS idsquadrafuori, partita.golcasa, partita.golfuori FROM 
partite AS partita 
INNER JOIN giornate AS giornata 
ON giornata.id = partita.idgiornata AND giornata.idstagione = 2 AND giornata.idcampionato = 13 AND partita.golcasa >= 0 
INNER JOIN squadre AS squadracasa
ON partita.casa = squadracasa.id 
INNER JOIN squadre AS squadrafuori
ON partita.fuori = squadrafuori.id
) AS partite 
union all
select squadrafuori as squadra,golfuori as fatti,golcasa as subiti,"T",
case
when golfuori > golcasa then 3
when golfuori = golcasa then 1
else 0
end as punteggio 
from 
(
SELECT squadracasa.nomesquadra AS squadracasa, squadrafuori.nomesquadra AS squadrafuori, partita.casa AS idsquadracasa, partita.fuori AS idsquadrafuori, partita.golcasa, partita.golfuori FROM 
partite AS partita 
INNER JOIN giornate AS giornata 
ON giornata.id = partita.idgiornata AND giornata.idstagione = 2 AND giornata.idcampionato = 13 AND partita.golcasa >= 0 
INNER JOIN squadre AS squadracasa
ON partita.casa = squadracasa.id 
INNER JOIN squadre AS squadrafuori
ON partita.fuori = squadrafuori.id
) AS partite 
) AS tab 
GROUP BY squadra
ORDER BY punteggio DESC

[nicola75ss]

Aggiungere quei dati supplementari è una sciocchezza.

sum(if(dove="C",fatti,0)) as fatti_casa

sum(if(dove="T",fatti,0)) as fatti_trasferta

ecc.

Non esiste alcun libro miracoloso. La cosa migliore è la pratica.

A questo indirizzo

http://planet.mysql.com/

trovi quotidianamente un sacco di link ad articoli molto interessanti. Poi c'è il forum ufficiale di mysql e anche su questo forum troverai una miriade di thread utili da cui trarre spunti.

[-demianlz-]

Ti ringrazio molto

[nicola75ss]

Posto la mia query con quelle statistiche supplementari che volevi.
Può sempre tornare utile a qualcuno.

select squadra,
count(squadra) as partite,
sum(if(punteggio=3,1,0)) as vittorie,
sum(if(punteggio=1,1,0)) as pareggi,
sum(if(punteggio=0,1,0)) as sconfitte,
sum(punteggio) as punteggio,
sum(fatti) as fatti,
sum(subiti) as subiti,
sum(fatti)-sum(subiti) as diff_reti,
sum(if(dove="C",fatti,0)) as fatti_casa,
sum(if(dove="T",fatti,0)) as fatti_trasferta,
sum(if(dove="C",subiti,0)) as subiti_casa,
sum(if(dove="T",subiti,0)) as subiti_trasferta,
sum(if(punteggio=3 and dove = "C",1,0)) as vittorie_casa,
sum(if(punteggio=1 and dove = "C",1,0)) as pareggi_casa,
sum(if(punteggio=0 and dove = "C",1,0)) as sconfitte_casa,
sum(if(punteggio=3 and dove = "T",1,0)) as vittorie_trasferta,
sum(if(punteggio=1 and dove = "T",1,0)) as pareggi_trasferta,
sum(if(punteggio=0 and dove = "T",1,0)) as sconfitte_trasferta,
sum(if(dove = "C",1,0)) as partite_casa,
sum(if(dove = "T",1,0)) as partite_trasferta
from
(
select sq_casa as squadra,gol_casa as fatti,gol_trasferta as subiti,"C" as dove, 
case
when gol_casa > gol_trasferta then 3
when gol_casa = gol_trasferta then 1
else 0
end as punteggio
from campionato
union all
select sq_trasferta as squadra,gol_trasferta as fatti,gol_casa as subiti,"T",
case
when gol_trasferta > gol_casa then 3
when gol_trasferta = gol_casa then 1
else 0
end as punteggio
from campionato
) as tab
group by squadra
order by punteggio desc

[-demianlz-]

grazie funziona davvero benissimo, e inoltre me la sono studiata e credo di aver capito bene come funziona. per me un buon punto di partenza per migliorare le mie query
grazie ancora
ciao

[davidino80]

Originariamente inviato da -demianlz-

SELECT 
squadracasa.nomesquadra AS squadracasa, squadrafuori.nomesquadra AS squadrafuori, partita.casa AS idsquadracasa, partita.fuori AS idsquadrafuori, partita.golcasa, partita.golfuori FROM  
partite AS partita  
INNER JOIN giornate AS giornata  ON giornata.id = partita.idgiornata AND giornata.idstagione = 2 AND giornata.idcampionato = 13 AND partita.golcasa >= 0  
INNER JOIN squadre AS squadracasa ON partita.casa = squadracasa.id  
INNER JOIN squadre AS squadrafuori ON partita.fuori = squadrafuori.id

ciao.
sto cercando di fare la stessa query per un sito calcistico.
siccome è la prima volta che uso le JOIN vorrei capire come recuperare "squadracasa" e "squadrafuori" per mostrarle all'utente al posto degli id squadra.

stavo cercando di applicare la tua query ma non capisco la prima parte:

SELECT
squadracasa.nomesquadra AS squadracasa, squadrafuori.nomesquadra AS squadrafuori, partita.casa AS idsquadracasa, partita.fuori AS idsquadrafuori,

nomesquadra è una colonna di che tabella? di squadracasa?? non credo.. almeno che tu non abbia strutturato il db diverso dal mio..

se non sono stato chiaro provo a postare i nomi delle mie tabelle e colonne.
grazie ciao.

[nicola75ss]

Originariamente inviato da davidino80
se non sono stato chiaro provo a postare i nomi delle mie tabelle e colonne.
grazie ciao.

Posta direttamente il dump delle tabelle coinvolte.

[davidino80]

eccoli:

Struttura della tabella `ca5_calendar`
--
CREATE TABLE IF NOT EXISTS `ca5_calendar` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`matchdate` int(11) NOT NULL,
`matchplace` varchar(200) NOT NULL,
`hometeam` int(11) NOT NULL,
`visitorteam` int(11) NOT NULL,
`homescore` int(2) NOT NULL,
`visitorscore` int(2) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM  
DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=424 ;

dove:
hometeam = ID squadra in casa
visitorteam = ID squadra fuori casa
homescore = gol squadra in casa
visitorscore = gol squadra fuori casa

Struttura della tabella `ca5_teams`
--
CREATE TABLE IF NOT EXISTS `ca5_teams` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`teamname` varchar(50) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM  
DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

dove:
uid = ID squadra
teamname = nome squadra completo


Vorrei riuscire in un'unica query ad estrarre le partite ma postando a video il nome della squadra e non l'ID.
Poi per il conteggio gol e altro ho fatto tutto in un'altra maniera, ma penso che prenderò spunto dal tuo script che mi sembra più semplice e veloce.

Grazie per l'aiuto.