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:

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

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

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

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

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

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

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

LAST_VALUE()

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