SQLの分析関数LAG

分析関数のLAGを最近知ったのでどんなものか紹介します。
適当な例を挙げるのが難しかったのですが、ありそうな例を考えてみました。

サンプルは、CentOS 6.3に付属のPostgreSQL 8.4で確認しています。最近のOracleでも動くと思います。MySQLは無理です。一応SQL標準なので他のDBMSでも実装されるかもしれません。

社員ごとの月ごとの売上が格納されたテーブルを用意します。テーブルは以下のDDLで作成します。

CREATE TABLE uriage_table(
  emp_no VARCHAR(3),
  year INTEGER,
  month INTEGER,
  uriage INTEGER,
  PRIMARY KEY(emp_no,year,month)
);

このテーブルに適当な以下のデータを流し込みます。

INSERT INTO uriage_table VALUES('001',2013,2,1000);
INSERT INTO uriage_table VALUES('001',2012,2,300);
INSERT INTO uriage_table VALUES('001',2013,1,1000);
INSERT INTO uriage_table VALUES('001',2012,1,1920);
INSERT INTO uriage_table VALUES('001',2012,12,222);
INSERT INTO uriage_table VALUES('001',2010,12,300);
INSERT INTO uriage_table VALUES('002',2013,2,300);
INSERT INTO uriage_table VALUES('002',2013,1,200);

この状態から2013年の売上が、前年同月と比較してどのくらい変動したのかをSQLを使って取得します。

結果としては、2012年と2013年のみを表示します。

SELECT emp_no,year,month,uriage,uriage-before_uriage AS hikaku
FROM (SELECT emp_no,year,month,uriage,
  LAG(uriage) OVER(
      PARTITION BY emp_no,month
      ORDER BY emp_no,month,year) AS before_uriage
      FROM uriage_table
      WHERE year>=2012) AS work
ORDER BY emp_no,month,year;

結果はこんな感じになります。

LAG関数は、上記のように引数を1つにした場合には同一パーティションの1つ前のレコードの値を取得できます。
今回は、emp_no、monthでパーティションを作っていて、かつ2012年以降のみに絞り込んでいます。
そのため、2012年と2013年に売上があり、かつ同一の月に売上がある場合にbefore_uriageに値が入ります。

LAG関数自体は

http://oracle.se-free.com/dml/0601_lag.html

の説明がわかりやすいです。1つ前の行だけでなく、任意指定の行数だけ前のレコードを取得したり、取得できなかった場合の初期値などが指定できます。