SQL中LAG、LEAD函数功能及用法

谢师

目录

• 一、函数定义
• 二、核心功能对比
• 三、使用示例

SQL中的LAG和LEAD函数是用于访问结果集中当前行前后数据的窗口函数，主要功能及用法如下：

一、函数定义

1‌、LAG函数‌
获取当前行之前的第N行数据，语法：

LAG(column, offset, default) OVER ([PARTITION BY] ORDER BY)

1、column：目标列名

2、offset：向前偏移的行数（默认1）

3、default：无数据时的默认值（默认NULL）

2、LEAD函数 获取当前行之后的第N行数据，语法与LAG类似（方向相反）  

LEAD(column, offset, default) OVER ([PARTITION BY] ORDER BY)

1、column：目标列名

2、offset：向前偏移的行数（默认1）

3、default：无数据时的默认值（默认NULL）

二、核心功能对比

函数	方向	典型应用场景
LAG	向前	计算环比、填充缺失值、异常检测
LEAD	向后	预测趋势、计算后续差值

三、使用示例

1、查询销售额及前一日数据：

SELECT 
    date,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue
FROM sales

结果中prev_revenue列显示前一日的销售额，首行默认值为0

2、按部门查询员工工资及前一位同事工资：

SELECT 
    deptno,
    empname,
    salary,
    LAG(salary) OVER (PARTITION BY deptno ORDER BY hiredate) AS prev_salary
FROM emp

通过PARTITION BY实现分组内偏移

3、计算每日销售额变化量：

SELECT 
    date,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM sales

4、查询连续3天下单的customer_name，比如zhangsan在12.1、12.2号和12.3号连续3天下单过

补充：TIMESTAMPDIFF函数

TIMESTAMPDIFF(DAY, buy_date, next1_buy_date) 

是 MySQL 中用于计算两个日期之间天数差的函数，其功能解析如下：

函数结构：

1、参数1 DAY：指定返回结果的时间单位（此处为天数）

2、参数2 buy_date：起始日期（较早时间点）

3、参数3 next1_buy_date：结束日期（较晚时间点）

4、返回值：next1_buy_date - buy_date 的天数差（整数，向下取整）

-- 写法一
select
customer_name
from 
（
select 
customer_name,
buy_date,
lag(buy_date,1) over(partition by customer_name order by buy_date) as next1_buy_date
lead(buy_date,1) over(partition by customer_name order by buy_date) as next1_buy_date
from
order_table
）
where
TIMESTAMPDIFF(day,buy_date,next1_buy_date) = -1
and
TIMESTAMPDIFF(day,buy_date,next2_buy_date) = 1;

-- 写法二
select
customer_name
from 
（
select 
customer_name,
buy_date,
lag(buy_date,1) over(partition by customer_name order by buy_date) as next1_buy_date
lag(buy_date,2) over(partition by customer_name order by buy_date) as next1_buy_date
from
order_table
）
where
TIMESTAMPDIFF(day,buy_date,next1_buy_date) = -1
and
TIMESTAMPDIFF(day,buy_date,next2_buy_date) = -2;