質問編集履歴

8

検討してみたことの 補足説明を改修してSELECT文の内容にあうように修正しました。

2022/04/26 06:04

投稿

DelphiumG
DelphiumG

スコア3

test CHANGED
File without changes
test CHANGED
@@ -57,8 +57,8 @@
57
57
   今回のように、会員によっては、世代がバラバラの場合、再帰的に世代をさかのぼる必要があると思われました。
58
58
 
59
59
  私の思いつく範囲、調べた範囲で試してみたこととしては、下記のようなSQLになります。
60
- 下記の方法だと世代前のカード番号までは遡って、集計出来るかと思いますが、
60
+ 下記の方法だと世代前のカード番号までは遡って、集計出来るかと思いますが、
61
- 世代、世代、5世代とさかのぼるには、さらにLEFT JOINを繰り返す必要があり
61
+ 世代、世代とさかのぼるには、さらにLEFT JOINを繰り返す必要があり
62
62
  冗長なSQLとなってしまいます。
63
63
  再帰的にCardMembersを参照するなどしてもっとシンプルなSQLにできないものかと考えています。
64
64
 
@@ -99,7 +99,7 @@
99
99
  ```
100
100
 
101
101
  **<試してみたSELECT文>**
102
-  世代前のカード番号まで遡って集計可能
102
+  世代前のカード番号まで遡って集計可能
103
103
 
104
104
  ```sql PostgreSQL v13 で試しています
105
105
 

7

今回のサンプルケースに合うように、4世代前のカード番号まで遡れるようSELECT文を修正しました。

2022/04/26 06:03

投稿

DelphiumG
DelphiumG

スコア3

test CHANGED
File without changes
test CHANGED
@@ -103,12 +103,14 @@
103
103
 
104
104
  ```sql PostgreSQL v13 で試しています
105
105
 
106
- SELECT COALESCE(C2.card_no, C1.card_no, Sales.card_no) card_no
106
+ SELECT COALESCE(C4.card_no, C3.card_no, C2.card_no, C1.card_no, Sales.card_no) card_no
107
107
  , SUM(Sales.sales_amount) sales_amount
108
108
  FROM Sales
109
109
  LEFT JOIN CardMembers AS C1 ON Sales.card_no = C1.prev_card_no
110
110
  LEFT JOIN CardMembers AS C2 ON C1.card_no = C2.prev_card_no
111
+ LEFT JOIN CardMembers AS C3 ON C2.card_no = C3.prev_card_no
112
+ LEFT JOIN CardMembers AS C4 ON C3.card_no = C4.prev_card_no
111
- GROUP BY COALESCE(C2.card_no, C1.card_no, Sales.card_no)
113
+ GROUP BY COALESCE( C4.card_no, C3.card_no, C2.card_no, C1.card_no, Sales.card_no)
112
114
 
113
115
 
114
116
  ```

6

試してみたSELECT文に誤りがあったため修正 PostgreSQL v13 で検証済み

2022/04/26 05:37

投稿

DelphiumG
DelphiumG

スコア3

test CHANGED
File without changes
test CHANGED
@@ -101,14 +101,14 @@
101
101
  **<試してみたSELECT文>**
102
102
   2世代前のカード番号まで遡って集計可能
103
103
 
104
- ```sql
104
+ ```sql PostgreSQL v13 で試しています
105
105
 
106
- SELECT COALESCE(C2.prev_card_no, C1.card_no, Salse.card_no) card_no
106
+ SELECT COALESCE(C2.card_no, C1.card_no, Sales.card_no) card_no
107
- , SUM(Salse.sales_amount) sales_amount
107
+ , SUM(Sales.sales_amount) sales_amount
108
108
  FROM Sales
109
109
  LEFT JOIN CardMembers AS C1 ON Sales.card_no = C1.prev_card_no
110
- LEFT JOIN CardMembers AS C2 ON H1.card_no = C2.prev_card_no
110
+ LEFT JOIN CardMembers AS C2 ON C1.card_no = C2.prev_card_no
111
- GROUP BY COALESCE(H2.card_no, H1.card_no, Salse.card_no)
111
+ GROUP BY COALESCE(C2.card_no, C1.card_no, Sales.card_no)
112
112
 
113
113
 
