在庫高をなるべく正確に速く求めるには

Question

（ほぼほぼ、処理系の話というよりもアルゴリズムの話になってしまいます。）
###前提・実現したいこと
PostgreSQLでデータ管理している自作POSシステムから、MS-Accessに部分的にデータを取り込んで分析ツールを作っております。
（データの取り込みは、テーブル単位で全レコードを対象にしています。）
分析ツール上で商品の分類ごとに「交差比率」を算出しており、この処理を高速化したいと考えています。

###発生している問題・エラーメッセージ
「交差比率」の算出には、「粗利益÷在庫高」を先に求める必要があります。
「粗利益」は、指定期間中の売上高と仕入高の差で簡単に求められます。
「在庫高」の算出に時間がかかっていて困っています。

「在庫高」の算出には、日毎の商品点数を求める必要があります。
POSシステムから抽出できるデータに、店舗間の商品移動（や売上や入荷）を捉えられるテーブルがあるので、各日付ごとの在庫高を求めるのに経過日数分商品の移動を追跡し該当日の在庫高を求めます。
しかし、特に過去の日付になればなるほど過去の商品移動を洗い出すのに時間がかかってしまいます。
（10年以上の商品移動履歴を蓄えているテーブルのため、MS-Access上でインデックスを設定していても遅いです。 とはいえ集計期間は昨対を見る程度なので過去3年程度で十分な範囲ですが。）
そのため、略式の算出方法として、月ごとに集計期間を分割して、月初と月末の2箇所のみを使って求める方式で一旦実装しています。
しかし、図に示すようにどうしても在庫高にはずれが生じてしまいます。

###補足
商品一つ一つ、日付ごとに何点どの店舗にあったのか、を記録するテーブル（在庫高テーブル）を作れば良さそうに思うのですが、商品のブランドごと、アイテム種類ごと、店舗ごと、ブランドの中でも商品番号（家電などでの型番のようなもの）ごとの集計をしており、在庫高テーブルに蓄えるデータ量が爆発的の膨らむのが想像できて、そういう実装は諦めました。

###追記：多数の回答ありがとうございます
業務がたて込み、ツール改修になかなか着手できずにおります。
BA決定までしばらく猶予ください。

Answer 1

在庫高テーブルに蓄えるデータ量が爆発的の膨らむ

というご懸念について、もし

精度の高い「交差比率」を求めたいのは過去３年程度まで。
それ以前は多少ズレても構わない。

と割り切ることが可能なら、在庫高テーブルに格納するデータの期限を「過去3年程度」に限定してしまってはいかがでしょうか？

アプリケーション側の実装が少々面倒になりますが、在庫高の算出方法を
・３年以内 => 在庫高テーブルから
・それ以前 => 略式の算出方法で
と、切り替えるわけです。

これなら、全期間のデータを蓄えるのと比べて在庫高テーブルのデータ量の増加を緩和できますので。

Comments

[退会済みユーザー]

3年程度でデータを減らしておいておく、良いヒントをいただけた気がします。

[退会済みユーザー]

長引かせても仕方ないので、こちらの回答を選ばせていただきました。皆様回答ありがとうございました。

Answer 2

補足で諦められている方法がスタンダードかつスマートな方法だと思います。

データが爆発的に増えることで困るのがディスク容量なのか、
検索速度が遅くなりそうということなのかで対処法が変わってきますが、
具体的にデータが爆発的に増えることでどのように困ると判断されましたか？

Comments

[退会済みユーザー]

在庫高テーブルがある意味キャッシュテーブルのような立ち位置になると思いました。
集計の条件がコロコロ変わるため、作っては消し作っては消しの繰り返しになりそうなことと、
現時点の在庫情報から過去に遡って洗い出す処理を繰り返し繰り返し行うことが
そもそも重いのではないかと考えて諦めたように思います。

[tanat]

なるほど、私の捕捉に対する認識と想定されている方法が少し違っていたようです。
*Accessのインデックスについてはほぼ知らないので、一般的なRDBMSの感覚で書いています。

私が想定する方法としては、以下の様な形です。
> 集計の条件がコロコロ変わる
というのが、データ項目自体がコロコロ変わるのか、単にいろんな条件を組み合わせたいということなのかで変わってきますが、後者なら可能だと思います。

[前提]
検索条件に関する要件（データ項目自体）がコロコロ変わって拡張性が求められるという事は発生しないとする。
*するのであれば、HWのアップグレードで力技で解決した方が早いかも・・・

