​ ​
FullSizeRender_jpg.png

SQLパズルを解いてみよう

こんにちは、ファームノートクラウド開発グループの永沼です。札幌は先日記録的な大雪が降り、11月上旬だというのに雪がもりもりと積もっています。シーズンのはじめで、まだ凍った路面に慣れていないのでおっかなびっくり歩いています。

ときに少し古い本ではあるのですが、SQLパズル(ISBN-13: 978-4798114132)という本をご存知でしょうか?これは米国データベース界隈での重鎮のひとり、Joe Celkoによるパズルブックで、75のパズルとそれに対するそれぞれ複数の回答例が紹介されています。SQLの考え方の練習、実践テクニックやその練習、腕試しにちょうどよいのではないかと思います。

今回はこの本の問題の中からひとつをご紹介し、別解を考えてみたいと思います。

問題

今回題材にするのは「パズル10 年金おくれよ」と題して紹介されているものです。問題の一部を以下に抜粋します。

CREATE TABLE Pensions
( sin CHAR(10) NOT NULL,
 pen_year INTEGER NOT NULL,
 month_cnt INTEGER DEFAULT 0 NOT NULL CHECK(month_cnt BETWEEN 0 AND 12),
 earnings DECIMAL(8,2) DEFAULT 0.00 NOT NULL);

 SIN はカナダで納税者を識別するために使われている社会保険番号(Social Insurance Number)で・・・pen_year 列は年金保険料の納付年を、month_cnt 列はその年に社員が働いた月数を、earnings 列はその年の総収入を保持している。

 問題は、各社員の直近 60 ヶ月の総収入を求めるというものだ。この数値は連続していなければならず、社員1人1人の年金を算出するために使われる。さかのぼる年は、最短で5年(各年の month_cnt が全部 12 だった場合)、最長で 60 年(各年の month_cnt が全部1だった場合)だ。ただし、丸4年働いた後、5年目は働かなかった(つまり全く働かなかった年がある)社員は年金の受給資格を一切得られない。

順序をSQLで扱う

さて、この問題ではSQLで順序を扱う必要があります。SQL で順序を扱うひとつの方法として、Window 関数が挙げられます。幸いこの本の回答例には Window 関数を利用したものはありませんでしたので、今回はこの方向で解くことにしました。

回答を考えるにあたっては書籍での回答例と同様、以下の制限を前提としました。また、回答については PostgreSQL での実行を念頭に考えています。

  • 社員が全く働かなかった年についてもテーブルに行が挿入されていること
  • 求めるのは最新の年を起点とする
  • 60ヶ月ちょっきりの総収入ではなく、働いた期間が60ヶ月以上となる年までの総収入を求める

とりあえず、「全く働かなかった年がある場合に年金の受給資格がない」という条件はいったん無視して、現在の年から数えて month_cnt の合計が 60 を超える年を求める方法を考えてみました。以下は考え方を図解したメモです(汚くてスミマセン。。。)

FullSizeRender.jpg

SUM() は集計関数なのですが、PostgreSQL などでは OVER 句を付与することで Window 関数として利用することが可能です。Window 関数として利用した場合、ウィンドウフレームの範囲を集約することができます。

ウィンドウフレームとは

多くの Window 関数ではパーティション内のウィンドウフレームの行のみに作用します。詳細はこちらをご確認ください。OVER 内で ORDER BY を指定した場合、デフォルトではウィンドウフレームはパーティションの最初の行から現在の行まで(RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)になります。

これによって、最新の年を起点に指定された年までの範囲の合計を求めることができます。

例えば適当なサンプルデータを入れてみると、以下のようにデータが集計されるのを確認することができます。

  • month_cnt:働いた月数
  • month_cnt_sum:2016年から pen_year までの働いた月数の合計
 SELECT sin,
pen_year,
month_cnt,
SUM(month_cnt) OVER by_sin AS month_cnt_sum
FROM Pensions
WINDOW by_sin AS (PARTITION BY sin ORDER BY pen_year DESC)
ORDER BY sin,
pen_year DESC;

