当前位置:首页 > 程序 > 正文

SQL进阶 面试常见的4种sql算法考试题(二):排序后相邻两行均值

2019-09-24 09:14 点击:6次 作者:biucz 我来投稿

SQL进阶 面试常见的4种sql算法考试题(二):排序后相邻两行均值

近期在不同群里有小伙伴们提出了一些在面试和笔试中遇到的Hive SQL问题,Hive作为算法工程师的一项必备技能,在面试中也是极有可能被问到的,所以有备无患,本文将对这四道题进行详细的解析,还是有一定难度的,希望你看完本文能够有所收获。

排序后相邻两行均值

第二题的原始数据如下:

SQL进阶 面试常见的4种sql算法考试题(二):排序后相邻两行均值

要求如下:

SQL进阶 面试常见的4种sql算法考试题(二):排序后相邻两行均值

分组排序想必大家都知道使用row_number()函数,但要找到同组前一行的值,可能有许多同学不太了解,这里是用的是lead/lag函数,两个函数用法如下:

lag(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式)

lead(字段名,N) over(partition by 分组字段 order by 排序字段 排序方式)

lag括号里理由两个参数,第一个是字段名,第二个是数量N,这里的意思是,取分组排序之后比该条记录序号小N的对应记录的指定字段的值,如果字段名为ts,N为1,就是取分组排序之后上一条记录的ts值。

lead括号里理由两个参数,第一个是字段名,第二个是数量N,这里的意思是,取分组排序之后比该条记录序号大N的对应记录的对应字段的值,如果字段名为ts,N为1,就是取分组排序之后下一条记录的ts值。

如果没有前一行或者后一行,对应的字段值为null。

所以,这里我们应该使用的是lag函数,来获取同组排序后前一行数据对应字段的值,SQL如下:

select
 year,chr,if(pre_val is null,val,(val + pre_val) / 2.0) as avg_val
from
(
select
 year,chr,val,
 lag(val,1) over(partition by year order by chr asc) as pre_val
from
 default.a2
) a

注意这里的一个小细节,如果分组后数据排在第一位,它是没有前一个数的,此时数仍保持原样,所以这里加了一个if判断,结果符合预期:

SQL进阶 面试常见的4种sql算法考试题(二):排序后相邻两行均值

推荐阅读:?

SQL进阶 面试常见的4种sql算法考试题(一):多列转多行

SQL进阶 面试常见的4种sql算法考试题(二):排序后相邻两行均值

SQL进阶 面试常见的4种sql算法考试题(三):获取字符串索引列表

SQL进阶 面试常见的4种sql算法考试题(四):分块排序