经典sql题之lag/lead/fisrt_value/last_value

一：求波峰波谷(lag/lead)

场景示例：

波峰：当天的价格大于前一天和后一天的；

波谷：当天的价格小于前一天和后一天的；

解题方法：

借助lag()，lead()窗口函数

1）先求出当天价格的上一天和下一天价格

2)将上一天价格，下一天价格和当前价格比较，当前价格均小于则是波谷，当前价格均大于则是波峰。（case when打标签）

二：前后列转换(fisrt_value/last_value)

场景示例：

左表👉转换成右表

 

知识扩展：

1) first_value/last_value

first_value:函数用于返回当前第一个值。可开启true命令，跳过null值
last_value:函数用于返回当前最后个值。可开启true命令，跳过null值

用法：

LAST_VALUE(column_name) OVER (PARTITION BY partition_expression ORDER BY sort_expression [ASC | DESC] ROWS BETWEEN start AND end)

column_name：要获取最后值的列名。

PARTITION BY partition_expression：可选，指定分区表达式，用于将结果分成多个分区，类似于 GROUP BY。

ORDER BY sort_expression：指定用于排序的列或表达式。

ASC | DESC：可选，用于指定排序顺序，默认为升序。

ROWS BETWEEN start AND end：可选，用于指定窗口的范围

2) 窗口函数over()

over():指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。以下四个是限制行数的，写在over()函数里面；

current now:当前行；

n preceding:往前n行数据

n following: 往后n行数据

unbounded:起点

下面这三个是写在over()函数里面的：

lag(col,n,default_val): 往前第n行数据

lead(col,n,deafult_val):往后第n行数据

ntile(n): 把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号。注意：n 必须为int 类型。

示例：

用行限制：rows betweent 3 preceding and current row 表示从当前行的前三行到当前行

unbounded preceding:当前行的所有行

n preceding: 当前行的n行

current row: 当前行

n following: 表示当前行之后的n行

unbounded folllowing:表示当前行之后的所有行

用特殊的值限制：range between interval ‘3’ hour preceding and current row 表示计算三个小时前的数据当当前行

解决方法：

直接用last_value返回当前行的上一行之后的所有行中符合is_open=1的最后个值，如果没有就用“1900-01-01”填充，用first_value返回当前行的下一行之后的所有行中符合is_open=1的第一个值，没有的话就用“9999-01-01”填充。即可得到符合条件的值，如图所示。

三：面试真题

题目：

有一张临时表test_sum_bc包含A，B两列，请使用SQL对该B列进行处理，形成C列，按A列顺序，B列值不变，则C列累计计数；B列值变化，则C列重新开始计数，如图所示。

解决：

1）用lag()函数按照字段A排序获取B的上一个值，记为last_v，即t1

2)  case when 判断last_v是否为空，为空则给is_chg赋值为1，如果last_v不等于当前B列的值，则表示该列已经改变，is_chg也为1，否则is_chg为0；

3）将is_chg按照A排序sum()函数累加得到分组标签group_tag，并给组表编号，不变的为一组

4）按组标签分组，组内按字段A使用row_number()函数排序，可得到字段C的值。