SQL窗口函数项目实战-英国大选数据分析

一：项目背景

General Elections were held in the UK in 2015 and 2017. Every citizen votes in a constituency. The candidate who gains the most votes becomes MP for that constituency.

二：数据表

All these results are recorded in a table ge

ge表中六个字段如下图所示。 

From sqlzoo

三：取数需求

需求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

结果：

需求2

问题：Who won?

需求描述：Show the party and RANK for constituency S14000024 in 2017. List the output by party

思路：用排序窗口函数，按votes降序排序，排序结果赋值于posn字段。

代码：

SELECT party, votes,
       RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party

结果：

需求3

背景：The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.

需求描述：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).

思路：用排序窗口函数，以年份yr作为分区，按votes降序排序，排序结果赋值于posn字段。

代码：

SELECT yr,party, votes,
      RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'

结果：

需求4

背景：Edinburgh constituencies are numbered S14000021 to S14000026.

需求分析：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.

思路：用排序窗口函数，以选区作为分区，按votes降序排序，排序结果赋值于posn字段。

代码：

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 ASC,constituency

结果：

需求5

需求描述：Show the parties that won for each Edinburgh constituency in 2017.Winners Only.

思路：用子查询查出同2017年同一个选区的所有votes，条件判断大于子查询的结果即可。

SELECT constituency,party
FROM ge x
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
   AND votes >= ALL(SELECT votes FROM ge y WHERE x.constituency=y.constituency AND yr=2017)
ORDER BY constituency

结果：

需求6

背景：Scottish constituencies start with 'S'.

需求描述：Show how many seats for each party in Scotland in 2017.

业务逻辑：每个选区中票数最多的候选人，其代表的party在这个选区胜出，获得seats。

思路：用GROUP BY函数对party分组，用COUNT聚合求个数，用通配符匹配“以S开头”的选区，用子查询先查出同2017年同一个选区的所有votes，条件判断大于子查询的结果即为票数最多的候选人。

代码：

SELECT party, COUNT(*)
FROM ge x
WHERE constituency LIKE 'S%'
   AND yr  = 2017
   AND votes >= ALL(SELECT votes FROM ge y WHERE x.constituency = y.constituency AND y.yr = 2017)
GROUP BY party

结果：