前回⏬️、TROCCOを用いてZAICOから在庫データ一覧をSnowflakeに転送しました。
今回はその応用として、Snowflake上で在庫の移動平均を求めたいと思います。
時系列での在庫変動を記録しつつSnowflakeにデータを送る
【TROCCO】転送設定の作成
転送設定STEP1～STEP2（プレビュー表示）までの設定
前回の記事と同じ手順で、転送設定STEP2（プレビューが表示されるところ）まで作成します。
転送設定STEP2 ▶ 転送日時カラム設定
今回、日々の在庫変動を記録していきたいので、転送日時カラム設定を用いて「いつ転送したデータか」を記録するよう設定します。
スケジュールの設定と転送実行
設定を保存したら、毎日決まった時間にデータを転送するように設定します。
設定は以上です。
毎日のスケジュール実行で、Snowflake上には⏬️のようにデータが積み上がっていきます。
ここまできたら、後はSQLを使って移動平均を計算します。
移動平均をもとめる
そもそも移動平均とは
時系列データにおいて、ある一定期間のデータの平均値を、期間をずらしながら計算していく手法。
短期的なノイズを平滑化 し、長期的なトレンドを把握する目的で使用します。
「短期的なノイズを平滑化」というところがポイントです。
・TROCCOは基本バッチ実行でのデータ転送を行う
・ZAICOの在庫データ一覧は、その時点での在庫量を返す
という特性があります。
そのため、バッチ実行で取得した在庫データは、取得タイミングによる一時的な変動（例えば、ちょうど大量入庫直後や出荷直後など）を含む可能性があります。
移動平均を使うことで、このような短期的なスパイクをならし、より安定した在庫量の推移を見ることができます。
そのため、日々の細かな増減に惑わされず、在庫の大まかな傾向（増加傾向なのか、減少傾向なのか、安定しているのか、とか）を捉えやすくなります。
移動平均の種類
移動平均には「単純移動平均」「加重移動平均」「指数平滑移動平均」という3種類があるようです
おおまかな使い分け方⏬️
- 年間の大まかな在庫推移や、安定的な平均在庫を知りたい ▶ 長期間の単純移動平均 (例: 60日, 90日)
- 月次での発注計画の参考に、最近の傾向を少し重視して見たい ▶ 中期間の単純移動平均 or 加重移動平均 (例: 20日, 30日)
- 日々の在庫チェックで、危険水準への接近などを早めに検知したい ▶ 短期間の指数平滑移動平均 or 加重移動平均 (例: 5日, 10日)
今回は最もシンプルな単純移動平均をSQLで求めたいと思います!!
単純移動平均の求め方
ざっくりとした計算プロセス
- 一定期間のデータを合計し、その期間数で割る。
- 計算する期間を一つずつずらしながら平均値を算出していく。
例（7日移動平均）:
ある日の移動平均値は、その日を含む過去7日間のデータの平均。
次の日の移動平均値は、その日を含む過去7日間のデータの平均（最も古い日のデータは除く）。
単純移動平均を求めるクエリサンプル
SELECT
ID,
DATE(TIME) as "日付",
SUM(TRY_CAST(QUANTITY AS NUMBER)) as "在庫数",
AVG(SUM(TRY_CAST(QUANTITY AS NUMBER))) OVER (ORDER BY DATE(TIME) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as "移動平均"
FROM
ZAICO_LIST
GROUP BY
ID,DATE(TIME)
;
ポイント💡
-
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
- 計算対象とする行（ウィンドウフレーム）の範囲を指定します
- CURRENT ROW: 現在処理している行（日付）
- 6 PRECEDING: 現在の行の前6行（6日前）
⏩️ つまり、「現在の行（当日）とその前の6行（6日前まで）の合計7行（7日間）」を指定しています
- 計算対象とする行（ウィンドウフレーム）の範囲を指定します
-
TRY_CAST(QUANTITY AS NUMBER)- もし QUANTITY の値が数値に変換できない形式（例: 空文字列、テキスト文字など）だった場合に、エラーを起こさずに NULL を返す関数です
- おまじないとして設定しておきました
クエリを実行
スプレッドシートにエクスポートして、グラフ化してみました。
増減は激しいものの、ならすと大体60～70ぐらいの在庫量であることがわかりました。
今回はスプレッドシートでグラフ化しましたが、BIツールと組み合わせることで、ID別・期間別の絞り込んだ、よりリッチで実用的な可視化ができそうです!!
いかがでしたでしょうか
今回はZAICOの在庫一覧データから、移動平均を求めてみました。
日次でDWHにデータを積み上げていけば、他にも色々なデータ活用方法がありそうですね。
この記事を読んでいただいたかたの、なにか少しでもお役に立てば幸いです🙏