FIRST VALUE: примеры (SQL)

Полный обзор функции FIRST_VALUE в Microsoft SQL Server
Раздел: Аналитические функции, Оконные
FIRST_VALUE(scalar_expression OVER [PARTITION BY ] ORDER BY  [ROWS/ RANGE ]): Same as scalar_expression
типовые примерыFIRST VALUE на mySQL, Oracle, PostgreSQL, SQLiteFIRST VALUE изменения sqlпримеры расширенные

Описание функции FIRST_VALUE

Функция FIRST_VALUE является оконной функцией в Microsoft SQL Server, которая возвращает первое значение из упорядоченного набора строк в рамках окна, определенного предложением OVER. Эта функция появилась в SQL Server 2012 и относится к категории аналитических функций, работающих с окнами данных.

Основное применение функции связано с необходимостью доступа к значению из первой строки окна без использования самосоединений или коррелированных подзапросов. Она часто используется для анализа временных рядов, сравнения текущих значений с начальными или для вычисления разностей.

Синтаксис и аргументы

FIRST_VALUE ( [scalar_expression ] )
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
  • scalar_expression - Выражение, значение которого возвращается из первой строки окна. Может быть столбцом, константой, переменной, скалярным подзапросом или функцией. Не может быть другой оконной функцией или агрегатной функцией.
  • OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] ) - Предложение, определяющее окно для функции.
    • partition_by_clause - Разделяет результирующий набор на секции (окна), к которым применяется функция. Если не указано, функция обрабатывает все строки как одну секцию.
    • order_by_clause - Обязательный аргумент. Определяет логический порядок строк в каждой секции, по которому определяется первая строка.
    • rows_range_clause - Дополнительно ограничивает строки в рамках секции, указывая начальную и конечную точки. Для FIRST_VALUE часто используется ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW или ROWS UNBOUNDED PRECEDING.

Функция возвращает значение того же типа, что и переданное scalar_expression. Если секция не содержит строк, возвращается NULL.

Базовые примеры использования

Простой пример с выводом первого значения в упорядоченном наборе.

SELECT
    ProductID,
    Name,
    ListPrice,
    FIRST_VALUE(Name) OVER (ORDER BY ListPrice) as FirstProduct
FROM Production.Product
WHERE ProductSubcategoryID = 1;
ProductID  Name                     ListPrice   FirstProduct
---------  -----------------------  ----------  --------------
707        Sport-100 Helmet, Red   33.6442     Sport-100 Helmet, Red
...

Использование секционирования (PARTITION BY).

SELECT
    TerritoryID,
    SalesOrderID,
    TotalDue,
    FIRST_VALUE(TotalDue) OVER (
        PARTITION BY TerritoryID
        ORDER BY OrderDate
    ) as FirstOrderInTerritory
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2011-01-01';
TerritoryID  SalesOrderID  TotalDue    FirstOrderInTerritory
-----------  ------------  ----------  -----------------------
1            43659         23153.2339  23153.2339
1            43660         1457.3288   23153.2339
...

Пример с явным указанием рамки окна (ROWS UNBOUNDED PRECEDING).

SELECT
    EmployeeID,
    Rate,
    RateChangeDate,
    FIRST_VALUE(Rate) OVER (
        PARTITION BY EmployeeID
        ORDER BY RateChangeDate
        ROWS UNBOUNDED PRECEDING
    ) as StartingRate
FROM HumanResources.EmployeePayHistory;
EmployeeID  Rate      RateChangeDate    StartingRate
----------  --------  ----------------  ------------
1           12.45     2009-01-14        12.45
1           25.50     2009-05-31        12.45
...

Похожие функции в MS SQL

  • LAST_VALUE - Возвращает последнее значение из окна. Имеет аналогичный синтаксис, но без дополнительной рамки окна (например, ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) может вести себя неинтуитивно, возвращая текущее значение.
  • LAG - Обращается к данным из предыдущей строки относительно текущей на заданное количество шагов назад. Более гибкая, чем FIRST_VALUE, для последовательного доступа к предыдущим строкам, но менее удобна для получения самого первого значения в секции.
  • NTH_VALUE - Доступна начиная с SQL Server 2012. Позволяет получить значение из строки с произвольным порядковым номером в окне (например, второе или третье). FIRST_VALUE(expr) логически эквивалентна NTH_VALUE(expr, 1).
  • MIN или MAX как оконные функции - Могут давать тот же результат, если порядок строк не важен, а нужно просто минимальное или максимальное значение в окне. Однако FIRST_VALUE строго зависит от порядка, заданного в ORDER BY.

Выбор функции зависит от задачи. FIRST_VALUE предпочтительнее, когда важен именно порядок, заданный ORDER BY, а не экстремальное значение столбца. Для получения предыдущего/следующего значения удобнее LAG/LEAD. Для произвольной позиции в окне используется NTH_VALUE.

Распространенные ошибки

Отсутствие обязательного предложения ORDER BY в OVER().

-- ОШИБКА: Предложение ORDER BY обязательно для оконных функций смещения.
SELECT
    FIRST_VALUE(Name) OVER (PARTITION BY ProductSubcategoryID)
FROM Production.Product;

Неправильное понимание рамки окна по умолчанию для FIRST_VALUE. По умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, что для некоторых задач может привести к неожиданным результатам, если в порядке есть одинаковые значения.

-- Если OrderDate имеет дубликаты, FIRST_VALUE может вести себя не как ожидается при RANGE.
SELECT
    CustomerID,
    OrderDate,
    TotalDue,
    FIRST_VALUE(TotalDue) OVER (
        PARTITION BY CustomerID
        ORDER BY OrderDate
        -- Без явного указания ROWS/ RANGE
    ) as FirstOrder
