[ SQL ] [ Window関数 ] 前後レコードとの演算結果をselectする

[ SQL ] [ Window関数 ] 前後レコードとの演算結果をselectする

本記事では、SQLで前後レコードとの演算を行う処理の実装を行って見ます。

 

SQLで前後比較処理を実装する利点

DBに存在するレコードそれぞれの相関を利用して処理を行おうとしたとき、一般的にはプログラムからSQLを使用してデータ群を取得し、プログラム内でforループを実装するなどして対応することと思います。

それは “SQLではレコード同士の複雑な比較演算はできない” という認識からの発想だと思いますが、実はSQLでも、このような処理を行うことが可能です。

今回はそんな、SQLによる前後レコードの比較処理の実装を行います。

仮想要件

要件

店舗(shop)毎の年度(year)別の売上(sale)を取得し、同店舗の前年度売上と比較した結果を算出する。
比較対象となる前年度データが存在しない場合は、空白を出力する。

対象データイメージ

取得結果イメージ

Pythonで実装

python に限った話ではありませんが、今回のように対象レコードと対象の直前レコードを比較しようとした時、通常はデータをプログラム側に抽出してから、プログラム内でforを回して比較演算を行うのが一般的かと思います。

ここで踏む手順は次の通りです。

  1. shop値一覧を取得する
  2. shop毎のデータ群を取得する
  3. shop内データについてforループを実行し、データの前後比較を行う
  4. 各データについて、元の値と比較結果を出力する

以下、サンプルです。

SQLで実装

pythonでは、各レコードでの比較をpython側のforループで処理しました、
なぜならば、SQLではfor的な比較演算を行うことはできないと思われたからです。

ですが、SQLでもレコード単位での比較処理を行うことができます。
それは、Window関数の使用によって実現することができます。

SQLにおける該当処理において踏む手順は、以下の通りです。

  1. shopでグルーピングする (partition by shop)
  2. グループ内データをyearでソートする (order by year)
  3. 対象データの直前1レコードを取得することを明示する ( rows between 1 preceding and 1 preceding)
  4. 対象データのsale値 ( 4行目先頭のsale ) と、1行前のsale ( max(sale) ) を演算する
  5. 結果をselectする

以下、実行例です。

 

このように、SQLだけでも比較的複雑なレコード同士の演算を行うことができます。

また、プログラムに書き起こした場合よりもコード量が圧縮される場合が多く、可読性の向上にも役立つと思われます。

 

まとめ

  • レコード同士の演算を行う必要がある場合、Window関数を利用するとSQLだけでも実装できることがある
  • Window関数で rows and preceding 1 and preceding 1 という記述を行うと、グループ内で現在参照しているレコードの、1つ前のレコードを参照することができる

SQLカテゴリの最新記事