中國運動員獎牌數的移動最大值
使用框架可以在不先用相對擷取函式 LAG 和 LEAD 把前一列的數值取到目前列的情況下，直接向前或向後「偷看」。
本練習屬於課程
PostgreSQL 統計摘要與視窗函式
練習說明
- 回傳運動員、所獲得的獎牌數，以及最大獎牌數。僅比較前兩位與當前的運動員，並依運動員姓名的字母順序排序。
動手互動練習
試著完成這個範例程式碼，體驗一下這個練習。
WITH Chinese_Medals AS (
SELECT
Athlete, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
Country = 'CHN' AND Medal = 'Gold'
AND Year >= 2000
GROUP BY Athlete)
SELECT
-- Select the athletes and the medals they've earned
___,
___,
-- Get the max of the last two and current rows' medals
___ OVER (ORDER BY ___ ASC
ROWS BETWEEN ___
AND ___) AS Max_Medals
FROM Chinese_Medals
ORDER BY Athlete ASC;