Solución - Precio de productos en una fecha determinada

Visión General

Referencia al problema:

Encontrar los precios de todos los productos el día 2019-08-16. Asuma que el precio de todos los productos antes de cualquier cambio es 10. Regrese el resultado en cualquier orden.

Necesitamos encontrar el último precio registrado para cada producto antes de la fecha dada (2019-08-16). Si el precio de algún producto no tiene una actualización antes de esta fecha, el resultado para ese producto será NULL. Necesitamos manejar valores NULL de tal forma que el precio de esos productos sea 10.

Base de datos

Estrategía 1: Dividir casos usando UNION ALL

Intuición

Podemos separar los casos usando UNION ALL. Si la primera fecha de cambio de precio (change_date) es más reciente que la fecha dada (2019-08-16), el precio no fue cambiado a tiempo, de manera que el campo new_price se asume es 10. De lo contrario, tendremos que buscar la última fecha de modificación de las demás filas agrupándolas para obtener el último precio modificado (new_price).

Sabemos que no hay tuplas duplicadas cuando unimos las dos tablas separadas porque obtenemos un campo usando GROUP BY para cada consulta. Por tanto, sería mejor usar UNION ALL en vez de UNION para tener un mejor rendimiento.

También debemos tener cuidado al agrupar la tabla para obtener el último cambio de precio porque no podemos obtener el precio directamente usando un único GROUP BY. Por ejemplo, si agrupamos los datos del caso de ejemplo donde el campo change_date es menor o igual que ‘2019-08-16’, la información queda como sigue:

product_idnew_pricelast_change_date
1202019-08-16
1302019-08-16
1352019-08-16
2502019-08-14
2652019-08-14

Podríamos intentar obtener la última fecha de modificación usando la función de agregación MAX y el product_id, el cual es la llave primaria y el objetivo de agrupación. Sin embargo, los DBMS (Sistema de Gestión de Bases de datos) no conocen que elegir para el campo new_price después de agrupar porque hay multiples filas de dónde elegir, asi que no podemos usar la función MAX. En otras palabras, la razón por la que no podemos usar la función MAX es porque necesitamos obtener únicamente el campo new_price para la última fecha de modificación, lo que podemos hacer es comparar el conjunto de los campos product_id y change_date.

Algoritmo
  1. Agrupar la tabla usando el campo product_id y encontar la primera fecha de modificación del precio mayor que 2019-08-16 usando la función de agregación MIN en la cláusula HAVING.
  2. Asignar el valor 10 para esa lista de productos.
  3. Agrupar la tabla usando el campo product_id de nuevo, y encontrar el campo product_id y la última fecha de modificación hasta el 2019-08-16.
  4. Encontrar el campo new_price de la última fecha de modificación.
  5. Fusionar las dos tablas usando UNION ALL.
Implementación MySQL
SELECT
  product_id,
  10 AS price
FROM
  Products
GROUP BY
  product_id
HAVING
  MIN(change_date) > '2019-08-16'
UNION ALL
SELECT
  product_id,
  new_price AS price
FROM
  Products
WHERE
  (product_id, change_date) IN (
    SELECT
      product_id,
      MAX(change_date)
    FROM
      Products
    WHERE
      change_date <= '2019-08-16'
    GROUP BY
      product_id
  )

Estrategía 2: Dividir los casos usando LEFT JOIN

Intuición

Podemos también manejar los valores NULL usando la cláusula LEFT JOIN. Por ejemplo, si no hay cambios antes de la fecha dada, el campo resultante de LEFT JOIN es NULL. Por lo tanto, después de que obtenemos la última fecha de modificación antes de la fecha dada, podríamos fusionar esa tabla con la fecha con un product_id único y manejar el valor NULL usando una condición.

Necesitamos usar dos tipos de unión, INNER JOIN y LEFT JOIN. Usamos INNER JOIN para obtener la fecha de modificación del precio hasta la fecha dada y LEFT JOIN para manejar el valor NULL.

