tekiehei2317's blog

TOPSIC SQL CONTEST第2回の解説

投稿日: 2022-12-14

SQLコンテストの第2回の問題を解いたので、解説を書いてみます。全部で4問あり、制限時間は60分です。

TOPSIC - 第2回 SQLコンテスト

小ネタですが、ウィンドウを2つ開いて並べておくと文章を読む用とコードを書く用に分けられるのでやりやすいです。

問題1. 地区名の更新

UPDATE文は、WHERE句で絞り込んだレコードを更新する文です。district_nameがnullまたは空文字列のレコードを更新するという条件があるので、それを書きます。

久しぶりにUPDATE文を書きました。

update population set district_name = '不明' where district_name is null or district_name = '' ;

提出: https://topsic-contest.jp/contests/contest002/submissions/11851

問題2. 曖昧検索

SQLで前方一致・後方一致・〜を含むなどの条件を表現するには、LIKE演算子を使います。LIKE演算子では、任意の文字列を%で表現するので、3つの条件は以下のようにかけます。

条件書き方
district_nameが東から始まるdistrict_name like ‘東%’
district_nameが東で終わるdistrict_name like ‘%東’
district_nameが東を含むdistrict_name like ‘%東%’

今回は東を含むという条件なので、3番目を使います。

また、複数のカラムでソートするためには、ORDER BY句にカンマ区切りでカラムを書きます。

select district_code as CODE, district_name as NAME, total_amt as TOTAL from population where total_amt >= 100000 and district_name like '%東%' order by total_amt desc, district_code ;

提出: https://topsic-contest.jp/contests/contest002/submissions/11853

問題3. 飲酒率

少し問題文がわかりづらかったので、具体的な例を見てみます。都道府県コード05000(秋田県)のデータのみ表示してみました。

CATEGORY_CODEGENDER_CODEPF_CODEAMT
110205000375
110305000427
120205000133
12030500031

CATEGOY_CODEの110が総数、120が毎日飲酒する人の数なので、(総数・男性, 総数・女性, 飲酒・男性, 飲酒・女性) = (375, 427, 133, 31)だということが分かります。なので答えは(133 + 31) / (375 + 427) * 100 = 20.4だと分かります。

これは都道府県で集約してCASE式で計算するとできます。PF_NAMEがないため後からJOINしています。

コード
select prefecture.pf_code as CODE, prefecture.pf_name as NAME, round(100.0 * population.drink_amount / population.total_amount, 1) as PERCENTAGE from (select pf_code, -- ある県の毎日飲酒する人数 sum(case when category_code = '120' then amt else 0 end) as drink_amount, -- ある県の合計人数 sum(case when category_code = '110' then amt else 0 end) as total_amount from drink_habits where gender_code in ('2', '3') group by pf_code ) population inner join prefecture using (pf_code) order by percentage desc, prefecture.pf_code desc ;

提出: https://topsic-contest.jp/contests/contest002/submissions/11857

また、以下のようにも書けます。標準SQLでは、SELECT句には、GROUP BY句に指定した集約キーと集約関数しか書けないというルールがあった気がするのですが、SQLiteではそうでないクエリも動くようです(prefecture.pf_nameの部分です)。

コード
select prefecture.pf_code as CODE, prefecture.pf_name as NAME, round( 100.0 * sum(case when category_code = '120' then amt else 0 end) / sum(case when category_code = '110' then amt else 0 end), 1 ) as PERCENTAGE from drink_habits inner join prefecture using (pf_code) where gender_code in ('2', '3') group by pf_code order by percentage desc, prefecture.pf_code desc ;

提出: https://topsic-contest.jp/contests/contest002/submissions/11856

問題4. 就学状況の表示変換

まず、分かりやすいように以下のクエリで都道府県ごとに集計しておきます。

with population as ( select pf_code, sum(elementary) as sum1, sum(middle) as sum2, sum(high) as sum3, sum(junior_clg) as sum4, sum(college) as sum5, sum(graduate) as sum6 from enrollment_status where survey_year = 2020 group by pf_code ) select * from population ;

WITH句は共通テーブル式(CTE)と呼ばれる機能で、クエリの結果を一時的なテーブルに保存する機能です。WITH句はSELECT文の一部なので、WITH句で定義したテーブルはそのSELECT文の中でのみ使えます。

あとは、これを縦持ちに変換すれば良いです。横持ちから縦持ちに変換するというのは、この場合は行を増やすということです。SQLで行を増やすには、JOINを使うか、UNIONを使って結果を結合するかのどちらかです。

pf_codesum1sum2sum3sum4sum5sum6
3600032425null17851331511405null
37000480842490626191534411958618

それぞれの方法を考えてみます。

JOINを使う場合

以下のようなテーブルをCROSS JOINすると、目的の行を作れます。CROSS JOINは全ての組み合わせを作るので、N行のテーブルとM行のテーブルをCROSS JOINすると、N×M行になります。

kind_id
1
2
3
4
5
6

あとは必要な情報を頑張って整形して取り出します。

コード
with population as ( select pf_code, sum(elementary) as sum1, sum(middle) as sum2, sum(high) as sum3, sum(junior_clg) as sum4, sum(college) as sum5, sum(graduate) as sum6 from enrollment_status where survey_year = 2020 group by pf_code ), kind as ( select 1 as kind_id, '小学校' as kind_name union all select 2, '中学校' union all select 3, '高校' union all select 4, '短大' union all select 5, '大学' union all select 6, '大学院' ) select 2020 as SV_YEAR, PR.pf_name as PREFECTURE, K.kind_name as KIND, (case K.kind_id when 1 then sum1 when 2 then sum2 when 3 then sum3 when 4 then sum4 when 5 then sum5 when 6 then sum6 else 0 end) as AMT from population P cross join kind K inner join prefecture PR on P.pf_code = PR.pf_code where AMT is not null order by P.pf_code, K.kind_id ;

提出: https://topsic-contest.jp/contests/contest002/submissions/11882

UNIONを使う場合

縦持ちにしたいカラムたちを1つずつ選んで、最後にUNIONでマージします。縦持ちにしたいカラムはsum1, …, sum6なので、それらを1つずつ選ぶクエリを作ります。

縦持ちにしたテーブルをソートする必要があるので、最後にFROM句にサブクエリとして渡してソートします。

コード
with population as ( select survey_year, pf_code, sum(elementary) as sum1, sum(middle) as sum2, sum(high) as sum3, sum(junior_clg) as sum4, sum(college) as sum5, sum(graduate) as sum6 from enrollment_status where survey_year = 2020 group by pf_code ) select survey_year as SV_YEAR, pf_name as PREFECTURE, kind_name as KIND, sum as AMT from ( select P.survey_year, PR.*, 1 as kind_id, '小学校' as kind_name, sum1 as sum from population P join prefecture PR using (pf_code) union all select P.survey_year, PR.*, 2, '中学校', sum2 from population P join prefecture PR using (pf_code) union all select P.survey_year, PR.*, 3, '高校', sum3 from population P join prefecture PR using (pf_code) union all select P.survey_year, PR.*, 4, '短大', sum4 from population P join prefecture PR using (pf_code) union all select P.survey_year, PR.*, 5, '大学', sum5 from population P join prefecture PR using (pf_code) union all select P.survey_year, PR.*, 6, '大学院', sum6 from population P join prefecture PR using (pf_code) ) where sum is not null order by pf_code, kind_id ;

提出: https://topsic-contest.jp/contests/contest002/submissions/11883

;