Window functions/ja

Language:Project:Language policy	English  • 日本語

イギリスでは2015年と2017年に総選挙が実施されました。すべての市民は選挙区で投票します。 最も多くの票を獲得した候補者がその選挙区の議員になります。

全ての結果がgeテーブルに記録されている。

年 yr	firstName	lastName	選挙区 constituency	政党 party	票 votes
2015	Ian	Murray	S14000024	Labour	19293
2015	Neil	Hay	S14000024	Scottish National Party	16656
2015	Miles	Briggs	S14000024	Conservative	8626
2015	Phyl	Meyer	S14000024	Green	2090
2015	Pramod	Subbaraman	S14000024	Liberal Democrat	1823
2015	Paul	Marshall	S14000024	UK Independence Party	601
2015	Colin	Fox	S14000024	Scottish Socialist Party	197
2017	Ian	MURRAY	S14000024	Labour	26269
2017	Jim	EADIE	S14000024	SNP	10755
2017	Stephanie Jane Harley	SMITH	S14000024	Conservative	9428
2017	Alan Christopher	BEAL	S14000024	Liberal Democrats	1388

ウォーミングアップ

2017年の選挙区constituency 'S14000024'について 、ラストネームlastNameと政党party と票votesを表示する

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

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

勝者は誰?

RANK関数を使って候補者の順位を確認できる。 RANKを(ORDER BY votes DESC)について使えば最多得票の候補者がランク1になる。

2017年の選挙区constituency S14000024 の政党partyと票votesと順位を政党順に表示する

set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');

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

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の各政党partyごとの各年yrの順位をPARTITIONを使って表示する。yr, party, votesと順位(最多得票の政党は1)を含める

set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')

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 yr,party, votes,
      RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency = 'S14000021'
ORDER BY party,yr

エディンバラの選挙区

エディンバラの選挙区はS14000021 から S14000026の番号です。

PARTITION BY constituency を使って2017年のエジンバラの選挙区の各政党の順位を表示する。結果は勝者達が選挙区順になるように表示する。

set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');

SELECT constituency,party, votes
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY constituency,votes DESC

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 within SELECTを使うとエディンバラの当選者達だけを表示できる。

2017年のエディンバラの選挙区ごとに当選した政党を表示する。

set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');

SELECT constituency,party, votes
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY constituency,votes DESC

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
   ) AS ed
WHERE posn = 1

スコットランドの議席

COUNT と GROUP BY を使って各政党がスコットランドでどうだったかを見る。スコットランドの選挙区は 'S' で始まる。

2017年のスコットランドの各政党の議席数を表示する。

set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');

SELECT constituency,party, votes
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017
ORDER BY constituency,votes DESC

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