MENU
  1. Inicio
  2. SQL
  3. Cómo utilizar las funciones FIRST_VALUE y LAST_VALUE en SQL para obtener el primer y último valor

Cómo utilizar las funciones FIRST_VALUE y LAST_VALUE en SQL para obtener el primer y último valor

SQL

En el análisis de datos o la generación de informes con SQL, a menudo se requiere obtener el primer o el último valor de un conjunto de datos. Las funciones FIRST_VALUE y LAST_VALUE facilitan esta tarea. En este artículo, explicaremos detalladamente cómo utilizar estas funciones para manipular datos de manera eficiente, con ejemplos concretos.

Índice

Uso básico de la función FIRST_VALUE

La función FIRST_VALUE se utiliza para obtener el primer valor dentro de una ventana o partición especificada. Esto permite extraer fácilmente el valor de la primera fila de un conjunto de datos.

Sintaxis básica

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS first_value
FROM 
    table_name;

En este ejemplo, se particiona el conjunto de datos por column2 y se ordena por column3, luego se obtiene el primer valor de column1 dentro de cada partición.

Ejemplo de uso

Por ejemplo, si deseamos obtener el nombre del primer empleado que ingresó en cada departamento, podemos usar la siguiente consulta:

SELECT 
    department,
    employee_name,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired
FROM 
    employees;

Esta consulta devuelve el nombre del primer empleado que ingresó en cada departamento.

Ejemplos avanzados de FIRST_VALUE

Además de su uso básico, la función FIRST_VALUE también es útil para consultas y análisis más complejos. A continuación, mostramos ejemplos de cómo utilizar esta función en diferentes escenarios de conjuntos de datos.

Obtener la primera fecha de venta de un producto

Por ejemplo, para obtener la primera fecha de venta de un producto en cada tienda, podemos utilizar la siguiente consulta:

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date) AS first_sale_date
FROM 
    sales;

Esta consulta devuelve la primera fecha de venta de cada producto en cada tienda.

Obtener el primer resultado de un examen de un estudiante

Si estamos gestionando los datos de calificaciones de estudiantes, podemos obtener el primer resultado de examen de cada estudiante.

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS first_exam_score
FROM 
    exam_results;

Esta consulta devuelve la primera calificación de cada estudiante.

Obtener el primer monto de compra en los datos de transacciones

A continuación, mostramos un ejemplo para obtener el primer monto de compra de cada cliente en un conjunto de datos de transacciones.

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS first_purchase_amount
FROM 
    transactions;

Esta consulta devuelve el monto de la primera compra de cada cliente.

Uso básico de la función LAST_VALUE

La función LAST_VALUE se utiliza para obtener el último valor dentro de una ventana o partición especificada. Esto permite extraer fácilmente el valor de la última fila de un conjunto de datos.

Sintaxis básica

SELECT 
    column1,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

En este ejemplo, se particiona el conjunto de datos por column2 y se ordena por column3, luego se obtiene el último valor de column1 en cada partición. Es importante usar la cláusula ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING para abarcar toda la ventana.

Ejemplo de uso

Por ejemplo, si deseamos obtener el nombre del último empleado que ingresó en cada departamento, podemos usar la siguiente consulta:

SELECT 
    department,
    employee_name,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired
FROM 
    employees;

Esta consulta devuelve el nombre del último empleado que ingresó en cada departamento.

Ejemplos avanzados de LAST_VALUE

Al igual que la función FIRST_VALUE, la función LAST_VALUE es extremadamente útil para el análisis de datos o la generación de informes. A continuación, mostramos ejemplos de cómo se puede utilizar esta función con conjuntos de datos específicos.

Obtener la última fecha de venta de un producto

Por ejemplo, si deseamos obtener la última fecha de venta de un producto en cada tienda, podemos usar la siguiente consulta:

SELECT 
    store_id,
    product_id,
    sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esta consulta devuelve la última fecha de venta de cada producto en cada tienda.

Obtener el último resultado de un examen de un estudiante

En la gestión de datos de calificaciones, podemos obtener el último resultado de examen de cada estudiante utilizando la siguiente consulta:

SELECT 
    student_id,
    exam_date,
    score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Esta consulta devuelve la última calificación de cada estudiante.

Obtener el último monto de compra en los datos de transacciones

A continuación, mostramos un ejemplo para obtener el último monto de compra de cada cliente en un conjunto de datos de transacciones.

SELECT 
    customer_id,
    transaction_date,
    amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Esta consulta devuelve el monto de la última compra de cada cliente.

Combinación de FIRST_VALUE y LAST_VALUE

Al combinar las funciones FIRST_VALUE y LAST_VALUE, podemos obtener simultáneamente el primer y el último valor de un conjunto de datos. Esto facilita análisis más avanzados, como la comparación entre los valores al inicio y al final de un período.

Obtener la primera y última fecha de venta de un producto

Por ejemplo, para obtener la primera y última fecha de venta de un producto en cada tienda, podemos utilizar la siguiente consulta:

SELECT 
    store_id,
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esta consulta devuelve la primera y la última fecha de venta de cada producto en cada tienda.

Obtener la primera y última fecha de contratación de empleados

En una base de datos de empleados, si deseamos obtener la primera y última fecha de contratación de cada departamento, podemos usar la siguiente consulta:

SELECT 
    department,
    employee_name,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Esta consulta devuelve los nombres del primer y último empleado que ingresó en cada departamento.

Obtener el primer y último monto de compra en los datos de transacciones

