记录一个需求:基础数据为两个字段,表中只有两条数据,目的是想要将其他年月的数据补齐,补齐方式为:2月无数据就把1月的金额拿过来,以此类推,到了6月本身有一条数据,那么就将5月和6月的金额加起来存到6月的数据中,下面用图表说明一下
收入表:
ny(年月) | srje(收入金额) |
202301 | 100 |
202306 | 100 |
期望的结果表:
ny(年月) | srje(收入金额) |
202301 | 100 |
202302 | 100 |
202303 | 100 |
202304 | 100 |
202305 | 100 |
202306 | 200 |
202307 | 200 |
202308 | 200 |
202309 | 200 |
202310 | 200 |
202311 | 200 |
202312 | 200 |
接下实现:
1、构建基础表与辅助表
CREATE TABLE IF NOT EXISTS `JE`(
`ny` string COMMENT'年月',
`srje` string COMMENT'收入金额'
)comment'测试'partitioned by(
pt string
)stored as parquet
lifecycle 7;
--创建月份表
CREATE TABLE IF NOT EXISTS `NF`(
`ny` string COMMENT'年月'
)comment'年月表'partitioned by(
pt string
)stored as parquet
lifecycle 7;
2、构建测试数据
insert into JE values ('202306','100');
insert into JE values ('202301','100');
insert into NF values ('202301');
insert into NF values ('202302');
insert into NF values ('202303');
insert into NF values ('202304');
insert into NF values ('202305');
insert into NF values ('202306');
insert into NF values ('202307');
insert into NF values ('202308');
insert into NF values ('202309');
insert into NF values ('202310');
insert into NF values ('202311');
insert into NF values ('202312');
3、写SQL
select
t2.ny,
sum(t1.srje) over ( order by t2.ny rows between unbounded preceding and current row)
from Nf t2
left join JE t1
on t1.ny = t2.ny
order by t2.ny;
4、看数据,实现了这个效果
在这个中最主要的就是rows between unbounded preceding and current row,滚动统计
详情可以查一下