SQLZOOのWindow functionsを解いてみた

さいきんSQLを勉強していまして、Gunosyの「非エンジニアが最速でSQLをマスターする方法」に書いてある『10年戦えるデータ分析入門』→SQLZOO (https://sqlzoo.net/wiki/SQL_Tutorial) という順番で学習を進めています。

　SQLZOOは出力が合っているのか違っているのかは教えてくれるのですが、どんなSQL文を書けば正解できるのか教えてくれません。すでに日本語でSQLZOOの解答を公開してくださっている方もおられますが、私も自分の勉強のアウトプットとして、解答例を公開したいと思います。

　SQLZOOは問題文が英語なので、あまり自信はありませんが問題文の和訳も用意しました。意訳なので正確さに欠けるかもしれませんが……

• SQLZOOのNumeric Examplesの解答例はこちら　→ https://rnsr0371.boy.jp/2020/12/22/sqlzoonumeric_examples/

Warming up

2017年の、選挙区がS14000024の候補者のラストネーム、政党、投票数を表示してください。

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

Who won?

候補者の順番を見るためにRANK関数が使えます。もし、RANKに続けて(ORDER BY votes DESC)を使うと、最も得票数の多かった候補者がrank 1になります。2017年の選挙区がS14000024の候補者の政党とランクを表示してください。政党ごとに列挙してください。

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

PARTITION BY

2015年の選挙は2017年の選挙と異なるPARTITIONです。各年の投票数の順番だけに興味があります。選挙区がS14000021の各政党のランキングを、PARTITIONを使って表示してください。選挙年、政党名、投票数、ランキング（最も得票数の多かった政党が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;

Edinburgh Constituency

Edinburghの選挙区はS14000021 から S14000026です。PARTITION BY を選挙区に使って、2017年のEdinburghの各政党のランキングを表示してください。当選者が初めに来るようにソートし、その後選挙区でソートしてください。

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;

Winners Only

サブクエリを用いて、Edinburghの当選者だけを選び出すことができます。2017年のEdinburgh各選挙区で勝った政党を表示してください。

SELECT
    constituency
    , party
FROM(SELECT
        constituency
        , party
        , 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) AS ge_with_rank
WHERE posn = 1;

Scottish seats

COUNTとGROUP BYを使うと、スコットランドの各政党の得票数を見ることができます。スコットランドの選挙区はSから始まります。2017年のスコットランドの各政党の議席を表示してください。

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