w i n d o w fu n cti o n s...i nte r me d i ate sq l introduci ng wi ndow functi ons ! p erfo rm...
TRANSCRIPT
![Page 1: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/1.jpg)
Window FunctionsI N T E R M E D I AT E S Q L
Mona KhalilData Scientist, Greenhouse Software
![Page 2: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/2.jpg)
INTERMEDIATE SQL
Working with aggregate valuesRequires you to use GROUP BY with all non-aggregate columns
SELECT
country_id,
season,
date,
AVG(home_goal) AS avg_home
FROM match
GROUP BY country_id;
ERROR: column "match.season" must appear in the GROUP BY
clause or be used in an aggregate function
![Page 3: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/3.jpg)
INTERMEDIATE SQL
Introducing window functions!Perform calculations on an already generated result set (a window)
Aggregate calculations
Similar to subqueries in SELECT
Running totals, rankings, moving averages
![Page 4: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/4.jpg)
INTERMEDIATE SQL
What's a window function?How many goals were scored in each match in 2011/2012, and how did
that compare to the average?
SELECT
date,
(home_goal + away_goal) AS goals,
(SELECT AVG(home_goal + away_goal)
FROM match
WHERE season = '2011/2012') AS overall_avg
FROM match
WHERE season = '2011/2012';
| date | goals | overall_avg |
|------------|-------|-------------------|
| 2011-07-29 | 3 | 2.71646 |
| 2011-07-30 | 2 | 2.71646 |
| 2011-07-30 | 4 | 2.71646 |
| 2011-07-30 | 1 | 2.71646 |
![Page 5: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/5.jpg)
INTERMEDIATE SQL
What's a window function?How many goals were scored in each match in 2011/2012, and how did
that compare to the average?
SELECT
date,
(home_goal + away_goal) AS goals,
AVG(home_goal + away_goal) OVER() AS overall_avg
FROM match
WHERE season = '2011/2012';
| date | goals | overall_avg |
|------------|-------|-------------------|
| 2011-07-29 | 3 | 2.71646 |
| 2011-07-30 | 2 | 2.71646 |
| 2011-07-30 | 4 | 2.71646 |
| 2011-07-30 | 1 | 2.71646 |
![Page 6: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/6.jpg)
INTERMEDIATE SQL
Generate a RANKWhat is the rank of matches based on number of goals scored?
SELECT
date,
(home_goal + away_goal) AS goals
FROM match
WHERE season = '2011/2012';
| date | goals |
|------------|-------|
| 2011-07-29 | 3 |
| 2011-07-30 | 2 |
| 2011-07-30 | 4 |
| 2011-07-30 | 1 |
![Page 7: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/7.jpg)
INTERMEDIATE SQL
Generate a RANKWhat is the rank of matches based on number of goals scored?
SELECT
date,
(home_goal + away_goal) AS goals,
RANK() OVER(ORDER BY home_goal + away_goal) AS goals_rank
FROM match
WHERE season = '2011/2012';
| date | goals | goals_rank |
|------------|-------|------------|
| 2012-04-28 | 0 | 1 |
| 2011-12-26 | 0 | 1 |
| 2011-09-10 | 0 | 1 |
| 2011-08-27 | 0 | 1 |
![Page 8: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/8.jpg)
INTERMEDIATE SQL
Generate a RANKWhat is the rank of matches based on number of goals scored?
SELECT
date,
(home_goal + away_goal) AS goals,
RANK() OVER(ORDER BY home_goal + away_goal DESC) AS goals_rank
FROM match
WHERE season = '2011/2012';
| date | goals | goals_rank |
|------------|-------|------------|
| 2011-11-06 | 10 | 1 |
| 2011-08-28 | 10 | 1 |
| 2012-05-12 | 9 | 3 |
| 2012-02-12 | 9 | 3 |
![Page 9: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/9.jpg)
INTERMEDIATE SQL
Key DifferencesProcessed after every part of query except ORDER BY
Uses information in result set rather than database
Available in PostgreSQL, Oracle, MySQL, SQL Server...
...but NOT SQLite
![Page 10: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/10.jpg)
Let's Practice!I N T E R M E D I AT E S Q L
![Page 11: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/11.jpg)
Window PartitionsI N T E R M E D I AT E S Q L
Mona KhalilData Scientist, Greenhouse Software
![Page 12: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/12.jpg)
INTERMEDIATE SQL
OVER and PARTITION BYCalculate separate values for different categories
Calculate different calculations in the same column
AVG(home_goal) OVER(PARTITION BY season)
![Page 13: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/13.jpg)
INTERMEDIATE SQL
Partition your dataHow many goals were scored in each match, and how did that compare
to the overall average?
SELECT date, (home_goal + away_goal) AS goals, AVG(home_goal + away_goal) OVER() AS overall_avg
FROM match;
| date | goals | overall_avg | |------------|-------|-------------| | 2011-12-17 | 3 | 2.73210 | | 2012-05-01 | 2 | 2.73210 | | 2012-11-27 | 4 | 2.73210 | | 2013-04-20 | 1 | 2.73210 | | 2013-11-09 | 5 | 2.73210 |
![Page 14: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/14.jpg)
INTERMEDIATE SQL
Partition your dataHow many goals were scored in each match, and how did that compare
to the season's average?
SELECT date, (home_goal + away_goal) AS goals, AVG(home_goal + away_goal) OVER(PARTITION BY season) AS season_avg
FROM match;
| date | goals | season_avg | |------------|-------|-------------| | 2011-12-17 | 3 | 2.71646 | | 2012-05-01 | 2 | 2.71646 | | 2012-11-27 | 4 | 2.77270 | | 2013-04-20 | 1 | 2.77270 | | 2013-11-09 | 5 | 2.76682 |
![Page 15: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/15.jpg)
INTERMEDIATE SQL
PARTITION by Multiple ColumnsSELECT c.name, m.season, (home_goal + away_goal) AS goals, AVG(home_goal + away_goal) OVER(PARTITION BY m.season, c.name) AS season_ctry_avg
FROM country AS c LEFT JOIN match AS m ON c.id = m.country_id
| name | season | goals | season_ctry_avg | |-------------|-----------|-----------|-----------------| | Belgium | 2011/2012 | 1 | 2.88 | | Netherlands | 2014/2015 | 1 | 3.08 | | Belgium | 2011/2012 | 1 | 2.88 | | Spain | 2014/2015 | 2 | 2.66 |
![Page 16: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/16.jpg)
INTERMEDIATE SQL
PARTITION BY considerationsCan partition data by 1 or more columns
Can partition aggregate calculations, ranks, etc
![Page 17: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/17.jpg)
Let's Practice!I N T E R M E D I AT E S Q L
![Page 18: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/18.jpg)
Sliding WindowsI N T E R M E D I AT E S Q L
Mona KhalilData Scientist, Greenhouse Software
![Page 19: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/19.jpg)
INTERMEDIATE SQL
Sliding WindowsPerform calculations relative to the current row
Can be used to calculate running totals, sums, averages, etc
Can be partitioned by one or more columns
![Page 20: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/20.jpg)
INTERMEDIATE SQL
Sliding Window Keywords
ROWS BETWEEN <start> AND <finish>
PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
![Page 21: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/21.jpg)
INTERMEDIATE SQL
Sliding Window Example-- Manchester City Home Games SELECT date, home_goal, away_goal, SUM(home_goal) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM match WHERE hometeam_id = 8456 AND season = '2011/2012';
| date | home_goal | away_goal | running_total | |------------|-----------|-----------|---------------| | 2011-08-15 | 4 | 0 | 4 | | 2011-09-10 | 3 | 0 | 7 | | 2011-09-24 | 2 | 0 | 9 | | 2011-10-15 | 4 | 1 | 13 |
![Page 22: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/22.jpg)
INTERMEDIATE SQL
Sliding Window Frame-- Manchester City Home Games
SELECT date,
home_goal,
away_goal,
SUM(home_goal)
OVER(ORDER BY date
ROWS BETWEEN 1 PRECEDING
AND CURRENT ROW) AS last2
FROM match
WHERE hometeam_id = 8456
AND season = '2011/2012';
![Page 23: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/23.jpg)
Let's Practice!I N T E R M E D I AT E S Q L
![Page 24: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/24.jpg)
Bringing it allTogetherI N T E R M E D I AT E S Q L
Mona KhalilData Scientist, Greenhouse Software
![Page 25: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/25.jpg)
INTERMEDIATE SQL
What you've learned so farCASE statements
Simple subqueries
Nested and correlated subqueries
Common table expressions
Window functions
![Page 26: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/26.jpg)
INTERMEDIATE SQL
Let's do a case study!Who defeated Manchester United in the 2013/2014 season?
![Page 27: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/27.jpg)
INTERMEDIATE SQL
Steps to construct the queryGet team names with CTEs
Get match outcome with CASE statements
Determine how badly they lost with a window function
![Page 28: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/28.jpg)
INTERMEDIATE SQL
Getting the database for yourselfFull European Soccer Database
![Page 29: W i n d o w Fu n cti o n s...I NTE R ME D I ATE SQ L Introduci ng wi ndow functi ons ! P erfo rm calcu lat io n s o n an alread y gen erat ed res u lt s et ( a wi ndow) A ggregat e](https://reader034.vdocuments.us/reader034/viewer/2022043020/5f3c1d0c5b71a723a46deb21/html5/thumbnails/29.jpg)
Let's Practice!I N T E R M E D I AT E S Q L