– 第9- 关 Window functions - SQLZOO


-- 练习窗口函数

--1. Show the lastName, party and votes for the constituency 'S14000024' in 2017.
-- 练习where 

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
-- 练习 rank 

select party
,votes 
, rank() over(order by votes desc) as posn 
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
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.

-- 提示：Edinburgh 就是区域在'S14000021' and 'S14000026'。

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


-- 5.Show the parties that won for each Edinburgh constituency in 2017.
-- 练习子查询

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

-- 6. Show how many seats for each party in Scotland in 2017.
-- 注意 Scottish constituencies start with 'S'

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


-- 方法2 
select party,count(*) 
from (
select constituency,party, votes,
rank() over (partition by constituency order by votes desc) as posn
from ge
where constituency like 'S%'
and yr  = 2017) rk
where rk.posn=1
group by rk.party