sql - 当字段的总和等于一个值时获取最大日期

Question

我在编写查询时遇到问题。
行数据如下：

DATE        CUSTOMER_ID AMOUNT
20170101    1           150
20170201    1           50
20170203    1           200
20170204    1           250
20170101    2           300
20170201    2           70

我想知道每个 customer_id 的金额总和何时（哪个日期）超过 350，如何编写此查询以获得这样的结果？

CUSTOMER_ID   MAX_DATE
1             20170203
2             20170201

谢谢，

标签： sql

Answer 1

只需使用 ANSI/ISO 标准窗口函数来计算运行总和：

select t.*
from (select t.*,
             sum(t.amount) over (partition by t.customer_id order by t.date) as running_amount
      from t
     ) t
where running_amount - amount < 350 and
      running_amount >= 350;

如果由于某种原因，您的数据库不支持此功能，您可以使用相关子查询：

select t.*
from (select t.*,
             (select sum(t2.amount)
              from t t2
              where t2.customer_id =  t.customer_id and
                    t2.date <= t.date
             ) as running_amount
      from t
     ) t
where running_amount - amount < 350 and
      running_amount >= 350;