114
114
  ```

5

サンプルSchemaにエラーがあったため修正(PostgresSQL v13で検証)

2022/04/26 05:33

投稿

DelphiumG
DelphiumG

スコア3

test CHANGED
File without changes
test CHANGED
@@ -1,5 +1,5 @@
1
1
  **困っていること:**
2
-   AWS Athena にて SQL(presto) で下記のような集計をしたいと考えています。
2
+   AWS Athena SQL(presto)またはPostgreSQL v13 で下記のような集計をしたいと考えています。
3
3
    カード会員情報テーブルから、各カード番号の最新カード番号を求めて、
4
4
    最新カード番号で売上を集計したいのですが、当方のSQLの知見では、どのように求めてよいのかわからず、
5
5
    方法についてご教授いただけましたら幸いです。
@@ -67,38 +67,35 @@
67
67
  **<サンプルSchema>**
68
68
 
69
69
  ```sql
70
- --サンプルSchema
70
+ --サンプルSchema PostgreSQL v13 で試しています
71
71
 
72
+ CREATE TABLE CardMembers (
72
- CREATE TABLE CardMembers(card_no INTEGER NOT NULL UNIQUE,
73
+ card_no INTEGER NOT NULL PRIMARY KEY,
73
- prev_card_no INTEGER,
74
+ prev_card_no INTEGER,
74
- is_expired INTEGER,
75
+ is_expired INTEGER);
75
- PRIMARY KEY (card_no));
76
76
 
77
+ INSERT INTO CardMembers VALUES (10005,10004,NULL);
78
+ INSERT INTO CardMembers VALUES (10004,10003,1);
79
+ INSERT INTO CardMembers VALUES (10003,10002,1);
80
+ INSERT INTO CardMembers VALUES (10002,10001,1);
77
- INSERT INTO CardMembers
81
+ INSERT INTO CardMembers VALUES (10001,NULL,1);
78
- VALUES
79
- (10005,10004,),
80
- (10004,10003,1),
81
- (10003,10002,1),
82
- (10002,10001,1),
83
- (10001,,1),
84
- (20002,20001,),
82
+ INSERT INTO CardMembers VALUES (20002,20001,NULL);
85
- (20001,,1);
83
+ INSERT INTO CardMembers VALUES (20001,NULL,1);
86
84
 
87
85
 