Algoritmo
  1. Agrupar la tabla mediante el product_id, y encontrar el campo product_id y la fecha de modificación más reciente hasta el 2019-08-16 usando la función de agregación MAX.
  2. Usar INNER JOIN para unir las tablas donde el conjunto de campos product_id y change_date es el mismo.
  3. Obtener el último cambio de precio y el product_id desde la tabla fusionada.
  4. Fusionar usando LEFT JOIN deonde el product_id es el mismo. Manejar el valor NULL, el cual indica que no hubo cambios de precio antes de la fecha dada, usando la función IFNULL.
Implementación MySQL
SELECT
  UniqueProductId.product_id,
  IFNULL (LastChangedPrice.new_price, 10) AS price
FROM
  (
    SELECT DISTINCT
      product_id
    FROM
      Products
  ) AS UniqueProductIds
  LEFT JOIN (
    SELECT
      Products.product_id,
      new_price
    FROM
      Products
      JOIN (
        SELECT
          product_id,
          MAX(change_date) AS change_date
        FROM
          Products
        WHERE
          change_date <= "2019-08-16"
        GROUP BY
          product_id
      ) AS LastChangedDate USING (product_id, change_date)
    GROUP BY
      product_id
  ) AS LastChangedPrice USING (product_id)

Estrategía 3: Utilizar la función ventana

Intuición

Podemos obtener el último precio usando la función de ventana FIRST_VALUE.

Función ventana

En MySQL, se dice que la función de ventana FIRST_VALUE realiza una operación similar a una agregación sobre un conjunto de datos. A pesar de que las funciones de agregación y las funciones ventanas funcionan casi de la misma forma, la función de agregación regresa una única fila por cada campo solicitado, mientras que la función ventana produce un resultado para cada fila.

Existen dos tipos de función ventana: la función agregada y la función no agregada. La función agregada podría ser la función ventana con la cláusula OVER, tales como MAX, MIN y SUM. Así, si utilizamos estas funciones agregadas sin la cláusula OVER, funciona como la función agregada; si las utilizamos con la cláusula OVER, funciona como la función ventana. Sin embargo, algunas funciones ventana, como LEAD, LAG, RANK y FIRST_VALUE son funciones no agregadas, lo que significa que deben utilizarse con la cláusula OVER.

Definimos el campo deseado para agrupar u ordenar en la cláusula OVER. Por tanto, si utilizamos la función ventana FIRST_VALUE, la sintaxis lucirá como se muestra en la imagen siguiente. (Para más detalles, consulte la especificación de la función ventana en la documentación)

product price at a given date fig1

PARTITION BY funciona igual que GROUP BY. La única diferencia es que PARTITION BY produce el resultado por cada fila. Ahora, podemos obtener el último precio cambiado mediante FIRST_VALUE en lugar de agrupar utilizando GROUP BY y JOIN. Podemos ordenar el campo change_date descendentemente y obtener el último precio en cada partición para agrupar la tabla. Debemos poner mucha atención a solo utilizar la función ventana PARTITION BY en la cláusula SELECT. De esta forma, esta se ejecuta después de las cláusulas JOIN, WHERE y GROUP BY.

Algoritmo
  1. Filtrar la tabla donde el valor del campo change_date es menor a la fecha dada (2019-08-16).
  2. Obtener el precio de la última fecha de modificación utilizando FIRST_VALUE para cada product_id.
  3. El resto del proceso es el mismo que la estrategía 2.
Implementación MySQL
SELECT
  product_id,
  IFNULL (price, 10) AS price
FROM
  (
    SELECT DISTINCT
      product_id
    FROM
      Products
  ) AS UniqueProducts
  LEFT JOIN (
    SELECT DISTINCT
      product_id,
      FIRST_VALUE (new_price) OVER (
        PARTITION BY
          product_id
        ORDER BY
          change_date DESC
      ) AS price
    FROM
      Products
    WHERE
      change_date <= '2019-08-16'
  ) AS LastChangedPrice USING (product_id);

Conclusión

Se recomienda la estrategía 1 por su simplicidad y rendimiento. Usualmente toma mucho más tiempo cuando utilizamos la cláusula UNION porque ordena la tabla para eliminar los campos duplicados. Sin embargo, UNION ALL no ordena la tabla porque no elimina los campos duplicados. Nos aseguramos que no hay duplicados porque usamos GROUP BY para obtener el último precio por cada product_id.

slackmart blog © 2025