売上テーブルから集計用テーブルへ会員ごとの売上額を合算して更新したい

Question

売上テーブルから集計用テーブルへ会員ごとの売上額を合算して更新したい

売上テーブル（uriage_total)と集計用テーブル（rfm_analytics_raw_total）があります。

uriage_total

会員ID	購入日	商品コード	枝番	個数	金額
0001	2019/1/1	A001	01	2	10000
0001	2019/2/14	A002	01	3	15000
0002	2019/1/10	A001	01	2	8000
0002	2019/2/20	A002	01	3	15000
0002	2019/3/24	A002	01	3	10000

↑他にも項目がありますが、今回は不要と考えますので割愛します。

rfm_analytics_raw_total

会員ID	購入日	商品コード	枝番	個数	金額	集計_直近購入日	集計_回数	集計_総額	Mランク
0001	2019/1/1	A001	01	2	10000	2019/2/14	2
0001	2019/2/14	A002	01	3	15000	2019/2/14	2
0002	2019/1/10	A001	01	2	8000	2019/3/24	3
0002	2019/2/20	A002	01	3	15000	2019/3/24	3
0002	2019/3/24	A002	01	3	10000	2019/3/24	3

↑上記のuriage_totalに集計用のカラムを追加したものです。
他にも集計用にあとからupdateする対象となる項目がありますが、今回は不要ですので割愛します。

上記の例のように「集計_直近購入日」と「集計_回数」には値が入っています。
同じ値が複数のレコードに入っており、本来はrfm_analytics_raw_totalは正規化すべきでしょうが、今回は今までの流れからこのように正規化していない状態です。ご了承ください。

###実現したいこと
やりたいこととしては右側の集計_総額に会員IDごとの金額の合計を入れて、その金額の範囲に応じてMランクに１〜３の値を入れることです。
アウトプットとしては下記のようになります。

会員ID	購入日	商品コード	枝番	個数	金額	集計_直近購入日	集計_回数	集計_総額	Mランク
0001	2019/1/1	A001	01	2	10000	2019/2/14	2	25000	2
0001	2019/2/14	A002	01	3	15000	2019/2/14	2	25000	2
0002	2019/1/10	A001	01	2	8000	2019/3/24	3	33000	1
0002	2019/2/20	A002	01	3	15000	2019/3/24	3	33000	1
0002	2019/3/24	A002	01	3	10000	2019/3/24	3	33000	1

下記のSQLを実行したのですが、

update rfm_analytics_raw_total
set 集計_総額= t1.amt_total
from (
 select tt2.会員ID, sum(tt1.金額) as amt_total
 from uriage_total tt1
 join rfm_analytics_raw_total tt2 using(会員ID)
 group by tt2.会員ID
 ) as t1
where rfm_analytics_raw_total.会員ID = 会員ID;

下記のエラーが出てしまいました。

ERROR:  integer out of range
SQL 状態： 22003

なお、集計_直近購入日を更新するために下記のSQLを実行してこちらは成功しました。
（これをベースに上記のSQLにアレンジしました）

update rfm_analytics_raw_total
set 集計_直近購入日= t1.maxdate
from (
 select tt2.会員ID, max(tt1.予約日) as maxdate
 from uriage_total tt1
 join rfm_analytics_raw_total tt2 using(会員ID)
 group by tt2.会員ID
 ) as t1
where rfm_analytics_raw_total.会員ID = t1.会員ID;

###環境
macローカル　PostgreSQL 13.4(pgadmin4)
テーブルのレコード数はそれぞれ240万件ほど（レコード数は同じ）です。

Comments

[jhashimoto]

xxxxx（修正: 置換前のカラム名のためマスキング）は何のカラムですか？会員IDとは別のIDがあるのでしょうか？

[shintaro1001]

失礼しました。案件名を特定できないように「会員ID」として一般化していたのですが、置換漏れだったようです。ご指摘のカラム名はすべて「会員ID」です。できれば質問を削除していただき、見なかったことにしていただけると幸いです。

[jhashimoto]

質問を修正しました。

[jhashimoto]

回答してしまいましたが、お使いのDBMSの追記をお願いします。

Answer 1

sum(integer)の結果はbigintですので、integerに格納しようとしてのエラーだと思われます。

sum()の結果をintegerにcastして下さい。
9.21. 集約関数

sum ( smallint ) → bigint

sum ( integer ) → bigint
sum ( bigint ) → numeric
sum ( numeric ) → numeric
sum ( real ) → real
sum ( double precision ) → double precision
sum ( interval ) → interval
sum ( money ) → money

追記(訂正)

update rfm_analytics_raw_total tgt
set 集計_総額= src.amt_total
from (
 select tt2.会員ID, sum(tt1.金額) ::integer as amt_total
 from uriage_total tt1
      join rfm_analytics_raw_total tt2 using(会員ID)
 group by tt2.会員ID
 ) src
where tgt.会員ID = src.会員ID

追記2

select *
     , case when 集計_総額 >= 33000 then 1 
            when 集計_総額 < 33000 and 集計_総額 >= 25000 then 2 
            else 3 
       end as Mランク
from (
 select *
      , max(購入日) over(partition by 会員ID) as 集計_直近購入日
      , count(*)    over(partition by 会員ID) as 集計_回数
      , sum(金額)   over(partition by 会員ID) as 集計_総額
 from uriage_total
 ) step1

