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

一、问题描述

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

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

二、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_amount
  from 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_amount
  from test

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

<三>、相关子查询

select product,
	   sale_date,
	   amount,
	   (select  amount
	     from (
			select amount 
			  from test t1 
			 where t1.product = t.product
			   and t1.sale_date <= t.sale_date
			   and amount is not null 
			 order by t1.sale_date desc
			) 
		where rownum <= 1) as last_amount
  from 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_amount
	 from test
    order by product,sale_date
)
	select product,
	       sale_date,
	       amount,
	       cast(last_amount as float) as last_amount
	  from 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 amount
         from test t1
        where t1.product = t.product
          and t1.sale_date <= t.sale_date 
        order by t1.sale_date desc
        limit 1) as last_amount
  from 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_amount
	  from test
)
	select product,
	       sale_date,
	       amount,
	       coalesce(amount,max(amount)over(partition by product order by sale_date)) as last_amount
	  from tmp