oracle sql にて2つの表を使用して在庫推移を集計したい

Question

前提・実現したいこと

Oracle sqlを使用し、2つの表から在庫量を集計したいです。
このようなパターンの集計作業は初なので、方針等アドバイスいただけないでしょうか。

今回使用するテーブルはこちらの2つです。

■TDP00(月次単位で作成する総在庫量データ)
(データ例)
|年月|アイテム|在庫量|登録日
|:--|:--:|--:|
|202004|アイテム1|1295.8|20200507|
|202005|アイテム1|1298.8|20200602|
|202006|アイテム1|1189.4|20200702|
|202007|アイテム1|1187.6|20200804|
…

■TDF00(入出庫に際して作成する入出庫量データ)
(データ例 ※マイナスは出庫に相当します)

登録日	アイテム	入出庫量
20200528	アイテム1	3
20200602	アイテム1	-42.6
20200616	アイテム1	-55
20200622	アイテム1	-6.3
20200622	アイテム1	2.5
20200623	アイテム1	-8
20200729	アイテム1	-1.8
…

2つのテーブルも元に
このような1つのテーブルを作成したいです。

登録日	アイテム	在庫量
20200507	アイテム1	1295.8
20200528	アイテム1	1298.8
20200602	アイテム1	1298.8
20200602	アイテム1	1256.2
20200616	アイテム1	1201.2
20200622	アイテム1	1194.9
20200622	アイテム1	1197.4
20200623	アイテム1	1189.4
20200702	アイテム1	1189.4
20200729	アイテム1	1187.6
20200804	アイテム1	1187.6
…

試したこと

ここに問題に対して試したことを記載してください。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Comments

[Orlofsky]

[Oracle]タグを追加してください。

[Orlofsky]

SQLで使われるテーブルはCREATE TABLEを実行しなければなりません。テーブルの中のデータもINSERTされていなければなりませんから、質問に CREATE TABLE, INSERT を https://teratail.com/help/question-tips#questionTips3-7 の [コード] で追記できた方が適切なコメントが付き易いです。

[sazi]

○○では分かりません。そこに入る数値は在庫量の推移なのでしょうか？
推移だとしたら、TDP00とTDF00の日付が同じ場合のTDF00は無視するなどの考慮は不要なのでしょうか？
→数値に直されましたけど、正しいですか？
例えば20200602の日付のデータとか。
また同じ日付の場合、TDP00とTDF00はどちらを先に処理するんでしょうか？

[kataya]

はい、在庫量の推移です。("○○"は私のミスです。先ほど更新しました。)
日付が同じ場合はTDP00の値にTDF00の値を加算/減算したいです。

Answer 1

回答しようとしたら、分析関数で被っていたので、分析関数を使用しないパターンで。

select 登録日, アイテム, 数量 as 在庫量, 識別
from (
      select 年月, 登録日, アイテム, 0 as 識別, 在庫量 as 数量 from TDF00
    union all
      select substr(t1.登録日, 1, 6), t1.登録日, t1.アイテム, 1, sum(t2.入出庫量)
      from    tdf00 t1 left join tdf00 t2
              on    substr(t1.登録日, 1, 6) = substr(t2.登録日, 1, 6)
                and t1.アイテム = t2.アイテム
                and t1.登録日 >= t2.登録日
      group by t1.年月, t1.登録日, t1.アイテム
) td
order by 登録日, アイテム, 識別

但し、他の方と同様にTDF00の同じ日付のものの順序に関する情報がないので、同じ日付に集約されます。
手打ちで動作未確認ですので、悪しからず。

Answer 2

あんまりスマートじゃないですが、

SELECT
  "登録日",
  "アイテム",
  SUM("在庫量") OVER(PARTITION BY "年月" ORDER BY "登録日", "親子フラグ") AS "在庫量"
FROM (
  SELECT "登録日", "アイテム", "在庫量", "年月", '1' AS "親子フラグ" FROM TDP00
  UNION ALL
  SELECT "登録日", "アイテム", "入出庫量" AS "在庫量", TO_CHAR(ADD_MONTHS(TO_DATE("登録日", 'YYYYMMDD'), -1), 'YYYYMM') AS "年月", '2' AS "親子フラグ" FROM TDF00
) T
ORDER BY "登録日"
;
/*
登録日 アイテム    在庫量
20200507	アイテム1	    1295.8
20200528	アイテム1	    1298.8
20200602	アイテム1	    1298.8
20200602	アイテム1	    1256.2
20200616	アイテム1	    1201.2
20200622	アイテム1	    1197.4
20200622	アイテム1	    1197.4
20200623	アイテム1	    1189.4
20200702	アイテム1	    1189.4
20200729	アイテム1	    1187.6
20200804	アイテム1	    1187.6
*/

また20200622分の計算過程の部分がちょっと上手く行きません。
分析関数SUMのORDER BYに割り当てる順序インデックスみたいなのがあれば処理できるのですが、
提示の情報ではわからないので暫定でこれにしています。

Comments

[kataya]

連絡遅くなり申し訳ございません。
また質問文に誤りがあり、誤解を与えてしまい。ご迷惑をおかけしました。
ほかの回答者様にも感謝しております。

Answer 3

Oracle Database でのSQLはマルチバイト文字を含むテーブル名や列名はダブルクォーティングしなければ動作保証されません。

INSERT INTO TABLE1("登録日", "アイテム", "在庫量")
SELECT "登録日", "アイテム", "在庫量" FROM TDP00
UNION ALL
SELECT "登録日", "アイテム",        0 FROM TDF00 ;

Comments

[kataya]

すみません、今回は説明のため便宜上このような列名としました。

[Orlofsky]

SQLではテーブルにINSERTされる順序は保証されません。
TABLE1 はSELECT時に必要な ORDER BY句で出力順を指定します。

[kataya]

すみません、作成したいテーブルの在庫量の値が○○のまま未計算になっていました(先ほど更新しました)。せっかく回答していただいたのですが、誤解を与えてしまい申し訳ございません。

[Orlofsky]

途中で質問を変更しないでください。
累計は分析関数の SUM ... OVER ... を使ってください。 https://ameblo.jp/tsu-a-sa-so/entry-10148818649.html