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_id | new_price | last_change_date |
---|---|---|
1 | 20 | 2019-08-16 |
1 | 30 | 2019-08-16 |
1 | 35 | 2019-08-16 |
2 | 50 | 2019-08-14 |
2 | 65 | 2019-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
- 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ónMIN
en la cláusulaHAVING
. - Asignar el valor 10 para esa lista de productos.
- Agrupar la tabla usando el campo
product_id
de nuevo, y encontrar el campoproduct_id
y la última fecha de modificación hasta el 2019-08-16. - Encontrar el campo
new_price
de la última fecha de modificación. - 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
- Agrupar la tabla mediante el
product_id
, y encontrar el campoproduct_id
y la fecha de modificación más reciente hasta el 2019-08-16 usando la función de agregaciónMAX
. - Usar
INNER JOIN
para unir las tablas donde el conjunto de camposproduct_id
ychange_date
es el mismo. - Obtener el último cambio de precio y el
product_id
desde la tabla fusionada. - Fusionar usando
LEFT JOIN
deonde elproduct_id
es el mismo. Manejar el valorNULL
, el cual indica que no hubo cambios de precio antes de la fecha dada, usando la funciónIFNULL
.
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)
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
- Filtrar la tabla donde el valor del campo
change_date
es menor a la fecha dada (2019-08-16). - Obtener el precio de la última fecha de modificación utilizando
FIRST_VALUE
para cadaproduct_id
. - 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
.