※rfm_analytics_raw_totalをデータとして確定したいのなら、上記を元にupsertすれば良いかと。

Comments

[shintaro1001]

ありがとうございます。
select tt2.会員ID, sum(cast(tt1.金額 as integer)) as amt_total
としてupdateを実行したところ、エラーは出ずに終了したのですが、対象のカラムがすべてnullになってしまいました。updateの内容自体もおかしいのでしょうか。
また、再度同じupdate文を実行したところ、前回と同じエラーが出てしまいました。
なにかおわかりになりますでしょうか。

[sazi]

castはsum()の結果に行わなければ意味がありません。
> 対象のカラムがすべてnullになってしまいました。updateの内容自体もおかしいのでしょうか。
結合の項目について参照先を明示してみてください。
※サンプル的に追記しました。

[shintaro1001]

> castはsum()の結果に行わなければ意味がありません。
ご指摘ありがとうございます。理解しました。

追記していただいたSQLのカラム名をリアルなものに変えて実行してみたのですが、下記のエラーが出てしまい、解消の仕方がわかりません。何度も申し訳ありませんが、ご教示いただけませんでしょうか。
ERROR: column "tgt" of relation "rfm_analytics_raw_total" does not exist
LINE 16: set tgt.集計_総額= src.amt_total
^
SQL 状態： 42703
文字： 388

[sazi]

asを付けすぎたかもしれませんので、回答を編集しました。
ただ、「「tgt」が「rfm_analytics_raw_total」のカラムに無い」というエラーなので、編集を誤っている可能性が高いです。
エラーの指摘は全文を見ないと何とも言えません。

[shintaro1001]

ありがとうございます。
> エラーの指摘は全文を見ないと何とも言えません。
ご提示いただいたSQLをコピーして「会員ID」だけをリアルなカラム名に変えて実行したのですが、同じエラーが出てしまいました。
このエラーに関する情報を調べていると↓の情報が得られたので、
https://qiita.com/kt215prg/items/55de78e057c99edcecab
> シンプルにカラム名の前のテーブル名が不要だったみたいです。

set tgt.集計_総額= src.amt_total
の行を
set 集計_総額= src.amt_total
に変えてbegin〜update実行したところ、エラーは発生しなかったものの、select文のオペレーションを誤ってしまったようで、rollbackして、再度実行したら、integer out of range のエラーが出るようになってしまいました。
何かおわかりになることありませんでしょうか。

[sazi]

sum()の結果がそもそもintegerの範囲をオーバーしているんではないでしょうか。
from句中のselectのみを実行して確認してみてください。
その場合、「集計_総額」自体がintegerでは不足という事になります。

[shintaro1001]

ご指南いただきありがとうございます。
確かにfrom句のなかのselect文だけを実行しても同じERROR: integer out of rangeのエラーが出ました。
そこで
sum(tt1.金額) ::integer as amt_total
を
sum(tt1.金額) ::bigint as amt_total
に変えたところ、値が返ってきました。
amt_totalをorder by desc して最高額を出したところ
amt_totalは約90億円となりました（そんなわけない…）が、uriage_totalでその会員IDの金額を合算した額は約1000万円（これはありえる）ですので、そもそもsumの仕方が間違っていたようです。。

何度も何度も申し訳ありませんが、当初の目的に立ち返り、rfm_analytics_raw_total の"集計_総額"に会員IDの金額を合算した額を入れる方法をご教授いただけませんでしょうか。
上記の例で言えば、合算した1000万円がrfm_analytics_raw_totalの会員IDの各レコードに入る形です。

よろしくお願いいたします。

[sazi]

> uriage_totalでその会員IDの金額を合算した額は約1000万円（これはありえる）ですので、そもそもsumの仕方が間違っていたようです。。

会員IDごとのSum()の方法が誤っているのではなくて、何度もリトライされているようですから、uriage_totalとrfm_analytics_raw_totalの件数が一致していないのではないでしょうか。

[sazi]

そもそも、rfm_analytics_raw_totalなどは作らずに、uriage_totalとWindow関数の組み合わせで求める結果は抽出できます。
※追記しました。

[shintaro1001]

> 会員IDごとのSum()の方法が誤っているのではなくて、何度もリトライされているようですから、
> uriage_totalとrfm_analytics_raw_totalの件数が一致していないのではないでしょうか。
レコードの件数は変わりありません。また、update文は都度beginしてから実行し、エラーが出たらrollbackしています。
> rfm_analytics_raw_totalなどは作らずに、
過去の経緯からこのあまりよろしくない仕様の集計用テーブルで集計してしまっていましたが、確かにまずは会員IDごとの合算をして、その範囲の分布を出したいです。
> uriage_totalとWindow関数の組み合わせで求める結果は抽出できると思いますが。
このあたりのスキルがなく自力で求めたい結果にたどり着けていない状況です。
ご指南いただけますと幸いです。

[sazi]

↑追記しています。

[shintaro1001]

ありがとうございました！
追記2で欲しいデータは取れました。
何度もお付き合いいただき申し訳ありませんでした。

Answer 2

エラーメッセージから、数値型が保持できる最大値を超過していると考えられます。集計_総額のデータ型と t1.amt_totalの最大値を確認してみてください。