Funciones analíticas. SQL Estandar

Las funciones analíticas o window functions se utilizan para realizar calculos de una manera más sencilla y elegante que con consultas SQL complejas, subconsultas u operaciones de combinación.

Beneficios de las funciones analíticas:

  • Facilitar la obtención de cálculos complejos en informes y procesos ETL de forma más sencilla.
  • Mejorar el rendimiento de las consultas SQL, al eliminar operaciones de combinación.
  • Proporcionar limpieza al código, minimizando con ello el mantenimiento y mejorando la productividad.
  • Forman parte del SQL estándar.

Ejemplo de función analítica usando marcos, en la que se muestra un campo que muestra las medias del precio de los productos por cada categoría.

SELECT 
  product_name,
  category_id,
  price,
  AVG (price) OVER (PARTITION BY category_id) AS media

FROM 
  ventas.tb_product
  
ORDER BY
  category_id
funciones-analiticas-1

Ejemplo 2 de función analítica usando marcos, en la que se realiza una suma del anterior y posterior de los precios de cada elemento por categorías.

SELECT 
  product_name,
  category_id,
  price,
  SUM (price) OVER (PARTITION BY category_id
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
                   ) AS suma

FROM 
  ventas.tb_product
  
ORDER BY
  category_id
funciones-analiticas-2

 

 

Una vez vistas las funciones estandar, se especificarán algunas de las funciones propias de PostgreSQL:

 

ROW_NUMBER()

Ejemplo 3, numera las filas de productos por categorías

SELECT 
  product_name,
  category_id,
  ROW_NUMBER() OVER (PARTITION BY category_id) AS valor

FROM 
  ventas.tb_product
  
ORDER BY
  category_id
funciones-analiticas-3

 

RANK()

Ejemplo 4, ordena las filas de productos por ranking dentro de cada categória

SELECT
product_name,
category_id,
price,
RANK() OVER (PARTITION BY category_id
ORDER BY price DESC
) AS valor

FROM
ventas.tb_product

ORDER BY
category_id

funciones-analiticas-4

DENSE_RANK()

Igual que la función anterior a excepción de que en caso de empate en rango no deja huecos entre los elementos Ej: 1,2,3,3,4

LAG()

Ejemplo 5, muestra la columna de precios en una nueva fila desplazada 3 posiciones hacia abajo

SELECT 
  product_name,
  price,
  LAG (price, 3) 
  
  OVER (ORDER BY product_name DESC) AS valor

FROM 
  ventas.tb_product
función analítica lag postgres

LEAD()

Ejemplo 5, muestra la columna de precios en una nueva fila desplazada 3 posiciones hacia arriba

SELECT 
  product_name,
  price,
  LEAD (price, 3) 
  
  OVER (ORDER BY product_name DESC) AS valor

FROM 
  ventas.tb_product
función analítica lead

FIRST_VALUE()

SELECT 
  product_name,
  category_id,
  price,
  price - FIRST_VALUE(price) OVER (PARTITION BY category_id
                     ORDER BY price DESC
                    ) AS diferencia
FROM 
  ventas.tb_product
  
ORDER BY
  category_id
función -analitica first_value

LAST_VALUE()

Igual que la función anterior a excepción de que en vez de coger el mayor se coge el menor.

Otros artículos que pueden ser de interés:

Autor: Diego Calvo