SQL进阶:如何跳过多个NULL值取第一个非NULL值?

一、问题描述

• 假如某张表中某个列有多个null值,如何跳过这多个null值取第一个不为空的值去填充,如下述表格所示

product	sale_dae	amount
1	2024/01/01	100
1	2024/01/02	200
1	2024/01/03
1	2024/01/04
2	2024/01/01	200
2	2024/01/02
2	2024/01/03	300
2	2024/01/04

某产品当天sale_date为空的销售价格取上一天的填充,最终想展示的如下述表格所示

product	sale_dae	amount
1	2024/01/01	100
1	2024/01/02	200
1	2024/01/03	200
1	2024/01/04	200
2	2024/01/01	200
2	2024/01/02	200
2	2024/01/03	300
2	2024/01/04	300

二、ORACLE

<一>、last_value () over ()

• last_value()是sql中的一个窗口函数,用于获取窗口中的最后一个值,默认计算范围是第一行到当前行,有窗口是窗口内的第一行到当前行

select product,sale_date,amount,last_value(amount ignore nulls)over(partition by product order by sale_date) as last_amountfrom test

• 如上述代码所示,按照产品分组,销售日期排序,若是有值则自己就是组内的最后一个值,把自己填充,若为NULL,则用IGNORE NULLS语法忽略NULL值,则用上一个值填充

<二>、lag () over()

• lag()也是sql中的窗口,用于获取当前行的前几条记录,默认为上一条记录

select product,sale_date,amount,coalsece(amount,lag(amount ignore nulls)over(partition by product order by sale_date)) as last_amountfrom test

如上述代码所示,按照产品分组,销售日期排序,用了coalesce()函数,表示若是amount为空,则取lag(),默认是取上一条,上一条若也为空,则用IGNORE NULLS语法忽略NULL值,再取上一个填充,直到找到非NULL值

<三>、相关子查询

select product,sale_date,amount,(select  amountfrom (select amount from test t1 where t1.product = t.productand t1.sale_date <= t.sale_dateand amount is not null order by t1.sale_date desc) where rownum <= 1) as last_amountfrom test t

• 用一个内部的子查询找到product相同,日期<=当前日期,且amount不为空的第一条

三、MYSQL

• MYSQL中虽然也有last_value()和lag()函数,但是不支持IGNORE NULLS选项,所以不能够直接用这两个函数实现

<一>、全局变量

• mysql中@表示全局变量,可以用全局变量递归实现

set @last_non_null := NULL;with tmp as (selet product,sale_date,amount,if(amount is null, @last_non_null := coalesce(@last_non_null,amount),@last_non_null := amount) as last_amountfrom testorder by product,sale_date
)select product,sale_date,amount,cast(last_amount as float) as last_amountfrom tmp

• 如上述代码所示,order by
  product,sale_date是保证同个产品是按照sale_date排序的,也就是按照产品分组,销售日期排序
• set @last_non_null : NULL表示设置全局变量last_non_null为NULL,后面的sql会用到这个变量
• amount不为空的时候,last_non_null直接赋值amount返回;当amount为空的时候,last_non_null此时没有被赋值,直接返回,返回的就是上一个非空值

<二>、coalesce() + lag() over()

• 上面说到Mysql的lag() over()是不支持ignore nulls选项的,那如何实现忽略null值呢?可以用一种笨方法,就是前面几条都写出来

select product,sale_date,amount,coalesce(amount,lag(amount,1)over(partition by product order by sale_date),lag(amount,2)over(partition by product order by sale_date),lag(amount,3)over(partition by product order by sale_date),...)from test

• 上述方法可以适用于连续NULL值比较少的情况,如果连续NULL值比较多,还是不建议的

<三>、相关子查询

• Mysql的相关子查询和Oracle逻辑是一样的,只不过取第一条的时候稍有不同,mysql是用limit

select product,sale_date,amount,(select amountfrom test t1where t1.product = t.productand t1.sale_date <= t.sale_date order by t1.sale_date desclimit 1) as last_amountfrom test

<四>、 recursive

<五>、lag() over() + min() over() / max() over()

• 这个方法比较特殊化,不是所有场景都适用,主要针对一些有序的场景,比如上述例子针对日期amount都是有序的,可以将它当做一种思路

with tmp as (select product,sale_date,amount,lag(amount) over (partition by product order by sale_date) as last_amountfrom test
)select product,sale_date,amount,coalesce(amount,max(amount)over(partition by product order by sale_date)) as last_amountfrom tmp