Introducción

En el desarrollo de proyectos de Business Intelligence, Big Data y Data Science la mayoría de veces por no decir casi siempre nos encontramos con que faltan registros en una tabla temporal. 

Cuando esto ocurre la solución casi siempre es obtener el último registro no nulo o el registro anterior y el objetivo de este artículo es enseñar al lector cómo obtener este registro con SQL sin necesidad de programar procedimientos almacenados, código en Python o R etc…

Ejemplo del problema

El cliente/negocio nos ofrece datos de los precios diarios de los productos que tienen en una web. A veces hay productos en los que no se ha almacenado el precio para un día por fallo del proceso BATCH que tienen automatizado o porque los días que el precio no ha cambiado no lo almacenan (sólo registran los cambios)

En esta imagen se visualiza muy bien que para muchos días faltan registros.

Cuando diseñamos un Datawarehouse y nos faltan datos para estos días no podemos responder por ejemplo a la pregunta “¿Qué precio tenía las Asics Tokio el 02/01/2021?” porque no existe registro para este día aunque aplicando la lógica de obtener el ÚLTIMO PRECIO NO NULO sabemos que para el 02/01/2021 es 185e

Solución

Ahora que he expuesto el problema apoyándome en Excel para visualizarlo, voy a proponer la solución a este problema con un ejemplo práctico que vosotros mismos podéis realizar.

TABLA CALENDARIO

Primero de todo debemos tener una TABLA CALENDARIO con todas las fechas del sistema. Para este ejemplo mi tabla calendario va a tener las fechas desde el 25/12/2020 hasta el 10/01/2021

CREATE TABLE calendario (
	fecha date
)

INSERT INTO calendario ( fecha ) values ( '2020/12/25')
INSERT INTO calendario ( fecha ) values ( '2020/12/26')
INSERT INTO calendario ( fecha ) values ( '2020/12/27')
INSERT INTO calendario ( fecha ) values ( '2020/12/28')
INSERT INTO calendario ( fecha ) values ( '2020/12/29')
INSERT INTO calendario ( fecha ) values ( '2020/12/30')
INSERT INTO calendario ( fecha ) values ( '2020/12/31')
INSERT INTO calendario ( fecha ) values ( '2021/01/01')
INSERT INTO calendario ( fecha ) values ( '2021/01/02')
INSERT INTO calendario ( fecha ) values ( '2021/01/03')
INSERT INTO calendario ( fecha ) values ( '2021/01/04')
INSERT INTO calendario ( fecha ) values ( '2021/01/05')
INSERT INTO calendario ( fecha ) values ( '2021/01/06')
INSERT INTO calendario ( fecha ) values ( '2021/01/07')
INSERT INTO calendario ( fecha ) values ( '2021/01/08')
INSERT INTO calendario ( fecha ) values ( '2021/01/09')
INSERT INTO calendario ( fecha ) values ( '2021/01/10')

select 
	*
from calendario

TABLA PRODUCTOS

create table productos(
	fecha date 
	, producto varchar(100)
	, precio float
)

INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/25', 'Nike Vaporfly 4%', 205)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/26', 'Nike Vaporfly 4%', 220)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/27', 'Nike Vaporfly 4%', 240)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/28', 'Nike Vaporfly 4%', 220)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/29', 'Nike Vaporfly 4%', 205)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2021/01/02', 'Nike Vaporfly 4%', 192)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2021/01/03', 'Nike Vaporfly 4%', 205)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2021/01/05', 'Nike Vaporfly 4%', 207)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/25', 'Adidas Adizero Adios PRO', 200)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/29', 'Adidas Adizero Adios PRO', 200)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/30', 'Adidas Adizero Adios PRO', 190)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/31', 'Adidas Adizero Adios PRO', 200)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2021/01/01', 'Adidas Adizero Adios PRO', 199)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/25', 'Asics Tokio', 200)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/26', 'Asics Tokio', 205)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/27', 'Asics Tokio', 185)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2020/12/25', 'Hoka Carbon X', 140)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2021/01/04', 'Hoka Carbon X', 145)
INSERT INTO productos (fecha, producto, precio ) VALUES ('2021/01/05', 'Hoka Carbon X', 140)

SELECT * FROM productos

Producto Cartesiano Calendario y Productos

Es necesario hacer el producto cartersiano por la razón de: QUEREMOS PARA TODOS LOS DÍAS EL PRECIO DE TODOS LOS PRODUCTOS. Así que lo primero que se hace es un Producto Cartesiano todos los días y todos los productos.

select	-- Producto Cartesiano
	A.fecha 
	, B.producto
from calendario A 
join (
	select distinct 
		producto
	from productos 
) B on ( 1 = 1 )

Comprobar si para el producto cartesiano existe un precio