A continuación, mostramos un ejemplo para obtener el primer y último monto de compra de cada cliente en un conjunto de datos de transacciones.

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Esta consulta devuelve el monto de la primera y última compra de cada cliente.

Uso de las funciones como funciones de ventana

Las funciones FIRST_VALUE y LAST_VALUE se pueden utilizar como funciones de ventana, lo que permite un análisis más flexible y detallado de los datos. Al utilizarlas de esta manera, se puede calcular el primer y último valor para cada fila dentro de un conjunto de datos.

Concepto básico de las funciones de ventana

Las funciones de ventana realizan cálculos sobre un conjunto de filas en relación con la fila actual. La ventana se define usando las cláusulas PARTITION BY y ORDER BY.

Sintaxis básica

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

En este caso, se particiona el conjunto de datos por column2 y se ordena por column3, obteniendo así el primer y último valor de column1 dentro de cada partición.

Ejemplo de uso: función de ventana en datos de ventas

Si queremos obtener la primera y última fecha de venta en cada tienda usando una función de ventana, podemos utilizar la siguiente consulta:

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esta consulta devuelve la primera y última fecha de venta para cada tienda.

Ejemplo de uso: función de ventana en datos de calificaciones

Si queremos obtener la primera y última calificación de examen para cada estudiante, podemos usar la siguiente consulta:

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Esta consulta devuelve la primera y última calificación de cada estudiante.

Uso de PARTITION BY y ORDER BY

Una de las características más potentes de las funciones FIRST_VALUE y LAST_VALUE es la capacidad de particionar y ordenar los datos. Esto permite obtener los primeros y últimos valores dentro de un grupo específico de datos.

Concepto básico de particionar los datos

La partición de datos implica agrupar los datos en función de un criterio específico, lo que permite realizar cálculos independientes dentro de cada grupo. Se utiliza la cláusula PARTITION BY para definir las particiones.

Concepto básico de ORDER BY

La cláusula ORDER BY se utiliza para ordenar las filas dentro de cada partición, lo que permite identificar correctamente los primeros y últimos valores.

Ejemplo de uso de partición y ORDER BY

A continuación, mostramos un ejemplo para obtener la primera y última fecha de venta de cada producto en cada tienda:

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Esta consulta devuelve la primera y última fecha de venta para cada producto en cada tienda.

Ejemplo de uso en datos de estudiantes

Si queremos obtener la primera y última calificación de cada estudiante, podemos utilizar la siguiente consulta:

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Esta consulta devuelve la primera y última calificación de cada estudiante.

Ejemplo de uso en datos de transacciones

Si queremos obtener el primer y último monto de compra de cada cliente en los datos de transacciones, podemos usar la siguiente consulta:

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Esta consulta devuelve el monto de la primera y última compra de cada cliente.

Ejercicios y soluciones

Para reforzar el conocimiento adquirido sobre las funciones FIRST_VALUE y LAST_VALUE, hemos preparado algunos ejercicios. Se proporcionan ejemplos de soluciones para que los pruebes por ti mismo.

Ejercicio 1: Obtener la primera y última fecha de contratación por departamento

A partir de la tabla employees, crea una consulta para obtener la primera y última fecha de contratación por departamento.

-- Tabla employees
+---------+------------+------------+
| emp_id  | department | hire_date  |
+---------+------------+------------+
| 1       | Sales      | 2020-01-01 |
| 2       | Sales      | 2021-05-10 |
| 3       | HR         | 2019-03-15 |
| 4       | HR         | 2020-07-23 |
| 5       | IT         | 2021-01-05 |
+---------+------------+------------+

Solución ejemplo

SELECT 
    department,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Ejercicio 2: Obtener la primera y última fecha de venta de cada producto

A partir de la tabla sales, crea una consulta para obtener la primera y última fecha de venta por producto.

-- Tabla sales
+---------+------------+------------+
| sale_id | product_id | sale_date  |
+---------+------------+------------+
| 1       | 101        | 2022-01-01 |
| 2       | 102        | 2022-01-05 |
| 3       | 101        | 2022-02-01 |
| 4       | 103        | 2022-01-10 |
| 5       | 102        | 2022-03-01 |
+---------+------------+------------+

Solución ejemplo

SELECT 
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Ejercicio 3: Obtener el primer y último monto de compra de cada cliente

A partir de la tabla transactions, crea una consulta para obtener el primer y último monto de compra de cada cliente.

-- Tabla transactions
+-------------+----------+--------+--------------+
| transaction_id | customer_id | amount | transaction_date |
+-------------+----------+--------+--------------+
| 1           | 1001     | 200    | 2023-01-01   |
| 2           | 1002     | 150    | 2023-01-05   |
| 3           | 1001     | 300    | 2023-02-01   |
| 4           | 1003     | 250    | 2023-01-10   |
| 5           | 1002     | 400    | 2023-03-01   |
+-------------+----------+--------+--------------+

Solución ejemplo

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Conclusión

En este artículo, hemos aprendido cómo utilizar las funciones FIRST_VALUE y LAST_VALUE en SQL para obtener el primer y último valor de un conjunto de datos. Hemos explorado desde el uso básico hasta ejemplos avanzados, cómo usarlas como funciones de ventana y cómo combinar PARTITION BY y ORDER BY para obtener análisis más detallados. Estas funciones son herramientas valiosas para mejorar la eficiencia en el análisis de datos y la generación de informes. ¡Pruébalas con tus propios conjuntos de datos!

SQL
Análisis de datos Partición SQL ORDER BY Funciones de Ventana FIRST_VALUE LAST_VALUE Datos de Transacciones Notas de Examen Fecha de Venta
Índice