SQL CONTESTの1回目の問題を解きました。
4問目はWindow関数を使う必要があったので、そのあたりのまとめとして作成しています。
Window関数とは
Mysqlでは 8.0から使えるようになった非集計型関数です。
集計型関数しかなかったころは、GROUP BYでグルーピングしたデータと、それらグループ全体の総計は別々に集約する必要がありました。
-- 1はprofitが全行でまとめられ、2はcountry単位でまとめられる
-- 集約関数を使用する場合、1、２は集約範囲が異なるため別々に集計する必要がある
mysql> SELECT SUM(profit) AS total_profit FROM sales; -- 1.全国のprofit合計
+--------------+
| total_profit |
+--------------+
| 7535 |
+--------------+
mysql> SELECT country, SUM(profit) AS country_profit
FROM sales
GROUP BY country
ORDER BY country; -- 2.各国のprofit合計
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland | 1610 |
| India | 1350 |
| USA | 4575 |
+---------+----------------+
ですが、Window関数を使うと上記の1,2を両立するクエリを書くことができます。
mysql> SELECT
year, country, product, profit,
SUM(profit) OVER() AS total_profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;
-- total_profit が1.全国のprofit
-- country_profit が2.国ごとのprofit
+------+---------+------------+--------+--------------+----------------+
| year | country | product | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer | 1500 | 7535 | 1610 |
| 2000 | Finland | Phone | 100 | 7535 | 1610 |
| 2001 | Finland | Phone | 10 | 7535 | 1610 |
| 2000 | India | Calculator | 75 | 7535 | 1350 |
| 2000 | India | Calculator | 75 | 7535 | 1350 |
| 2000 | India | Computer | 1200 | 7535 | 1350 |
| 2000 | USA | Calculator | 75 | 7535 | 4575 |
| 2000 | USA | Computer | 1500 | 7535 | 4575 |
| 2001 | USA | Calculator | 50 | 7535 | 4575 |
| 2001 | USA | Computer | 1200 | 7535 | 4575 |
| 2001 | USA | Computer | 1500 | 7535 | 4575 |
| 2001 | USA | TV | 100 | 7535 | 4575 |
| 2001 | USA | TV | 150 | 7535 | 4575 |
+------+---------+------------+--------+--------------+----------------+
上記のクエリでは、
- 結果件数はDBに保存されているデータ数とイコールである(グルーピングしてない)
- total_profitで結果件数全体を集計した値をもつ
- country_profitでcountryごとにを集計した値をもつ
という結果になっています。
個人的には「集約(寄せ集めて1つにまとめる)せずに、集計(合計の値の算出)だけ行うことができる」
という表現がしっくりきます。
OVERとPARTITION BY
Window関数は必ずOver句を使います。
また、OVER句の設定するにはPARTITION BYを使います。
mysql> SELECT
year, country, product, profit,
SUM(profit) OVER() AS total_profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit
↑先ほどのクエリのSELECT句の抜粋です。
SUM(profit) OVER() => 結果全ての行を対象にprofitの合計値を集計
SUM(profit) OVER(PARTITION BY country) AS country_profit => 結果countryカラム単位で区切ってprofitの合計値を集計
今までGROUP BYに書いていたグルーピングをOVER()内に書くようなイメージが分かりやすいと思います。
応用的な使い方
Window関数は集計した値をもとにソートしたり、PARTITION BYに複数のカラム名を書き、細かくパーティショニングすることができます。
以下は複数カラムでパーティショニングする例です。ORDER BYを使ったことがあればすぐ慣れるはず。
mysql> SELECT
year, country, product, profit,
SUM(profit) OVER() AS total_profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit,
SUM(profit) OVER(PARTITION BY country, product) AS c_product_profit -- 各国の、製品毎のprofit合計値
-- 以下略
+------+---------+------------+--------+--------------+----------------+----------------+
| year | country | product | profit | total_profit | country_profit |c_product_profit|
+------+---------+------------+--------+--------------+----------------+----------------+
| 2000 | Finland | Computer | 1500 | 7535 | 1610 | 1500 |
| 2000 | Finland | Phone | 100 | 7535 | 1610 | 110 |
| 2001 | Finland | Phone | 10 | 7535 | 1610 | 110 |
| 2000 | India | Calculator | 75 | 7535 | 1350 | 150 |
| 2000 | India | Calculator | 75 | 7535 | 1350 | 150 |
| 2000 | India | Computer | 1200 | 7535 | 1350 | 1200 |
| 2000 | USA | Calculator | 75 | 7535 | 4575 | 125 |
| 2000 | USA | Computer | 1500 | 7535 | 4575 | 4200 |
| 2001 | USA | Calculator | 50 | 7535 | 4575 | 125 |
| 2001 | USA | Computer | 1200 | 7535 | 4575 | 4200 |
| 2001 | USA | Computer | 1500 | 7535 | 4575 | 4200 |
| 2001 | USA | TV | 100 | 7535 | 4575 | 250 |
| 2001 | USA | TV | 150 | 7535 | 4575 | 250 |
+------+---------+------------+--------+--------------+----------------+----------------+
第1回SQL CONTEST 4問目を解いてみる
2022年7月より開催されているSQL CONTESTの第1回目に、外国籍分布を集計する問題が出題されました。
まさにWindow関数を活用できる問題なので、解いてみましょう。
＊これから書く解答はあくまで回答例の1つである点を念頭において見てください。
Step1 都道府県毎の外国人の合計人数を取得
表示項目として提示されているもののうち、[都道府県毎の外国人の合計人数]はPARTITION BYで区切って集計することで取得できます。
国籍コード= '113'（その他）のデータは除いて集計することという但書に留意してクエリを書きます。
SELECT
f.PF_CODE as 都道府県コード,
p.PF_NAME as 都道府県名,
n.NATION_NAME as 国名,
SUM(AMT) OVER(PARTITION BY f.PF_CODE) as 合計人数
FROM FOREIGNER f
INNER JOIN PREFECTURE p
ON (p.PF_CODE = f.PF_CODE)
INNER JOIN NATIONALITY n
ON (n.NATION_CODE = f.NATION_CODE)
WHERE
f.NATION_CODE != '113'
;
+------------+----------+----------+----------+
| 都道府県コード | 都道府県名 | 国名 | 合計人数 |
+------------+----------+----------+----------+
| 11000 | 埼玉県 | 韓国，朝鮮 | 114219 |
| 11000 | 埼玉県 | 中国 | 114219 |
| 11000 | 埼玉県 | フィリピン | 114219 |
| (略) |
| 11000 | 東京都 | 韓国，朝鮮 | 392726 |
| 11000 | 東京都 | 中国 | 392726 |
| 11000 | 東京都 | フィリピン | 392726 |
| (略) |
| 11000 | 神奈川県 | 韓国，朝鮮 | 157765 |
| 11000 | 神奈川県 | 中国 | 157765 |
| 11000 | 神奈川県 | フィリピン | 157765 |
+------------+----------+----------+----------+
これで都道府県単位の部分集合は集計できました。
Step2 都道府県内で国籍毎に人口を集計＆ランク付け
各都道府県に在住する外国人の国籍も表示できていますが、現状では人数の多い順の並びになっていません。
よって、次は
都道府県単位 (PARTITION BY) で、
在住する外国籍の人数が多い順 (ORDER BY) に並びかえて、(第2基準として都道府県コードのASC)
順位をわりふり (Window関数) ます。
また、最終的には国籍毎の人口も要求されるため、併せて取得します。
取得する行に番号をふるにはROW_NUMBER()という関数を使います。
SELECT
f.PF_CODE as 都道府県コード,
p.PF_NAME as 都道府県名,
ROW_NUMBER() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT DESC, f.NATION_CODE) as 同都道府県内国籍別順位,
n.NATION_NAME as 国名,
f.AMT,
SUM(AMT) OVER(PARTITION BY f.PF_CODE) as 合計人数
FROM FOREIGNER f
INNER JOIN PREFECTURE p
ON (p.PF_CODE = f.PF_CODE)
INNER JOIN NATIONALITY n
ON (n.NATION_CODE = f.NATION_CODE)
WHERE
f.NATION_CODE != '113'
;
+------------+----------+--------------------+----------+----------+----------+
| 都道府県コード | 都道府県名 | 同都道府県内国籍別順位 | 国名 | AMT | 合計人数 |
+------------+----------+--------------------+----------+----------+----------+
| 11000 | 埼玉県 | 1 | 中国 | 53966 | 114219 |
| 11000 | 埼玉県 | 2 | ベトナム | 15951 | 114219 |
| 11000 | 埼玉県 | 3 | フィリピン | 14186 | 114219 |
| (略) |
| 11000 | 東京都 | 1 | 中国 | 195219 | 392726 |
| 11000 | 東京都 | 2 | 韓国，朝鮮 | 79079 | 392726 |
| 11000 | 東京都 | 3 | フィリピン | 27156 | 392726 |
| (略) |
| 11000 | 神奈川県 | 1 | 中国 | 62932 | 157765 |
| 11000 | 神奈川県 | ２ | 韓国，朝鮮 | 23991 | 157765 |
| 11000 | 神奈川県 | 3 | フィリピン | 17920 | 157765 |
+------------+----------+---------------------+----------+----------+---------+
これで一通り必要な情報を取得できましたね！
STEP3 ランク1,2,3を取得する
最終的に必要なデータは都道府県ごとの人口が多い1,2,3番目のデータです。
最終的に必要となるデータについて考えてみます。
- データ1行は都道府県単位
- ランク1~3のデータは1レコードにまとまっている
- 並び順は、対象の都道府県に在住する外国籍の合計数の降順 + 都道府県コードの昇順
STEP2で作ったデータは、データ1行が都道府県x外国籍の数だけありました。
これを都道府県単位に集約(GROUP BY)してあげることで単位が合いそうです。
ランクをデータ1行にまとめるには、ランク1~3の項目をSELECT文で書く必要があります。
都道府県単位にGROUP BY する際に、ランキングNo(同都道府県内国籍別順位)が1~3のデータについて国名&人口を取得したいです。カラムの値に応じて、値を取得するにはCASE文を使います。
これらを考慮してできる最終的なSQLはこちらです。SQL CONTEST上での結果
select
都道府県コード,
都道府県名,
MAX(CASE WHEN 同都道府県内国籍別順位 = 1 THEN 国名 ELSE NULL END) as "1位 国名",
SUM(CASE WHEN 同都道府県内国籍別順位 = 1 THEN AMT ELSE 0 END) as "1位 人数",
MAX(CASE WHEN 同都道府県内国籍別順位 = 2 THEN 国名 ELSE NULL END) as "2位 国名",
SUM(CASE WHEN 同都道府県内国籍別順位 = 2 THEN AMT ELSE 0 END) as "2位 人数",
MAX(CASE WHEN 同都道府県内国籍別順位 = 3 THEN 国名 ELSE NULL END) as "3位 国名",
SUM(CASE WHEN 同都道府県内国籍別順位 = 3 THEN AMT ELSE 0 END) as "3位 人数",
合計人数
FROM
(
SELECT
f.PF_CODE as 都道府県コード,
p.PF_NAME as 都道府県名,
ROW_NUMBER() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT DESC, f.NATION_CODE) as 同都道府県内国籍別順位,
n.NATION_NAME as 国名,
f.AMT,
SUM(AMT) OVER(PARTITION BY f.PF_CODE) as 合計人数
FROM FOREIGNER f
INNER JOIN PREFECTURE p
ON (p.PF_CODE = f.PF_CODE)
INNER JOIN NATIONALITY n
ON (n.NATION_CODE = f.NATION_CODE)
WHERE
f.NATION_CODE != '113'
) as rank
GROUP BY 都道府県コード
ORDER BY 合計人数 DESC, 同都道府県内国籍別順位
;
Window関数に馴染みのなかった方も、実際に触ることでGROUP BY との違いがわかるのではないかと思います。
GROUP BYしか使えなかった頃に一覧と集計で別々にクエリを打っていたのも、Window関数が使えば1本にまとめられるので、どんどん使っていきましょう！
参照元
SQL CONTEST
Mysql公式リファレンス Window 関数の概念と構文
Mysql公式リファレンス Window 関数の説明