当前位置：首页 > news >正文

SQL进阶技巧：如何删除第N次连续出现NULL值所存在的行？

news 2025/6/30 10:25:23

0 场景描述

1 数据准备

2 问题分析

问题拓展：如何删除第2次、第3次、第N次连续出现NULL值所在的行？

3 小结

如果觉得本文对你有帮助，想进一步学习SQL语言这门艺术的，那么不妨也可以选择去看看我的博客专栏，部分内容如下：

数字化建设通关指南

专栏原价99，现在活动价59.9，按照阶梯式增长，直到恢复原价。

0 场景描述

有下面的场景：

我们希望删除某id中连续存在NULL值的所有行，但是保留第一次出现不为NULL值的以下所有存在NULL值的行。具体如下图所示：

如何删除第2次、第3次连续出现NULL值得所有行？

1 数据准备

create table null_del as(select stack(15,NULL, 20220201,NULL, 20220202,NULL, 20220203,NULL, 20220204,NULL, 20220205,NULL, 20220206,NULL, 20220207,125, 20220208,126, 20220209,127, 20220210,NULL, 20220211,NULL, 20220212,128, 20220213,NULL, 20220214,NULL, 20220215) as (id, dt))

+------------+------------+
| id         |        ts  |
+------------+------------+
| id         | ts         |
| NULL       | 20220201   |
| NULL       | 20220202   |
| NULL       | 20220203   |
| NULL       | 20220204   |
| NULL       | 20220205   |
| NULL       | 20220206   |
| NULL       | 20220207   |
| 125        | 20220208   |
| 126        | 20220209   |
| 127        | 20220210   |
| NULL       | 20220211   |
| NULL       | 20220212   |
| 128        | 20220213   |
| NULL       | 20220214   |
| NULL       | 20220215   |
+------------+------------+

2 问题分析

问题分析：如果直接使用id is not null，则会将整个NULL值所在的行都删除掉，本题要求保留第一次出现不为NULL值的以下所有存在NULL值的行，也就是只删除id从表中第一行开始出现NULL到第一次出现非NULL之间的所有行，此时我们可以借助于窗口函数进行辅助计算。

第一步：利用last_value()函数ignore nulls特性构造删除标记

select id,dt,last_value(id, true) over (order by dt) del_flg
from null_del

使用x(id) over(order by dt) 也可以达到相同的效果


select id,dt,--last_value(id, true) over (order by dt) del_flgmax(id) over (order by dt) del_flgfrom null_del

使用count(id) over(order by dt)也可以进行删除

select id,dt,--last_value(id, true) over (order by dt) del_flg--max(id) over (order by dt) del_flgcount(id) over (order by dt) del_flg
from null_del

第二步：删除符合条件的NULL值所在的行

完整的SQL如下：

select id, dt
from (select id,dt,last_value(id, true) over (order by dt) del_flgfrom null_del) t
where del_flg is not null

问题拓展：如何删除第2次、第3次、第N次连续出现NULL值所在的行？

第一步：利用count(id)over(order by dt)构建辅助删除标记

select id,dt,--last_value(id, true) over (order by dt) del_flg--max(id) over (order by dt) del_flgcount(id) over (order by dt) del_flg
from null_del

第二步:针对NULL值对应的del_flg分组进行rn编号，获取对应删除标记分组的次序。

按照null值分组，使用dense_rank()进行等位排名（注意此处不能使用row_number()因为有重复值）

select id,dt,dense_rank() over (partition by id order by del_flg) rn
from (select id,dt,count(id) over (order by dt) del_flgfrom null_del) t

可以看出结果中id非NULL的分组中也进行了顺序编号，此时我们只需要将结果中id非NULL值的rn置为NULL即可。具体SQL如下：

select id,dt,case when id is null then dense_rank() over (partition by id order by del_flg) end rn
from (select id,dt,count(id) over (order by dt) del_flgfrom null_del) t

注意：这样写一定要记住执行顺序，先执行窗口函数，再执行case when 语句

第三步：如果要删除第二次连续出现的NULL值所在的行，只需要令rn <>2即可，以此类推

最终完整的SQL如下：

select id,dt
from
(
select  id,dt,case when id is null then dense_rank() over (partition by id order by del_flg) end rnfrom (select id,dt,count(id) over (order by dt) del_flgfrom null_del) t) t
where nvl(rn,-1) != 2
order by dt
;

当连续分组的顺序编号出来以后，可以按照需求进行任意扩展。

由于rn中存在NULL值，所以此处在过滤时注意NULL值转换，否在会连rn 中 NULL值一起过滤掉。

关于该问题的坑，可以参考文章：

避坑：Hive中使用 != 或＜＞符号进行条件过滤时的坑

获取的结果如下：

3 小结

本文基于SQL语言给出了一种删除第N次连续出现NULL值所存在行的通用解决方案，当然删除的不一定是NULL值，也可以是其他X值，其解决思路类似，本质上是断点分组思想的应用，在sql复杂场景中，往往利用该思想处理一些持续性、连续性、或在某一时刻点发生转折（变化）的问题，采用该方法可以提高处理问题的可扩展性和通用性，逻辑性更强，看待问题的观点更高一层。