BLOG

2020-12-23

【SQLの神髄】第5回 PARTITIONとROWS BETWEENを使ったレコード間比較

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

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

 

【二郎売上テーブル】

店舗
company


year
売上(単位:兆円)
sale
神保町 2017 300
神保町 2018 250
神保町 2019 400
関内 2017 500
関内 2018 550
関内 2019 520
三田 2017 450
三田 2018 460
三田 2019 455

 

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

 

【年別売上の変化】

店舗

売上(単位:兆円) 前年比(単位:兆円)
神保町 2017 300 NULL
神保町 2018 250 -50
神保町 2019 400 150
関内 2017 500 NULL
関内 2018 550 50
関内 2019 520 -30
三田 2017 450 NULL
三田 2018 460 10
三田 2019 455 -5

 

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


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環境で動作を確認しています

The following two tabs change content below.

アフロ・ナミヘイ

PostgreSQL推進委員長。アンチOracle派筆頭。 標準SQLに反する書式は許しません。 (+)とか使ってると激おこ。 アフロは天然。