２つのテーブルを条件に従い可能な限り正確に突き合わせたい

実現したいこと

購入テーブルと返品テーブルを条件に従って可能な限り突き合わせたいです。

前提

テーブル名：PurchaseTable
ID | Amount | Date
001 1 2023/1/1
001 1 2023/1/2
001 2 2023/1/2
002 2 2023/1/2
002 1 2023/1/3

テーブル名：ReturnTable
ID | Amount | Date
001 1 2023/1/3
001 2 2023/1/4
002 2 2023/1/4

<条件>
・購入日 <= 返品日となる
・購入時と返品時の個数は一致する
→ ID 001のものを２個購入したとして、返品する際には必ず２個返品する
・日付が異なるが購入個数が同じレコードが複数ある場合は最も近い日付で返品されたとする
→ ex) ID001を1/1, 1/2に１個ずつ購入しているが、1/3に返品したレコードは直近の1/2のレコードに対応する

期待する結果

ID | Amount | PurchaseDate | ReturnDate
001 1 2023/1/1
001 1 2023/1/2 2023/1/3
001 2 2023/1/2 2023/1/4
002 2 2023/1/2 2023/1/4
002 1 2023/1/3

発生している問題・エラーメッセージ

購入と返品のテーブルで共通の重複しないキーがないため、日付や個数から推定して突き合わせるしかないのですが難しく困っています。
ご教示・アドバイス頂けると非常に助かります。

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

SQL Server 2016のため標準的なSQLしか利用できません

maisumakun

2023/05/29 00:33 編集

これらのテーブルにプライマリーキーはない、ということで間違いないですか？（特に、「同じ日に同じものを同じ個数だけ購入した」レコードが複数あると、それらは識別困難に陥ってしまいます）

neko_the_shadow

2023/05/29 01:01

Primary Keyはどのカラムですか?

kinoko

2023/05/29 02:17

ご確認ありがとうございます。Primary Keyがないため困っています。後からで申し訳ないですが、実際には日付と時刻のカラムがあります。（DateでなくDateTimeと思って頂ければと思います）時刻まで一致するケースはそうそうないため識別ができるのではないかと考えています。。

sazi

2023/05/29 10:45 編集

追加購入があり追加前の購入を返品するというようなオペレーションが想定されるなら、返品がPurchaseTableのどのデータであるか明示されていない限り無理でしょうね。

kinoko

2023/05/29 09:31

皆さんありがとうございます。色々試してみましてなんとかなりそうです。振り返りも含めて記載していますので、もし何か問題や簡単にできそうな点があればご指摘いただけますと幸いです。日付は見にくいのでH:mm:ssを省いていますが実際には入っています。テーブル名：PurchaseTable ID | Quantity | Date 001 1 2023/1/1 001 1 2023/1/2 001 2 2023/1/2 002 2 2023/1/2 002 1 2023/1/3 テーブル名：ReturnTable ID | Quantity| Date 001 1 2023/1/3 001 2 2023/1/4 002 2 2023/1/4 ①ReturnTableにRow_NumberでId,数量ごとに日付昇順に番号を割り振りました。 ROW_NUMBER() OVER(PARTITION BY p.Id, r.Quantity ORDER BY rDate) AS RowNumber イメージ ID | Quantity | rDate | RowNumber 001 1 2023/1/3 1 001 2 2023/1/4 1 002 2 2023/1/4 1 ②テンポラリーテーブルとして、下記のようにPurchaseDate <= ReturnDateという条件のもと日付降順に採番し、PurchaseTableを整形しました。これで返品対象となりうる購入レコードを抽出します。 WITH CTE AS ( SELECT p.Id, p.Quantity, p.pDate, ROW_NUMBER() OVER(PARTITION BY p.Id, p.Quantity ORDER BY pDate DESC) AS RowNumber FROM PurchaseTable p WHERE EXISTS (SELECT 1 FROM ReturnTable r WHERE p.pDate <= r.rDate) ) SELECT * FROM CTE を行うと下記のような結果になりました。 ID | Quantity | pDate | RowNumber 001 1 2023/1/2 1 001 1 2023/1/1 2 001 2 2023/1/3 1 002 2 2023/1/3 1 002 1 2023/1/2 1 ③最後にこれらのテーブルを結合しました。日付の条件、数量の条件、新たに追加した採番番号が一致するという条件のもとこれらを結合すると、採番番号が一致しているデータは返品できたということになります。 SELECT p.Id, p.Quantity, p.pDate, r.rDate, CTE.RowNumber, r.RowNumber FROM CTE INNER JOIN ReturnTable r ON CTE.Id = r.ID AND CTE.Quantity = ABS(r.Quantity) AND CTE.RowNumber = r.RowNumber RIGHT JOIN PurchaseTable p ON CTE.Id = p.ID AND CTE.Quantity = p.Quantity AND CTE.pDate = p.pDate 結果 ID | Quantity | pDate | rDate | CTE.RowNumber | r.RowNumber 001 1 (2023/1/1) NULL NULL NULL 001 1 (2023/1/2) (2023/1/3) 1 1 001 2 (2023/1/3) (2023/1/4) 1 1 002 2 (2023/1/2) (2023/1/4) 1 1 002 1 (2023/1/3) NULL NULL NULL 私が検証用に簡易的に作成したローカル環境では上手くいったのですが、テスト環境ではまだエラーがでるため何か問題があるかもしれませんが、問題のロジックは解決しているように見えました。何か問題、ご指摘あればぜひお願いいたします。

