歯抜け日付を埋めるSQL（無理矢理感強し）

需要予測や在庫適正のシミュレーションをする上で、売上データや入荷データの増減で持って、時系列分析をするということは普段皆さん当たり前に実施なさっているかと思います。

しかしながら、毎日、売上データや入荷データ上に必ずしも該当のデータのトランザクションが発生するとは限らない。

商品によっては１ヶ月まるまる動きがないものもあります。

その欠損・欠落によって、精度の高い予測ができないこともしばしばあるかと思います。

まー動きがないものに対して、在庫適正や需要予測する必要があるのか？と疑問をお持ちになることは当然ではありますが、そこは置いといて、

今回は上記のような歯抜け日付を数量0で埋める方法をご紹介します。

なお、環境はPostgreSQL 9.6.2となっています。

前提

・基礎になるデータは売上データです

・売上データの粒度は日付→伝票→商品となります

　日付→商品単位で歯抜け日付を埋めます

・歯抜け埋めする範囲は初回売上日から最終売上日までとします。

・レスポンスを考慮しません。

　というのも需要予測や在庫適正の分析する上でリアルタイム度を要求するかというとしないかと思います。DWHにデータを格納する前提で、前日データまでを夜間に差分もしくは全件洗い替えするような環境を前提としています。

データ構造

今回は売上データから時系列分析で持って、売上予想を立てるために必要なデータとして

①売上データ

②商品マスタ

の二つのテーブルを構築します。なお、売上データは正規化していません。

sales table　＝　売上データ

product　＝　商品マスタ

データは以下を利用しました。Githubに格納してあります。

Fill the missing dates using SQL

https://github.com/Taihei19/missing_dates

まずは商品「174」の購入履歴を日付のみでソートしました。

良い歯抜け具合になっています。

なお、この状態でTableauで時系列グラフを表示した場合、以下ようにビジュアライズされます

※Tableauでも、もちろん歯抜け日付の対応は可能です。しかし、ここはあくまでもSQLで対応とします。イメージを持っていただくために、Tableauでの表示をしてます。

歯抜け日付部分は数量0にプロットされていないので、継続して売れているように見えてしまってますね。

今からこちらの歯抜けを埋めていきます。

考え方として、以下の手順で処理します。

1. salesデータから最小オーダー日と最大オーダー日を取得する
2. 最小オーダー日から最大オーダー日までの日付を列挙する（以下、日付列挙クエリ）
3. 日付列挙クエリとproductテーブルを交差結合する（商品日付列挙クエリ）
4. 商品日付列挙クエリにsalesテーブルを日付と商品IDで外部結合する

salesデータから最小オーダー日と最大オーダー日を取得する

こちらは特に説明不要かと思いますが、以下の通りです。

--最小オーダー日を取得
select min(orderdate) from sales

--最大オーダー日を取得
salect max(orderdate) from sales

最小オーダー日から最大オーダー日までの日付を列挙する（以下、日付列挙クエリ）

generate_seriesを使って処理します。

--最小オーダー日から最大オーダー日までを1日ずつステップアップ
select generate_series
   from generate_series((select min(orderdate) from sales), (select max(orderdate) from sales), '1 day')

日付列挙クエリとproductテーブルを交差結合する（商品日付列挙クエリ）

--日付列挙と商品マスタを交差結合
select cast(generate_series as date) as orderdate
       ,p.productid
       ,p.productname
       ,p.categoryid
       ,p.categoryname
   from generate_series((select min(orderdate) from sales), (select max(orderdate) from sales), '1 day')
  cross join product p

なお、こちらの実行結果は以下の通り、日付毎に全商品がクロスに結合されるようになります。

商品日付列挙クエリにsalesテーブルを日付の商品IDで外部結合する

最後に上記で作成した商品日付列挙クエリにsalesテーブルを外部結合します。

select g.orderdate
       ,coalesce(s.orderid,'') as orderid
       ,coalesce(s.customerid,'') as customerid
       ,coalesce(s.store,'') as store
       ,g.productid
       ,g.productname
       ,g.categoryid
       ,g.categoryname
       ,coalesce(s.cost,0) as cost
       ,coalesce(s.salesprice,0) as salesprice
       ,coalesce(s.quantity,0) as quantity
       ,coalesce(s.amountcost,0) as amountcost
       ,coalesce(s.amountsales,0) as amountsales
       ,coalesce(s.profit,0) as profit
   from(
 select cast(generate_series as date) as orderdate
       ,p.productid
       ,p.productname
       ,p.categoryid
       ,p.categoryname
   from generate_series((select min(orderdate) from sales), (select max(orderdate) from sales), '1 day')
  cross join product p ) g --商品日付列挙クエリ
   left join sales s --売上データ
     on g.orderdate = s.orderdate
    and g.productid = s.productid

改めて、このクエリを使って、商品ID「174」を見てみると、当然ですが、数量0の日もプロットされています。

以上が歯抜け日付を商品単位で埋めるSQLでした。

UNIONを使っても同様処理ができますね。

データ粒度によって、操作の面倒くささに違いが出てきますね。

本日のドキュメントは以下に格納しています。

Fill the missing dates using SQL

https://github.com/Taihei19/missing_dates

では。