SQL进阶:如何跳过多个NULL值取第一个非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_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)!