深圳网站推广百度百家号
NULL
- 一、问题描述
- 二、ORACLE
- <一>、last_value () over ()
- <二>、lag () over()
- <三>、相关子查询
- 三、MYSQL
- <一>、全局变量
- <二>、coalesce() + lag() over()
- <三>、相关子查询
- <四>、 recursive
- <五>、lag() over() + min() over() / max() over()
一、问题描述
- 假如某张表中某个列有多个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