時系列での在庫変動を記録しつつ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にデータを積み上げていけば、他にも色々なデータ活用方法がありそうですね。

この記事を読んでいただいたかたの、なにか少しでもお役に立てば幸いです🙏