【Mysql系列】LAG与LEAD开窗函数
作者:mmseoamin日期:2023-12-11

💝💝💝欢迎来到我的博客,很高兴能够在这里和您见面!希望您在这里可以感受到一份轻松愉快的氛围,不仅可以获得有趣的内容和知识,也可以畅所欲言、分享您的想法和见解。

【Mysql系列】LAG与LEAD开窗函数,img,第1张

  • 推荐:kwan 的首页,持续学习,不断总结,共同进步,活到老学到老
  • 导航
    • 檀越剑指大厂系列:全面总结 java 核心技术点,如集合,jvm,并发编程 redis,kafka,Spring,微服务,Netty 等
    • 常用开发工具系列:罗列常用的开发工具,如 IDEA,Mac,Alfred,electerm,Git,typora,apifox 等
    • 数据库系列:详细总结了常用数据库 mysql 技术点,以及工作中遇到的 mysql 问题等
    • 懒人运维系列:总结好用的命令,解放双手不香吗?能用一个命令完成绝不用两个操作
    • 数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂

      非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨

博客目录

    • 一.基本介绍
      • 1.LAG 和 LEAD 概述
      • 2.作用特性
      • 3.LAG 函数
      • 4.LEAD 函数
      • 二.使用案例
        • 1.LAG 示例
        • 2.LEAD 示例
        • 3.总结说明

          一.基本介绍

          1.LAG 和 LEAD 概述

          在数据库查询中,开窗函数(Window Functions)是一种强大的工具,用于在结果集的子集上执行计算。LAG和LEAD是两个常用的开窗函数,它们允许你在当前行的前面或后面的行中访问数据。

          在数据库中,LAG和LEAD函数的全称是:

          • LAG函数的全称是"LAST VALUE"。
          • LEAD函数的全称是"LEAD VALUE"。

            这两个函数是 SQL 标准中的开窗函数,它们允许在查询结果的子集上执行类似于聚合函数的计算,但不会导致结果集的单一行。它们的目的是在当前行的前面(LAG)或后面(LEAD)的行中访问数据,使得在处理时间序列数据或需要比较相邻行之间的值时非常有用。

            2.作用特性

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

            在实际应用当中,若要用到取今天和昨天的某字段差值时,Lag 和 Lead 函数的应用就显得尤为重要。当然,这种操作可以用表的自连接实现,但是 LAG 和 LEAD 与 left join、right join 等自连接相比,效率更高,SQL 更简洁。

            3.LAG 函数

            LAG函数用于获取结果集中当前行之前的某一行的值。语法如下:

            LAG (expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
            
            • expression: 要检索的列或表达式。
            • offset: 要返回的行数,如果不指定,默认为 1,即上一行。
            • default: 当指定的行数超出结果集范围时,返回的默认值。

              4.LEAD 函数

              LEAD函数用于获取结果集中当前行之后的某一行的值。语法如下:

              LEAD (expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
              
              • expression: 要检索的列或表达式。
              • offset: 要返回的行数,如果不指定,默认为 1,即下一行。
              • default: 当指定的行数超出结果集范围时,返回的默认值。

                二.使用案例

                1.LAG 示例

                考虑一个名为sales的表,包含销售数据:

                CREATE TABLE sales (
                    sale_date DATE,
                    revenue INT
                );
                INSERT INTO sales VALUES
                ('2023-01-01', 100),
                ('2023-01-02', 150),
                ('2023-01-03', 200),
                ('2023-01-04', 120);
                

                使用LAG函数,你可以获取前一天的销售额:

                SELECT sale_date                                             -- 日期
                     , revenue-- 当前销售额
                     , LAG(revenue) OVER (ORDER BY sale_date) AS lag_revenue -- 前一行的销售额
                FROM sales;
                

                结果:

                | sale_date  | revenue | lag_revenue |
                |------------|---------|-------------|
                | 2023-01-01 | 100     | NULL        |
                | 2023-01-02 | 150     | 100         |
                | 2023-01-03 | 200     | 150         |
                | 2023-01-04 | 120     | 200         |
                

                2.LEAD 示例

                使用LEAD函数,你可以获取后一天的销售额:

                SELECT sale_date-- 日期
                     , revenue  -- 日期
                     , LEAD(revenue) OVER (ORDER BY sale_date) AS lead_revenue -- 后一行的销售额
                FROM sales;
                

                结果:

                | sale_date  | revenue | lead_revenue |
                |------------|---------|--------------|
                | 2023-01-01 | 100     | 150          |
                | 2023-01-02 | 150     | 200          |
                | 2023-01-03 | 200     | 120          |
                | 2023-01-04 | 120     | NULL         |
                

                在这两个示例中,LAG和LEAD函数通过ORDER BY子句按销售日期对结果集进行排序。这允许你在时间序列数据中访问前一行或后一行的值,以进行比较或计算差异等操作。

                3.总结说明

                1. 时间序列分析: 在时间序列数据中,你可能需要比较当前时间点的值与前一时间点或后一时间点的值,以便分析趋势、变化或周期性。
                2. 差异计算: 你可能需要计算当前行与前一行或后一行的差异,例如,计算每日销售额的增长或减少量。
                3. 移动平均计算: 通过结合LAG和LEAD函数,你可以计算移动平均值,以平滑数据并更好地理解趋势。
                4. 排名和百分比计算: 通过比较当前行与前一行或后一行的值,你可以进行排名或计算百分比变化,从而了解相对于其他行的位置或变化。
                5. 查找极值点: 通过比较当前值与相邻值,你可以轻松地识别极值点,例如找到峰值或谷值。
                6. 数据填充: 你可以使用LAG和LEAD函数来填充缺失的数据。如果某些行的数据缺失,你可以使用相邻行的值进行填充。
                7. 数据平滑计算: 你可以使用LAG和LEAD函数来进行数据平滑计划,让结果绘制的曲线更加平滑。这种操作可以代替表的自联接,并且 LAG 和 LEAD 有更高的效率。

                LAG 和 LEAD 主要用来计算当前行的前后 N 行的这种场景,一般情况下我们会对数据进行排序,因为只有在有序的情况下,前面多少行和后面多少行才有意义。

                LAG 和 LEAD 可以用在某些场景下代替自关联的写法。

                觉得有用的话点个赞 👍🏻 呗。

                ❤️❤️❤️本人水平有限,如有纰漏,欢迎各位大佬评论批评指正!😄😄😄

                💘💘💘如果觉得这篇文对你有帮助的话,也请给个点赞、收藏下吧,非常感谢!👍 👍 👍

                🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧!🌙🌙🌙

                【Mysql系列】LAG与LEAD开窗函数,img,第2张