FROM Sales.SalesOrderHeader;

Для точного контроля лучше явно указывать ROWS UNBOUNDED PRECEDING.

Использование функции в неподдерживаемых контекстах, например, в предложении WHERE или HAVING без вложенного запроса.

-- ОШИБКА: Оконные функции можно использовать только в SELECT или ORDER BY.
SELECT EmployeeID, Rate
FROM HumanResources.EmployeePayHistory
WHERE FIRST_VALUE(Rate) OVER (PARTITION BY EmployeeID ORDER BY RateChangeDate) > 10;

Для фильтрации по результату оконной функции требуется обернуть запрос.

История изменений

  • SQL Server 2012 (11.x) - Функция FIRST_VALUE была впервые представлена вместе с другими оконными функциями смещения (LAST_VALUE, LAG, LEAD).
  • SQL Server 2017 (14.x) и более поздние - Существенных изменений в синтаксисе или поведении функции не было. Все улучшения связаны с общей оптимизацией оконных функций и производительности обработки запросов, особенно при использовании индексов, подходящих для ORDER BY в предложении OVER.
  • Стандартизация функции соответствует стандарту ANSI SQL:2003.

Расширенные примеры

Расчет накопленного итога и сравнение с первым значением.

Пример sql
SELECT
    MONTH(OrderDate) as OrderMonth,
    SUM(TotalDue) as MonthlySales,
    SUM(SUM(TotalDue)) OVER (ORDER BY MONTH(OrderDate) ROWS UNBOUNDED PRECEDING) as RunningTotal,
    FIRST_VALUE(SUM(TotalDue)) OVER (ORDER BY MONTH(OrderDate)) as FirstMonthSales,
    (SUM(SUM(TotalDue)) OVER (ORDER BY MONTH(OrderDate) ROWS UNBOUNDED PRECEDING) -
     FIRST_VALUE(SUM(TotalDue)) OVER (ORDER BY MONTH(OrderDate))) as DiffFromFirst
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2011
GROUP BY MONTH(OrderDate)
ORDER BY OrderMonth;
OrderMonth  MonthlySales  RunningTotal  FirstMonthSales  DiffFromFirst
----------  ------------  ------------  ---------------  -------------
1           126317.6879   126317.6879   126317.6879      0.0000
2           158044.9386   284362.6265   126317.6879      158044.9386
...

Использование в составе CASE для логического разделения.

Пример sql
SELECT
    SalesOrderID,
    CustomerID,
    OrderDate,
    TotalDue,
    CASE
        WHEN TotalDue >= FIRST_VALUE(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) * 1.5
        THEN 'Значительно вырос'
        ELSE 'В пределах нормы'
    END as GrowthStatus
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

Поиск даты первого заказа и сравнение с текущим интервалом.

Пример sql
SELECT
    CustomerID,
    OrderDate,
    TotalDue,
    DATEDIFF(day,
        FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate),
        OrderDate
    ) as DaysSinceFirstOrder
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (11000, 11001);
CustomerID  OrderDate   TotalDue    DaysSinceFirstOrder
----------  ----------  ----------  --------------------
11000       2009-05-31  3953.9884   0
11000       2009-07-01  3953.9884   31
...

Комбинирование с другими оконными функциями для комплексного анализа.

Пример sql
SELECT
    ProductID,
    LocationID,
    Quantity,
    FIRST_VALUE(Quantity) OVER (PARTITION BY ProductID ORDER BY Quantity) as MinStock,
    LAST_VALUE(Quantity) OVER (PARTITION BY ProductID ORDER BY Quantity ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as MaxStock,
    AVG(Quantity) OVER (PARTITION BY ProductID) as AvgStock
FROM Production.ProductInventory
ORDER BY ProductID, Quantity;

Применение к строковым данным для нахождения первой по алфавиту записи в группе.

Пример sql
SELECT
    Department,
    Employee,
    Salary,
    FIRST_VALUE(Employee) OVER (PARTITION BY Department ORDER BY Employee) as FirstEmployeeAlphabetically
FROM dbo.EmployeeTable
ORDER BY Department, Employee;

Аналоги в других СУБД и языках

PostgreSQL

Поддерживает FIRST_VALUE с синтаксисом, идентичным SQL Server.

-- PostgreSQL
SELECT
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY payment_date)
FROM payment;

Oracle

Также имеет функцию FIRST_VALUE. Отличие может быть в обработке NULL значений с помощью предложения IGNORE NULLS, которое в SQL Server для этой функции недоступно.

-- Oracle с IGNORE NULLS
FIRST_VALUE(column_name IGNORE NULLS) OVER (ORDER BY ...)

MySQL (с версии 8.0)

Реализация соответствует стандарту SQL и аналогична SQL Server.

-- MySQL 8.0
SELECT
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date)
FROM employees;

SQLite (с версии 3.25.0)

Добавлена поддержка оконных функций, включая FIRST_VALUE.

-- SQLite
SELECT
    FIRST_VALUE(quantity) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)
FROM orders;

Системы без прямой поддержки

В Sybase ASE до определенных версий или в более старых СУБД можно эмулировать поведение с помощью подзапросов:

-- Эмуляция через коррелированный подзапрос
SELECT
    t1.id,
    t1.value,
    t1.date,
    (SELECT TOP 1 t2.value
     FROM table t2
     WHERE t2.id = t1.id
     ORDER BY t2.date) as first_value
FROM table t1;

MS SQL FIRST_VALUE function comments

En
FIRST VALUE Returns the first value in an ordered set of values