last_value 窗口函数用于获取窗口帧中最后一行的值，适用于获取分组或分区内最新记录。1. 基本语法包含 expression、partition by、order by 和可选 frame_clause；2. 默认窗口帧为 range between unbounded preceding and current row，需显式指定 range between unbounded preceding and unbounded following 才能获取整个分区的最后一行；3. 当 order by 列存在重复值时，可通过 row_number() 生成唯一排序键避免错误；4. last_value 与 first_value 分别用于获取最后和第一行的值，适用场景不同；5. 性能优化包括创建索引、避免不必要的排序、选择合适窗口帧、简化窗口内表达式及使用物化视图。正确理解其用法、陷阱和优化方法有助于提升 sql 查询效率和准确性。
在SQL中，LAST_VALUE 窗口函数用于获取窗口帧中最后一行的值。它特别适用于需要访问分组或分区内最后一条记录的场景，比如计算累计百分比、获取最新状态等。理解其用法和一些潜在的坑，能有效提升SQL查询的效率和准确性。
解决方案
LAST_VALUE 的基本语法如下：
LAST_VALUE (expression)
OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
[frame_clause]
)-
expression: 你想要获取的列或表达式。 -
PARTITION BY: 可选，将结果集分成多个分区。 -
ORDER BY: 定义每个分区内行的排序方式。这至关重要，因为LAST_VALUE基于这个顺序来确定最后一行。 -
frame_clause: 可选，定义窗口帧。 默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这可能会导致一些意想不到的结果。
示例：获取每个部门的最新员工信息
假设我们有一个 employees 表，包含员工ID、部门ID和入职日期：
CREATE TABLE employees (
employee_id INT,
department_id INT,
hire_date DATE,
employee_name VARCHAR(255)
);
INSERT INTO employees (employee_id, department_id, hire_date, employee_name) VALUES
(1, 10, '2023-01-15', 'Alice'),
(2, 10, '2023-02-20', 'Bob'),
(3, 20, '2023-03-10', 'Charlie'),
(4, 20, '2023-04-05', 'David'),
(5, 10, '2023-05-01', 'Eve');要获取每个部门最新入职的员工姓名，可以使用以下查询：
SELECT
employee_id,
department_id,
hire_date,
employee_name,
LAST_VALUE(employee_name) OVER (PARTITION BY department_id ORDER BY hire_date) AS latest_employee
FROM
employees;结果会显示每个员工的信息，以及同一部门最新入职的员工姓名。 注意默认的窗口帧，这会导致每一行显示的 latest_employee 都是截止到当前行的最新员工，而不是整个部门的。 要获取整个部门的最新员工，需要显式指定窗口帧：
SELECT
employee_id,
department_id,
hire_date,
employee_name,
LAST_VALUE(employee_name) OVER (PARTITION BY department_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_employee
FROM
employees;常见陷阱：默认窗口帧
LAST_VALUE 最大的陷阱就是其默认的窗口帧定义。 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 意味着，对于每一行，LAST_VALUE 只会考虑从分区开始到当前行的记录。 这在很多情况下不是我们期望的行为，特别是当我们想要获取整个分区最后一行的数据时。 因此，务必根据实际需求显式定义 frame_clause。
如何处理 ORDER BY 子句中的重复值？
当 ORDER BY 子句中存在重复值时，LAST_VALUE 的行为取决于具体的数据库系统和窗口帧的定义。 如果使用默认的 RANGE 窗口帧，所有与当前行具有相同排序值的行都会被认为是当前窗口的一部分。 这可能导致 LAST_VALUE 返回错误的结果。
解决这个问题的方法之一是使用 ROW_NUMBER() 函数生成唯一的排序键，并将其用于 ORDER BY 子句。 例如：
SELECT
employee_id,
department_id,
hire_date,
employee_name,
LAST_VALUE(employee_name) OVER (PARTITION BY department_id ORDER BY hire_date, row_num) AS latest_employee
FROM (
SELECT
employee_id,
department_id,
hire_date,
employee_name,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) as row_num
FROM
employees
) AS subquery;在这个例子中，ROW_NUMBER() 函数为每个部门内的员工分配一个唯一的序号，然后将其与 hire_date 一起用于排序。 这样即使 hire_date 存在重复，也能确保 LAST_VALUE 返回正确的结果。
LAST_VALUE 与 FIRST_VALUE 的区别和应用场景？
LAST_VALUE 用于获取窗口帧中最后一行的值，而 FIRST_VALUE 用于获取窗口帧中第一行的值。 它们的语法类似，但应用场景不同。
-
LAST_VALUE: 适用于需要获取分组或分区内最后一条记录的场景，例如获取最新状态、计算累计百分比等。 -
FIRST_VALUE: 适用于需要获取分组或分区内第一条记录的场景，例如获取最早日期、计算与起始值的差异等。
例如，要计算每个员工的入职日期与部门内最早入职日期的差值，可以使用 FIRST_VALUE：
SELECT
employee_id,
department_id,
hire_date,
employee_name,
hire_date - FIRST_VALUE(hire_date) OVER (PARTITION BY department_id ORDER BY hire_date) AS days_since_first_hire
FROM
employees;这个查询会返回每个员工的入职日期，以及该员工入职日期与同一部门最早入职日期之间的天数差。 注意这里不需要显式指定窗口帧，因为我们只需要第一行的值。
如何优化 LAST_VALUE 的性能？
LAST_VALUE 是一个窗口函数，其性能通常受到数据量和排序方式的影响。 以下是一些优化 LAST_VALUE 性能的技巧：
-
索引优化: 确保
PARTITION BY和ORDER BY子句中使用的列都有适当的索引。 这可以显著提高排序和分组的效率。 -
避免不必要的排序: 如果不需要排序，可以省略
ORDER BY子句。 但这会使LAST_VALUE返回的结果变得不确定。 - 使用合适的窗口帧: 根据实际需求选择合适的窗口帧。 避免使用过于宽泛的窗口帧，这会增加计算量。
- 避免在窗口函数中使用复杂的表达式: 尽量在窗口函数外部计算复杂的表达式，并将结果作为输入传递给窗口函数。
-
考虑使用物化视图: 对于频繁使用的
LAST_VALUE查询，可以考虑创建物化视图。 物化视图可以预先计算结果并将其存储在磁盘上，从而提高查询速度。
总之，LAST_VALUE 是一个强大的窗口函数，可以方便地获取分组或分区内最后一行的数据。 理解其用法、常见陷阱和优化技巧，能帮助你编写更高效、更准确的 SQL 查询。
