英文:
Replacing NULL value in a column by a value from previous column Snowflake
问题
你可以尝试使用窗口函数（Window Function）来处理这个问题，这将使你的查询更加简洁和可扩展。以下是一个示例查询：
WITH cte AS (
SELECT
name,
week1,
COALESCE(week2, LAG(week2) OVER (ORDER BY name)) AS week2,
COALESCE(week3, LAG(week3) OVER (ORDER BY name)) AS week3,
COALESCE(week4, LAG(week4) OVER (ORDER BY name)) AS week4
FROM temp
)
SELECT * FROM cte;
这个查询使用了窗口函数 LAG 来获取前一行的非空值，并将其用于替换当前行中的 null 值。这种方法可以更容易地扩展到处理更多的列。
英文:
I have some sample data in table temp as follows;
| name | week1 | week2 | week3 | week4 |
|---|---|---|---|---|
| Bamboo | 56 | null | null | null |
| Pearl | 42 | null | 43 | 12 |
| ZZplant | 42 | null | null | 13 |
| Pothos | 12 | 10 | null | 0 |
Here we need to replace null values in a column by non null values in previous column. Hence we she have the output as;
Expected output
| name | week1 | week2 | week3 | week4 |
|---|---|---|---|---|
| Bamboo | 56 | 56 | 56 | 56 |
| Pearl | 42 | 42 | 43 | 12 |
| ZZplant | 42 | 42 | 42 | 13 |
| Pothos | 12 | 10 | 10 | 0 |
Here is my attempt,
with week2 as(
select name,
week1,
coalesce(week2,week1) as week2,
week3,
week4
from temp),
week3 as(
select name,
week1,
week2,
coalesce(week3, week2) as week3,
week4
from week2)
select name,
week1,
week2,
week3,
coalesce(week4, week3) as week4
from week3;
As seen, I am coalescing one column at a time. This seems to be working but is not scalable. Is it possible To get some advice/help here?
答案1
得分: 0
使用COALESCE：
SELECT
name,
week1,
COALESCE(week2, week1) AS week2,
COALESCE(week3, week2, week1) AS week3,
COALESCE(week4, week3, week2, week1) AS week4
FROM temp1;
或者，您也可以使用引用在同一级别的列表达式：
SELECT
t.name,
t.week1 AS week1_,
COALESCE(t.week2, week1_) AS week2_,
COALESCE(t.week3, week2_) AS week3_,
COALESCE(t.week4, week3_) AS week4_
FROM temp1 AS t;
对于示例数据：
CREATE TABLE temp1(name TEXT, week1 INT, week2 INT, week3 INT, week4 INT)
AS SELECT 'Bamboo', 56, null, null, null
UNION SELECT 'Pearl', 42, null, 43, 12
UNION SELECT 'ZZplant', 42, null, null, 13
UNION SELECT 'Pothos', 12, 10, null, 0;
输出：
NAME WEEK1_ WEEK2_ WEEK3_ WEEK4_
Bamboo 56 56 56 56
Pearl 42 42 43 12
Pothos 12 10 10 0
ZZplant 42 42 42 13
英文:
Using COALESCE:
SELECT
name,
week1,
COALESCE(week2, week1) AS week2,
COALESCE(week3, week2, week1) AS week3,
COALESCE(week4, week3, week2, week1) AS week4
-- each column has expression containing all prev columns
FROM temp1;
Alternatively using the fact we could refer to column expression at the same level:
SELECT
t.name,
t.week1 AS week1_,
COALESCE(t.week2, week1_) AS week2_,
COALESCE(t.week3, week2_) AS week3_,
COALESCE(t.week4, week3_) AS week4_
-- each column refers only to previous alias
FROM temp1 AS t;
For sample data:
CREATE TABLE temp1(name TEXT, week1 INT, week2 INT, week3 INT, week4 INT)
AS SELECT 'Bamboo', 56 ,null ,null ,null
UNION SELECT 'Pearl', 42 ,null ,43 ,12
UNION SELECT 'ZZplant', 42 ,null ,null ,13
UNION SELECT 'Pothos', 12 ,10 ,null ,0;
Output:
NAME WEEK1_ WEEK2_ WEEK3_ WEEK4_
Bamboo 56 56 56 56
Pearl 42 42 43 12
Pothos 12 10 10 0
ZZplant 42 42 42 13
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论