英文:
Replace null value with value from the previous row
问题
无法解决这个问题，因为列：ID 不是唯一的。
我不知道如何用前一行的值替换空值。
原样
| ID | NAME |
|---|---|
| 001 | NULL |
| 001 | A |
| 001 | NULL |
| 001 | NULL |
| 001 | B |
| 001 | NULL |
期望
| ID | NAME |
|---|---|
| 001 | NULL |
| 001 | A |
| 001 | A |
| 001 | A |
| 001 | B |
| 001 | B |
英文:
I can not to solve in this problem because column : ID is not unique.
I don't know to replace null value with value from the previous row.
AS IS
| ID | NAME |
|---|---|
| 001 | NULL |
| 001 | A |
| 001 | NULL |
| 001 | NULL |
| 001 | B |
| 001 | NULL |
TO BE
| ID | NAME |
|---|---|
| 001 | NULL |
| 001 | A |
| 001 | A |
| 001 | A |
| 001 | B |
| 001 | B |
答案1
得分: 1
如@siggemannen在评论中提到的，您需要为每个ID提供一个日期/时间戳或唯一标识以使其成为可能。
假设您有一个称为rn_id的唯一标识符，窗口函数MAX()与范围子句可以帮助您，只有当NAME按字母顺序排序时才能使用：
select id, max(NAME) over ( partition by ID order by rn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as NAME
from mytable
结果：
id |NAME
----|----
1 |null
1 |A
1 |A
1 |A
1 |B
1 |B
如果不按字母顺序排序，那么我们将需要使用两个窗口函数：
IIF()函数将在值存在时返回1，否则将返回零。窗口化的总和将显示递增的运行总和，这将为值及其后面的null创建一个分组。
然后first_value()将捕获每个组的最后一个非null值：
with cte as (
SELECT
ID,
rn_id,
NAME,
SUM(IIF(NAME IS NULL, 0,1)) OVER (partition by ID ORDER BY rn_id ASC) AS grp
FROM
mytable
)
select id, First_value(NAME) over ( partition by ID, grp order by rn_id) as NAME_
from cte
英文:
As @siggemannen mentioned in the comments, you need a date/timestamp or a unique id per ID to make it possible.
Assuming you have a unique id per ID called rn_id, the window function MAX() with a range clause can help you only if NAME are ordered alphabetically :
select id, max(NAME) over ( partition by ID order by rn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as NAME
from mytable
Result :
| id | NAME |
|---|---|
| 1 | null |
| 1 | A |
| 1 | A |
| 1 | A |
| 1 | B |
| 1 | B |
If not ordered alphabetically then we will need to use two window functions :
The IIF() function will return 1 when a value is present, otherwise it will return zero. The windowed sum will show the running total increasing, which will create a grouping for a value and it's following nulls.
Then first_value() will catch the last non null value per group :
with cte as (
SELECT
ID,
rn_id,
NAME,
SUM(IIF(NAME IS NULL, 0,1)) OVER (partition by ID ORDER BY rn_id ASC) AS grp
FROM
mytable
)
select id, First_value(NAME) over ( partition by ID, grp order by rn_id) as NAME_
from cte
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论