sql - Presto 中按 id 和按月的累积总和

Question

在 Amazon Athena 中，我有一张如下所示的表:

id   amount date
1    100    2018-04-05
1    50     2018-06-18
2    10     2018-04-23
2    100    2018-04-28
2    50     2018-07-07
2    10     2018-08-08

我想要这样的结果

id   cum_sum date
1    100    2018-04
1    100    2018-05
1    150    2018-06
1    150    2018-07
1    150    2018-08
2    110    2018-04
2    110    2018-05
2    110    2018-06 
2    160    2018-07
2    170    2018-08

因此，我希望每个月末(每月最后一天)每个 ID 的累计金额。我知道如何逐月进行，但不是在一个查询中。

另一个问题也成为填充空月份(即 ID 1 没有所有月份的条目，因此必须重复使用累积总和)。

如果还有针对 MySQL 的解决方案，我也将不胜感激。

我希望这是有道理的，并提前致谢。

Answer 1

您可以在 PrestoDB 中使用窗口函数。您可以生成日期。列出这些也很简单:

with months as (
      selecct '2018-04-01' as yyyy_mm union all    -- use the first of the month
      select '2018-05-01' union all
      select '2018-06-01' union all
      select '2018-07-01' union all
      select '2018-08-01'
     )
select i.id, m.yyyy_mm, sum(t.amt) as month_amount,
       sum(sum(t.amt)) over (partition by i.id order by m.yyyy_mm) as cumulative_amt
from (select distinct id from t) i cross join
     months m left join
     t
     on t.id = i.id and
        t.date >= m.yyyy_mm and
        t.date < m.yyyy_mm + interval '1 day'
group by i.id, m.yyyy_mm
order by i.id, m.yyyy_mm;

这也应该适用于 MySQL 8.0。在早期版本中，您需要变量或相关子查询。第一个在 PrestoDB 中不起作用。第二个可能有更糟糕的性能。