mysql - 如何使用 sql 和按日期分组显示指标随日期的累积增长？

Question

我想显示某一日期内指标的累积增长，同时还按日期进行分组，以便该日期仅在表中出现一次

我有以下数据集:

data = [{'date':'2016-01-01','value':'1'},{'date':'2016-01-01','value':'3'},{'date':'2016-01-02','value':'3'},{'date':'2016-01-02','value':'2'}]

以下查询生成下表:

select 
date, 
sum(value) over (order by date rows unbounded preceding) as value
from data
group by 
date, 
value

下面是我的表格

date        value       
2016-01-01  1   
2016-01-01  4
2016-01-02  7    
2016-01-02  9

但是，我想生成下表:

date        value       
2016-01-01  4   
2016-01-02  9

当我尝试以下查询并且不按值分组时，出现以下错误:

select 
date, 
sum(value) over (order by date rows unbounded preceding) as value
from data
group by 
date

错误:

column "value" must appear in the GROUP BY clause or be used in an aggregate function

Answer 1

也许可以在计算运行总计之前按日期对值进行求和？

select date, 
    sum(value) over (order by date) as value
from (
    select date, sum(value) as value
    from data
    group by date
) t