88
- CREATE TABLE Sals(card_no INTEGER NOT NULL,
86
+ CREATE TABLE Sales(card_no INTEGER NOT NULL,
89
87
  sales_amount INTEGER NOT NULL,
90
88
  sales_date VARCHAR NOT NULL,
91
89
  PRIMARY KEY (card_no,sales_amount,sales_date));
92
90
 
93
- INSERT INTO Sales
94
- VALUES
95
- (10005,300,'2022/1/3'),
91
+ INSERT INTO Sales VALUES (10005,300,'20220103');
96
- (10004,500,'2022/1/3'),
92
+ INSERT INTO Sales VALUES (10004,500,'20220102');
97
- (10003,200,'2022/1/3'),
93
+ INSERT INTO Sales VALUES (10003,200,'20220101');
98
- (10002,100,'2022/1/3'),
94
+ INSERT INTO Sales VALUES (10002,100,'20211231');
99
- (10001,1000,'2022/1/3'),
95
+ INSERT INTO Sales VALUES (10001,1000,'20221230');
96
+
100
- (20002,300,'2022/1/3'),
97
+ INSERT INTO Sales VALUES (20002,300,'20211229');
101
- (20001,400,'2022/1/3');
98
+ INSERT INTO Sales VALUES (20001,400,'20211228');
102
99
  ```
103
100
 
104
101
  **<試してみたSELECT文>**

4

検討したことについて、 各SQLに見出しをつけて見やすくしました。サンプルSQLで遡れる世代に誤りがあったため訂正しました。

2022/04/26 04:57

投稿

DelphiumG
DelphiumG

スコア3

test CHANGED
File without changes
test CHANGED
@@ -57,12 +57,14 @@
57
57
   今回のように、会員によっては、世代がバラバラの場合、再帰的に世代をさかのぼる必要があると思われました。
58
58
 
59
59
  私の思いつく範囲、調べた範囲で試してみたこととしては、下記のようなSQLになります。
60
- 下記の方法だと世代前のカード番号までは遡って、集計出来るかと思いますが、
60
+ 下記の方法だと世代前のカード番号までは遡って、集計出来るかと思いますが、
61
- 4世代、5世代とさかのぼるには、さらにLEFT JOINを繰り返す必要があり
61
+ 3世代、4世代、5世代とさかのぼるには、さらにLEFT JOINを繰り返す必要があり
62
62
  冗長なSQLとなってしまいます。
63
63
  再帰的にCardMembersを参照するなどしてもっとシンプルなSQLにできないものかと考えています。
64
64
 
65
65
  よりよいやりかたがございましたら、ご教授いただけましたら幸いです。
66
+
67
+ **<サンプルSchema>**
66
68
 
67
69
  ```sql
68
70
  --サンプルSchema
@@ -99,6 +101,9 @@
99
101
  (20001,400,'2022/1/3');
100
102
  ```
101
103
 
104
+ **<試してみたSELECT文>**
105
+  2世代前のカード番号まで遡って集計可能
106
+
102
107
  ```sql
103
108
 
104
109
  SELECT COALESCE(C2.prev_card_no, C1.card_no, Salse.card_no) card_no

3

サンプルのDDLを追記いたしました。

2022/04/26 04:40

投稿

DelphiumG
DelphiumG

スコア3

test CHANGED
File without changes
test CHANGED
@@ -65,6 +65,41 @@
65
65
  よりよいやりかたがございましたら、ご教授いただけましたら幸いです。
66
66
 
67
67
  ```sql
68
+ --サンプルSchema
69
+
70
+ CREATE TABLE CardMembers(card_no INTEGER NOT NULL UNIQUE,
71
+ prev_card_no INTEGER,
72
+ is_expired INTEGER,
73
+ PRIMARY KEY (card_no));
74
+
75
+ INSERT INTO CardMembers
76
+ VALUES
77
+ (10005,10004,),
78
+ (10004,10003,1),
79
+ (10003,10002,1),
80
+ (10002,10001,1),
81
+ (10001,,1),
82
+ (20002,20001,),
83
+ (20001,,1);
84
+
85
+
86
+ CREATE TABLE Sals(card_no INTEGER NOT NULL,
87
+ sales_amount INTEGER NOT NULL,
88
+ sales_date VARCHAR NOT NULL,
89
+ PRIMARY KEY (card_no,sales_amount,sales_date));
90
+
91
+ INSERT INTO Sales
92
+ VALUES
93
+ (10005,300,'2022/1/3'),
94
+ (10004,500,'2022/1/3'),
95
+ (10003,200,'2022/1/3'),
96
+ (10002,100,'2022/1/3'),
97
+ (10001,1000,'2022/1/3'),
98
+ (20002,300,'2022/1/3'),
99
+ (20001,400,'2022/1/3');
100
+ ```
101
+
102
+ ```sql
68
103
 
69
104
  SELECT COALESCE(C2.prev_card_no, C1.card_no, Salse.card_no) card_no
70
105
  , SUM(Salse.sales_amount) sales_amount

2

これまで自分で検討したことについて、具体例を追記いたしました。

2022/04/26 04:38

投稿

DelphiumG
DelphiumG

スコア3

test CHANGED
File without changes
test CHANGED
@@ -54,7 +54,26 @@
54
54
 
55
55
   上記①を以下に求めるかがポイントだとわわかるのですが、その方法がわかりません。
56
56
   1世代、2世代、3世代などカード切替の世代が限定されている場合は自己結合で出来るかと思うのですが、
57
-  今回のように、会員によっては、世代がバラバラの場合、再帰的に世代をさかのぼる必要があると思われまして、
57
+  今回のように、会員によっては、世代がバラバラの場合、再帰的に世代をさかのぼる必要があると思われました。
58
+
59
+ 私の思いつく範囲、調べた範囲で試してみたこととしては、下記のようなSQLになります。
60
+ 下記の方法だと3世代前のカード番号までは遡って、集計出来るかと思いますが、
61
+ 4世代、5世代とさかのぼるには、さらにLEFT JOINを繰り返す必要があり
62
+ 冗長なSQLとなってしまいます。
63
+ 再帰的にCardMembersを参照するなどしてもっとシンプルなSQLにできないものかと考えています。
64
+
58
-  効率的?なやりかたがございましたら、ご教授いただけましたら幸いです。
65
+ よりよいやりかたがございましたら、ご教授いただけましたら幸いです。
66
+
67
+ ```sql
68
+
69
+ SELECT COALESCE(C2.prev_card_no, C1.card_no, Salse.card_no) card_no
70
+ , SUM(Salse.sales_amount) sales_amount
71
+ FROM Sales
72
+ LEFT JOIN CardMembers AS C1 ON Sales.card_no = C1.prev_card_no
73
+ LEFT JOIN CardMembers AS C2 ON H1.card_no = C2.prev_card_no
74
+ GROUP BY COALESCE(H2.card_no, H1.card_no, Salse.card_no)
75
+
76
+
77
+ ```
59
78
   
60
79
 

1

タイトルと 本文の内容に相違があったため、タイトルを修正しました。

2022/04/26 02:59

投稿

DelphiumG
DelphiumG

スコア3

test CHANGED
@@ -1 +1 @@
1
- SQL: 再帰的に過去の会員番号をもとめて、一つの会員として売上を集計したい。
1
+ SQL: 再帰的に最新の会員番号をもとめて、一つの会員として売上を集計したい。
test CHANGED
File without changes