SQL
Funciones (analíticas)

Introducción

Sintaxis

1. FIRST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
2. LAST_VALUE (scalar_expression) OVER ([partition_by_clause] order_by_clause [rows_range_clause])
3. LAG (scalar_expression [, offset] [, predeterminado]) OVER ([partition_by_clause] order_by_clause)
4. LEAD (scalar_expression [, offset], [predeterminado]) 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 (ORDEN POR order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])
8. PERCENTILE_CONT (numeric_literal) WITHIN GROUP (ORDEN POR order_by_expression [ASC | DESC]) OVER ([<partition_by_clause>])

FIRST_VALUE

Utiliza la función FIRST_VALUE para determinar el primer valor en un conjunto de resultados ordenados, que identifica utilizando una expresión escalar.

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

En este ejemplo, la función FIRST_VALUE se usa para devolver el ID del estado o provincia con la tasa impositiva más baja. La cláusula OVER se utiliza para ordenar las tasas de impuestos para obtener la tasa más baja.

LAST_VALUE

La función LAST_VALUE proporciona el último valor en un conjunto de resultados ordenados, que usted especifica usando una expresión escalar.

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

Este ejemplo utiliza la función LAST_VALUE para devolver el último valor para cada conjunto de filas en los valores ordenados.

LAG y LEAD

La función LAG proporciona datos en filas antes de la fila actual en el mismo conjunto de resultados. Por ejemplo, en una instrucción SELECT , puede comparar valores en la fila actual con valores en una fila anterior.

Utiliza una expresión escalar para especificar los valores que deben compararse. El parámetro de desplazamiento es el número de filas antes de la fila actual que se utilizará en la comparación. Si no especifica el número de filas, se utiliza el valor predeterminado de una fila.

El parámetro predeterminado especifica el valor que debe devolverse cuando la expresión en el desplazamiento tiene un valor NULL . Si no especifica un valor, se devuelve un valor de NULL .

La función LEAD proporciona datos en filas después de la fila actual en el conjunto de filas. Por ejemplo, en una declaración SELECT , puede comparar valores en la fila actual con valores en la fila siguiente.

Usted especifica los valores que deben compararse usando una expresión escalar. El parámetro de desplazamiento es el número de filas después de la fila actual que se utilizará en la comparación.

Usted especifica el valor que debe devolverse cuando la expresión en el desplazamiento tiene un valor NULL usando el parámetro predeterminado. Si no especifica estos parámetros, se utiliza el valor predeterminado de una fila y se devuelve un valor de 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;

Este ejemplo utiliza las funciones LEAD y LAG para comparar los valores de ventas de cada empleado hasta la fecha con los de los empleados enumerados arriba y abajo, con registros ordenados según la columna BusinessEntityID.

PERCENT_RANK y CUME_DIST

La función PERCENT_RANK calcula la clasificación de una fila en relación con el conjunto de filas. El porcentaje se basa en el número de filas del grupo que tienen un valor inferior al de la fila actual.

El primer valor en el conjunto de resultados siempre tiene un rango de porcentaje de cero. El valor para el valor más alto (o último) en el conjunto es siempre uno.

La función CUME_DIST calcula la posición relativa de un valor especificado en un grupo de valores, determinando el porcentaje de valores menores o iguales a ese valor. Esto se llama la distribución acumulativa.

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;

En este ejemplo, usa una cláusula ORDER para particionar, o agrupar, las filas recuperadas por la declaración SELECT en función de los títulos de trabajo de los empleados, con los resultados en cada grupo ordenados según el número de horas de licencia por enfermedad que los empleados han utilizado.

La función PERCENT_RANK clasifica las entradas dentro de cada grupo. Para cada entrada, devuelve el porcentaje de entradas en el mismo grupo que tienen valores más bajos.

La función CUME_DIST es similar, excepto que devuelve el porcentaje de valores menores o iguales al valor actual.

PERCENTILE_DISC y PERCENTILE_CONT

La función PERCENTILE_DISC enumera el valor de la primera entrada donde la distribución acumulada es más alta que el percentil que proporciona usando el parámetro numeric_literal .

Los valores se agrupan por conjunto de filas o partición, según lo especificado por la cláusula WITHIN GROUP .

La función PERCENTILE_CONT es similar a la función PERCENTILE_DISC , pero devuelve el promedio de la suma de la primera entrada coincidente y la entrada siguiente.

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;

Para encontrar el valor exacto de la fila que coincide o supera el percentil 0.5, se pasa el percentil como el literal numérico en la función PERCENTILE_DISC . La columna Percentil discreto en un conjunto de resultados enumera el valor de la fila en la cual la distribución acumulativa es más alta que el percentil especificado.

Para basar el cálculo en un conjunto de valores, utilice la función PERCENTILE_CONT . La columna "Percentil Continuo" en los resultados enumera el valor promedio de la suma del valor del resultado y el siguiente valor coincidente más alto.

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;