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

目录

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

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

一、函数定义

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 ([PARTITgGipIION 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;

到此这篇关于SQL中LAGpython、LEAD函数功能及用法的文章就介绍到这了,更多编程相关sql lag lead函数内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！