当前位置：首页 > article >正文

SQL Server中FIRST_VALUE和 LAST_VALUE窗口函数允许在一个指定的窗口内返回第一个或最后一个值

article 2024/12/28 23:39:45

在 SQL Server 中，FIRST_VALUE 和 LAST_VALUE 是用于窗口函数（Window Functions）的两个非常有用的函数。它们允许你在一个指定的窗口内返回第一个或最后一个值。这两个函数通常与 OVER 子句一起使用，以定义窗口的范围和排序规则。

语法

`FIRST_VALUE`

FIRST_VALUE (scalar_expression) OVER ([PARTITION BY partition_expression, ...] ORDER BY sort_expression [ASC | DESC], ...)

`LAST_VALUE`

LAST_VALUE (scalar_expression) OVER ([PARTITION BY partition_expression, ...] ORDER BY sort_expression [ASC | DESC], ...)

参数

scalar_expression：你想要获取第一个或最后一个值的表达式。
PARTITION BY（可选）：用于将结果集划分为分区，每个分区独立计算 FIRST_VALUE 或 LAST_VALUE。
ORDER BY：定义窗口内行的排序顺序，这对于确定哪个值是第一个或最后一个至关重要。

示例

假设有一个名为 Sales 的表，其中包含以下数据：

CREATE TABLE Sales (
    SaleID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleID, SaleDate, Amount) VALUES
(1, '2023-01-01', 100.00),
(2, '2023-01-02', 150.00),
(3, '2023-01-03', 200.00),
(4, '2023-01-04', 250.00),
(5, '2023-01-05', 300.00);

使用 `FIRST_VALUE`

下面的查询将返回每个销售记录的日期，以及按销售日期排序后的第一个销售金额：

SELECT
    SaleID,
    SaleDate,
    Amount,
    FIRST_VALUE(Amount) OVER (ORDER BY SaleDate ASC) AS FirstAmount
FROM
    Sales;

结果：

SaleID | SaleDate  | Amount | FirstAmount
-------|-----------|--------|------------
1      | 2023-01-01| 100.00 | 100.00
2      | 2023-01-02| 150.00 | 100.00
3      | 2023-01-03| 200.00 | 100.00
4      | 2023-01-04| 250.00 | 100.00
5      | 2023-01-05| 300.00 | 100.00

使用 `LAST_VALUE`

下面的查询将返回每个销售记录的日期，以及按销售日期排序后的最后一个销售金额：

SELECT
    SaleID,
    SaleDate,
    Amount,
    LAST_VALUE(Amount) OVER (ORDER BY SaleDate ASC) AS LastAmount
FROM
    Sales;

结果：

SaleID | SaleDate  | Amount | LastAmount
-------|-----------|--------|-----------
1      | 2023-01-01| 100.00 | 300.00
2      | 2023-01-02| 150.00 | 300.00
3      | 2023-01-03| 200.00 | 300.00
4      | 2023-01-04| 250.00 | 300.00
5      | 2023-01-05| 300.00 | 300.00

分区（Partitioning）

你也可以使用 PARTITION BY 子句来将数据划分为多个分区，每个分区独立计算 FIRST_VALUE 和 LAST_VALUE。例如，假设 Sales 表中有一个 Region 列，你可以按区域分区：

-- 假设 Sales 表中有 Region 列
-- ALTER TABLE Sales ADD Region NVARCHAR(50);
-- UPDATE Sales SET Region = 'North' WHERE SaleID IN (1, 2);
-- UPDATE Sales SET Region = 'South' WHERE SaleID IN (3, 4, 5);

SELECT
    SaleID,
    Region,
    SaleDate,
    Amount,
    FIRST_VALUE(Amount) OVER (PARTITION BY Region ORDER BY SaleDate ASC) AS FirstAmountPerRegion,
    LAST_VALUE(Amount) OVER (PARTITION BY Region ORDER BY SaleDate ASC) AS LastAmountPerRegion
FROM
    Sales;