用最后一个非空量填充空值 - Oracle SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26977267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fill null values with last non-null amount - Oracle SQL
提问by user1723699
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:
我有一个有 4 列的表：项目、年、月、金额。Amount 的一些值是空的，当发生这种情况时，我想用以前不为空的 Amount 值填充这些值。当只有一个空值时，我可以使用 LAG 函数轻松地做到这一点，但当一行中有多个时，我不知道如何处理它。下面是一个示例，该表可能看起来像我想在查询中添加的列：
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
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?
我有两个想法，我似乎无法开始工作以产生我想要的东西。首先我打算使用 LAG 但后来我注意到当一行中有多个空值时它不会满足这一点。接下来，我将使用 FIRST_VALUE 但这在这种情况下无济于事，其中有一个空值，后跟值，然后是更多的空值。有没有办法使用 FIRST_VALUE 或其他类似的函数来检索最后一个非空值？
回答by Multisync
last_valuewith IGNORE NULLS works fine in Oracle 10g:
带有 IGNORE NULLS 的 last_value在 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 precedingsets the window for analytic function.
rows between unbounded preceding and 1 preceding设置解析函数的窗口。
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 正在从开始 (UNBOUNDED PRECEDING) 到当前行 - 1 (1 PRECEDING) 在 PARTITION BY（同一项目）中定义的组内搜索 LAST_VALUE
It's a common replacement for LEAD/LAG with IGNORE NULLS in Oracle 10g
这是 Oracle 10g 中 LEAD/LAG 与 IGNORE NULLS 的常见替代品
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 11g，则可以使用 Gordon Linoff 的答案中的 LAG（“忽略空值”有一个小错误）
回答by Gordon Linoff
Here is an approach. Count the number of non-null values before a given row. Then use this as a group for a window function:
这是一个方法。计算给定行之前的非空值的数量。然后将其用作窗口函数的组：
select t.item, t.year, t.month, t.amount,
max(t.amount) over (partition by t.item, grp) as new_amount
from (select t.*,
count(Amount) over (Partition by item order by year, month) as grp
from table t
) t;
In Oracle version 11+, you can use ignore nullsfor lag()and lead():
在 Oracle 11+ 版本中，您可以使用ignore nullsforlag()和lead()：
select t.item, t.year, t.month, t.amount,
lag(t.amount ignore nulls) over (partition by t.item order by year, month) as new_amount
from table t
回答by Pavel
the answers are quite bad:
答案很糟糕：
Item | Year | Month | Amount | New_Amount
AAA | 2013 | 01 | 100 | null
AAA | 2013 | 02 | | 100
AAA | 2013 | 03 | 150 | 100
AAA | 2013 | 04 | 125 | 150
AAA | 2013 | 05 | | 125
AAA | 2013 | 06 | | 125
AAA | 2013 | 07 | | 125
AAA | 2013 | 08 | 175 | 125
is a quite bad result :)
这是一个非常糟糕的结果:)
--
——
select item, year, month, amount,
last_value(amount ignore nulls)
over(partition by item
order by year, month
rows between unbounded preceding and CURRENT ROW) from tab;
is better
更好
回答by zheng wee
Just In Case you are using other database(eg. sqlite) and it didn't have a ignore null function. i post another solution here for your reference.
以防万一您正在使用其他数据库（例如 sqlite）并且它没有忽略空函数。我在这里发布了另一个解决方案供您参考。
- You need use a 0/1 column to indicate the Null/Non-Null data
- Then create accumulate summary column to calculate the indicator number in step 1. -now you can see the data already looks like grouped by your non_Null data.
- As last step, pls use a Max function group by the accumulate sum (in step 2) to populate the data(here is amont) in to empty items.
- 您需要使用 0/1 列来指示 Null/Non-Null 数据
- 然后创建累积汇总列以计算步骤 1 中的指标编号。 - 现在您可以看到数据已经看起来像按您的 non_Null 数据分组。
- 作为最后一步，请通过累加总和（在步骤 2 中）使用 Max 函数组将数据（这里是 amont）填充到空项目中。
attach the pic to help you understand.
附上图片以帮助您理解。
