SQLZOO Window functions

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

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

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

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

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

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

SELECT x.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
 )AS x
WHERE x.posn=1
GROUP BY x.party