如何用上一行的值填充空列？
我正在尝试执行一些操作。我想让所有颜色填充值。但是，当我有空列时，我想用前一个非空列中的值填充它。
with cte as ( select '2019-11-12 16:01:55' as timestamp, null as owner_id, null as owner_assigneddate, null as lastmodifieddate union all select '2019-11-12 19:03:18' as timestamp, 39530934 as owner_id, '2019-11-12 19:03:18' as owner_assigneddate, '2019-11-12 19:03:18' as lastmodifieddate union all select '2019-11-12 19:03:19' as timestamp, null as owner_id, null as owner_assigneddate, '2019-11-12 19:03:19' as lastmodifieddate union all select '2019-11-12 19:03:20' as timestamp, null as owner_id, null as owner_assigneddate, '2019-11-12 19:03:20' as lastmodifieddate union all select '2019-11-12 19:03:31' as timestamp, 40320368 as owner_id, '2019-11-12 19:03:31' as owner_assigneddate, '2019-11-12 19:03:31' as lastmodifieddate union all select '2019-11-12 19:03:33' as timestamp, null as owner_id, null as owner_assigneddate, '2019-11-12 19:03:33' as lastmodifieddate union all select '2019-11-12 19:03:56' as timestamp, null as owner_id, null as owner_assigneddate, '2019-11-12 19:03:356' as lastmodifieddate)
select timestamp, owner_id, owner_assigneddate, lastmodifieddate, COALESCE(owner_id, LEAD(owner_id) OVER(ORDER BY timestamp DESC)) AS test_column from cte order by timestamp asc 通过上一个查询，我已经设法仅将值放在下一行中。
我想要做的是让所有列都基于上一行填充值。第4行的值应为39530934，第7行的值应为40320368。我想我在这里遗漏了一些东西，但我不知道是什么。
问题来源于stack overflow
写回答
-
这应该符合您的cte定义：
... select timestamp, owner_id, owner_assigneddate, lastmodifieddate, LAST_VALUE(owner_id IGNORE NULLS) OVER(ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS test_column from cte order by timestamp asc
2019-11-18 11:55:02赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
