過去1か月の購入金額の合計を追加したい

Question

前提・実現したいこと

UID	day	購入金額
001	2020/01/01	100
001	2020/01/02	200
001	2020/03/02	200
002	2020/01/02	200
002	2020/01/02	200
003	2020/01/02	200

のデータから右に各UIDの過去1か月の購入金額を追加したいです。
over句などを駆使しましたが、ほしい結果が実現できませんでした。

ほしい結果のイメージ

UID	day	購入金額	過去1か月の購入金額
001	2020/01/01	100	100
001	2020/01/02	200	300
001	2020/03/02	200	200
002	2020/01/02	100	100
002	2020/01/02	200	300
003	2020/01/02	200	200

補足情報（FW/ツールのバージョンなど）

amazon athenaを使用しています。
ドキュメントは基本prestoを参考にしてます。

Answer 1

PostgreSQLですが Z.DAY - interval '1 month' の部分をAmazon Athena用の方言に変えればいけるのではないでしょうか。

SELECT 
  UID
, DAY
, 購入金額
, (SELECT SUM(購入金額) FROM TBL AS A
                       WHERE A.DAY BETWEEN Z.DAY - interval '1 month' AND Z.DAY
                         AND A.UID = Z.UID) AS 過去1か月の購入金額
FROM TBL AS Z

---

追記　2021/11/27
当方Amazon Athenaを使用出来る環境にないのですが
マニュアルを見るところwindow関数が使えるようなので
window関数を使ったSQLは以下のようになると思われます。
ただし、RANGEフレームは使用出来ない可能性が高いと思います。

SELECT 
  UID
, DAY
, 購入金額
, SUM(購入金額) OVER (
    PARTITION BY UID
    ORDER BY DAY
    RANGE BETWEEN '1 month' PRECEDING AND CURRENT ROW
   ) AS 過去1か月の購入金額
FROM TBL

参考
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/presto-functions.html
https://masahikosawada.github.io/2018/07/07/Window-Frame/#rangeモード

Comments

[yukadayo21]

rangeはエラーが出てしまったので、先に回答いただいていたものを
(SELECT SUM(購入金額) FROM TBL AS A
WHERE A.DAY BETWEEN Z.DAY - interval '1' month AND Z.DAY
AND A.UID = Z.UID) AS 過去1か月の購入金額
とすることで算出できました