SQL Zoo 9-.Window functions

以下数据均来自SQL Zoo

1.Show the lastName, party and votes for the constituency 'S14000024' in 2017.（显示2017年选区“S14000024”的姓氏、政党和选票）

SELECT lastName, party, votes
  FROM ge
 WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY votes DESC

2.Show the party and RANK for constituency S14000024 in 2017. List the output by party.（显示2017年S14000024选区的政党和RANK。按各方列出输出）

select party,votes,rank()over(order by votes desc) rosn 
from ge 
where constituency = "S14000024" and yr = 2017 
order by party

3.Use PARTITION to show the ranking of each party in S14000021 in each year.  Include yr, party, votes and ranking (the party with the most votes is 1).（使用PARTITION显示S14000021中各参与方每年的排名。包括候选人、政党、得票和排名(得票最多的政党为1)）

select yr,party,votes,rank()over(partition by yr order by votes desc) as posn 
from ge 
where constituency = "S14000021" 
order by party,yr

4.Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017.  Order your results so the winners are shown first, then ordered by constituency.（使用分区按选区显示2017年爱丁堡各政党的排名。排序结果，首先显示获胜者，然后按选区排序）

select constituency,party,votes,
rank()over(partition by constituency order by votes desc) as posn
from ge
where yr = 2017
and constituency between 'S14000021' and 'S14000026'
order by posn,constituency

5.Show the parties that won for each Edinburgh constituency in 2017.(列出2017年爱丁堡各选区获胜的政党)

select constituency,party from 
(select constituency,party,votes,
rank()over(partition by constituency order by votes desc) posn 
from ge 
where yr = 2017 and 
constituency between "S14000021" and "S14000026") as rk 
where rk.posn = 1 
order by constituency

6.Show how many seats for each party in Scotland in 2017.(显示2017年苏格兰各党派的席位)

select a.party, count(*) seats
from 
	(select party,
            rank() over(partition by constituency
                        order by votes desc) as nums
	  from ge
	  where constituency like 'S%' and yr = 2017) as a
where a.nums = 1
group by a.party;