自学内容网 自学内容网

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

一、问题描述

  • 假如某张表中某个列有多个null值,如何跳过这多个null值取第一个不为空的值去填充,如下述表格所示
productsale_daeamount
12024/01/01100
12024/01/02200
12024/01/03
12024/01/04
22024/01/01200
22024/01/02
22024/01/03300
22024/01/04

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

productsale_daeamount
12024/01/01100
12024/01/02200
12024/01/03200
12024/01/04200
22024/01/01200
22024/01/02200
22024/01/03300
22024/01/04300

二、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

原文地址:https://blog.csdn.net/nzbing/article/details/143978319

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!