**

Simple OVER() function

**

Instead of OVER(), AVG() is a simple aggregate function, which calculates the AVG of each partition in the way of GROUP BY

SELECT 
	-- Select the id, country name, season, home, and away goals
	m.id, 
    c.name AS country, 
    m.season,
	m.home_goal,
	m.away_goal,
    -- Use a window to include the aggregate average in each row
	AVG(m.home_goal + m.away_goal)  AS overall_avg
FROM match AS m
LEFT JOIN country AS c ON m.country_id = c.id
GROUP BY m.id, c.name;

After OVER() is used

SELECT 
	-- Select the id, country name, season, home, and away goals
	m.id, 
    c.name AS country, 
    m.season,
	m.home_goal,
	m.away_goal,
    -- At this time, it is not added partition by Of over()，Partition is the whole DATASET，So it is overall_avg，Amount to Tableau Medium{AVG()}
	AVG(m.home_goal + m.away_goal) OVER() AS overall_avg
FROM match AS m
LEFT JOIN country AS c ON m.country_id = c.id;

RANK() OVER() function

SELECT 
	-- Select the league name and average goals scored
	l.name AS league,
    AVG(m.home_goal + m.away_goal) AS avg_goals,
    -- Rank each league according to the average goals Add a new column
    RANK() OVER(ORDER BY AVG(m.home_goal + m.away_goal)) AS league_rank
FROM league AS l
LEFT JOIN match AS m 
ON l.id = m.country_id
WHERE m.season = '2011/2012'
GROUP BY l.name
-- Order the query by the rank you created Still need to use the new one rank Column sorting
ORDER BY league_rank;

SELECT 
	-- Select the league name and average goals scored
	name AS league,
    AVG(m.home_goal + m.away_goal) AS avg_goals,
    -- Rank leagues in descending order by average goals use DESC Reverse order
    RANK() OVER (ORDER BY AVG(m.home_goal + m.away_goal) DESC) AS league_rank
FROM league AS l
LEFT JOIN match AS m 
ON l.id = m.country_id
WHERE m.season = '2011/2012'
GROUP BY l.name
-- Order the query by the rank you created
ORDER BY league_rank;

RANK() sort:

- rank()over(order by column name sort) results are discontinuous. If there are 4 people, 3 of them are tied for the first place, then the final sorting results are as follows: 1 11 4

rank()over(order by TOTALEXAMSCORE desc) AS orderbyNum

- the result of deny ﹐ rank() over (order by column name) is continuous. If there are 4 people, 3 of them are tied for the first place, the final sorting result is as follows: 11 1 2

dense_rank()over(order by TOTALEXAMSCORE desc) AS orderbyNum

----Rank() over (partition by column nameorder by column namesort) uses partition method to get the highest score of each course

rank () OVER (PARTITION BY coursename ORDER BY TOTALEXAMSCORE DESC) AS orderbynum

OVER() with Partition

1. Partition by 1 column

SELECT
	date,
	season,
	home_goal,
	away_goal,
	CASE WHEN hometeam_id = 8673 THEN 'home' 
		 ELSE 'away' END AS warsaw_location,
    -- with season Is a partition, the same Season Of AVG value equally
    AVG(home_goal) OVER(PARTITION BY season) AS season_homeavg,
    AVG(away_goal) OVER(PARTITION BY season) AS season_awayavg
FROM match
-- Filter the data set for Legia Warszawa matches only
WHERE 
	hometeam_id = 8673 
    OR awayteam_id = 8673
ORDER BY (home_goal + away_goal) DESC;

1. Partition by MULTIPLE column

SELECT 
	date,
	season,
	home_goal,
	away_goal,
	CASE WHEN hometeam_id = 8673 THEN 'home' 
         ELSE 'away' END AS warsaw_location,
	-- use season and month Partition, same season，In different months AVG()It's different
    AVG(home_goal) OVER(PARTITION BY season, 
         	EXTRACT(MONTH FROM date)) AS season_mo_home,
    AVG(away_goal) OVER(PARTITION BY season, 
            EXTRACT(MONTH FROM date)) AS season_mo_away
FROM match
WHERE 
	hometeam_id = 8673 
    OR awayteam_id = 8673
ORDER BY (home_goal + away_goal) DESC;

OVER() combined with CTE

-- Set up the home team CTE First CTE，Find out all M Team when home team Hour match
WITH home AS (
  SELECT m.id, t.team_long_name,
	  CASE WHEN m.home_goal > m.away_goal THEN 'Manchester United Win'
		   WHEN m.home_goal < m.away_goal THEN 'Manchester United Loss' 
  		   ELSE 'Tie' END AS outcome
  FROM match AS m
  LEFT JOIN team AS t ON m.hometeam_id = t.team_api_id),
-- Set up the away team CTE First CTE，Find out all M Team when away team Hour match
away AS (
  SELECT m.id, t.team_long_name,
	  CASE WHEN m.home_goal > m.away_goal THEN 'Manchester United Loss'
		   WHEN m.home_goal < m.away_goal THEN 'Manchester United Win' 
  		   ELSE 'Tie' END AS outcome
  FROM match AS m
  LEFT JOIN team AS t ON m.awayteam_id = t.team_api_id)
-- Select columns and and rank the matches by date Find out all M The game the team lost, according to date sort
SELECT DISTINCT
    m.date,
    home.team_long_name AS home_team,
    away.team_long_name AS away_team,
    m.home_goal, m.away_goal,
    RANK() OVER(ORDER BY date) as date_rank
-- Join the CTEs onto the match table because SELECT There are home, away Two CTE Field of table, so it cannot be LEFT JOIN
FROM match AS m
INNER JOIN home ON m.id = home.id
INNER JOIN away ON m.id = away.id
WHERE m.season = '2014/2015'
      AND ((home.team_long_name = 'Manchester United' AND home.outcome = 'Manchester United Loss')
      OR (away.team_long_name = 'Manchester United' AND away.outcome = 'Manchester United Loss'));

First CTE (second similar) =

Final result = >