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/