项目数据及累计预提

Question

我正在尝试将累积数据集的最后一个值向前推到当前时间。

初始化测试数据:

drop table if exists test_table;
create table test_table
as select data_date::date, floor(random() * 10) as data_value
from
generate_series('2021-08-25'::date, '2021-08-31'::date, '1 day') data_date;

上面的测试数据产生如下内容:

data_date    data_value   cumulative_value
2021-08-25       1               1
2021-08-26       7               8
2021-08-27       8              16
2021-08-28       7              23
2021-08-29       2              25
2021-08-30       2              27
2021-08-31       7              34

我想做的是将最后一个数据值(2021-08-31 7)向前推到当前时间。例如，假设今天的日期是20121-09-03，我希望结果是这样的:

data_date    data_value   cumulative_value
2021-08-25       1               1
2021-08-26       7               8
2021-08-27       8              16
2021-08-28       7              23
2021-08-29       2              25
2021-08-30       2              27
2021-08-31       7              34
2021-09-01       7              41
2021-09-02       7              48
2021-09-03       7              55

Answer 1

您需要获得表中最后日期的值。公共表表达式是一个很好的方法:

with cte as (
select data_value as last_val
from test_table 
order by data_date desc 
limit 1)
select 
gen_date::date as data_date, 
coalesce(data_value, last_val) as data_value, 
sum(coalesce(data_value, last_val)) over (order by gen_date) as cumulative_sum
from generate_series('2021-08-25'::date, '2021-09-03', '1 day') as gen_date
left join test_table on gen_date = data_date
cross join cte

在db<>fiddle中测试。

Answer 2

您可以使用union和标量子查询来查找新行data_value的最新值。cumulative_value重估

select *, sum(data_value) over (rows between unbounded preceding and current row) as cumulative_value
from 
(
select data_date, data_value from test_table
UNION all 
select rd, (select data_value from test_table where data_date = '2021-08-31')
from generate_series('2021-09-01'::date, '2021-09-03', '1 day') rd
) t 
order by data_date;

这里没有固定的日期文字，它更聪明一些。

with cte(latest_date) as (select max(data_date) from test_table) 
select *, sum(data_value) over (rows between unbounded preceding and current row) as cumulative_value
from 
(
select data_date, data_value from test_table
UNION ALL 
select rd::date, (select data_value from test_table, cte where data_date = latest_date)
from generate_series((select latest_date from cte) + 1, CURRENT_DATE, '1 day') rd
) t 
order by data_date;

SQL Fiddle here.