Window functions

ge.png

1
2
3
4
5
6
1.Show the lastName, party and votes for the constituency 'S14000024' in 2017.

SELECT lastName, party, votes
  FROM ge
 WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY votes DESC
1
2
3
4
5
6
7
2.Show the party and RANK for constituency S14000024 in 2017. List the output by party

SELECT party, votes,
       RANK() OVER (ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party
1
2
3
4
5
6
7
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).

SELECT yr,party, votes,
      RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency = 'S14000021'
ORDER BY party,yr
1
2
3
4
5
6
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.

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
1
2
3
4
5
6
7
8
9
10
11
12
5.Show the parties that won for each Edinburgh constituency in 2017.

select within select:

select constituency,party from 
(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)as ge1
where ge1.posn = 1

提取每组最值的另一个方法是group by分组条件后，先执行聚合函数max后
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
6.Show how many seats for each party in Scotland in 2017.

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

终于看懂这道题想表达啥意思了，不太了解选举制度所有半天没有理解'seats'是啥意思，原来是说每个选区得票数最多的政党会拿到这个选区的位置，然后统计每个政党到底拿了多少个位置。再思考如果有个政党没有拿到如果位置的话（虽然可能概率挺小），所以需要后面right join地区内所有政党，就像这样：

select party2.party,party2.seats from
(
 select ge1.party,count(*) from 
 (select party,
  rank() over(partition by constituency order by votes desc) as posn from ge
  where constituency like 'S%' and yr = 2017
 )as ge1
 where ge1.posn = 1
 group by ge1.party
)as party2
right join
(
select distinct party from ge where constituency like 'S%' and yr = 2017
)as party1
on (party1.party=party2.party)
where party2.party is not null

需要最后排除掉party是null的情况

Summary

  • 窗口函数不错子，之后还要深入学习，之后再专门做个窗口函数的笔记吧…