按性别统计历届冠军
您已经为某个项目获取了上一年的冠军。但如果表中有多个项目、性别或其他指标作为列，您需要将表拆分为多个分区，才能避免某个项目或性别的冠军被误当作另一个项目或性别的上一届冠军。
本练习是课程的一部分
PostgreSQL 汇总统计与窗口函数
练习说明
- 按性别返回每一年赛事的上一届冠军。
交互式实操练习
通过完成这段示例代码来试试这个练习。
WITH Tennis_Gold AS (
SELECT DISTINCT
Gender, Year, Country
FROM Summer_Medals
WHERE
Year >= 2000 AND
Event = 'Javelin Throw' AND
Medal = 'Gold')
SELECT
Gender, Year,
Country AS Champion,
-- Fetch the previous year's champion by gender
___ OVER (___
ORDER BY ___ ASC) AS Last_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;