【SQL練習】【SQLZOO】 9- Window function

9- Window function

問題はこちらです。
Window functions - SQLZOO


以下が解答となります。

1.Warming up

【問題】
Show the lastName, party and votes for the constituency 'S14000024' in 2017.

2017年の構成員「S14000024」の「lastName」、「party」、「votes」を表示してください。

【解答】

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

2.Who won?

【問題】
You can use the RANK function to see the order of the candidates. If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.

RANK関数を使用して、候補者の順序を確認できます。（ORDER BY votes DESC）を使用してランク付けすると、投票数が最も多い候補者のランクが1になります。

Show the party and RANK for constituency S14000024 in 2017. List the output by party

2017年の構成員「S14000024」の「party」と「RANK」を表示してください。「party」ごとの出力を一覧表示してください。

【解答】

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

3.PARTITION BY

【問題】
The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.

2015年の選挙は、2017年の選挙とは異なるパーティションです。私たちは毎年の投票の順序だけを気にしてください。

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」を使用して、各年の「S14000021」の「the ranking of each party（各政党のランキング）を表示してください。「yr」、「party 」、「votes」、「ranking」を含めます（投票数が最も多い「party 」は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;

4.Edinburgh Constituency

【問題】
Edinburgh constituencies are numbered S14000021 to S14000026.

「Edinburgh constituencies（エディンバラの構成員）」には、「S14000021」から「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.

「PARTITION BY Constituency」を使用して、「the ranking of each party in Edinburgh in 2017（2017年のエディンバラの各政党のランキング）」を表示してください。結果を並べ替えて、「the winners are shown first（勝者が最初に表示され）」、次に「constituency（選挙区）」ごとに並べ替えしてください。

【解答】

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.Winners Only

【問題】
You can use SELECT within SELECT to pick out only the winners in Edinburgh.

SELECT内でSELECTを使用して、エディンバラの勝者のみを選択できます。

Show the parties that won for each Edinburgh constituency in 2017.

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) x
 WHERE x.posn = 1;

6.Scottish seats

【問題】
You can use COUNT and GROUP BY to see how each party did in Scotland. Scottish constituencies start with 'S'

「COUNT」と「GROUP BY」を使用して、各政党がスコットランドでどのように行動したかを確認できます。スコットランドの構成員は「S」で始まります。

Show how many seats for each party in Scotland in 2017.

2017年のスコットランドの各政党の議席数を表示してください。

【解答】

SELECT party,COUNT(1) 
  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) x
 WHERE x.posn=1
 GROUP BY x.party;