暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

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

askTom 2018-02-01
390

问题描述

你好,

我有一张有这种结构的表


表值:
(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
复制

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论