SPSS Modelerで@OFFSETはSQLプッシュバックが行えない関数として代表的なものです。
これをSQLプッシュバックさせる方法を考えてみます。これは、産業技術総合研究所 本田様に教えていただいたアイディアをDb2のDB関数も使って実装したものです。
-
テスト環境
- Modeler 18.4
- Db2 warehouse on cloud
-
サンプルストリーム
- サンプルデータ
用意したデータは以下のように顧客ごと日付ごとに購入金額と購入点数が記録されています。
ここから1回前のトランザクションの購入金額と購入点数の列を＠OFFSETで追加します。
1. @OFFSETでの実現
まず、＠OFFSETでの実現方法を確認しておきます。
CUSTID（顧客ID）とDATE（購入日）でソートします。
「フィールド作成」ノードで同じCUSTID（顧客ID）の1回前のAMOUNT（購入額）、NUMPURCHASE（購入点数）の値を取得しています。
以下のようにひとつ前トランザクションの値を取得できます。これで前回からの購入金額の差などを計算することができます。
しかし、@OFFSETがSQLプッシュバックしません。
2. SQLプッシュバックの実現
「フィールド作成」ノードでSYSIBM.ROWNUMBERを選びます。そしてCUSTIDでパーティション化して、DATEでソートして連番を振ります。
以下のようにCUSTIDごとにDATE順に連番が振られます（RDBの処理なので順序は保証されません。この表示と同じ準にするためにはCUSTIDとDATEでソートをして確認してください）。
内部的には以下のSQLがプッシュバックされています。
SYSIBM.ROWNUMBER()
OVER( PARTITION BY T0.CUSTID ORDER BY T0."DATE" ASC) AS NUM
次に「置換」ノードで今作ったNUMに１足したデータを作ります。
そして、これらをCUSTIDとNUMで部分外部結合します。
フィルタータブで1トランザクション前のDATEは削除し、AMOUNTとNUMPRUCHASEには「_LAG」の接尾辞をつけました。
最後にCUSTIDとDATEでソートをしました。
@OFFSETで得られた結果と同じ結果が得られました。
これならSQLプッシュバックが可能です。
ここではDb2のデータベース関数のROWNUMBERをつかって実現していますが、もしこれに該当する関数が接続しているRDBにない場合は、いったん@INDEXなどで連番を振って一時表をRDBに作ってから行うということもできます（この処理だけならCUSTIDでパーティション化する必要はありません）。@INDEXもSQLプッシュバックが効きませんが、一時表以降はSQLプッシュバックが可能になります。
参考：OLAP 指定 - IBM Documentation