FIRST_VALUE 和 LAST_VALUE 函数示例

FIRST_VALUE 和 LAST_VALUE 函数从窗口的第一行和最后一行返回值。这允许查询在无需自连接的情况下一次访问多个行的值。

这两个函数不同于其它窗口集合函数，因为它们必须配合窗口一起使用。同样，不像其它窗口集合函数，这些函数允许使用 IGNORE NULLS 子句。如果指定 IGNORE NULLS，将返回所求表达式的第一个或最后一个非 NULL 值。否则，将返回第一个值或最后一个值。

示例 1：组中第一个条目

FIRST_VALUE 函数可用于在一组有序值中检索第一个条目。对于每张订单，以下查询返回订单中第一项的产品标识符；也就是说，返回每张订单中具有最小 LineID 的项目的 ProductID。

请注意，查询使用 DISTINCT 关键字删除重复项，若不使用 DISTINCT 关键字，每张订单中的每一项都将返回重复行。

SELECT DISTINCT ID,
FIRST_VALUE( ProductID ) OVER ( PARTITION BY ID ORDER BY LineID )
FROM SalesOrderItems
ORDER BY ID;

示例 2：最高销售额百分比

FIRST_VALUE 函数常见的用途是将每行中的一个值与当前组内的最大或最小值进行比较。以下查询计算每个销售代表的总销售额，然后比较同一产品中该销售代表的销售额和总销售额。结果以总销售额的百分比表示。

SELECT s.ProductID AS prod_id, o.SalesRepresentative AS sales_rep,
    SUM( s.Quantity * p.UnitPrice ) AS total_sales,
    100 * total_sales / ( FIRST_VALUE( SUM( s.Quantity * p.UnitPrice ) )
                          OVER Sales_Window ) AS total_sales_percentage
  FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
  GROUP BY o.SalesRepresentative, s.ProductID    
    WINDOW Sales_Window AS ( PARTITION BY s.ProductID 
                             ORDER BY SUM( s.Quantity * p.UnitPrice ) DESC )
  ORDER BY s.ProductID;

示例 3：填充 NULL 值，使数据更紧凑

FIRST_VALUE 和 LAST_VALUE 函数在使数据更紧凑和需要填充值（代替 NULL）时很有用。例如，假设每天完成最高销售额的销售代表获得日销售冠军的称号。下面的查询列出了 2001 年四月的第一个星期的获胜销售代表：

SELECT v.OrderDate, v.SalesRepresentative AS rep_of_the_day
FROM ( SELECT o.SalesRepresentative, o.OrderDate,
              RANK() OVER ( PARTITION BY o.OrderDate
                            ORDER BY SUM( s.Quantity * 
                              p.UnitPrice ) DESC ) AS sales_ranking
       FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
       GROUP BY o.SalesRepresentative, o.OrderDate ) AS v
WHERE v.sales_ranking = 1
AND v.OrderDate BETWEEN '2001-04-01' AND '2001-04-07'
ORDER BY v.OrderDate;

此查询会返回以下结果：

OrderDate	rep_of_the_day
2001-04-01	949
2001-04-02	856
2001-04-05	902
2001-04-06	467
2001-04-07	299

但是，请注意，没有销售额的日子不返回结果。以下查询使数据更紧凑，为没有销售额的日子创建记录。此外，它使用 LAST_VALUE 函数用上次获胜代表的 ID 为 rep_of_the_day（在非获胜日）填充 NULL 值，直到新的获胜者在结果中出现。

SELECT d.dense_order_date,
                   LAST_VALUE( v.SalesRepresentative IGNORE NULLS )
                       OVER ( ORDER BY d.dense_order_date )
                       AS rep_of_the_day
FROM ( SELECT o.SalesRepresentative, o.OrderDate,
              RANK() OVER ( PARTITION BY o.OrderDate
                            ORDER BY SUM( s.Quantity *
                             p.UnitPrice ) DESC ) AS sales_ranking
       FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
       GROUP BY o.SalesRepresentative, o.OrderDate ) AS v
RIGHT OUTER JOIN ( SELECT DATEADD( day, row_num, '2001-04-01' )
                        AS dense_order_date
                   FROM sa_rowgenerator( 0, 6 )) AS d 
ON v.OrderDate = d.dense_order_date AND sales_ranking = 1
ORDER BY d.dense_order_date;

此查询会返回以下结果：

OrderDate	rep_of_the_day
2001-04-01	949
2001-04-02	856
2001-04-03	856
2001-04-04	856
2001-04-05	902
2001-04-06	467
2001-04-07	299

上一个查询的派生表 v 连接到派生表 d，表 d 包含需要考虑的所有日期。这为每个目标日生成一行，但是此外连接在没有销售额的日期的 SalesRepresentative 列中包含 NULL。使用 LAST_VALUE 函数通过将给定行的 rep_of_the_day 定义为到相应日子一直胜出的 SalesRepresentative 的最后非 NULL 值来解决此问题。

FIRST_VALUE 和 LAST_VALUE 函数示例

示例 1：组中第一个条目

示例 2：最高销售额百分比

示例 3：填充 NULL 值，使数据更紧凑

另请参见