今回は久しぶりにSQL関係。
日商の売上を見る際に前日や翌日との比率を出す、差額を出すというようなレポート作成はごく当たり前にされているかと思います。
この場合、参照レコードに対して、前後のレコードの値を取得し、同じレコード内で処理をする必要がございますが、
その際に参考になるWindows関数がlagとleadです。
早速それぞれの関数を見ていきたいと思います。
lag
lag(value anyelement [, offset integer [, default anyelement ]])
--戻り値＝valueと同じ型
--パーティション内の現在行よりoffset行だけ前の行で評価されたvalueを返す。
--該当する行がない場合、その代わりとしてdefault(valueと同じ型でなければならない)を返す。
--offsetとdefaultは共に現在行について評価される。
--省略された場合、offsetは1となり、defaultはNULLになる。 lead
lead(value anyelement [, offset integer [, default anyelement ]])
--戻り値＝valueと同じ型
--パーティション内の現在行よりoffset行だけ前の行で評価されたvalueを返す。
--該当する行がない場合、その代わりとしてdefault(valueと同じ型でなければならない)を返す。
--offsetとdefaultは共に現在行について評価される。
--省略された場合、offsetは1となり、defaultはNULLになる。 PostgreSQL 12.4文書
出典元：https://www.postgresql.jp/document/12/html/functions-window.html
第76回びわ湖毎日マラソン大会の日本新記録の5kmペースの前後比較をする
2021年2月28日（日）に開催されました第76回びわ湖毎日マラソンで日本記録を更新した鈴木健吾選手の5km毎のペース記録をもとに
lag関数とlead関数の使い方を見ていきます。
データの準備
データは以下のサイトの情報を使いました。
びわ湖毎日マラソン公式サイト
第76回びわ湖毎日マラソン大会結果PDF版
http://www.lakebiwa-marathon.com/spotnews/result_76.pdf
create table marathon(
km numeric
,pace time
);
insert into marathon
values
(5,'00:14:53'),
(10,'00:29:46'),
(15,'00:44:32'),
(20,'00:59:21'),
(25,'01:14:09'),
(30,'01:28:59'),
(35,'01:44:01'),
(40,'01:58:40'),
(42.195,'02:04:56');
select *
from marathon;
前後のpaceの値を取得する
select km
,pace
,lag(pace, 1) over (order by km) as lagpace --前の結果を取得
,lead(pace, 1) over (order by km) as leadpace --後の結果を取得
from marathon;結果
ちなみにoffsetを2にするとlagは前の前の値、leadを後の後の値を取得します。
select km
,pace
,lag(pace, 1) over (order by km) as lagpace --前の結果を取得
,lag(pace, 2) over (order by km) as lag2pace --前の前の結果を取得
,lead(pace, 1) over (order by km) as leadpace --後の結果を取得
,lead(pace, 2) over (order by km) as lead2pace --後の後の結果を取得
from marathon;結果
5km毎のラップタイムを取得する
単純に元の値からlagで取得した前のレコードの値を引き算するだけです。
select km
,pace
,lag(pace, 1) over (order by km) as lagpace --前の結果を取得
,pace - lag(pace, 1) over (order by km) as laptime --paceから前のレコードのpaceを引く
from marathon;結果
・・・マラソン、詳しくないため、このラップタイムを見てコメントしようがないのですが、
lag関数でラップタイムを簡単に取得できることがわかりました。
はい。
lag関数やlead関数を使うと以下を簡易に対処できます。
・前後のレコードの値を容易に取得できる
・単純に一つ前、一つ後でなく、２、３、４、と引数を設定すれば、設定した分だけの前後のレコードの値を取得できる
ぜひ、分析業務にお役立ていただければと思います。
では。