Oracle SQL请求与分析填充以前的值

你好，

我有一张有这种结构的表


表值:

(code varchar2(10),
date_value date,
value number);

示例数据:

'Code1','15/03/2017',25000
'Code1','06/06/2017',26000
'Code1','18/07/2017',29000
'Code1','21/07/2017',3000
'Code2','18/07/2017',89000
'Code1','05/10/2017',35000

我还有另一个表 “日历”，其中包含日期 (从2000到2999的实例，1行/天)

table calendar
(cal_date date);

为了创建一个图形，我想有一个月的总和，所有月份的空值bi填充以前的非空月值。例如，结果将是:

01/2017 'Code1' (null)
02/2017 'Code1' (null)
03/2017 'Code1' 25000
04/2017 'Code1' 25000
05/2017 'Code1' 25000
06/2017 'Code1' 26000
07/2017 'Code1' 31000
08/2017 'Code1' 31000
09/2017 'Code1' 31000
10/2017 'Code1' 35000
11/2017 'Code1' 35000
12/2017 'Code1' 35000

01/2017 'Code2' (null)
02/2017 'Code2' (null)
03/2017 'Code2' (null)
04/2017 'Code2' (null)
05/2017 'Code2' (null)
06/2017 'Code2' (null)
07/2017 'Code2' 89000
08/2017 'Code2' 89000
09/2017 'Code2' 89000
10/2017 'Code2' 89000
11/2017 'Code2' 89000
12/2017 'Code2' 89000

使用分析，我认为使用超前/滞后，但使用 “前一行”，因为在某些情况下，前一个值为空，但 “第二” 前一个不是。

有什么想法吗？

这里有几件事你需要做:

1.生成每个月的行和代码
2.填写 “缺失” 值

对于步骤1，您可以使用分区的外部联接。这看起来像:

left join t partition by (t.column)

对于内部表中的每一行，这将为 “按分区” 列中的每个值生成一组新的值。在您的示例中，日历中的每一行对于每个代码值都会出现一次。

要填充缺失的值，请将last_value与ignore null子句一起使用。这将根据分区和order by子句查找最后一个非null值。

总的来说，这看起来像:

create table t (
  code varchar2(10),
  date_value date,
  val number
);

alter session set nls_date_format = 'dd/mm/yyyy';

insert into t values ('Code1','15/03/2017',25000);
insert into t values ('Code1','06/06/2017',26000);
insert into t values ('Code1','18/07/2017',29000);
insert into t values ('Code1','21/07/2017',3000);
insert into t values ('Code2','18/07/2017',89000);
insert into t values ('Code1','05/10/2017',35000);

with dates as (
  select add_months(date'2017-01-01', level-1) dt 
  from   dual
  connect by level <= 12
), monthly_tots as (
  select d.dt, t.code,
         sum(t.val) sm
  from   dates d
  left join t partition by (t.code)
  on     d.dt <= t.date_value 
  and    t.date_value < add_months(d.dt, 1)
  group  by d.dt, t.code
)
  select dt, code,  
         last_value(sm) ignore nulls over (
           partition by code order by dt
         ) val
  from   monthly_tots;

DT           CODE    VAL      
01/01/2017   Code1      
01/02/2017   Code1      
01/03/2017   Code1      25000 
01/04/2017   Code1      25000 
01/05/2017   Code1      25000 
01/06/2017   Code1      26000 
01/07/2017   Code1      32000 
01/08/2017   Code1      32000 
01/09/2017   Code1      32000 
01/10/2017   Code1      35000 
01/11/2017   Code1      35000 
01/12/2017   Code1      35000 
01/01/2017   Code2      
01/02/2017   Code2      
01/03/2017   Code2      
01/04/2017   Code2      
01/05/2017   Code2      
01/06/2017   Code2      
01/07/2017   Code2      89000 
01/08/2017   Code2      89000 
01/09/2017   Code2      89000 
01/10/2017   Code2      89000 
01/11/2017   Code2      89000 
01/12/2017   Code2      89000