分析函数之Lead()、Lag()

目录

• 一、功能介绍
• 二、Lead()，Lag()函数语法
• 三、Lead()用法案例

一、功能介绍

Lag 分析函数可以在同一次查询中取出同一字段的前N行的数据作为独立的列。
Lead 分析函数可以在同一次查询中取出同一字段的后N行的数据作为独立的列。

二、Lead()，Lag()函数语法

lag(exp_str,offset,defval) over(partion by ..order by …)

lead(exp_str,offset,defval) over(partion by ..order by …)

• 其中exp_str是字段名

• Offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。

• Defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL

三、Lead()用法案例

• 数据源

UserVisits 表：
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1       | 2020-11-28 |
| 1       | 2020-10-20 |
| 1       | 2020-12-3  |
| 2       | 2020-10-5  |
| 2       | 2020-12-9  |
| 3       | 2020-11-11 |
+---------+------------+

• 需求

假设今天的日期是 '2021-1-1' 。
编写 SQL 语句，对于每个 user_id ，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数 window 。
返回结果表，按用户编号 user_id 排序。
查询格式如下示例所示：

结果表：
+---------+---------------+
| user_id | biggest_window|
+---------+---------------+
| 1       | 39            |
| 2       | 65            |
| 3       | 51            |
+---------+---------------+

• SQL

select user_id,max(datediff(ld,visit_date)) biggest_window from (
    select user_id,visit_date,
        lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date asc) ld
    from UserVisits
) t
group by user_id
order by user_id 

• 解题说明

1. 先取当前行，与下一行组成新的临时表，让其根据日期升序排列，最后填充今日的日期
2. 求出日期差值，分组取出每个用户的最大日期差值