「データ」と「情報」について
かの有名な「SQLアンチパターン」の監訳者まえがきで、和田省二さん（和田卓人さんのお父様）が、「データ」と「情報」について以下の様に記載しています。
さて、みなさんは「情報」と「データ」の違いをご存知でしょうか。この二者の関係は「多くの情報を秘めた貴重なデータ」という表現で言い尽くせます。いつでも我々が欲しいのは、意味のある（目的を持った）正しい情報なのです。一方、データは単なる各種の事実の値（何らかの、名称とか日付とか金額とか）であって、それ自体に目的はありません。事実は唯一無二なので同じデータを複数持つのは良くありません。そして目的を持った情報は、無目的な事実を集積したデータを種々加工して得られます。データは唯一無二の事実値ですから、それから作り出される情報はどれも正しく、互いに整合性がとれています。
SQLアンチパターン xii
実は私は今まで「データ」と「情報」に関して強く意識したことは多くありませんでした。しかし、今回、在庫管理システムを開発するにあたって、「データ」と「情報」について意識をして開発する必要がありました。なぜなら、在庫数とは紛れもなく「情報」であるからです。
ここで「データ」と「情報」についてより簡単な例を挙げてみます。題材としてはWebの黎明期のホームページによくあったカウンターです。そうです、「あなたはxxxx番目の訪問者です！」ということを来訪者に教えてくれ、キリ番ゲットしたら掲示板に報告するカウンターです。
カウンターの実装方法としては、大きく分けて２つあると思います。一つは、何番目かを記録する領域を持ち、その領域に対し、誰かがアクセスした際に、既存の数値に1を足したものを再度保存する方法です。記録する領域がリレーショナルデータベースのあるテーブルのcounterカラムの場合、SQLで表現するとUPDATE some_table SET counter = counter + 1 という形です。もう一つは、アクセスしたという情報をアクセス履歴として残し、そのアクセス履歴の数を「何番目」かとする方法です。カウントする部分をSQLで表現すると SELECT COUNT(*) FROM accesses; といった形になります。この二つの方法のうち、在庫管理などの数値を誤ることが許されないシステムの場合、後者（アクセス履歴をカウントする方法）の実装になります。なぜなら、前者の実装方法だと同時アクセスがあった場合に、counterカラムの値が同じ値に対し+1をしてしまうからです。ホームページのカウンターといった多少の数え漏れが許されるケースにおいては前者でも良いと思いますので、求められる状況によって最適な実装方法は変わってきます。
さて、後者の例を用いて、「データ」と「情報」についての説明をします。ここで、accessesテーブルにおける１行１行のレコードは、いつアクセスしたかを記録する「データ」になります。User-AgentやIPアドレス、アクセス日時などの情報が保存されているものになります。このデータ自体はもちろん有意義なものですが、ひとつひとつを見たとしてもあまり良い示唆は得られません。例えば、今まで何人のユーザーがアクセスしてくれたのか、日毎のアクセス数はいくらなのか、そういったことを知りたいと思うでしょう。日毎のアクセス数を知るためには
SELECT
CAST(access_time AS date) AS access_date
, count(*) AS access_count
FROM
accesses
GROUP BY
CAST(access_time AS date);
といったクエリを発行することで日毎のアクセス数を把握することができます。場合によっては、毎回このクエリを発行して処理するのが大変なため、アクセス数を日毎にサマリーしたテーブル（例：access_summary(access_date, access_count)）を作ってデータとして格納することもあるでしょう。この access_summaryが、情報といえます。この情報をみて、１日あたりのアクセス数が増えてきているな、減ってきているな、といったことを把握し、減ってきているならどうすれば改善することができるのかといったことを考えるきっかけとなるものになります。
在庫数は「情報」である
「データ」と「情報」についての理解を深めたところで、本記事のテーマである在庫についての話に入ります。在庫数の計算は、考え方はシンプルで入庫数から出庫数を引いたものが在庫数になります。式で書くと、
・$b_{i,n}^k$ を開始から$n$日目の$k$番目の商品$i$の伝票の入庫数
・$c_{i,n}^j$ を開始から$n$日目の$j$番目の商品$i$の伝票の出庫数
とする。n日目の入庫数と出庫数はそれぞれ、
\displaylines{
\sum_k b_{n}^k \\
\sum_j c_{n}^j
}
となります。ある商品$i$のシステム運用開始から$n$日目の在庫数$z_{i,n}$は、システムの運用開始からの入庫数から在庫数を引けば良いので、
z_{i,n} = \sum_n \sum_k b_{i,n}^k - \sum_n \sum_j c_{i,n}^j
となります。
ここで、$b_{i,n}^k$や$c_{i,n}^j$ は伝票の入庫数、出庫数ですので「データ」であると言えます。一方で、在庫数$z_{i,n}$（入庫数を全て足したものから、出庫数を全て足したものを引いたもの）は、今手元にいくつある在庫があるのかという「情報」であると言えます。
在庫計算は「漸化式」である
この時点では、漸化式ではなくただ入庫数の総和から出庫数の総和を引いただけです。
しかし、これをそのまま計算するとシステムの運用が長くなるにつれ、対象となる伝票の数が多くなるので、処理が遅くなってしまう恐れがあります。したがって、実際の運用としては、おそらくどこかの時点での在庫数を基点としてそこからの差分の入庫数を足し、出庫数を引くということが多いと思われます。例えば、日単位で前日の在庫数に当日の入庫数を足し、出庫数を引いて現在の在庫数を求めます。月単位（締め期間単位）で考える場合もあるかもしれません。ここでは、日単位で計算した場合を考えましょう。
$z_{i,n}$を商品$i$の$n$日目の在庫数とします。すると、前日分（$n-1$日目）の在庫数に$n$日目の入庫数を足し、出庫数を引けばいいので、$z_{i,n}$は
z_{i,n} = z_{i,n-1} + \sum_k b_{i,n}^k - \sum_k c_{i,n}^k
となります。
具体的な在庫計算クエリ
本記事で伝えたかったことは、在庫計算は漸化式であるということなので、ここで記事を終わりにしても良かったのですが、せっかくですので、具体的な在庫計算をSQLで記述するとどうなるかを考えてみます。
上記テーブルを生成するDDL
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE slips (
id BIGSERIAL PRIMARY KEY,
slip_date DATE NOT NULL,
slip_type INT NOT NULL
);
CREATE TABLE slip_details (
id BIGSERIAL PRIMARY KEY,
slip_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (slip_id) REFERENCES slips(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE daily_stocks (
id BIGSERIAL PRIMARY KEY,
date DATE NOT NULL,
product_id BIGINT NOT NULL,
stock_quantity INT NOT NULL,
FOREIGN KEY (product_id) REFERENCES products(id)
);
ALTER TABLE daily_stocks ADD UNIQUE(date, product_id);
INSERT INTO products(name) VALUES ('商品1'), ('商品2'), ('商品3'), ('商品4'), ('商品5');
INSERT INTO slips(slip_date, slip_type) VALUES
('2024-10-29', 1) -- 1
, ('2024-10-29', 1) -- 2
, ('2024-10-29', 2) -- 3
, ('2024-10-31', 1) -- 4
, ('2024-11-01', 2) -- 5
, ('2024-11-03', 1) -- 6
, ('2024-11-04', 2) -- 7
;
INSERT INTO slip_details(slip_id, product_id, quantity) VALUES
(1, 3, 10)
, (1, 5, 30)
, (2, 1, 15)
, (2, 2, 20)
, (2, 3, 18)
, (3, 1, 5)
, (3, 5, 17)
, (4, 5, 20)
, (4, 1, 15)
, (4, 2, 10)
, (5, 2, 20)
, (5, 3, 30)
, (6, 1, 23)
, (6, 3, 16)
, (6, 2, 32)
, (6, 5, 19)
, (6, 4, 7)
, (7, 4, 9)
, (7, 2, 14)
;
ここで、テーブル設計においては、主キーとして、連番であるidを付与する様にしています。これに関しては、私の過去記事「SQLアンチパターン第3章 IDリクワイアド（とりあえずID）はベストプラクティスなのではないか」をご覧ください。また、DDLの中のALTER TABLE daily_stocks ADD UNIQUE(date, product_id);という記載も大切な箇所です。主キーに連番を利用すると、その他のカラムにおいて、何がユニークなものなのかというのを設定しなくとも利用することができます。それを放っておくと、重複してはいけないレコードが生成されてしまいます。今回の設計においては、daily_stocksテーブルにおいては、dateとproduct_idが複合でユニークになるので、ユニーク制約をつけています。ここで、注目していただきたいのは、ユニークインデックスではなく、ユニーク制約をつけていることです。これはユニークインデックスでは、下記のSQLにおいて、INSERT INTO ~ ON CONFLICTで制約として利用することができないので、CONSTRAINTとする必要があります。（ユニーク制約とユニークインデックスは異なるので注意1）
それでは、日々の在庫情報をもつテーブルdaily_stocksの在庫数を更新するSQLを以下に示します。
INSERT INTO daily_stocks(
date
, product_id
, stock_quantity
)
WITH prev_daily_stock AS (
SELECT
date
, product_id
, stock_quantity
FROM
daily_stocks AS ds1
WHERE
ds1.date = (
SELECT
MAX(ds2.date)
FROM
daily_stocks AS ds2
WHERE
ds2.date < :target_date::date
AND ds1.product_id = ds2.product_id
)
AND ds1.product_id = :product_id
),
product_quantity_by_date AS (
SELECT
:target_date::date AS date
, p.id AS product_id
, SUM(CASE WHEN s.slip_type = 1 THEN sd.quantity WHEN s.slip_type = 2 THEN -sd.quantity END) AS stock_quantity
FROM
slip_details AS sd
ON
sd.product_id = p.id
LEFT JOIN
slips AS s
ON
sd.slip_id = s.id
WHERE
s.slip_date = :target_date::date
AND sd.product_id = :product_id
GROUP BY
sd.product_id
)
SELECT
:target_date::date AS date
, p.id
, COALESCE(pds.stock_quantity, 0) + COALESCE(pqbd.stock_quantity, 0)
FROM
products AS p
LEFT JOIN
product_quantity_by_date AS pqbd
ON
pqbd.product_id = p.id
LEFT JOIN
prev_daily_stock AS pds
ON
pds.product_id = p.id
ON CONFLICT
ON CONSTRAINT daily_stocks_date_product_id_key
DO UPDATE SET
stock_quantity=excluded.stock_quantity
RETURNING product_id, date
;
前日の在庫（prev_daily_stock）に対して、当日分の伝票を足し引きすることで、今の在庫数を求めています。
実際には、
・棚卸し
・伝票の修正
・過去伝票・未来伝票が登録される
といったことが行われるのでより複雑になり、伝票日付ではなく、伝票が登録された時刻をベースに計算するといった工夫をしていますが、在庫計算の大きな流れとしては、以上の様になります。