数据准备

CREATE TABLE IF NOT EXISTS index_date_value
(
    `dt` date NOT NULL COMMENT "日期",
    `amt` DECIMAL(9,2) COMMENT "金额"
)
UNIQUE KEY(`dt`)
DISTRIBUTED BY HASH(`dt`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"enable_unique_key_merge_on_write" = "true"
);

insert into index_date_value values('2024-01-01',1.0);
insert into index_date_value values('2024-01-02',2.0);
insert into index_date_value values('2024-01-03',2.0);
insert into index_date_value values('2024-01-04',2.0);
insert into index_date_value values('2024-01-05',3.0);
insert into index_date_value values('2024-01-06',3.0);
insert into index_date_value values('2024-01-07',3.0);

insert into index_date_value values('2024-02-01',2.0);
insert into index_date_value values('2024-02-02',2.0);
insert into index_date_value values('2024-02-03',3.0);
insert into index_date_value values('2024-02-04',4.0);
insert into index_date_value values('2024-02-05',5.0);
insert into index_date_value values('2024-02-06',6.0);

查询SQL

select 
    dt,amt, DATE_FORMAT(dt,'%Y-%m') as dm,sum(amt) over( 
    partition by DATE_FORMAT(dt,'%Y-%m') order by dt 
    )   as sum_amt
from index_date_value
order by dt;

结果

dt	amt	dm	sum_amt
2024-01-01	1.00	2024-01	1.00
2024-01-02	2.00	2024-01	3.00
2024-01-03	2.00	2024-01	5.00
2024-01-04	2.00	2024-01	7.00
2024-01-05	3.00	2024-01	10.00
2024-01-06	3.00	2024-01	13.00
2024-01-07	3.00	2024-01	16.00
2024-02-01	2.00	2024-02	2.00
2024-02-02	2.00	2024-02	4.00
2024-02-03	3.00	2024-02	7.00
2024-02-04	4.00	2024-02	11.00
2024-02-05	5.00	2024-02	16.00
2024-02-06	6.00	2024-02	22.00

说明

我们使用 DATE_FORMAT(dt,'%Y-%m') as dm 实现的日期格式化为月份，增加了一列，用来做窗口里的 partition字段。这样就能够按月来累计这一个月里 每一天的累计值。窗口里 按 日期排序，就能保证日期是从小到大的。再用 sum函数来求和，就能实现 累计值的计算了。