SQL: 再帰的に最新の会員番号をもとめて、一つの会員として売上を集計したい。

Question

困っていること：
AWS　Athena　SQL（presto）またはPostgreSQL v13 で下記のような集計をしたいと考えています。
カード会員情報テーブルから、各カード番号の最新カード番号を求めて、
最新カード番号で売上を集計したいのですが、当方のSQLの知見では、どのように求めてよいのかわからず、
方法についてご教授いただけましたら幸いです。


前提条件：

　・下記の２つのテーブルがあります。
A:ポイントカード会員の会員番号を格納した会員情報テーブル
B:会員の売上明細テーブル

　
・Aの会員番号テーブルはカードの紛失や再発行などで、会員のカード番号が切り替わることがあります。
その場合、新規にカード番号が追加され、切替前カード番号欄に、以前のカード番号が入力されます。

　　下記表の例では切替前後のカード番号に法則性があるようにも見えますが、実際には
切替前後のカード番号に規則性（先頭○桁目が同じだったり、前の番号の方が若いなど）はありません。
切り替わった後の旧カード番号のis_experedには、フラグ１が入ります。有効の場合はnullです。
切替え可能回数に制限はありません。

　・売上明細テーブル上のカード番号は、販売時点のカード番号のままです。

　＜テーブルのイメージ＞
A:カード会員情報：CardMembers

　B:会員の売上明細テーブル: Sales

求めたい集計結果

　単純にカード番号ごとに集計しただけだと、本来切替前後で同じ会員であるにも関わらず、カード番号がわかれているため、
別の会員番号ごとに集計されています。
これらをまとめて一つの会員番号（最新会員番号）ごとに集計したいと考えています。

※最終的に求めたい結果は下記③ですが、そのためには下記、①→②→③の順番で処理していく必要があると考えています。

　①カード会員情報テーブルの各カード番号に最新のカード番号を付与（切替前カード番号からたどって最新カード番号を更新）

　

　
②　Bの売上明細に、上記①をleft　join しての最新カード番号　を各明細に付与

　③上記テーブル②でセットした最新カード番号をもとにGROUP　BYして最新カード番号ごとに売上を集計する　

検討したこと

　上記①を以下に求めるかがポイントだとわわかるのですが、その方法がわかりません。
１世代、２世代、３世代などカード切替の世代が限定されている場合は自己結合で出来るかと思うのですが、
今回のように、会員によっては、世代がバラバラの場合、再帰的に世代をさかのぼる必要があると思われました。

私の思いつく範囲、調べた範囲で試してみたこととしては、下記のようなSQLになります。
下記の方法だと４世代前のカード番号までは遡って、集計出来るかと思いますが、
５世代、６世代とさかのぼるには、さらにLEFT　JOINを繰り返す必要があり
冗長なSQLとなってしまいます。
再帰的にCardMembersを参照するなどしてもっとシンプルなSQLにできないものかと考えています。

よりよいやりかたがございましたら、ご教授いただけましたら幸いです。

＜サンプルSchema＞

--サンプルSchema PostgreSQL v13 で試しています

CREATE TABLE CardMembers (
    card_no INTEGER NOT NULL PRIMARY KEY,
    prev_card_no INTEGER,
    is_expired INTEGER);

INSERT INTO CardMembers VALUES (10005,10004,NULL);
INSERT INTO CardMembers VALUES (10004,10003,1);
INSERT INTO CardMembers VALUES (10003,10002,1);
INSERT INTO CardMembers VALUES (10002,10001,1);
INSERT INTO CardMembers VALUES (10001,NULL,1);
INSERT INTO CardMembers VALUES (20002,20001,NULL);
INSERT INTO CardMembers VALUES (20001,NULL,1);


CREATE TABLE Sales(card_no INTEGER NOT NULL,
sales_amount INTEGER NOT NULL,
sales_date VARCHAR NOT NULL,
    PRIMARY KEY (card_no,sales_amount,sales_date));

INSERT INTO Sales VALUES (10005,300,'20220103');
INSERT INTO Sales VALUES (10004,500,'20220102');
INSERT INTO Sales VALUES (10003,200,'20220101');
INSERT INTO Sales VALUES  (10002,100,'20211231');
INSERT INTO Sales VALUES (10001,1000,'20221230');

INSERT INTO Sales VALUES (20002,300,'20211229');
INSERT INTO Sales VALUES (20001,400,'20211228');

＜試してみたSELECT文＞
４世代前のカード番号まで遡って集計可能

SELECT COALESCE(C4.card_no, C3.card_no, C2.card_no, C1.card_no, Sales.card_no) card_no
, SUM(Sales.sales_amount) sales_amount 
FROM Sales
LEFT JOIN CardMembers AS C1 ON Sales.card_no = C1.prev_card_no
LEFT JOIN CardMembers AS C2 ON C1.card_no = C2.prev_card_no
LEFT JOIN CardMembers AS C3 ON C2.card_no = C3.prev_card_no
LEFT JOIN CardMembers AS C4 ON C3.card_no = C4.prev_card_no
GROUP BY COALESCE( C4.card_no, C3.card_no, C2.card_no, C1.card_no, Sales.card_no)

　

Comments

[y_waiwai]

わからない、だけではなにがどうわからないのかわからないので教えようがないです
効率的でないやり方ならわかるんでしょうか。そのコードを提示しよう

[DelphiumG]

失礼いたしました。私の現時点で思いつく対応方法を記載して、再度ご教授を依頼させていただきたいと思います。

Answer 1

再帰クエリを活用されるとよいかと思います。

WITH RECURSIVE R (newest_card_no, card_no, prev_card_no) AS (
    SELECT card_no, card_no, prev_card_no 
    FROM CardMembers 
    WHERE is_expired IS NULL
  UNION ALL
    SELECT R.newest_card_no, C.card_no, C.prev_card_no
    FROM R
    JOIN CardMembers C ON R.prev_card_no = C.card_no
)
SELECT newest_card_no, SUM(sales_amount)
FROM R 
JOIN Sales S ON R.card_no = S.card_no
GROUP BY newest_card_no

Comments

[DelphiumG]

ありがとうございました！　まさに、求めていた方法です。
再帰クエリを使うことまでは、想像していたのですが、再帰クエリの動作がよく理解できていないため、
どのようにクエリをかけばよいかがわかっていませんでした。

ご教授いただいたクエリと、Qiitaのこちらの再帰SQLの解説で理解が深まりました。
https://qiita.com/Shoyu_N/items/f1786f99545fa5053b75

あらためまして大変ありがとうございました！