Blog

ブログ

ループ処理に苦しんでいる皆さんお元気ですか？
どうもご無沙汰しています。アフロナミヘイです。

SQLの神髄第５弾となる今回は、PARTITIONとROWS BETWEENを使ったレコード間比較のテクニックをご紹介します。
ラーメン二郎全店舗の売上を前年度と比較しろ、と上司に言われて困っている皆さん！
もしDBから全レコードを取ってきてぐるぐるループしようとしているなら、一旦思いとどまってください。
こちらの記事を読めばそのループ、なくせます。

上記のテーブルにはラーメン二郎各店舗における会計年ごとの売上額が年別に記録されています（一部抜粋）。
こういったデータから、前年と比べて売上がいくら上がったのか？下がったのか？を知りたいというケース、あると思います。
欲しいのは以下のような結果です。

このとき、以下のような方法を考えませんか？


SELECT * FROM Sales ORDER BY company, year;

で全件を取得してからの、

while(取得レコード分ループ){
　if( 現レコード店舗＝前レコード店舗 ){
　　前年比 = 前レコード売上 - 現レコード売上
　} else {
　　前年比 = NULL
　}
　前レコード店舗 = 現レコード店舗
　前レコード売上 = 現レコード売上
}

コードを最後まで書くのが嫌になってしまいましたが、要はSELECT文で全レコード取得 → ループ処理で前年度の売上と比較して差額を算出、という流れです。

考え方としてはわかりやすいですが、プログラムにおいてこういったループ処理は試験パターンが多くなりがちで大変なところです。
また使う変数が多くなることもバグが発生しやすくなる要因です。

これに対して、PARTITIONとROWS BETWEENを使った答えはこうなります。

SELECT company,
　　　　year,
　　　　sale,
　　　　sale - MAX(sale) OVER ( PARTITION BY company ORDER BY year
　　　　　　　　　　　　　　　　　ROWS BETWEEN 1 PRECEDING
　　　　　　　　　　　　　　　　　　　　　AND 1 PRECEDING) As 前年比
FROM Sales
ORDER BY company, year;

なんということでしょう。
煩雑だったバックエンドのループ処理がなくなり、SQLだけで結果を取得できています。
ポイントを以下に解説します。

PARTITION BY
レコードを指定カラムで文字通り区切ります。GROUP BYと違ってレコードの集約はしません。
こちらのSQLではcompanyを指定することで店舗ごとにレコードを区切り、同一店舗の売上を比較できるようにしています。

ROWS BETWEEN
これは検索結果中の現在レコードからさかのぼったレコードを参照するのに使えます。
さかのぼる範囲を”1 PRECEDING AND 1 PRECEDING“で指定しており、文字通り「1行前から1行前」の意味なので、直前の1行を参照することになります。
神田神保町店を例にとると、

1行前レコード：2017年, 300兆円
現在レコード：2018年, 250兆円

この2レコード間で比較するので、売上差額を-50兆円と算出できるのです。

いかがでしたでしょうか。
今回は単純に売上差額を出しましたが、SELECT句をアレンジすれば売上変化がプラスorマイナスかを記号で出力したり、増減比率をパーセンテージで出力したりといったことも自在です。
ぜひこちらを参考に無用なループをなくしていってください。

※MySQL8.0からウィンドウ関数が使えるようになったことを記念して、文中のSQLはMySQL8.0環境で動作を確認しています