Tricky MySQL view

I am finding it very difficult to create a view in MySQL, and hope someone could help me out. This is the schema of my 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)
);

This database collects a series of football games and many characteristics for each one, as well as all the teams and the seasons. Thanks to this view (https://stackoverflow.com/a/70794440/17987986), I am able to generate the points, the goals scored as well as those conceded for each season:

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);

Which results in the following:

SELECT * FROM stats;

View Columns are, in order: season, eam, TGS (team golas scored), TGC (team goals conceded)

This is great, but what I need now is to generate the rankings. Ideally, I’d like to have something like the following:

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,,,13

One main difficulty is that, as you can see, ranking are from year 14 (season 2014-2015). Therefor, this data is not present in the db. Although this is a bad thing, is it possible to hardcode it inside the view? I always need the data of the year before each game, so adding older games would just recreate the problem… Also, the current season should be excluded (in the example, see it as if the current season is 2020-2021). Any help or suggestion is appreciated!

Note

I know the db structure is probably not the best, but I have to stick with it for now…

Answer

Why not just calculate the rank for each team / season within the current view. Now you can join with that for any rank (and for any season) you wish, as needed.

If you need the previous year rank, we can use LAG to do that, in a subsequent expression, or just join based on some expression related to the year.

Notice the RANK expression in the select list.

The fiddle

The suggestion (for 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
;

Note

Advertisement

Answer