SQLにおけるデータが存在しない場合の差分の取得について（在庫等）

Question

一定期間毎の差分を取得するためにWithを利用して取得を試みたが、対象データの一部が存在しない場合差分が取れないため解消したいと考えております。何か良い方法がありましたらご助力頂けると幸いです。

---

サンプル

CREATE TABLE zaikoTable(
id integer not null,
 zaiko integer not null,
 date date not null );

INSERT INTO zaikotable VALUES(1,100,'2021-01-01');
INSERT INTO zaikotable VALUES(1,150,'2021-02-01');
INSERT INTO zaikotable VALUES(1,130,'2021-03-01');
INSERT INTO zaikotable VALUES(2,100,'2021-01-01');
INSERT INTO zaikotable VALUES(2,95,'2021-02-01');
INSERT INTO zaikotable VALUES(2,90,'2021-03-01');
INSERT INTO zaikotable VALUES(3,100,'2021-01-01');
INSERT INTO zaikotable VALUES(3,80,'2021-02-01');
INSERT INTO zaikotable VALUES(3,120,'2021-03-01');
INSERT INTO zaikotable VALUES(4,100,'2021-02-01');
INSERT INTO zaikotable VALUES(4,110,'2021-03-01');

※id:4 は2021/1/1のデータが存在しないものとします

id	zaiko	date
1	100	2021-01-01
1	150	2021-02-01
1	130	2021-03-01
2	100	2021-01-01
2	95	2021-02-01
2	90	2021-03-01
3	100	2021-01-01
3	80	2021-02-01
3	120	2021-03-01
4	100	2021-02-01
4	110	2021-03-01

取得したい値
2021/3/1を基準に2/1、1/1のデータと数量を比較します
このときid:4の1/1時点のデータを0として差分を取得させたい

id	diff 1month	diff 2month
1	-20	30
2	-5	-10
3	40	20
4	10	110

---

差分取得
2021/3/1を基準に2/1、1/1のデータと数量を比較します

With g0 As(
    SELECT id, zaiko, date
    FROM ZaikoTable
    WHERE date = '2021/3/1'
) , g1 As(
    SELECT id, zaiko, date
    FROM ZaikoTable
    WHERE date = '2021/2/1'
) ,g2 As(
    SELECT id, zaiko, date
    FROM ZaikoTable
    WHERE date = '2021/1/1'
) 
SELECT
    g0.id,
    g0.zaiko - g1.zaiko As "diff 1month",
    g0.zaiko - g2.zaiko As "diff 2month"
FROM g0, g1, g2
WHERE g0.id = g1.id AND g0.id = g2.id ;

実行結果
whereで全て等式を条件にした結果全ての1/1、2/1、3/1すべてのデータが存在しないと出力されない結果となる

id	diff 1month	diff 2month
1	-20	30
2	-5	-10
3	40	20

---

Answer 1

こういうのはどうでしょうか。

SELECT DISTINCT id FROM ZaikoTable でZaikoTableに存在する全てのidが得られますよね。
その結果を使って、データがほしい日付をカラムに加えて、

select z.id, '2021/1/1' as "date"
from (select distinct id from ZaikoTable) as z

これとまた ZaikoTable と突き合わせて、

select zz.id, zz.date, coalesce(zt.zaiko, 0) as zaiko
from (
  select z.id, '2021/1/1' as "date"
  from (select distinct id from ZaikoTable) as z
) as zz
left join (
  select id, "date", zaiko
  from ZaikoTable
  where "date" = '2021/1/1'
) as zt on .id = zt.id and zz.date = zt.date

とすると、在庫データがないところも0が詰められます。
（実行していない机上のコードなので、ミスありましたらご指摘ください。）

Comments

[okkoto]

回答ありがとうございました
サブクエリでの処理も考えたのですがカラムが増えると複雑になって断念していたところでした。
idだけ全体から抽出してしまえば結合で対応出来そうですね。

Answer 2

zaikoカラムには0以上の値が入っているという前提に立つと、以下のようなSQLで実現できるかと思います。

SELECT 
  id, 
  MAX(CASE WHEN date = '2021-03-01' THEN zaiko ELSE 0 END) - MAX(CASE WHEN date = '2021-02-01' THEN zaiko ELSE 0 END) AS "diff 1month",
  MAX(CASE WHEN date = '2021-03-01' THEN zaiko ELSE 0 END) - MAX(CASE WHEN date = '2021-01-01' THEN zaiko ELSE 0 END) AS "diff 2month"
FROM zaikotable
GROUP BY id
ORDER BY id

Comments

[okkoto]

確かにMAXとCASE WHENでシンプルに処理できますね！
zaikoは0以上の自然数となるので回答の形式で対応可能です。
ありがとうございました。