lead() over(partition by order by )与lag() over(partition by order by )的用法

`lead()` 和 `lag()` 都是窗口函数，可以用来访问一个数据集中的前后行。`lead()` 函数返回从当前行开始的指定行数后的行，而 `lag()` 函数返回从当前行开始的指定行数前的行。

`lead()` 和 `lag()` 都可以添加 `partition by` 和 `order by` 子句来进行分组和排序。`partition by` 子句用于将数据集划分为多个小组，每个小组内部按照指定列进行排序，而 `order by` 子句则用于对整个数据集进行排序。

例如，以下是使用 `lead()` 函数获取下一个月销售额的示例：

SELECT month, sales, LEAD(sales) OVER (ORDER BY month) AS next_month_sales
FROM sales_data

在上面的例子中，我们使用 `order by` 按照月份对数据集进行排序，并使用 `lead()` 函数获取每个月的下一个月的销售额。

以下是使用 `lag()` 函数获取上一个月销售额的示例：

SELECT month, sales, LAG(sales) OVER (ORDER BY month) AS last_month_sales
FROM sales_data

在上面的例子中，我们使用 `order by` 按照月份对数据集进行排序，并使用 `lag()` 函数获取每个月的上一个月的销售额。

相关问题

oracle over order by

Oracle的窗口函数`OVER (ORDER BY)`是SQL查询中的一个高级特性，它允许你在分组查询结果的基础上，对每个组内的行进行排序，并基于这个排序执行计算。`OVER`关键字后面跟着一个`(ORDER BY)`子句，其中列名指定了用于排序的列。

窗口函数在SQL中主要用于分析每个分组中的数据，比如计算每一行的排名、行数、累计总和等，而不仅仅是针对整个查询结果。例如，`RANK()`、`ROW_NUMBER()`、`LEAD()`、`LAG()`等都是常见的窗口函数，它们会在每个分组内部为行分配一个唯一的标识或进行前后行的比较。

使用`OVER (ORDER BY)`的一个常见场景是在统计每个部门员工的平均工资，先按照工资排序，然后计算每个员工相对于其所在部门其他员工的工资位置。

这里是几个相关的概念：

1. **窗口函数的使用**：如`ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary)`, 这会为每个部门按工资排序并分配一个行号。

2. **RANK()和DENSE_RANK()**：这两个函数会根据指定的顺序分配等级，但RANK有重复值时会有间隙，DENSE_RANK则不会有。

3. **LEAD()和LAG()**：前者返回当前行之后的行，后者返回之前的行，这对于移动窗口分析很有用。

lead(a,1) over (partition by b order by b,a) 当a有重复时，怎样能取到不同于a的下一条数据

如果要在 `lead()` 函数中取到不同于 `a` 的下一条数据，可以考虑在 `order by` 中使用 `CASE` 表达式来实现。具体来说，可以将 `CASE` 表达式嵌套在 `order by` 中，根据 `a` 是否与前一行的 `a` 相同来决定排序的优先级。

例如，假设有以下表格 `tbl`：

+---+---+
| a | b |
+---+---+
| 1 | A |
| 2 | A |
| 2 | A |
| 3 | B |
| 4 | B |
+---+---+

现在想要对 `b` 进行分组，并且在每个分组内，对于相同的 `a` 取下一条数据，如果下一条数据的 `a` 与当前行相同，则再取下一条数据。可以使用以下 SQL 语句：

SELECT a, b, LEAD(a, 1) OVER (PARTITION BY b ORDER BY b, CASE WHEN a = lag(a) OVER (PARTITION BY b ORDER BY b, a) THEN 1 ELSE 0 END, a) AS next_a
FROM tbl;

查询结果如下：

+---+---+--------+
| a | b | next_a |
+---+---+--------+
| 1 | A |      2 |
| 2 | A |      3 |
| 2 | A |      3 |
| 3 | B |      4 |
| 4 | B |   NULL |
+---+---+--------+

可以看到，对于 `b` 分组后的每个分组，在排序时，先按照 `a` 是否与前一行相同进行判断，如果相同则优先级较低，否则按照 `a` 的大小进行排序。这样就可以确保取到不同于 `a` 的下一条数据了。