Me resulta muy difícil crear una vista en MySQL y espero que alguien pueda ayudarme. Este es el esquema de mi db:
CREATE DATABASE football; CREATE TABLE `team` ( `name` varchar(15) PRIMARY KEY NOT NULL ); CREATE TABLE `season` ( `name` char(9) PRIMARY KEY NOT NULL, `begin` date, `end` date); CREATE TABLE `game` ( `id` int PRIMARY KEY NOT NULL AUTO_INCREMENT, `Date` date DEFAULT NULL, `HomeTeam` varchar(15) NOT NULL, `AwayTeam` varchar(15) NOT NULL, `FTHG` int DEFAULT NULL, (full time home goal) `FTAG` int DEFAULT NULL, (full time away goal) `FTR` char(1) DEFAULT NULL, `HTHG` int DEFAULT NULL, `HTAG` int DEFAULT NULL, `HTR` char(1) DEFAULT NULL, `H_S` int DEFAULT NULL, `A_S` int DEFAULT NULL, `HST` int DEFAULT NULL, `AST` int DEFAULT NULL, `HC` int DEFAULT NULL, `AC` int DEFAULT NULL, `HF` int DEFAULT NULL, `AF` int DEFAULT NULL, `HY` int DEFAULT NULL, `AY` int DEFAULT NULL, `HR` int DEFAULT NULL, `AR` int DEFAULT NULL, `season` char(9), FOREIGN KEY (HomeTeam) REFERENCES team(name), FOREIGN KEY (AwayTeam) REFERENCES team(name), FOREIGN KEY (season) REFERENCES season(name), CHECK (HomeTeam != AwayTeam) );Esta base de datos recoge una serie de partidos de fútbol y muchas características de cada uno, así como todos los equipos y las temporadas. Gracias a esta vista ( https://stackoverflow.com/a/70794440/17987986 ), puedo generar los puntos, los goles marcados y los recibidos para cada temporada:
CREATE VIEW stats AS (SELECT season,team,sum(TGS) TGS,sum(TGC) TGC,sum(pts) pts FROM (SELECT season, HomeTeam team, FTHG TGS, FTAG TGC, CASE WHEN FTHG > FTAG THEN 3 WHEN FTHG=FTAG THEN 1 ELSE 0 END pts FROM game UNION ALL SELECT season, AwayTeam team, FTAG TGS, FTHG TGC, CASE WHEN FTAG > FTHG THEN 3 WHEN FTAG=FTHG THEN 1 ELSE 0 END pts FROM game) games GROUP BY season,team);Lo que resulta en lo siguiente:
SELECT * FROM stats;Las columnas de vista son, en orden: temporada, equipo, TGS (goles de equipo anotados), TGC (goles de equipo concedidos)
Esto es genial, pero lo que necesito ahora es generar las clasificaciones. Idealmente, me gustaría tener algo como lo siguiente:
Team,19,18,17,16,15,14 (years) Juventus,1,1,1,1,1,1 (came first each year) Inter,2,4,4,7,4,8 Atalanta,3,3,7,4,13,17 Lazio,4,8,5,5,8,3 Roma,5,6,3,2,3,2 Milan,6,5,6,6,7,10 Napoli,7,2,2,3,2,5 Sassuolo,8,11,11,10,6,12 Verona,9,,19,,,13Una dificultad principal es que, como puedes ver, las clasificaciones son del año 14 (temporada 2014-2015). Por lo tanto, estos datos no están presentes en la base de datos. Aunque esto es algo malo, ¿es posible codificarlo dentro de la vista? Siempre necesito los datos del año antes de cada juego, por lo que agregar juegos más antiguos solo recrearía el problema... Además, la temporada actual debe excluirse (en el ejemplo, véalo como si la temporada actual fuera 2020-2021). Cualquier ayuda o sugerencia es apreciada!
Sé que la estructura de la base de datos probablemente no sea la mejor, pero tengo que seguir con ella por ahora...
¿Por qué no simplemente calcular el rango de cada equipo/temporada dentro de la vista actual? Ahora puede unirse a eso para cualquier rango (y para cualquier temporada) que desee, según sea necesario.
Si necesita la clasificación del año anterior, podemos usar LAG para hacerlo, en una expresión posterior, o simplemente unirnos en función de alguna expresión relacionada con el año.
Observe la expresión RANK en la lista de selección.
La sugerencia (para MySQL 8.0+):
CREATE VIEW stats AS SELECT season, team , sum(TGS) TGS, sum(TGC) TGC, sum(pts) pts , RANK() OVER (PARTITION BY season ORDER BY SUM(pts) DESC) AS rnk FROM ( SELECT season, HomeTeam team, FTHG TGS, FTAG TGC , CASE WHEN FTHG > FTAG THEN 3 WHEN FTHG = FTAG THEN 1 ELSE 0 END pts FROM game UNION ALL SELECT season, AwayTeam team, FTAG TGS, FTHG TGC , CASE WHEN FTAG > FTHG THEN 3 WHEN FTAG = FTHG THEN 1 ELSE 0 END pts FROM game ) games GROUP BY season, team ;