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

会飞的一十六 2024-10-31

点击上方【蓝色】字体关注我们

01 场景描述

有下面的场景：

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

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

02 数据准备

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   |
+------------+------------+

03 问题分析

问题分析：如果直接使用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

使用max(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_flg
      from 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_flg
             count(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_flg
      from 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_flg
       count(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_flg
      from 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_flg
      from 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 rn
 from (select id,
              dt,
              count(id) over (order by dt) del_flg
       from null_del) t
 ) t
where nvl(rn,-1) != 2
order by dt
;

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

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

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

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

04 小结

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

往期精彩

Hive with语句你所不知道的秘密？

SQL进阶技巧：如何使用Order by 中 NULLS LAST特性进行自然排序？

SQL进阶技巧：如何优雅求解指标累计去重问题？

SQL进阶技巧：统计各时段观看直播的人数？

SQL进阶技巧：近N指标如何精确计算并扩充？

解锁SQL无限可能 | 如何利用SQL求解力扣难题接雨水问题？