回答編集履歴
5
追記
answer
CHANGED
|
@@ -23,4 +23,22 @@
|
|
|
23
23
|
group by tt2.会員ID
|
|
24
24
|
) src
|
|
25
25
|
where tgt.会員ID = src.会員ID
|
|
26
|
-
```
|
|
26
|
+
```
|
|
27
|
+
|
|
28
|
+
追記2
|
|
29
|
+
--
|
|
30
|
+
```SQL
|
|
31
|
+
select *
|
|
32
|
+
, case when 集計_総額 >= 33000 then 1
|
|
33
|
+
when 集計_総額 < 33000 and 集計_総額 >= 25000 then 2
|
|
34
|
+
else 3
|
|
35
|
+
end as Mランク
|
|
36
|
+
from (
|
|
37
|
+
select *
|
|
38
|
+
, max(購入日) over(partition by 会員ID) as 集計_直近購入日
|
|
39
|
+
, count(*) over(partition by 会員ID) as 集計_回数
|
|
40
|
+
, sum(金額) over(partition by 会員ID) as 集計_総額
|
|
41
|
+
from uriage_total
|
|
42
|
+
) step1
|
|
43
|
+
```
|
|
44
|
+
※rfm_analytics_raw_totalをデータとして確定したいのなら、上記を元にupsertすれば良いかと。
|
4
訂正
answer
CHANGED
|
@@ -11,11 +11,11 @@
|
|
|
11
11
|
sum ( interval ) → interval
|
|
12
12
|
sum ( money ) → money
|
|
13
13
|
|
|
14
|
-
追記
|
|
14
|
+
追記(訂正)
|
|
15
15
|
--
|
|
16
16
|
```SQL
|
|
17
17
|
update rfm_analytics_raw_total tgt
|
|
18
|
-
set
|
|
18
|
+
set 集計_総額= src.amt_total
|
|
19
19
|
from (
|
|
20
20
|
select tt2.会員ID, sum(tt1.金額) ::integer as amt_total
|
|
21
21
|
from uriage_total tt1
|
3
推敲
answer
CHANGED
|
@@ -14,13 +14,13 @@
|
|
|
14
14
|
追記
|
|
15
15
|
--
|
|
16
16
|
```SQL
|
|
17
|
-
update rfm_analytics_raw_total
|
|
17
|
+
update rfm_analytics_raw_total tgt
|
|
18
18
|
set tgt.集計_総額= src.amt_total
|
|
19
19
|
from (
|
|
20
20
|
select tt2.会員ID, sum(tt1.金額) ::integer as amt_total
|
|
21
21
|
from uriage_total tt1
|
|
22
22
|
join rfm_analytics_raw_total tt2 using(会員ID)
|
|
23
23
|
group by tt2.会員ID
|
|
24
|
-
)
|
|
24
|
+
) src
|
|
25
25
|
where tgt.会員ID = src.会員ID
|
|
26
26
|
```
|
2
追記
answer
CHANGED
|
@@ -9,4 +9,18 @@
|
|
|
9
9
|
sum ( real ) → real
|
|
10
10
|
sum ( double precision ) → double precision
|
|
11
11
|
sum ( interval ) → interval
|
|
12
|
-
sum ( money ) → money
|
|
12
|
+
sum ( money ) → money
|
|
13
|
+
|
|
14
|
+
追記
|
|
15
|
+
--
|
|
16
|
+
```SQL
|
|
17
|
+
update rfm_analytics_raw_total as tgt
|
|
18
|
+
set tgt.集計_総額= src.amt_total
|
|
19
|
+
from (
|
|
20
|
+
select tt2.会員ID, sum(tt1.金額) ::integer as amt_total
|
|
21
|
+
from uriage_total tt1
|
|
22
|
+
join rfm_analytics_raw_total tt2 using(会員ID)
|
|
23
|
+
group by tt2.会員ID
|
|
24
|
+
) as src
|
|
25
|
+
where tgt.会員ID = src.会員ID
|
|
26
|
+
```
|
1
追記
answer
CHANGED
|
@@ -1,3 +1,12 @@
|
|
|
1
|
-
sum()の結果はbigintですので、integerに格納
|
|
1
|
+
sum(integer)の結果はbigintですので、integerに格納しようとしてのエラーだと思われます。
|
|
2
2
|
|
|
3
|
-
sum()の結果をintegerにcastして下さい。
|
|
3
|
+
sum()の結果をintegerにcastして下さい。
|
|
4
|
+
[9.21. 集約関数](https://www.postgresql.jp/document/13/html/functions-aggregate.html)
|
|
5
|
+
> sum ( smallint ) → bigint
|
|
6
|
+
sum ( integer ) → bigint
|
|
7
|
+
sum ( bigint ) → numeric
|
|
8
|
+
sum ( numeric ) → numeric
|
|
9
|
+
sum ( real ) → real
|
|
10
|
+
sum ( double precision ) → double precision
|
|
11
|
+
sum ( interval ) → interval
|
|
12
|
+
sum ( money ) → money
|