質問編集履歴
8
検討してみたことの 補足説明を改修してSELECT文の内容にあうように修正しました。
test
CHANGED
File without changes
|
test
CHANGED
@@ -57,8 +57,8 @@
|
|
57
57
|
今回のように、会員によっては、世代がバラバラの場合、再帰的に世代をさかのぼる必要があると思われました。
|
58
58
|
|
59
59
|
私の思いつく範囲、調べた範囲で試してみたこととしては、下記のようなSQLになります。
|
60
|
-
下記の方法だと
|
60
|
+
下記の方法だと4世代前のカード番号までは遡って、集計出来るかと思いますが、
|
61
|
-
|
61
|
+
5世代、6世代とさかのぼるには、さらにLEFT JOINを繰り返す必要があり
|
62
62
|
冗長なSQLとなってしまいます。
|
63
63
|
再帰的にCardMembersを参照するなどしてもっとシンプルなSQLにできないものかと考えています。
|
64
64
|
|
@@ -99,7 +99,7 @@
|
|
99
99
|
```
|
100
100
|
|
101
101
|
**<試してみたSELECT文>**
|
102
|
-
|
102
|
+
4世代前のカード番号まで遡って集計可能
|
103
103
|
|
104
104
|
```sql PostgreSQL v13 で試しています
|
105
105
|
|
7
今回のサンプルケースに合うように、4世代前のカード番号まで遡れるようSELECT文を修正しました。
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 で検証済み
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.
|
106
|
+
SELECT COALESCE(C2.card_no, C1.card_no, Sales.card_no) card_no
|
107
|
-
, SUM(Sals
|
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
|
110
|
+
LEFT JOIN CardMembers AS C2 ON C1.card_no = C2.prev_card_no
|
111
|
-
GROUP BY COALESCE(
|
111
|
+
GROUP BY COALESCE(C2.card_no, C1.card_no, Sales.card_no)
|
112
112
|
|
113
113
|
|
114
114
|
```
|
5
サンプルSchemaにエラーがあったため修正(PostgresSQL v13で検証)
test
CHANGED
File without changes
|
test
CHANGED
@@ -1,5 +1,5 @@
|
|
1
1
|
**困っていること:**
|
2
|
-
AWS Athena
|
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
|
-
|
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
|
-
|
82
|
+
INSERT INTO CardMembers VALUES (20002,20001,NULL);
|
85
|
-
|
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
|
-
|
91
|
+
INSERT INTO Sales VALUES (10005,300,'20220103');
|
96
|
-
|
92
|
+
INSERT INTO Sales VALUES (10004,500,'20220102');
|
97
|
-
|
93
|
+
INSERT INTO Sales VALUES (10003,200,'20220101');
|
98
|
-
|
94
|
+
INSERT INTO Sales VALUES (10002,100,'20211231');
|
99
|
-
|
95
|
+
INSERT INTO Sales VALUES (10001,1000,'20221230');
|
96
|
+
|
100
|
-
|
97
|
+
INSERT INTO Sales VALUES (20002,300,'20211229');
|
101
|
-
|
98
|
+
INSERT INTO Sales VALUES (20001,400,'20211228');
|
102
99
|
```
|
103
100
|
|
104
101
|
**<試してみたSELECT文>**
|
4
検討したことについて、 各SQLに見出しをつけて見やすくしました。サンプルSQLで遡れる世代に誤りがあったため訂正しました。
test
CHANGED
File without changes
|
test
CHANGED
@@ -57,12 +57,14 @@
|
|
57
57
|
今回のように、会員によっては、世代がバラバラの場合、再帰的に世代をさかのぼる必要があると思われました。
|
58
58
|
|
59
59
|
私の思いつく範囲、調べた範囲で試してみたこととしては、下記のようなSQLになります。
|
60
|
-
下記の方法だと
|
60
|
+
下記の方法だと2世代前のカード番号までは遡って、集計出来るかと思いますが、
|
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を追記いたしました。
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
これまで自分で検討したことについて、具体例を追記いたしました。
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
タイトルと 本文の内容に相違があったため、タイトルを修正しました。
test
CHANGED
@@ -1 +1 @@
|
|
1
|
-
SQL: 再帰的に
|
1
|
+
SQL: 再帰的に最新の会員番号をもとめて、一つの会員として売上を集計したい。
|
test
CHANGED
File without changes
|