SQL
Функции (аналитические)

Вступление

Синтаксис

1. FIRST_VALUE (скалярное выражение) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
2. LAST_VALUE (скалярное выражение) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
3. LAG (scalar_expression [, offset] [, default]) OVER ([partition_by_clause] order_by_clause)
4. LEAD (scalar_expression [, offset], [default]) OVER ([partition_by_clause] order_by_clause)
5. PERCENT_RANK () OVER ([partition_by_clause] order_by_clause)
6. CUME_DIST () OVER ([partition_by_clause] order_by_clause)
7. PERCENTILE_DISC (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])
8. PERCENTILE_CONT (numeric_literal) WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])

FIRST_VALUE

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

SELECT StateProvinceID, Name, TaxRate,
       FIRST_VALUE(StateProvinceID)
        OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

В этом примере функция FIRST_VALUE используется для возврата ID штата или провинции с самой низкой ставкой налога. Предложение OVER используется для заказа ставок налога для получения самой низкой ставки.

LAST_VALUE

Функция LAST_VALUE предоставляет последнее значение в упорядоченном наборе результатов, который вы определяете с помощью скалярного выражения.

SELECT TerritoryID, StartDate, BusinessentityID,
       LAST_VALUE(BusinessentityID) 
        OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

В этом примере функция LAST_VALUE возвращает последнее значение для каждого набора строк в упорядоченных значениях.

LAG и LEAD

Функция LAG предоставляет данные о строках перед текущей строкой в ​​том же наборе результатов. Например, в SELECT вы можете сравнивать значения в текущей строке со значениями в предыдущей строке.

Вы используете скалярное выражение для указания значений, которые следует сравнивать. Параметр offset - это количество строк перед текущей строкой, которые будут использоваться при сравнении. Если вы не укажете количество строк, используется значение по умолчанию для одной строки.

Параметр по умолчанию указывает значение, которое должно быть возвращено, когда выражение со смещением имеет значение NULL . Если вы не укажете значение, возвращается значение NULL .

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

Вы указываете значения, которые следует сравнивать с помощью скалярного выражения. Параметр offset - это количество строк после текущей строки, которая будет использоваться при сравнении.

Вы указываете значение, которое должно быть возвращено, когда выражение со смещением имеет значение NULL используя параметр по умолчанию. Если вы не укажете эти параметры, используется значение по умолчанию для одной строки и возвращается значение NULL .

SELECT BusinessEntityID, SalesYTD,
       LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
       LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

В этом примере используются функции LEAD и LAG для сравнения значений продаж для каждого сотрудника на сегодняшний день с показателями сотрудников, перечисленных выше и ниже, с записями, упорядоченными на основе столбца BusinessEntityID.

PERCENT_RANK и CUME_DIST

Функция PERCENT_RANK вычисляет ранжирование строки относительно набора строк. Процент основан на количестве строк в группе, которые имеют меньшее значение, чем текущая строка.

Первое значение в наборе результатов всегда имеет процентный ранг нуля. Значение для наивысшего ранжированного или последнего значения в наборе всегда одно.

Функция CUME_DIST вычисляет относительное положение заданного значения в группе значений, определяя процент значений, меньших или равных этому значению. Это называется кумулятивным распределением.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Cumulative Distribution"
FROM Employee;

В этом примере вы используете предложение ORDER для разделения или группировки - строки, полученные SELECT на основе названий рабочих мест сотрудников, при этом результаты в каждой группе сортируются в зависимости от количества часов отпуска по болезни, которые использовались сотрудниками.

Функция PERCENT_RANK оценивает записи внутри каждой группы. Для каждой записи возвращается процент записей в той же группе, которые имеют более низкие значения.

Функция CUME_DIST аналогична, за исключением того, что она возвращает процент значений, меньших или равных текущему значению.

PERCENTILE_DISC и PERCENTILE_CONT

Функция PERCENTILE_DISC отображает значение первой записи, где кумулятивное распределение выше, чем процентиль, который вы предоставляете, используя параметр numeric_literal .

Значения сгруппированы по набору строк или разделов, как указано в предложении WITHIN GROUP .

Функция PERCENTILE_CONT аналогична функции PERCENTILE_DISC , но возвращает среднее значение суммы первой соответствующей записи и следующей записи.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

Чтобы найти точное значение из строки, которая соответствует или превышает 0,5 процентиля, вы передаете процентиль в виде числового литерала в функции PERCENTILE_DISC . Столбец Percentile Discreet в результирующем наборе перечисляет значение строки, в которой кумулятивное распределение выше указанного процентиля.

Чтобы основывать вычисления на наборе значений, вы используете функцию PERCENTILE_CONT . Столбец «Percentile Continuous» в результатах отображает среднее значение суммы значения результата и следующего максимального значения соответствия.

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;