SQL中LAG、LEAD函数功能及用法
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、函数定义</a></li><li><a href="#_label1">二、核心功能对比</a></li><li><a href="#_label2">三、使用示例</a></li></ul></div><p>SQL中的LAG和LEAD函数是用于访问结果集中当前行前后数据的窗口函数，主要功能及用法如下：</p><p class="maodian"><a name="_label0"></a></p><h2>一、函数定义</h2>
<p>1‌、<strong>LAG函数</strong>‌<br />获取当前行之前的第N行数据，语法：</p>
<div class="jb51code"><pre class="brush:sql;">LAG(column, offset, default) OVER ( ORDER BY)</pre></div>
<p><code>1、column</code>：目标列名</p>
<p><code>2、offset</code>：向前偏移的行数（默认1）</p>
<p><code>3、default</code>：无数据时的默认值（默认NULL）</p>
<p>2、<strong>LEAD函数 </strong><span>获取当前行之后的第N行数据，语法与LAG类似（方向相反） </span></p>
<div class="jb51code"><pre class="brush:sql;">LEAD(column, offset, default) OVER ( ORDER BY)</pre></div>
<p><code>1、column</code>：目标列名</p>
<p><code>2、offset</code>：向前偏移的行数（默认1）</p>
<p><code>3、default</code>：无数据时的默认值（默认NULL）</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、核心功能对比</h2>
<table><thead><tr><th>函数</th><th>方向</th><th>典型应用场景</th></tr></thead><tbody><tr><td>LAG</td><td>向前</td><td>计算环比、填充缺失值、异常检测</td></tr><tr><td>LEAD</td><td>向后</td><td>预测趋势、计算后续差值</td></tr></tbody></table>
<p class="maodian"><a name="_label2"></a></p><h2>三、使用示例</h2>
<p>1、查询销售额及前一日数据：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
date,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue
FROM sales</pre></div>
<p>结果中<code>prev_revenue</code>列显示前一日的销售额，首行默认值为0</p>
<p>2、按部门查询员工工资及前一位同事工资：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
deptno,
empname,
salary,
LAG(salary) OVER (PARTITION BY deptno ORDER BY hiredate) AS prev_salary
FROM emp</pre></div>
<p>通过<code>PARTITION BY</code>实现分组内偏移</p>
<p>3、计算每日销售额变化量：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
date,
revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM sales</pre></div>
<p>4、<strong>查询连续3天下单的customer_name，比如zhangsan在12.1、12.2号和12.3号连续3天下单过</strong></p>
<p>补充：<code>TIMESTAMPDIFF函数</code></p>
<p><code>TIMESTAMPDIFF(DAY, buy_date, next1_buy_date)</code> </p>
<p>是 MySQL 中用于计算两个日期之间天数差的函数，其功能解析如下：</p>
<p>函数结构：</p>
<p>1、参数1 <code>DAY</code>：指定返回结果的时间单位（此处为天数）</p>
<p>2、参数2 <code>buy_date</code>：起始日期（较早时间点）</p>
<p>3、参数3 <code>next1_buy_date</code>：结束日期（较晚时间点）</p>
<p>4、返回值：<code>next1_buy_date - buy_date</code> 的天数差（整数，向下取整）</p>
<div class="jb51code"><pre class="brush:sql;">-- 写法一
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;</pre></div>
<div class="jb51code"><pre class="brush:sql;">-- 写法二
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;</pre></div>
頁:
[1]