sqlzoo 9-.Window functions 答案

有什么问题，欢迎评论或私聊。转载请私聊博主，谢谢。

原题链接：https://sqlzoo.net/wiki/Window_functions

其他题解连接：https://blog.csdn.net/aiqiyizz/article/details/109057732

题解对应的是英文版题目。

这一节内容应该是关于一些常用的函数

9- Window function

9-.1 Warming up

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

9-.2 Who won?

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

9-.3

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

9-.4

输出格式：constituency, party, votes, rank

知道要输出什么就不难了

SELECT constituency,party, votes, RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) AS rank
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
 ORDER BY rank, constituency

9-.5 Winners Only

上一道题和SELECT IN SELECT的应用

SELECT constituency, party
FROM (
  SELECT constituency,party, votes, RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) AS rank
    FROM ge
  WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
    AND yr  = 2017
  ORDER BY rank, constituency
) temp1
WHERE rank = 1

9-.6 Scottish seats

问题好像带点俚语？难理解题意。看了数据才大概理解。

how many seats for each party in Scotland$\Rightarrow$前面题目的winner的意思

占据一席之位对应winner，原谅我理解能力低。

SELECT party, COUNT(*) AS winner
FROM (
  SELECT constituency,party, votes, RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) AS rank
    FROM ge
  WHERE constituency LIKE 'S%'
    AND yr  = 2017
  ORDER BY rank, constituency
) temp1
WHERE rank = 1
GROUP BY party