行動規範の内容に同意します

回答3件

通常購入と返品は同じテーブルで処理し、返品の数量をマイナスにすることで対応します
それにより購入が返品より先に処理されることが担保されます

投稿2023/05/29 00:35

yambejp

総合スコア114769

kinoko

2023/05/29 02:23

ご確認ありがとうございます。これらのテーブルは元のテーブルから抽出して作成したテーブルにあたります。元のテーブルでは購入と返品が同じテーブルにあり、返品の数量はマイナスで記録されています。最終的に行いたいこととしては、結局返品されずに売り上げとして計上できるデータのみを残したいのですが、こうした場合はどうでしょうか...？

maisumakun

2023/05/29 02:55

> これらのテーブルは元のテーブルから抽出して作成したテーブルにあたります。元のテーブルから集計したほうがやりやすい可能性もあります。

yambejp

2023/06/01 00:18

> 返品されずに売り上げとして計上できるデータのみを残したい考え方は以下のどちらか (1)プラス・マイナスの両方を集計し合計数量（金額）を売上として計上する (2)返品データに突合する売上データのユニークコード（主キーもしくは売上番号）を埋め込み消込作業をする

行動規範の内容に同意します

当方 SQL Server 2016 の環境がないので、エラーになるかもしれませんが、
Window関数が使用できるようなので

sql
1WITH CTE1 AS (
2SELECT
3  ID
4, Amount
5, Date
6, 1 AS FLG
7FROM PurchaseTable
8UNION ALL
9SELECT
10  ID
11, Amount
12, Date
13, 2 AS FLG
14FROM ReturnTable
15), CTE2 AS (
16SELECT *
17, LEAD(Date) OVER(PARTITION BY ID, Amount ORDER BY Date, FLG) AS Next_Date
18, LEAD(FLG)  OVER(PARTITION BY ID, Amount ORDER BY Date, FLG) AS Next_FLG
19FROM CTE1
20)
21SELECT ID
22, Amount
23, Date AS PurchaseDate
24, CASE WHEN Next_FLG=2 THEN Next_Date END AS ReturnDate
25FROM CTE2
26WHERE FLG = 1

参考までに

sql
1SELECT *
2FROM CTE2

の結果は

ID	Amount	Date	FLG	Next_Date	Next_FLG
1	1	2023/01/01	1	20230102	1
1	1	2023/01/02	1	20230103	2
1	1	2023/01/03	2	« NULL »	« NULL »
1	2	2023/01/02	1	20230104	2
1	2	2023/01/04	2	« NULL »	« NULL »
2	1	2023/01/03	1	« NULL »	« NULL »
2	2	2023/01/02	1	20230104	2
2	2	2023/01/04	2	« NULL »	« NULL »

投稿2023/05/31 01:13

takanaweb5

総合スコア358

takanaweb5

2023/05/31 01:22 編集

参考までにWEBアプリで実行出来ました https://dbfiddle.uk/QRUNBtAB

kinoko

2023/06/03 14:04

返信遅くなり申し訳ありません！記載頂いたクエリをSQL Fiddle上で一部データを変えながら試してみたところ、上手く紐づきがされないケースがありました😭　ですがまだよく理解できていないので解読した上でいろいろ試してみます！ありがとうございます！ https://dbfiddle.uk/9g9SqARi

takanaweb5

2023/06/03 22:22

