用最后一个非空值填充空值-Oracle SQL [英] Fill null values with last non-null amount - Oracle SQL

问题描述

我有一个包含4列的表格:项目，年份，月份，金额. Amount的某些值是null，当发生这种情况时，我想用以前的非null的Amount值来填充这些值.当只有一个空值时，我可以使用LAG函数轻松地做到这一点，但是当连续有多个空值时，我不确定如何处理它.下面是一个表的示例，其中添加了要在查询中添加的内容的列:

I have a table that has 4 columns: Item, Year, Month, Amount. Some of the values for Amount are null and when that happens I want to fill those values in with the previous Amount value that is not null. I can easily do this with the LAG function when there is only one null value but when there are multiple in a row I am not sure how to approach it. Below is an example of what the table might look like with an added column for what I want to add in my query:

Item | Year | Month | Amount | New_Amount
AAA  | 2013 | 01    | 100    | 100
AAA  | 2013 | 02    |        | 100
AAA  | 2013 | 03    | 150    | 150
AAA  | 2013 | 04    | 125    | 125
AAA  | 2013 | 05    |        | 125
AAA  | 2013 | 06    |        | 125
AAA  | 2013 | 07    |        | 125
AAA  | 2013 | 08    | 175    | 175

我有两个想法，我似乎无法将其付诸实践以产生自己想要的东西.首先，我要使用LAG，但后来我注意到，当连续有多个空值时，它就不能满足要求.接下来，我将使用FIRST_VALUE，但这在这种情况下无济于事，在这种情况下，将有一个null，然后是值，然后是更多的null.有没有办法使用FIRST_VALUE或其他类似的函数来检索最后一个非空值?

I had two ideas which I can't seem to get to work to produce what I want. First I was going to use LAG but then I noticed when there are multiple null values in a row it won't satisfy that. Next I was going to use FIRST_VALUE but that wouldn't help in this situation where there is a null followed by values followed by more nulls. Is there a way to use FIRST_VALUE or another similar function to retrieve the last non-null value?

推荐答案

last_value 与IGNORE NULLS在Oracle 10g中工作正常:

last_value with IGNORE NULLS works fine in Oracle 10g:

select item, year, month, amount, 
       last_value(amount ignore nulls) 
         over(partition by item 
              order by year, month 
              rows between unbounded preceding and 1 preceding) from tab;

rows between unbounded preceding and 1 preceding设置分析功能的窗口.

rows between unbounded preceding and 1 preceding sets the window for analytic function.

在这种情况下，Oracle从开头(UNBOUNDED PRECEDING)到当前行-1(1 PRECEDING)一直在PARTITION BY(同一项目)定义的组内搜索LAST_VALUE

In this case Oracle is searching for LAST_VALUE inside the group defined in PARTITION BY (the same item) from the begining (UNBOUNDED PRECEDING) until current row - 1 (1 PRECEDING)

它是Oracle 10g中带有IGNORE NULLS的LEAD/LAG的常见替代品

It's a common replacement for LEAD/LAG with IGNORE NULLS in Oracle 10g

但是，如果您使用的是Oracle 11g，则可以使用Gordon Linoff的答案中的LAG(有一个小的拼写错误，带有忽略null")

However, if you're using Oracle 11g you can use LAG from the Gordon Linoff's answer (there is a small typo with "ignore nulls")

这篇关于用最后一个非空值填充空值-Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！