select	
	A.fecha 
	, B.producto
	, CASE 
		WHEN (C.fecha is null) then 
			'No existe precio'
		ELSE 
			'EXISTE'
		END as existePrecio
	, C.precio
from calendario A 
join (
	select distinct
		producto
	from productos 
) B on ( 1 = 1 )	-- Producto Cartesiano para obtener la combinación de todas las fechas con todos los productos
LEFT join productos C on ( A.fecha = C.fecha
							and B.producto = C.producto )
order by A.fecha 

Obtener el último precio no nulo

El objetivo es obtener el último precio no nulo o el primer precio que encuentres anterior a la fecha no nulo.

Con esta imagen explico qué es lo que se intenta conseguir. Algo que es muy fácil de entender al aplicar la solución con una Query SQL se puede complicar mucho.

La solución:

Una vez obtenido el producto cartesiano y hemos detectado cuando una fecha no tiene precio, lo que se debe hacer es una consulta a la tabla de precios para obtener el primer registro no nulo. 

select top 1
	*
from productos 
where 1 = 1
	and fecha < '2020-12-31'
	and producto = 'Asics Tokio'
order by fecha desc
		, producto 

Con la consulta anterior se obtiene el primer registro no nulo anterior a la fecha indicada

Ahora hay qué obtener la primera fecha no nula de la tabla productos anterior a la fecha del calendario

select	
	A.fecha 
	, B.producto
	, CASE 
		WHEN (C.fecha is null) then 
			'No existe precio'
		ELSE 
			'EXISTE'
		END as existePrecio
	, CASE 
		WHEN (C.fecha is null) then 
			(
				select top 1
					fecha 
				from productos 
				where 1 = 1
					and fecha < A.fecha
					and producto = B.producto
				order by fecha desc
						, producto 			
			)
		ELSE 
			A.fecha 
		end as fechaPrecioProducto
from calendario A 
join (
	select distinct
		producto
	from productos 
) B on ( 1 = 1 )	-- Producto Cartesiano para obtener la combinación de todas las fechas con todos los productos
LEFT join productos C on ( A.fecha = C.fecha
							and B.producto = C.producto )
where 1 = 1
	and B.producto = 'Hoka Carbon X'
order by A.fecha 

Con la Query anterior genero una nueva columna con la última fecha encontrada no nula cuando no existe precio para esa columna y la fecha del calendario cuando sí encuentra precio.

Ahora lo único que queda es volver a unir esta consulta con la tabla producto

SELECT 
	fechasProductos.*
	, B.precio
FROM (
	select	
		A.fecha 
		, B.producto
		, CASE 
			WHEN (C.fecha is null) then 
				'No existe precio'
			ELSE 
				'EXISTE'
			END as existePrecio
		, CASE 
			WHEN (C.fecha is null) then 
				(
					select top 1	-- Obtener la primera fecha NO NULA anterior a la fecha del calendario
						fecha 
					from productos 
					where 1 = 1
						and fecha < A.fecha
						and producto = B.producto
					order by fecha desc
							, producto 			
				)
			ELSE 
				A.fecha 
			end as fechaPrecioProducto
	from calendario A 
	join (
		select distinct
			producto
		from productos 
	) B on ( 1 = 1 )	-- Producto Cartesiano para obtener la combinación de todas las fechas con todos los productos
	LEFT join productos C on ( A.fecha = C.fecha
								and B.producto = C.producto )
	where 1 = 1
		and B.producto = 'Hoka Carbon X'
) fechasProductos 
JOIN productos B on ( fechasProductos.fechaPrecioProducto = B.fecha
					and fechasProductos.producto = B.producto )
order by fechasProductos.fecha 
	 	, fechasProductos.producto

Este es el resultado para el producto “Hoka Carbon X”

Este es el resultado para todos los productos y fechas.

Conclusión

Cuando se trabaja con datos y se desarrollan proyectos de Business Intelligence es muy importante tener conocimientos avanzados en SQL y modelamiento de datos.

Saber SQL te permite consultar los datos con la estructura que necesitas sin depender de otros lenguajes como puede ser R o Python. El ejemplo de este artículo con un lenguaje de programación hubiera sido «sencillo» de desarrollar con un bucle for y dos variables pero el objetivo de este artículo es no depender de programar cuando en la misma consulta el dato ya viene preparado.

Y en cuanto al modelamiento, es lo más importante junto a los ETL en un proyecto de Business Intelligence, si se diseña un modelo de datos complejo, consultar la información será complicada. Por esta razón en el Business Intelligence se utilizan los Modelos de Estrella. Estos modelos son los que las herramientas de BI actuales están optimizados para trabajar y consultar.

En el siguiente enlace se puede consultar que és un modelo de estrella: https://datamanagement.es/2019/06/27/business-intelligence-modelo-estrella/