@kinoko さんのサンプルデータでは、Id=001,Amount=1のデータは 2023-01-03 と 2023-01-04 の２回返却されていますが案１ ID Amount PurchaseDate ReturnDate 1 1 2023-01-01 2023-01-03 1 1 2023-01-02 2023-01-04 案２ ID Amount PurchaseDate ReturnDate 1 1 2023-01-01 2023-01-04 1 1 2023-01-02 2023-01-03 いずれが正しいのでしょうか？ >・日付が異なるが購入個数が同じレコードが複数ある場合は最も近い日付で返品されたとする ↓ ・日付が異なるが購入個数が同じレコードが複数ある場合は最も早く貸し出されたものが返却されたこととするという解釈でしょうか？

takanaweb5

2023/06/03 23:00

案１なら https://dbfiddle.uk/WW_OA9D3

takanaweb5

2023/06/03 23:34

案２なら https://dbfiddle.uk/abzWIIND

kinoko

2023/06/05 09:33

何度もご確認いただきありがとうございます。案2の解釈に近い...のかなと思います。上手く伝えられず申し訳ないのですが、下記のような購買返品フローをイメージしてもらえますと伝わるでしょうか... Aを購入した→問題なかった→もう一個同じAをリピート購入した→届いたAには汚れがあった→それを返品したなので例に当てはめると、案２が結果として出てほしいです。記載頂いているクエリを確認させていただきました。購入日 < 返品日という条件がありませんでしたが、自分の回答と似ていて安心しました。やはりROW_NUMBERを割り振って紐づけるしかないのかなという結論に至っています。一旦この方針で進めていこうと考えていますが、実は問題がまだ残っていまして下記のようなケースでは正しく紐づかないんですよね...（1-3の返品は1-2に、1-8の返品は1-4に紐づいてほしい） https://dbfiddle.uk/1RPwXvN4 ですがレアケースではありますし、できる限り正確に紐づけたいというのが元々の要件ですのでもう諦めようと考えています。もしこのあたりも何か良い解決策があればぜひ教えていただけると嬉しいです。

行動規範の内容に同意します

皆さんありがとうございます。色々試してみましてなんとかなりそうです。
振り返りも含めて記載していますので、もし何か問題や簡単にできそうな点があればご指摘いただけますと幸いです。
日付は見にくいのでH:mm:ssを省いていますが実際には入っています。

テーブル名：PurchaseTable
ID | Quantity | Date
001 1 2023/1/1
001 1 2023/1/2
001 2 2023/1/2
002 2 2023/1/2
002 1 2023/1/3

テーブル名：ReturnTable
ID | Quantity| Date
001 1 2023/1/3
001 2 2023/1/4
002 2 2023/1/4

①ReturnTableにRow_NumberでId,数量ごとに日付昇順に番号を割り振りました。
ROW_NUMBER() OVER(PARTITION BY p.Id, r.Quantity ORDER BY rDate) AS RowNumber
イメージ
ID | Quantity | rDate | RowNumber
001 1 2023/1/3 1
001 2 2023/1/4 1
002 2 2023/1/4 1

②テンポラリーテーブルとして、下記のようにPurchaseDate <= ReturnDateという条件のもと日付降順に採番し、PurchaseTableを整形しました。これで返品対象となりうる購入レコードを抽出します。
WITH CTE AS (
SELECT
p.Id,
p.Quantity,
p.pDate,
ROW_NUMBER() OVER(PARTITION BY p.Id, p.Quantity ORDER BY pDate DESC) AS RowNumber
FROM
PurchaseTable p
WHERE EXISTS (SELECT 1 FROM ReturnTable r WHERE p.pDate <= r.rDate)
)

SELECT * FROM CTE を行うと下記のような結果になりました。
ID | Quantity | pDate | RowNumber
001 1 2023/1/2 1
001 1 2023/1/1 2
001 2 2023/1/3 1
002 2 2023/1/3 1
002 1 2023/1/2 1

③最後にこれらのテーブルを結合しました。日付の条件、数量の条件、新たに追加した採番番号が一致するという条件のもとこれらを結合すると、採番番号が一致しているデータは返品できたということになります。

SELECT p.Id, p.Quantity, p.pDate, r.rDate, CTE.RowNumber, r.RowNumber FROM CTE

INNER JOIN ReturnTable r ON CTE.Id = r.ID AND CTE.Quantity = ABS(r.Quantity) AND CTE.RowNumber = r.RowNumber

RIGHT JOIN PurchaseTable p ON CTE.Id = p.ID AND CTE.Quantity = p.Quantity AND CTE.pDate = p.pDate

私が検証用に簡易的に作成したローカル環境では上手くいったのですが、テスト環境ではまだエラーがでるため何か問題があるかもしれませんが、問題のロジックは解決しているように見えました。
何か問題、ご指摘あればぜひお願いいたします。

投稿2023/05/29 09:46

kinoko

総合スコア0