質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
Amazon Athena

Amazon Athenaは、標準SQLを使用してAmazon S3内のデータを直接分析することができるインタラクティブなクエリサービスです。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

再帰

情報工学における再帰とは、プログラムのあるメソッドの処理上で自身のメソッドが再び呼び出されている処理の事をいいます。

AWS(Amazon Web Services)

Amazon Web Services (AWS)は、仮想空間を機軸とした、クラスター状のコンピュータ・ネットワーク・データベース・ストーレッジ・サポートツールをAWSというインフラから提供する商用サービスです。

解決済

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

DelphiumG
DelphiumG

総合スコア3

Amazon Athena

Amazon Athenaは、標準SQLを使用してAmazon S3内のデータを直接分析することができるインタラクティブなクエリサービスです。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

再帰

情報工学における再帰とは、プログラムのあるメソッドの処理上で自身のメソッドが再び呼び出されている処理の事をいいます。

AWS(Amazon Web Services)

Amazon Web Services (AWS)は、仮想空間を機軸とした、クラスター状のコンピュータ・ネットワーク・データベース・ストーレッジ・サポートツールをAWSというインフラから提供する商用サービスです。

1回答

0評価

0クリップ

218閲覧

投稿2022/04/26 02:45

編集2022/04/26 18:47

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


前提条件:

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

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

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

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

 <テーブルのイメージ>
A:カード会員情報:CardMembers
イメージ説明

 B:会員の売上明細テーブル: Sales
イメージ説明

求めたい集計結果

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

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

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

 イメージ説明

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

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

検討したこと

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

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

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

<サンプルSchema>

sql

--サンプル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文>
4世代前のカード番号まで遡って集計可能

sql

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)

 

良い質問の評価を上げる

以下のような質問は評価を上げましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

  • プログラミングに関係のない質問
  • やってほしいことだけを記載した丸投げの質問
  • 問題・課題が含まれていない質問
  • 意図的に内容が抹消された質問
  • 過去に投稿した質問と同じ内容の質問
  • 広告と受け取られるような投稿

評価を下げると、トップページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

y_waiwai

2022/04/26 02:54

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

2022/04/26 04:31

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

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

Amazon Athena

Amazon Athenaは、標準SQLを使用してAmazon S3内のデータを直接分析することができるインタラクティブなクエリサービスです。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

再帰

情報工学における再帰とは、プログラムのあるメソッドの処理上で自身のメソッドが再び呼び出されている処理の事をいいます。

AWS(Amazon Web Services)

Amazon Web Services (AWS)は、仮想空間を機軸とした、クラスター状のコンピュータ・ネットワーク・データベース・ストーレッジ・サポートツールをAWSというインフラから提供する商用サービスです。