解锁SQL的无限可能 | 且看如何秒解墨天轮SQL挑战赛第二期题目

目 录

墨天轮SQL挑战赛第二期

比赛题目

数据准备 

解题过程

方法1: 优雅的解法 |  last_value分析函数忽略NULL值特性实现

方法2: 通用的解法 |  断点分组思想

小结 

 Sql进阶技巧： 如何分析商品入库采购成本数据缺失问题？| 近距离有效的数据缺失值填充问题

 SQL进阶技巧：近距离有效的缺失值填充问题【last_value实现版】

 SQL进阶技巧：埋点日志事件缺失值填充 | 近距离有效的数据缺失值填充问题 

 SQL进阶技巧：按指定排序规则填充数据缺失值 

SQL进阶技巧：近距离有效的数据缺失值填充问题 | 稀疏表补全法 

---

墨天轮SQL挑战赛第二期

原文链接：解锁SQL的无限可能 | 墨天轮SQL挑战赛第二期，等你来战！ - 墨天轮 (modb.pro)

在这个数据驱动的时代，SQL不仅仅是一门语言，更是我们探索和理解数据世界的钥匙。在第一期“SQL挑战赛第一期”中，涌现出了很多优秀、有创意的作品，为了能给大家提供一个展示数据处理技巧、逻辑思维和创造力的平台，墨天轮特举办SQL挑战赛第二期！期待大家在这个过程中，能享受到学习的乐趣和竞争的刺激。

比赛题目

原始数据如图1所示，希望通过一条SQL查出虚拟列AMOUNT_ALL，该列以SEQNO的顺序并根据AMOUNT字段的值填充，如果AMOUNT有值则虚拟列等于该条记录中AMOUNT的值，如果AMOUNT的值为NULL，则该虚拟列为上一条AMOUNT不为NULL的值，输出要求如图所示：

数据准备 

Hive开发环境

create table test_gen as 
(

	select stack(
	     
		    11,
			1  ,     1    ,
	        2  ,     null ,
	        3  ,     null ,
	        4  ,     null ,
	        5  ,     2    ,
	        6  ,     null ,
	        7  ,     null ,
	        8  ,     3    ,
	        9  ,     null  ,
	        10 ,     null  ,
	        11 ,     5     
	
	) as (seqno,amount)

);

解题过程

本题属于近距离有效的数据缺失值填充问题，在我的博客专栏里面，此类问题非常多，现给出如下解法：

方法1: 优雅的解法 |  last_value分析函数忽略NULL值特性实现

利用last_value(a,true) over(order by b)求解，如果该函数第二个参数为true，则按照order by指定的顺序获取距离当前行最近且值不为NULL的一行对应字段值，oracle数据库则使用last_value(a ignore nulls) over(order by b) 语法，另外oracle数据库lag函数的分析函数，也可以忽略null值，实际上对该问题lag函数更为精确，而对于Hive数据库中则lag()函数的分析函数没有忽略NULL值这一特性，只能使用last_value分析函数。

select seqno
     , amount
     , coalesce(amount, last_value(amount, true) over (order by seqno)) amount_all
from test_gen；

方法2: 通用的解法 |  断点分组思想

对于数据库中不支持last_value函数或last_value函数不支持忽略NULL值这一用法的，可以使用该方法，也是比较通用的写法。

select seqno
     , amount
     --取出分组中数据
     , max(amount) over (partition by grp_flg) amount_all
from (select seqno
           , amount
           --找出分组的标记
           , count(amount) over (order by seqno) grp_flg
      from test_gen
      ) t
order by seqno;

小结 

对于本期墨天伦SQL挑战赛的题目不算很难，但是要找出最优雅的解法，对于初中级别的同学则需要一定的积累和沉淀。而对于本期挑战赛的题目，其实在我的CSDN博客专栏：数字化建设通关指南中或SQL进阶实战技巧专栏中，早已将相关的方法进行总结，订购了我的专栏，看过相关文章的同学相信定会秒杀该题目，并得到较好的成绩。其相关技巧及案例的文章整理如下：

 Sql进阶技巧： 如何分析商品入库采购成本数据缺失问题？| 近距离有效的数据缺失值填充问题

 SQL进阶技巧：近距离有效的缺失值填充问题【last_value实现版】

 SQL进阶技巧：埋点日志事件缺失值填充 | 近距离有效的数据缺失值填充问题 

 SQL进阶技巧：按指定排序规则填充数据缺失值 

SQL进阶技巧：近距离有效的数据缺失值填充问题 | 稀疏表补全法 

~~如果想要交流可以关注我的公众号：会飞的一十六 。留言相关问题，作者看到后会回复~~