sin | pen_year | month_cnt | month_cnt_sum
------------+----------+-----------+---------------
A123456789 | 2016 | 3 | 3
A123456789 | 2015 | 11 | 14
A123456789 | 2014 | 5 | 19
A123456789 | 2013 | 7 | 26
A123456789 | 2012 | 0 | 26
A123456789 | 2011 | 10 | 36
...

あとはここから合計が 60 ヶ月以上になる最初の年を抜き出せば「全く働かなかった年がある場合に年金の受給資格がない」という条件を除いては回答を満たすことができそうです。

「全く働かなかった年」を判定する

さて、あとは「全く働かなかった年」を判定してやれば必要な条件を満たすことができそうです。こちらも考え方は先程の期間の集計と同様なのですが、こちらでは条件判定のために集計関数 BOOL_OR() を利用します。より複雑な条件の場合には CASE 式を使う必要がありますが、今回は特に利用しなくてもよさそうです。

同じ Window 定義を利用して、全く働かなかった年を TRUE、それ以外を FALSE として OR 条件で集約します。

  • month_cnt:働いた月数
  • non_working_year_included:2016年から pen_year までの間に全く働かなかった年があるかどうか
  SELECT sin,
pen_year,
month_cnt,
BOOL_OR(month_cnt = 0) OVER by_sin AS non_working_year_included
FROM Pensions
WINDOW by_sin AS (PARTITION BY sin ORDER BY pen_year DESC)
ORDER BY sin,
pen_year DESC;

sin | pen_year | month_cnt | non_working_year_included
------------+----------+-----------+---------------------------
A123456789 | 2016 | 3 | f
A123456789 | 2015 | 11 | f
A123456789 | 2014 | 5 | f
A123456789 | 2013 | 7 | f
A123456789 | 2012 | 0 | t
A123456789 | 2011 | 10 | t
...

ここでは2012年に全く働いていない年(pen_year が 0)があるため、2012年、2011年が TRUE と判定されています。

組み立ててみる

あとは earnings 列への集計を追加し、働いた期間の合計が 60 ヶ月以上となる最初の年、かつ、全く働いていない年を含まないという条件でさらに検索をかけるように変更してみましょう。以下の SQL のサブクエリの中がここまで考えてきた部分になっています。

  SELECT sin,
MAX(start_year),
MAX(end_year),
MIN(month_cnt_sum),
MIN(earnings_sum)
FROM (
SELECT sin,
pen_year AS start_year,
FIRST_VALUE(pen_year) OVER by_sin AS end_year,
SUM(month_cnt) OVER by_sin AS month_cnt_sum,
SUM(earnings) OVER by_sin AS earnings_sum,
BOOL_OR(month_cnt = 0) OVER by_sin AS non_working_year_included
FROM Pensions
WINDOW by_sin AS (partition by sin order by pen_year desc)
) AS intermediate
WHERE month_cnt_sum >= 60
AND NOT non_working_year_included
GROUP BY sin;

sin | max | max | min | min
------------+------+------+-----+-----------
B234567890 | 2006 | 2016 | 66 | 437811.46
C345678901 | 2008 | 2016 | 64 | 432319.94
(2 rows)

うまく抽出することができたようです。ちょうど 2012 年に働いていない SIN 'A123456789' も除外できていることが確認できます。

ちなみに今回利用したデータは以下のクエリで作成したものを利用しました。

insert into Pensions select sin, pen_year, cast(random() * 12 as integer), cast(random() * 100000 as decimal(8, 2)) from (values('A123456789'), ('B234567890'), ('C345678901')) as sins(sin) cross join (select generate_series(2016, 1960, -1)) as pen_years(pen_year);
update Pensions set earnings = 0 where month_cnt = 0;

おわりに

今回題材にしたパズルでは順序が重要なキーだったので、Window 関数を利用した別解を考えてみました。書籍で紹介されている他のパズルや解についても唸らされるものが少なくありません。是非SQLパズルを手にとって確かめてみてください。

弊社ではお客様により素早く、より多く価値を提供するための技術のひとつとして、SQL やパフォーマンス・チューニングに精通したエンジニアを求めています!弊社HPWantedlyからのご応募お待ちしています。

このエントリーをはてなブックマークに追加