[実装]
・集計に必要なデータで特に集計が重いものに関しては日次で集計テーブルを作る。
・これは必要な分だけテーブルを作る（検索速度優先である程度非正規化する）もしくは、低コストで検索できるような集計レコードテーブルをいくつか作って組み合わせられるようにする。
・作ったテーブルは過去データのはずなので、一回作ったら更新しない（追記されていくだけ）
・ストレージが足りなくなる様なら増強する。

・リアルタイムに集計も出来るように、集計データがある範囲は集計データを使い、無い範囲はこれまでと同様に検索するようになる（日次で集計データを作れば、最大で24時間分の集計をすればいいだけになる）

[デプロイ]
過去データから集計データを作るのは時間がかかるはずなので、
一時的に別のHWを用意してそちらで過去データの集計をさせておくか、
期間指定でデータ解析と集計データの投入が出来るようにして、
深夜などに一定期間（一晩で可能な範囲、毎日1ヵ月分処理できるならで120日かける）ごとにデータを投入し、作成が終わるまでは従来の検索システムを継続して使い、
作成が終わったら、検索プログラムをアップデートする

という感じでしょうか。

Answer 3

データ量によりますが、処理の一部を nysol にするのも手です。http
://qiita.com/gg_hatano/items/7a11e3a203a7646f05bf

私も、quita に書いています。
http://qiita.com/zanjibar/items/3fceeb44bb26693ec83a
１０００万行ぐらいまでは、nysol で十分な感触ありです。

nysol の先祖は、高卒の女子職員に、業務アプリを２週間程度で開発できるようにするためにつくられたアプリです。新橋の芸者でもつくれる真空管的なアプリですね。

Comments

[退会済みユーザー]

nysolそのもの、全く知りませんでした。情報提供ありがとうございます。
分析ツールを私が直接使うのではなく、ITスキルが低めの人達なので、一定レベルの使いやすさを提供するためにAccessで組んでいる状況です。
nysolをどう活用して組み合わせていくか、今ひとつピンときませんが、前処理としてデータ加工に使えばいいんでしょうかね。

[zanjibar]

データの量が多くて、アクセスだと身動きとれないようなときにつかうといいですね。データを巨大なひとつなファイルにするほうがいい場合にはおすすめです。

Answer 4

POSシステムから抽出できるデータに、店舗間の商品移動（や売上や入荷）を捉えられるテーブルがあるので、各日付ごとの在庫高を求めるのに経過日数分商品の移動を追跡し該当日の在庫高を求めます。
しかし、特に過去の日付になればなるほど過去の商品移動を洗い出すのに時間がかかってしまいます。

在庫高を出すなら、棚卸しのデータから日々の商品の移動を日が増える方向で計算して出すべきではないでしょうか？質問文を読むと最大10年分まで遡る方向で計算しているようなので気になります。
○棚卸しの日→日々の変化（？日分）→該当日→次の棚卸しの日
X該当日←日々の変化（？年分？日分）←現在地

商品がPOSシステムのデータ通りにあれば遡って計算してもズレないでしょうが、実際はいろんな要因でズレるわけで、そのために棚卸しをするわけです。
「交差比率」というものの使い方を理解していないため見当違いの意見かもしれませんが、最大10年分のズレを持つデータから出す数字は果たして価値はあるのでしょうか。

Comments

[退会済みユーザー]

棚卸データの集計をExcel上で揉んでいてPOSデータベースや分析ツールに戻していないため、棚卸確定データを基準に求める方式は検討しておりませんでした。
商品を個体管理する商品マスター上で在庫として扱う商品がどの店舗にあるのかがすぐ取得できるため、現在から遡る方式でやっておりました。
仕組みのお粗末さもあって、確かにズレるケースが有り、いちいち遡っての在庫高算出は狂いやすいと感じております。
月またぎの瞬間の商品所在を押さえて蓄積する方式も検討中です。
ご指摘ありがとうございます。

Answer 5

私なら、日次の在庫高テーブルは諦め、月次の在庫高テーブルを作成します。
または、年次の棚卸しデータ（＋棚卸し誤差）を元に在庫高データを作成します。

そこから、該当日のデータを計算すれば、計算量をぐんと減らすことが出来るはずです。

月次くらいであれば、データ容量も許容されるのではありませんか？

Comments

[退会済みユーザー]

棚卸在庫高データが使えればそこが正しいのですが、月末できっちり求まっているものではないので、活用にはさらに工夫が必要で検討すらしていませんでした。
柔軟な集計期間を入力できる仕様ですが、実際に活用する現場ではシーズン（およそ8ヶ月）通しでの推移を毎週集計しているもののようでしたので、月単位で在庫高を押さえておく（テーブルに持っとく）のが適切な気がしますね。ありがとうございます。