[SQL] Intermediate SQL(4)
12 minute read
OVER()
RANK()PARTITION BY()ROWS BETWEEN [start] AND [finish]
PRECEDINGFOLLOWINGUNBOUNDED PRECEDINGUNBOUNDED FOLLOWINGCURRENT ROW
- Practice
%reload_ext sql
%sql postgresql://postgres:1234@0.0.0.0:5432/football
'Connected: postgres@football'
Over()
- perform calculations on an already generated result set, meaning that is uses information in result set rather than database
- processed after every part of query except ORDER BY
- quick
- unable in sqlite
-
mysql >= 8.0
- get id, country, season, home_team_goal, away_team_goal and avg_goal
%%sql
SELECT
m.id,
c.name AS country,
m.season,
m.home_team_goal,
m.away_team_goal,
AVG(m.home_team_goal + m.away_team_goal) OVER() AS avg_goal
FROM matches AS m
LEFT JOIN country AS c
ON m.country_id = c.id
LIMIT 10
mysql://root:***@0.0.0.0:3306/football
* postgresql://postgres:***@0.0.0.0:5432/football
10 rows affected.
| id |
country |
season |
home_team_goal |
away_team_goal |
avg_goal |
| 1 |
Belgium |
2008/2009 |
1 |
1 |
2.7585261875761267 |
| 2 |
Belgium |
2008/2009 |
0 |
0 |
2.7585261875761267 |
| 3 |
Belgium |
2008/2009 |
0 |
3 |
2.7585261875761267 |
| 4 |
Belgium |
2008/2009 |
5 |
0 |
2.7585261875761267 |
| 5 |
Belgium |
2008/2009 |
1 |
3 |
2.7585261875761267 |
| 6 |
Belgium |
2008/2009 |
1 |
1 |
2.7585261875761267 |
| 7 |
Belgium |
2008/2009 |
2 |
2 |
2.7585261875761267 |
| 8 |
Belgium |
2008/2009 |
1 |
2 |
2.7585261875761267 |
| 9 |
Belgium |
2008/2009 |
1 |
0 |
2.7585261875761267 |
| 10 |
Belgium |
2008/2009 |
4 |
1 |
2.7585261875761267 |
RANK()
- get ranked list according to which leagues, on average, score the most goals in a match
%%sql
SELECT
l.name AS league,
AVG(m.home_team_goal + m.away_team_goal) AS avg_goals,
RANK() OVER(ORDER BY AVG(m.home_team_goal + m.away_team_goal) DESC) AS league_rank
FROM league AS l
LEFT JOIN matches AS m
ON l.id = m.country_id
WHERE m.season = '2011/2012'
GROUP BY l.name
ORDER BY league_rank;
mysql://root:***@0.0.0.0:3306/football
* postgresql://postgres:***@0.0.0.0:5432/football
10 rows affected.
| league |
avg_goals |
league_rank |
| Netherlands Eredivisie |
3.2581699346405229 |
1 |
| Belgium Jupiler League |
2.8791666666666667 |
2 |
| Germany 1. Bundesliga |
2.8594771241830065 |
3 |
| England Premier League |
2.8052631578947368 |
4 |
| Spain LIGA BBVA |
2.7631578947368421 |
5 |
| Portugal Liga ZON Sagres |
2.6416666666666667 |
6 |
| Scotland Premier League |
2.6359649122807018 |
7 |
| Italy Serie A |
2.5837988826815642 |
8 |
| France Ligue 1 |
2.5157894736842105 |
9 |
| Poland Ekstraklasa |
2.1958333333333333 |
10 |
PARTITION BY()
- get data set of games played by FC Barcelona in their home comparing their individual game performance to the overall average for that season
%%sql
SELECT
date,
season,
home_team_goal,
AVG(home_team_goal) OVER(PARTITION BY season) AS season_homeavg
FROM matches
WHERE
home_team_api_id = 8634;
mysql://root:***@0.0.0.0:3306/football
* postgresql://postgres:***@0.0.0.0:5432/football
137 rows affected.
| date |
season |
home_team_goal |
season_homeavg |
| 2008-11-08T00:00:00Z |
2008/2009 |
6 |
3.2105263157894737 |
| 2008-11-23T00:00:00Z |
2008/2009 |
1 |
3.2105263157894737 |
| 2008-12-06T00:00:00Z |
2008/2009 |
4 |
3.2105263157894737 |
| 2008-12-13T00:00:00Z |
2008/2009 |
2 |
3.2105263157894737 |
| 2009-01-03T00:00:00Z |
2008/2009 |
3 |
3.2105263157894737 |
| 2009-01-17T00:00:00Z |
2008/2009 |
5 |
3.2105263157894737 |
| 2008-09-13T00:00:00Z |
2008/2009 |
1 |
3.2105263157894737 |
| 2009-01-24T00:00:00Z |
2008/2009 |
4 |
3.2105263157894737 |
| 2009-02-08T00:00:00Z |
2008/2009 |
3 |
3.2105263157894737 |
| 2009-02-21T00:00:00Z |
2008/2009 |
1 |
3.2105263157894737 |
| 2009-03-07T00:00:00Z |
2008/2009 |
2 |
3.2105263157894737 |
| 2009-03-22T00:00:00Z |
2008/2009 |
6 |
3.2105263157894737 |
| 2009-04-11T00:00:00Z |
2008/2009 |
2 |
3.2105263157894737 |
| 2009-04-22T00:00:00Z |
2008/2009 |
4 |
3.2105263157894737 |
| 2009-05-10T00:00:00Z |
2008/2009 |
3 |
3.2105263157894737 |
| 2009-05-23T00:00:00Z |
2008/2009 |
0 |
3.2105263157894737 |
| 2008-09-24T00:00:00Z |
2008/2009 |
3 |
3.2105263157894737 |
| 2008-10-04T00:00:00Z |
2008/2009 |
6 |
3.2105263157894737 |
| 2008-10-25T00:00:00Z |
2008/2009 |
5 |
3.2105263157894737 |
| 2009-08-31T00:00:00Z |
2009/2010 |
3 |
3.0000000000000000 |
| 2009-11-07T00:00:00Z |
2009/2010 |
4 |
3.0000000000000000 |
| 2009-11-29T00:00:00Z |
2009/2010 |
1 |
3.0000000000000000 |
| 2009-12-12T00:00:00Z |
2009/2010 |
1 |
3.0000000000000000 |
| 2010-01-02T00:00:00Z |
2009/2010 |
1 |
3.0000000000000000 |
| 2010-01-16T00:00:00Z |
2009/2010 |
4 |
3.0000000000000000 |
| 2010-02-06T00:00:00Z |
2009/2010 |
2 |
3.0000000000000000 |
| 2010-02-20T00:00:00Z |
2009/2010 |
4 |
3.0000000000000000 |
| 2010-02-27T00:00:00Z |
2009/2010 |
2 |
3.0000000000000000 |
| 2010-03-14T00:00:00Z |
2009/2010 |
3 |
3.0000000000000000 |
| 2010-03-24T00:00:00Z |
2009/2010 |
2 |
3.0000000000000000 |
| 2009-09-19T00:00:00Z |
2009/2010 |
5 |
3.0000000000000000 |
| 2010-04-03T00:00:00Z |
2009/2010 |
4 |
3.0000000000000000 |
| 2010-04-14T00:00:00Z |
2009/2010 |
3 |
3.0000000000000000 |
| 2010-04-24T00:00:00Z |
2009/2010 |
3 |
3.0000000000000000 |
| 2010-05-04T00:00:00Z |
2009/2010 |
4 |
3.0000000000000000 |
| 2010-05-16T00:00:00Z |
2009/2010 |
4 |
3.0000000000000000 |
| 2009-10-03T00:00:00Z |
2009/2010 |
1 |
3.0000000000000000 |
| 2009-10-25T00:00:00Z |
2009/2010 |
6 |
3.0000000000000000 |
| 2010-11-13T00:00:00Z |
2010/2011 |
3 |
2.4210526315789474 |
| 2010-11-29T00:00:00Z |
2010/2011 |
5 |
2.4210526315789474 |
| 2010-12-12T00:00:00Z |
2010/2011 |
5 |
2.4210526315789474 |
| 2011-01-02T00:00:00Z |
2010/2011 |
2 |
2.4210526315789474 |
| 2011-01-16T00:00:00Z |
2010/2011 |
4 |
2.4210526315789474 |
| 2010-09-11T00:00:00Z |
2010/2011 |
0 |
2.4210526315789474 |
| 2011-01-22T00:00:00Z |
2010/2011 |
3 |
2.4210526315789474 |
| 2011-02-05T00:00:00Z |
2010/2011 |
3 |
2.4210526315789474 |
| 2011-02-20T00:00:00Z |
2010/2011 |
2 |
2.4210526315789474 |
| 2011-03-05T00:00:00Z |
2010/2011 |
1 |
2.4210526315789474 |
| 2011-03-19T00:00:00Z |
2010/2011 |
2 |
2.4210526315789474 |
| 2011-04-09T00:00:00Z |
2010/2011 |
3 |
2.4210526315789474 |
| 2011-04-23T00:00:00Z |
2010/2011 |
2 |
2.4210526315789474 |
| 2011-05-08T00:00:00Z |
2010/2011 |
2 |
2.4210526315789474 |
| 2011-05-15T00:00:00Z |
2010/2011 |
0 |
2.4210526315789474 |
| 2010-09-22T00:00:00Z |
2010/2011 |
1 |
2.4210526315789474 |
| 2010-10-03T00:00:00Z |
2010/2011 |
1 |
2.4210526315789474 |
| 2010-10-16T00:00:00Z |
2010/2011 |
2 |
2.4210526315789474 |
| 2010-10-30T00:00:00Z |
2010/2011 |
5 |
2.4210526315789474 |
| 2011-10-29T00:00:00Z |
2011/2012 |
5 |
3.8421052631578947 |
| 2011-11-19T00:00:00Z |
2011/2012 |
4 |
3.8421052631578947 |
| 2011-12-03T00:00:00Z |
2011/2012 |
5 |
3.8421052631578947 |
| 2011-11-29T00:00:00Z |
2011/2012 |
4 |
3.8421052631578947 |
| 2012-01-15T00:00:00Z |
2011/2012 |
4 |
3.8421052631578947 |
| 2011-08-29T00:00:00Z |
2011/2012 |
5 |
3.8421052631578947 |
| 2012-05-02T00:00:00Z |
2011/2012 |
4 |
3.8421052631578947 |
| 2012-02-04T00:00:00Z |
2011/2012 |
2 |
3.8421052631578947 |
| 2012-02-19T00:00:00Z |
2011/2012 |
5 |
3.8421052631578947 |
| 2012-03-03T00:00:00Z |
2011/2012 |
3 |
3.8421052631578947 |
| 2012-03-20T00:00:00Z |
2011/2012 |
5 |
3.8421052631578947 |
| 2012-03-31T00:00:00Z |
2011/2012 |
2 |
3.8421052631578947 |
| 2012-04-10T00:00:00Z |
2011/2012 |
4 |
3.8421052631578947 |
| 2012-04-21T00:00:00Z |
2011/2012 |
1 |
3.8421052631578947 |
| 2012-05-05T00:00:00Z |
2011/2012 |
4 |
3.8421052631578947 |
| 2011-09-17T00:00:00Z |
2011/2012 |
8 |
3.8421052631578947 |
| 2011-09-24T00:00:00Z |
2011/2012 |
5 |
3.8421052631578947 |
| 2011-10-15T00:00:00Z |
2011/2012 |
3 |
3.8421052631578947 |
| 2011-10-22T00:00:00Z |
2011/2012 |
0 |
3.8421052631578947 |
| 2012-08-19T00:00:00Z |
2012/2013 |
5 |
3.3157894736842105 |
| 2012-11-03T00:00:00Z |
2012/2013 |
3 |
3.3157894736842105 |
| 2012-11-17T00:00:00Z |
2012/2013 |
3 |
3.3157894736842105 |
| 2012-12-01T00:00:00Z |
2012/2013 |
5 |
3.3157894736842105 |
| 2012-12-16T00:00:00Z |
2012/2013 |
4 |
3.3157894736842105 |
| 2013-01-06T00:00:00Z |
2012/2013 |
4 |
3.3157894736842105 |
| 2013-01-27T00:00:00Z |
2012/2013 |
5 |
3.3157894736842105 |
| 2013-02-10T00:00:00Z |
2012/2013 |
6 |
3.3157894736842105 |
| 2013-02-23T00:00:00Z |
2012/2013 |
2 |
3.3157894736842105 |
| 2013-03-09T00:00:00Z |
2012/2013 |
2 |
3.3157894736842105 |
| 2013-03-17T00:00:00Z |
2012/2013 |
3 |
3.3157894736842105 |
| 2012-09-02T00:00:00Z |
2012/2013 |
1 |
3.3157894736842105 |
| 2013-04-06T00:00:00Z |
2012/2013 |
5 |
3.3157894736842105 |
| 2013-04-20T00:00:00Z |
2012/2013 |
1 |
3.3157894736842105 |
| 2013-05-05T00:00:00Z |
2012/2013 |
4 |
3.3157894736842105 |
| 2013-05-19T00:00:00Z |
2012/2013 |
2 |
3.3157894736842105 |
| 2013-06-01T00:00:00Z |
2012/2013 |
4 |
3.3157894736842105 |
| 2012-09-22T00:00:00Z |
2012/2013 |
2 |
3.3157894736842105 |
| 2012-10-07T00:00:00Z |
2012/2013 |
2 |
3.3157894736842105 |
| 2013-08-18T00:00:00Z |
2013/2014 |
7 |
3.3684210526315789 |
| 2013-10-26T00:00:00Z |
2013/2014 |
2 |
3.3684210526315789 |
| 2013-11-01T00:00:00Z |
2013/2014 |
1 |
3.3684210526315789 |
| 2013-11-23T00:00:00Z |
2013/2014 |
4 |
3.3684210526315789 |
| 2013-12-14T00:00:00Z |
2013/2014 |
2 |
3.3684210526315789 |
| 2014-01-05T00:00:00Z |
2013/2014 |
4 |
3.3684210526315789 |
| 2014-01-26T00:00:00Z |
2013/2014 |
3 |
3.3684210526315789 |
| 2014-02-01T00:00:00Z |
2013/2014 |
2 |
3.3684210526315789 |
| 2014-02-15T00:00:00Z |
2013/2014 |
6 |
3.3684210526315789 |
| 2014-03-02T00:00:00Z |
2013/2014 |
4 |
3.3684210526315789 |
| 2014-03-16T00:00:00Z |
2013/2014 |
7 |
3.3684210526315789 |
| 2014-03-26T00:00:00Z |
2013/2014 |
3 |
3.3684210526315789 |
| 2014-04-05T00:00:00Z |
2013/2014 |
3 |
3.3684210526315789 |
| 2014-04-20T00:00:00Z |
2013/2014 |
2 |
3.3684210526315789 |
| 2014-05-03T00:00:00Z |
2013/2014 |
2 |
3.3684210526315789 |
| 2014-05-17T00:00:00Z |
2013/2014 |
1 |
3.3684210526315789 |
| 2013-09-14T00:00:00Z |
2013/2014 |
3 |
3.3684210526315789 |
| 2013-09-24T00:00:00Z |
2013/2014 |
4 |
3.3684210526315789 |
| 2013-10-05T00:00:00Z |
2013/2014 |
4 |
3.3684210526315789 |
| 2014-08-24T00:00:00Z |
2014/2015 |
3 |
3.3684210526315789 |
| 2014-11-01T00:00:00Z |
2014/2015 |
0 |
3.3684210526315789 |
| 2014-11-22T00:00:00Z |
2014/2015 |
5 |
3.3684210526315789 |
| 2014-12-07T00:00:00Z |
2014/2015 |
5 |
3.3684210526315789 |
| 2014-12-20T00:00:00Z |
2014/2015 |
5 |
3.3684210526315789 |
| 2015-01-11T00:00:00Z |
2014/2015 |
3 |
3.3684210526315789 |
| 2015-02-01T00:00:00Z |
2014/2015 |
3 |
3.3684210526315789 |
| 2015-02-15T00:00:00Z |
2014/2015 |
5 |
3.3684210526315789 |
| 2015-02-21T00:00:00Z |
2014/2015 |
0 |
3.3684210526315789 |
| 2015-03-08T00:00:00Z |
2014/2015 |
6 |
3.3684210526315789 |
| 2015-03-22T00:00:00Z |
2014/2015 |
2 |
3.3684210526315789 |
| 2014-09-13T00:00:00Z |
2014/2015 |
2 |
3.3684210526315789 |
| 2015-04-08T00:00:00Z |
2014/2015 |
4 |
3.3684210526315789 |
| 2015-04-18T00:00:00Z |
2014/2015 |
2 |
3.3684210526315789 |
| 2015-04-28T00:00:00Z |
2014/2015 |
6 |
3.3684210526315789 |
| 2015-05-09T00:00:00Z |
2014/2015 |
2 |
3.3684210526315789 |
| 2015-05-23T00:00:00Z |
2014/2015 |
2 |
3.3684210526315789 |
| 2014-09-27T00:00:00Z |
2014/2015 |
6 |
3.3684210526315789 |
| 2014-10-18T00:00:00Z |
2014/2015 |
3 |
3.3684210526315789 |
| 2015-11-08T00:00:00Z |
2015/2016 |
3 |
3.2500000000000000 |
| 2015-11-28T00:00:00Z |
2015/2016 |
4 |
3.2500000000000000 |
| 2015-12-12T00:00:00Z |
2015/2016 |
2 |
3.2500000000000000 |
| 2015-12-30T00:00:00Z |
2015/2016 |
4 |
3.2500000000000000 |
- get data set of games played by FC Barcelona in their home comparing their individual game performance to the overall average for that season by month
%%sql
-- convert date
with matches_converted as (
select
TO_DATE(date, 'yyyy-mm-dd') AS date, season, home_team_goal, away_team_goal, home_team_api_id, away_team_api_id
from matches
)
SELECT
date,
season,
EXTRACT(month FROM date) AS month,
home_team_goal,
away_team_goal,
AVG(home_team_goal) OVER(PARTITION BY season, EXTRACT(month FROM date)) AS season_mo_home
FROM matches_converted
WHERE
home_team_api_id = 8634;
mysql://root:***@0.0.0.0:3306/football
* postgresql://postgres:***@0.0.0.0:5432/football
137 rows affected.
| date |
season |
month |
home_team_goal |
away_team_goal |
season_mo_home |
| 2009-01-03 |
2008/2009 |
1 |
3 |
1 |
4.0000000000000000 |
| 2009-01-24 |
2008/2009 |
1 |
4 |
1 |
4.0000000000000000 |
| 2009-01-17 |
2008/2009 |
1 |
5 |
0 |
4.0000000000000000 |
| 2009-02-08 |
2008/2009 |
2 |
3 |
1 |
2.0000000000000000 |
| 2009-02-21 |
2008/2009 |
2 |
1 |
2 |
2.0000000000000000 |
| 2009-03-07 |
2008/2009 |
3 |
2 |
0 |
4.0000000000000000 |
| 2009-03-22 |
2008/2009 |
3 |
6 |
0 |
4.0000000000000000 |
| 2009-04-11 |
2008/2009 |
4 |
2 |
0 |
3.0000000000000000 |
| 2009-04-22 |
2008/2009 |
4 |
4 |
0 |
3.0000000000000000 |
| 2009-05-10 |
2008/2009 |
5 |
3 |
3 |
1.5000000000000000 |
| 2009-05-23 |
2008/2009 |
5 |
0 |
1 |
1.5000000000000000 |
| 2008-09-24 |
2008/2009 |
9 |
3 |
2 |
2.0000000000000000 |
| 2008-09-13 |
2008/2009 |
9 |
1 |
1 |
2.0000000000000000 |
| 2008-10-04 |
2008/2009 |
10 |
6 |
1 |
5.5000000000000000 |
| 2008-10-25 |
2008/2009 |
10 |
5 |
0 |
5.5000000000000000 |
| 2008-11-08 |
2008/2009 |
11 |
6 |
0 |
3.5000000000000000 |
| 2008-11-23 |
2008/2009 |
11 |
1 |
1 |
3.5000000000000000 |
| 2008-12-13 |
2008/2009 |
12 |
2 |
0 |
3.0000000000000000 |
| 2008-12-06 |
2008/2009 |
12 |
4 |
0 |
3.0000000000000000 |
| 2010-01-16 |
2009/2010 |
1 |
4 |
0 |
2.5000000000000000 |
| 2010-01-02 |
2009/2010 |
1 |
1 |
1 |
2.5000000000000000 |
| 2010-02-06 |
2009/2010 |
2 |
2 |
1 |
2.6666666666666667 |
| 2010-02-27 |
2009/2010 |
2 |
2 |
1 |
2.6666666666666667 |
| 2010-02-20 |
2009/2010 |
2 |
4 |
0 |
2.6666666666666667 |
| 2010-03-24 |
2009/2010 |
3 |
2 |
0 |
2.5000000000000000 |
| 2010-03-14 |
2009/2010 |
3 |
3 |
0 |
2.5000000000000000 |
| 2010-04-03 |
2009/2010 |
4 |
4 |
1 |
3.3333333333333333 |
| 2010-04-24 |
2009/2010 |
4 |
3 |
1 |
3.3333333333333333 |
| 2010-04-14 |
2009/2010 |
4 |
3 |
0 |
3.3333333333333333 |
| 2010-05-04 |
2009/2010 |
5 |
4 |
1 |
4.0000000000000000 |
| 2010-05-16 |
2009/2010 |
5 |
4 |
0 |
4.0000000000000000 |
| 2009-08-31 |
2009/2010 |
8 |
3 |
0 |
3.0000000000000000 |
| 2009-09-19 |
2009/2010 |
9 |
5 |
2 |
5.0000000000000000 |
| 2009-10-03 |
2009/2010 |
10 |
1 |
0 |
3.5000000000000000 |
| 2009-10-25 |
2009/2010 |
10 |
6 |
1 |
3.5000000000000000 |
| 2009-11-07 |
2009/2010 |
11 |
4 |
2 |
2.5000000000000000 |
| 2009-11-29 |
2009/2010 |
11 |
1 |
0 |
2.5000000000000000 |
| 2009-12-12 |
2009/2010 |
12 |
1 |
0 |
1.00000000000000000000 |
| 2011-01-16 |
2010/2011 |
1 |
4 |
1 |
3.0000000000000000 |
| 2011-01-22 |
2010/2011 |
1 |
3 |
0 |
3.0000000000000000 |
| 2011-01-02 |
2010/2011 |
1 |
2 |
1 |
3.0000000000000000 |
| 2011-02-05 |
2010/2011 |
2 |
3 |
0 |
2.5000000000000000 |
| 2011-02-20 |
2010/2011 |
2 |
2 |
1 |
2.5000000000000000 |
| 2011-03-05 |
2010/2011 |
3 |
1 |
0 |
1.5000000000000000 |
| 2011-03-19 |
2010/2011 |
3 |
2 |
1 |
1.5000000000000000 |
| 2011-04-09 |
2010/2011 |
4 |
3 |
1 |
2.5000000000000000 |
| 2011-04-23 |
2010/2011 |
4 |
2 |
0 |
2.5000000000000000 |
| 2011-05-15 |
2010/2011 |
5 |
0 |
0 |
1.00000000000000000000 |
| 2011-05-08 |
2010/2011 |
5 |
2 |
0 |
1.00000000000000000000 |
| 2010-09-11 |
2010/2011 |
9 |
0 |
2 |
0.50000000000000000000 |
| 2010-09-22 |
2010/2011 |
9 |
1 |
0 |
0.50000000000000000000 |
| 2010-10-03 |
2010/2011 |
10 |
1 |
1 |
2.6666666666666667 |
| 2010-10-16 |
2010/2011 |
10 |
2 |
1 |
2.6666666666666667 |
| 2010-10-30 |
2010/2011 |
10 |
5 |
0 |
2.6666666666666667 |
| 2010-11-13 |
2010/2011 |
11 |
3 |
1 |
4.0000000000000000 |
| 2010-11-29 |
2010/2011 |
11 |
5 |
0 |
4.0000000000000000 |
| 2010-12-12 |
2010/2011 |
12 |
5 |
0 |
5.0000000000000000 |
| 2012-01-15 |
2011/2012 |
1 |
4 |
2 |
4.0000000000000000 |
| 2012-02-19 |
2011/2012 |
2 |
5 |
1 |
3.5000000000000000 |
| 2012-02-04 |
2011/2012 |
2 |
2 |
1 |
3.5000000000000000 |
| 2012-03-31 |
2011/2012 |
3 |
2 |
0 |
3.3333333333333333 |
| 2012-03-03 |
2011/2012 |
3 |
3 |
1 |
3.3333333333333333 |
| 2012-03-20 |
2011/2012 |
3 |
5 |
3 |
3.3333333333333333 |
| 2012-04-10 |
2011/2012 |
4 |
4 |
0 |
2.5000000000000000 |
| 2012-04-21 |
2011/2012 |
4 |
1 |
2 |
2.5000000000000000 |
| 2012-05-02 |
2011/2012 |
5 |
4 |
1 |
4.0000000000000000 |
| 2012-05-05 |
2011/2012 |
5 |
4 |
0 |
4.0000000000000000 |
| 2011-08-29 |
2011/2012 |
8 |
5 |
0 |
5.0000000000000000 |
| 2011-09-17 |
2011/2012 |
9 |
8 |
0 |
6.5000000000000000 |
| 2011-09-24 |
2011/2012 |
9 |
5 |
0 |
6.5000000000000000 |
| 2011-10-29 |
2011/2012 |
10 |
5 |
0 |
2.6666666666666667 |
| 2011-10-15 |
2011/2012 |
10 |
3 |
0 |
2.6666666666666667 |
| 2011-10-22 |
2011/2012 |
10 |
0 |
0 |
2.6666666666666667 |
| 2011-11-19 |
2011/2012 |
11 |
4 |
0 |
4.0000000000000000 |
| 2011-11-29 |
2011/2012 |
11 |
4 |
0 |
4.0000000000000000 |
| 2011-12-03 |
2011/2012 |
12 |
5 |
0 |
5.0000000000000000 |
| 2013-01-06 |
2012/2013 |
1 |
4 |
0 |
4.5000000000000000 |
| 2013-01-27 |
2012/2013 |
1 |
5 |
1 |
4.5000000000000000 |
| 2013-02-10 |
2012/2013 |
2 |
6 |
1 |
4.0000000000000000 |
| 2013-02-23 |
2012/2013 |
2 |
2 |
1 |
4.0000000000000000 |
| 2013-03-09 |
2012/2013 |
3 |
2 |
0 |
2.5000000000000000 |
| 2013-03-17 |
2012/2013 |
3 |
3 |
1 |
2.5000000000000000 |
| 2013-04-06 |
2012/2013 |
4 |
5 |
0 |
3.0000000000000000 |
| 2013-04-20 |
2012/2013 |
4 |
1 |
0 |
3.0000000000000000 |
| 2013-05-05 |
2012/2013 |
5 |
4 |
2 |
3.0000000000000000 |
| 2013-05-19 |
2012/2013 |
5 |
2 |
1 |
3.0000000000000000 |
| 2013-06-01 |
2012/2013 |
6 |
4 |
1 |
4.0000000000000000 |
| 2012-08-19 |
2012/2013 |
8 |
5 |
1 |
5.0000000000000000 |
| 2012-09-02 |
2012/2013 |
9 |
1 |
0 |
1.5000000000000000 |
| 2012-09-22 |
2012/2013 |
9 |
2 |
0 |
1.5000000000000000 |
| 2012-10-07 |
2012/2013 |
10 |
2 |
2 |
2.0000000000000000 |
| 2012-11-17 |
2012/2013 |
11 |
3 |
1 |
3.0000000000000000 |
| 2012-11-03 |
2012/2013 |
11 |
3 |
1 |
3.0000000000000000 |
| 2012-12-16 |
2012/2013 |
12 |
4 |
1 |
4.5000000000000000 |
| 2012-12-01 |
2012/2013 |
12 |
5 |
1 |
4.5000000000000000 |
| 2014-01-05 |
2013/2014 |
1 |
4 |
0 |
3.5000000000000000 |
| 2014-01-26 |
2013/2014 |
1 |
3 |
0 |
3.5000000000000000 |
| 2014-02-15 |
2013/2014 |
2 |
6 |
0 |
4.0000000000000000 |
| 2014-02-01 |
2013/2014 |
2 |
2 |
3 |
4.0000000000000000 |
| 2014-03-16 |
2013/2014 |
3 |
7 |
0 |
4.6666666666666667 |
| 2014-03-26 |
2013/2014 |
3 |
3 |
0 |
4.6666666666666667 |
| 2014-03-02 |
2013/2014 |
3 |
4 |
1 |
4.6666666666666667 |
| 2014-04-05 |
2013/2014 |
4 |
3 |
1 |
2.5000000000000000 |
| 2014-04-20 |
2013/2014 |
4 |
2 |
1 |
2.5000000000000000 |
| 2014-05-17 |
2013/2014 |
5 |
1 |
1 |
1.5000000000000000 |
| 2014-05-03 |
2013/2014 |
5 |
2 |
2 |
1.5000000000000000 |
| 2013-08-18 |
2013/2014 |
8 |
7 |
0 |
7.0000000000000000 |
| 2013-09-24 |
2013/2014 |
9 |
4 |
1 |
3.5000000000000000 |
| 2013-09-14 |
2013/2014 |
9 |
3 |
2 |
3.5000000000000000 |
| 2013-10-26 |
2013/2014 |
10 |
2 |
1 |
3.0000000000000000 |
| 2013-10-05 |
2013/2014 |
10 |
4 |
1 |
3.0000000000000000 |
| 2013-11-01 |
2013/2014 |
11 |
1 |
0 |
2.5000000000000000 |
| 2013-11-23 |
2013/2014 |
11 |
4 |
0 |
2.5000000000000000 |
| 2013-12-14 |
2013/2014 |
12 |
2 |
1 |
2.0000000000000000 |
| 2015-01-11 |
2014/2015 |
1 |
3 |
1 |
3.0000000000000000 |
| 2015-02-15 |
2014/2015 |
2 |
5 |
0 |
2.6666666666666667 |
| 2015-02-01 |
2014/2015 |
2 |
3 |
2 |
2.6666666666666667 |
| 2015-02-21 |
2014/2015 |
2 |
0 |
1 |
2.6666666666666667 |
| 2015-03-08 |
2014/2015 |
3 |
6 |
1 |
4.0000000000000000 |
| 2015-03-22 |
2014/2015 |
3 |
2 |
1 |
4.0000000000000000 |
| 2015-04-18 |
2014/2015 |
4 |
2 |
0 |
4.0000000000000000 |
| 2015-04-28 |
2014/2015 |
4 |
6 |
0 |
4.0000000000000000 |
| 2015-04-08 |
2014/2015 |
4 |
4 |
0 |
4.0000000000000000 |
| 2015-05-23 |
2014/2015 |
5 |
2 |
2 |
2.0000000000000000 |
| 2015-05-09 |
2014/2015 |
5 |
2 |
0 |
2.0000000000000000 |
| 2014-08-24 |
2014/2015 |
8 |
3 |
0 |
3.0000000000000000 |
| 2014-09-13 |
2014/2015 |
9 |
2 |
0 |
4.0000000000000000 |
| 2014-09-27 |
2014/2015 |
9 |
6 |
0 |
4.0000000000000000 |
| 2014-10-18 |
2014/2015 |
10 |
3 |
0 |
3.0000000000000000 |
| 2014-11-01 |
2014/2015 |
11 |
0 |
1 |
2.5000000000000000 |
| 2014-11-22 |
2014/2015 |
11 |
5 |
1 |
2.5000000000000000 |
| 2014-12-20 |
2014/2015 |
12 |
5 |
0 |
5.0000000000000000 |
| 2014-12-07 |
2014/2015 |
12 |
5 |
1 |
5.0000000000000000 |
| 2015-11-08 |
2015/2016 |
11 |
3 |
0 |
3.5000000000000000 |
| 2015-11-28 |
2015/2016 |
11 |
4 |
0 |
3.5000000000000000 |
| 2015-12-30 |
2015/2016 |
12 |
4 |
0 |
3.0000000000000000 |
| 2015-12-12 |
2015/2016 |
12 |
2 |
2 |
3.0000000000000000 |
ROWS BETWEEN [start] AND [finish]
- Sliding windows
- perform calculations relative to the current row
- can be used to calculate totals, sums, averages, etc.
- can be partitioned by one or more columns
- types
PRECEDING - specifiy number of rows before the current row to include in calculationFOLLOWING - specifiy number of rows after the current row to include in calculationUNBOUNDED PRECEDING - include every row since the beginning of the data set in calculationUNBOUNDED FOLLOWING - include every row to the end of the data set in calculationCURRENT ROW - stop calculation at the current row
- 2008/2009시즌 FC 바르셀로나의 경기에 따른 누적 득점 수와, 누적 득점 평균은?
%%sql
SELECT
date,
home_team_goal,
SUM(home_team_goal) OVER(ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(home_team_goal) OVER(ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg
FROM matches
WHERE
home_team_api_id = 8634
AND season = '2008/2009';
mysql://root:***@0.0.0.0:3306/football
* postgresql://postgres:***@0.0.0.0:5432/football
19 rows affected.
| date |
home_team_goal |
running_total |
running_avg |
| 2008-09-13T00:00:00Z |
1 |
1 |
1.00000000000000000000 |
| 2008-09-24T00:00:00Z |
3 |
4 |
2.0000000000000000 |
| 2008-10-04T00:00:00Z |
6 |
10 |
3.3333333333333333 |
| 2008-10-25T00:00:00Z |
5 |
15 |
3.7500000000000000 |
| 2008-11-08T00:00:00Z |
6 |
21 |
4.2000000000000000 |
| 2008-11-23T00:00:00Z |
1 |
22 |
3.6666666666666667 |
| 2008-12-06T00:00:00Z |
4 |
26 |
3.7142857142857143 |
| 2008-12-13T00:00:00Z |
2 |
28 |
3.5000000000000000 |
| 2009-01-03T00:00:00Z |
3 |
31 |
3.4444444444444444 |
| 2009-01-17T00:00:00Z |
5 |
36 |
3.6000000000000000 |
| 2009-01-24T00:00:00Z |
4 |
40 |
3.6363636363636364 |
| 2009-02-08T00:00:00Z |
3 |
43 |
3.5833333333333333 |
| 2009-02-21T00:00:00Z |
1 |
44 |
3.3846153846153846 |
| 2009-03-07T00:00:00Z |
2 |
46 |
3.2857142857142857 |
| 2009-03-22T00:00:00Z |
6 |
52 |
3.4666666666666667 |
| 2009-04-11T00:00:00Z |
2 |
54 |
3.3750000000000000 |
| 2009-04-22T00:00:00Z |
4 |
58 |
3.4117647058823529 |
| 2009-05-10T00:00:00Z |
3 |
61 |
3.3888888888888889 |
| 2009-05-23T00:00:00Z |
0 |
61 |
3.2105263157894737 |
Practice
- 바르세로나가 2008/2009 시즌에 치른 경기 중 득실차의 절대값이 높은 순으로 구하시오.
%%sql
WITH home AS (
SELECT m.id, t.team_long_name,
CASE WHEN m.home_team_goal > m.away_team_goal THEN 'FCB Win'
WHEN m.home_team_goal < m.away_team_goal THEN 'FCB Loss'
ELSE 'Tie' END AS outcome
FROM matches AS m
LEFT JOIN team AS t ON m.home_team_api_id = t.team_api_id),
away AS (
SELECT m.id, t.team_long_name,
CASE WHEN m.home_team_goal > m.away_team_goal THEN 'FCB Loss'
WHEN m.home_team_goal < m.away_team_goal THEN 'FCB Win'
ELSE 'Tie' END AS outcome
FROM matches AS m
LEFT JOIN team AS t ON m.away_team_api_id = t.team_api_id)
SELECT DISTINCT
m.date,
home.team_long_name AS home_team,
away.team_long_name AS away_team,
m.home_team_goal, m.away_team_goal,
ABS(m.home_team_goal - m.away_team_goal) AS abs_goal,
RANK() OVER(ORDER BY ABS(home_team_goal - away_team_goal) DESC) as rank
FROM matches AS m
LEFT JOIN home ON m.id = home.id
LEFT JOIN away ON m.id = away.id
WHERE m.season = '2008/2009'
AND ((home.team_long_name = 'FC Barcelona') OR (away.team_long_name = 'FC Barcelona'))
order by rank;
mysql://root:***@0.0.0.0:3306/football
* postgresql://postgres:***@0.0.0.0:5432/football
38 rows affected.
| date |
home_team |
away_team |
home_team_goal |
away_team_goal |
abs_goal |
rank |
| 2008-11-08T00:00:00Z |
FC Barcelona |
Real Valladolid |
6 |
0 |
6 |
1 |
| 2009-03-22T00:00:00Z |
FC Barcelona |
Málaga CF |
6 |
0 |
6 |
1 |
| 2008-09-21T00:00:00Z |
Real Sporting de Gijón |
FC Barcelona |
1 |
6 |
5 |
3 |
| 2008-10-04T00:00:00Z |
FC Barcelona |
Atlético Madrid |
6 |
1 |
5 |
3 |
| 2008-10-25T00:00:00Z |
FC Barcelona |
UD Almería |
5 |
0 |
5 |
3 |
| 2009-01-17T00:00:00Z |
FC Barcelona |
RC Deportivo de La Coruña |
5 |
0 |
5 |
3 |
| 2008-12-06T00:00:00Z |
FC Barcelona |
Valencia CF |
4 |
0 |
4 |
7 |
| 2009-04-22T00:00:00Z |
FC Barcelona |
Sevilla FC |
4 |
0 |
4 |
7 |
| 2009-05-02T00:00:00Z |
Real Madrid CF |
FC Barcelona |
2 |
6 |
4 |
7 |
| 2008-11-01T00:00:00Z |
Málaga CF |
FC Barcelona |
1 |
4 |
3 |
10 |
| 2008-11-29T00:00:00Z |
Sevilla FC |
FC Barcelona |
0 |
3 |
3 |
10 |
| 2009-01-24T00:00:00Z |
FC Barcelona |
CD Numancia |
4 |
1 |
3 |
10 |
| 2008-11-16T00:00:00Z |
RC Recreativo |
FC Barcelona |
0 |
2 |
2 |
13 |
| 2008-12-13T00:00:00Z |
FC Barcelona |
Real Madrid CF |
2 |
0 |
2 |
13 |
| 2009-01-03T00:00:00Z |
FC Barcelona |
RCD Mallorca |
3 |
1 |
2 |
13 |
| 2009-02-08T00:00:00Z |
FC Barcelona |
Real Sporting de Gijón |
3 |
1 |
2 |
13 |
| 2009-03-07T00:00:00Z |
FC Barcelona |
Athletic Club de Bilbao |
2 |
0 |
2 |
13 |
| 2009-03-15T00:00:00Z |
UD Almería |
FC Barcelona |
0 |
2 |
2 |
13 |
| 2009-04-11T00:00:00Z |
FC Barcelona |
RC Recreativo |
2 |
0 |
2 |
13 |
| 2008-08-31T00:00:00Z |
CD Numancia |
FC Barcelona |
1 |
0 |
1 |
20 |
| 2008-09-24T00:00:00Z |
FC Barcelona |
Real Betis Balompié |
3 |
2 |
1 |
20 |
| 2008-09-27T00:00:00Z |
RCD Espanyol |
FC Barcelona |
1 |
2 |
1 |
20 |
| 2008-10-19T00:00:00Z |
Athletic Club de Bilbao |
FC Barcelona |
0 |
1 |
1 |
20 |
| 2008-12-21T00:00:00Z |
Villarreal CF |
FC Barcelona |
1 |
2 |
1 |
20 |
| 2009-01-11T00:00:00Z |
CA Osasuna |
FC Barcelona |
2 |
3 |
1 |
20 |
| 2009-02-01T00:00:00Z |
Racing Santander |
FC Barcelona |
1 |
2 |
1 |
20 |
| 2009-02-21T00:00:00Z |
FC Barcelona |
RCD Espanyol |
1 |
2 |
1 |
20 |
| 2009-03-01T00:00:00Z |
Atlético Madrid |
FC Barcelona |
4 |
3 |
1 |
20 |
| 2009-04-04T00:00:00Z |
Real Valladolid |
FC Barcelona |
0 |
1 |
1 |
20 |
| 2009-04-18T00:00:00Z |
Getafe CF |
FC Barcelona |
0 |
1 |
1 |
20 |
| 2009-05-17T00:00:00Z |
RCD Mallorca |
FC Barcelona |
2 |
1 |
1 |
20 |
| 2009-05-23T00:00:00Z |
FC Barcelona |
CA Osasuna |
0 |
1 |
1 |
20 |
| 2008-09-13T00:00:00Z |
FC Barcelona |
Racing Santander |
1 |
1 |
0 |
33 |
| 2008-11-23T00:00:00Z |
FC Barcelona |
Getafe CF |
1 |
1 |
0 |
33 |
| 2009-02-14T00:00:00Z |
Real Betis Balompié |
FC Barcelona |
2 |
2 |
0 |
33 |
| 2009-04-25T00:00:00Z |
Valencia CF |
FC Barcelona |
2 |
2 |
0 |
33 |
| 2009-05-10T00:00:00Z |
FC Barcelona |
Villarreal CF |
3 |
3 |
0 |
33 |
| 2009-05-30T00:00:00Z |
RC Deportivo de La Coruña |
FC Barcelona |
1 |
1 |
0 |
33 |
