带条件的BigQuery LAST_VALUE
带条件的BigQuery LAST_VALUE
提问于 2021-02-20 21:29:04
下面有示例数据，我希望在expectedOutput列中填充该值。我想要根据规则计算它--如果isValid = 1，给我上次isValid = 1的结果值。所以在这个例子中，第1行为null，因为这是第一个值。第6行显示的是苹果，因为这一行是有效的，苹果是最后一次出现有效值时的水果。
row fruit isValid expectedOutput (Prior valid value)
1 apple 1 NULL
2 apple 0 NULL
3 apple 0 NULL
4 apple 0 NULL
5 orange 0 NULL
6 orange 1 apple
7 grape 1 orange
8 grape 0 NULL以下是我尝试过的，并不能让我得到正确的结果。
LAST_VALUE(case when isValid = 1 then fruit end IGNORE NULLS) OVER(PARTITION BY all ORDER BY row)回答 2
Stack Overflow用户
回答已采纳
发布于 2021-02-20 22:03:12
需要向窗口添加边界以排除当前行：
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING试试这个：
WITH
table1 AS (
SELECT 1 as row, 'apple' as fruit, 1 as isValid union all
SELECT 2 as row, 'apple' as fruit, 0 as isValid union all
SELECT 3 as row, 'apple' as fruit, 0 as isValid union all
SELECT 4 as row, 'apple' as fruit, 0 as isValid union all
SELECT 5 as row, 'orange' as fruit, 0 as isValid union all
SELECT 6 as row, 'orange' as fruit, 1 as isValid union all
SELECT 7 as row, 'grape' as fruit, 1 as isValid union all
SELECT 8 as row, 'grape' as fruit, 0 as isValid
)
SELECT
*,
CASE
WHEN isValid = 1 THEN LAST_VALUE(IF(isValid=1, fruit, NULL) IGNORE NULLS) OVER(ORDER BY row ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
END AS expectedOutput
FROM
table1Stack Overflow用户
发布于 2021-02-20 21:57:45
见下面的例子
#standardSQL
with `project.dataset.table` as (
select 1 row , 'apple' fruit, 1 isValid union all
select 2, 'apple', 0 union all
select 3, 'apple', 0 union all
select 4, 'apple', 0 union all
select 5, 'orange', 0 union all
select 6, 'orange', 1 union all
select 7, 'grape', 1 union all
select 8, 'grape', 0
)
select *,
if(isValid = 1, lag(fruit) over(partition by isValid order by row), null) as Prior_valid_value
from `project.dataset.table`
# order by row 带输出
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66296759
复制相关文章
相似问题
腾讯云开发者
