Window functions/ja

From SQLZoo
Language: Project:Language policy English  • 日本語

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

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

年 yrfirstNamelastName選挙区 constituency政党 party票 votes
2015IanMurrayS14000024Labour19293
2015NeilHayS14000024Scottish National Party16656
2015MilesBriggsS14000024Conservative8626
2015PhylMeyerS14000024Green2090
2015PramodSubbaramanS14000024Liberal Democrat1823
2015PaulMarshallS14000024UK Independence Party601
2015ColinFoxS14000024Scottish Socialist Party197
2017IanMURRAYS14000024Labour26269
2017JimEADIES14000024SNP10755
2017Stephanie Jane HarleySMITHS14000024Conservative9428
2017Alan ChristopherBEALS14000024Liberal Democrats1388

ウォーミングアップ

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

SELECTlastName,party,votes
FROMge
WHEREconstituency='E14000539'ANDyr=2017
ORDERBYvotesDESC

SELECTlastName,party,votes
FROMge
WHEREconstituency='S14000024'ANDyr=2017
ORDERBYvotesDESC

勝者は誰?

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

2017年の選挙区constituency S14000024 の政党partyと票votesと順位を政党順に表示する
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECTparty,votes,
RANK()OVER(ORDERBYvotesDESC)asposn
FROMge
WHEREconstituency='E14000539'ANDyr=2017
ORDERBYvotes

SELECTparty,votes,
RANK()OVER(ORDERBYvotesDESC)asposn
FROMge
WHEREconstituency='S14000024'ANDyr=2017
ORDERBYparty

PARTITION BY

2015年と2017年の選挙は異なる区分PARTITIONです。各年の得票の順序にのみ関心があります。

選挙区S14000021の各政党partyごとの各年yrの順位をPARTITIONを使って表示する。yr, party, votesと順位(最多得票の政党は1)を含める
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')
SELECTyr,party,votes,
RANK()OVER(PARTITIONBYyrORDERBYvotesDESC)asposn
FROMge
WHEREconstituency='S14000021'
ORDERBYparty,yr

SELECTyr,party,votes,
RANK()OVER(PARTITIONBYyrORDERBYvotesDESC)asposn
FROMge
WHEREconstituency='S14000021'
ORDERBYparty,yr

エディンバラの選挙区

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

PARTITION BY constituency を使って2017年のエジンバラの選挙区の各政党の順位を表示する。結果は勝者達が選挙区順になるように表示する。
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECTconstituency,party,votes
FROMge
WHEREconstituencyBETWEEN'S14000021'AND'S14000026'
ANDyr=2017
ORDERBYconstituency,votesDESC

SELECTconstituency,party,votes,
RANK()OVER(PARTITIONBYconstituencyORDERBYvotesDESC)
ASposn
FROMge
WHEREconstituencyBETWEEN'S14000021'AND'S14000026'
ANDyr=2017
ORDERBYposn,constituency

当選者達だけ

SELECT within SELECTを使うとエディンバラの当選者達だけを表示できる。

2017年のエディンバラの選挙区ごとに当選した政党を表示する。
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECTconstituency,party,votes
FROMge
WHEREconstituencyBETWEEN'S14000021'AND'S14000026'
ANDyr=2017
ORDERBYconstituency,votesDESC

SELECTconstituency,party
FROM(
SELECTconstituency,party,
RANK()OVER(PARTITIONBYconstituencyORDERBYvotesDESC)
ASposn
FROMge
WHEREconstituencyBETWEEN'S14000021'AND'S14000026'
ANDyr=2017
)ASed
WHEREposn=1

スコットランドの議席

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

2017年のスコットランドの各政党の議席数を表示する。
set sql_mode = replace(@@sql_mode,'ONLY_FULL_GROUP_BY','');
SELECTconstituency,party,votes
FROMge
WHEREconstituencyBETWEEN'S14000021'AND'S14000026'
ANDyr=2017
ORDERBYconstituency,votesDESC

SELECTparty,COUNT(1)
FROM(
SELECTconstituency,party,
RANK()OVER(PARTITIONBYconstituencyORDERBYvotesDESC)
ASposn
FROMge
WHEREconstituencyLIKE'S%'
ANDyr=2017
)ASed
WHEREposn=1
GROUPBYparty
Kaleidoscope - get a kaleidoscope view using your phone/laptop camera
  • Served by: noddy at 2026年07月06日T02:10