SPSS Modelerで@OFFSETはSQLプッシュバックが行えない関数として代表的なものです。
以前ナンバリングをする方法でプッシュバックを行いました。今回はDBウインドウ関数のLAGをつかってSQLプッシュバックさせる方法を考えてみます。パフォーマンスはナンバリングする方が速いと思いますが、LAGを使うと１ノードで実現できるというメリットがあります。
-
テスト環境
- Modeler 18.5
- Db2 warehouse on cloud
-
サンプルストリーム
- サンプルデータ
用意したデータは以下のように顧客ごと日付ごとに購入金額と購入点数が記録されています。
ここから1回前のトランザクションの購入金額と購入点数の列を＠OFFSETで追加します。
1. @OFFSETでの実現
まず、＠OFFSETでの実現方法を確認しておきます。
CUSTID（顧客ID）とDATE（購入日）でソートします。
「フィールド作成」ノードで同じCUSTID（顧客ID）の1回前のAMOUNT（購入額）、NUMPURCHASE（購入点数）の値を取得しています。
@OFFSET(@FIELD,1)
以下のようにひとつ前トランザクションの値を取得できます。これで前回からの購入金額の差などを計算することができます。
しかし、@OFFSETがSQLプッシュバックしません。
2. SQLプッシュバックの実現
「フィールド作成」ノードでデータベースウィンドウ関数のSYSIBM.LAGを選びます。。そしてCUSTIDでパーティション化して、DATEでソートして１つまえのLAGをとります。
SYSIBM.LAG(@FIELD,1,to_integer(undef) ,'IGNORE NULLS',CUSTID, DATE,'ASC',undef,undef,undef)
内部的には以下のSQLがプッシュバックされています。
SELECT T0.CUSTID AS CUSTID,T0."DATE" AS "DATE",T0.AMOUNT AS AMOUNT,T0.NUMPURCHASE AS NUMPURCHASE,
SYSIBM.LAG(T0.AMOUNT,1,CAST(NULL AS BIGINT),'IGNORE NULLS') OVER( PARTITION BY T0.CUSTID ORDER BY T0."DATE" ASC) AS "AMOUNT_LAG1",
SYSIBM.LAG(T0.NUMPURCHASE,1,CAST(NULL AS BIGINT),'IGNORE NULLS') OVER( PARTITION BY T0.CUSTID ORDER BY T0."DATE" ASC) AS "NUMPURCHASE_LAG1"
FROM "INDIV_DAILY_SALES" T0
@OFFSETで得られた結果と同じ結果が得られました。
これならSQLプッシュバックが可能です。
なお、後方の値を取得する@OFFSET(@FIELD,-1)を行うにはLEAD関数を使います。
SYSIBM.LEAD(@FIELD,1,to_integer(undef) ,'IGNORE NULLS',CUSTID, DATE,'ASC',undef,undef,undef)
参考：DB2 OLAP 指定 - IBM Documentation