mysql中的lead函数和over函数

mysql中的lead函数和over函数

作用

over函数是用来分组加排序的，然后等over函数分好组排好序之后，再使用lead函数去找当前分组内的下一行数据的某个字段值，具体是哪个字段由over函数指定，如果当前分组内没有下一条数据的话，那么返回null空值。

需求场景

我们有一个销售表，有很多类型的产品，我们想要根据产品id分组，然后再根据产品销售时间排序，我们想要显示下个月的销售额，以及下个月增加的销售额。

上面这个场景需求就是可以使用lead函数结合over函数来实现。

实现方法

首先看一下我们的销售表结构，如下图：

然后看一下里面我们存储的数据，如下图：

接着来看下我们执行的sql语句如下：

SELECT
    product_id,
    sales_date,
    sales_amount,
    LEAD(sales_amount) OVER (PARTITION BY product_id ORDER BY sales_date) AS next_sales_amount,
    (LEAD(sales_amount) OVER (PARTITION BY product_id ORDER BY sales_date) - sales_amount) AS growth_amount
FROM
    sales_data;

我们上面查询的时候追加了两个字段，一个是next_sales_amount，也就是分组内下一条数据的销售额；另外一个是growth_amount，也就是销售额增长量，也就是分组内下一条数据的销售额减去当前销售额；

over函数的功能：现根据product_id产品id进行分组，分组完之后又根据sales_date销售日期进行排序，然后取出当前行的销售额sales_amount；
lead函数的功能：取出当前分组的下一条数据的某个字段的值，如果当前分组内已经是最后一条数据了，那么会返回null。

上面的sql语句的执行结果，如下图：

group by分组和使用over函数分组有什么区别？

1.输出结果不同

• over函数：over函数允许在不减少结果集行数的情况下添加聚合列。即每行都可以保留原有的列值的同时，增加额外的分析结果列，比如说下一条数据是多少，或者是下一条数据比起当前数据的增长额是多少。
• group by子句：group by子句则将数据汇总为较少的行，每个分组只返回一行聚合结果，因此，它的输出行通常会比表的原始行数要少，适用于需要对数据进行高度汇总的情况。比如在student学生表中，计算每个班级的学生的平均分，这个使用group by子句先要根据班级id进行分组，然后再使用avg(score)聚合函数计算平均分，原始数据可能有540行，如果一个班级90人的话，那么使用group by之后的输出结果只会有6行，因为只有两个班级嘛！但是如果是使用over函数进行班级分组的话，那么输出结果行数仍会有540行。

2.适用场景不同

• over函数：适用于需要同时查看原始行和聚合数据的场景，输出结果中不会少原始行，并且也会新增聚合数据。例如，在销售数据表中计算每个产品在连续月份中的销售额增长情况，可以使用lead函数结合over函数来实现。
• group by子句：适用于需要对数据进行分组并汇总的场景。例如，计算每个部门的总薪水时，使用group by子句可以直接得到每个部门的薪水总和，而不显示每个员工的薪水详情。可能你的员工表的数据有1000行，但是公司只有36个部门，那么你的输出结果行数就只有36行。