Hace poco me enfrente a un reto con un cliente que requeria hacer seguimiento a las ventas en Looker, se realizó el dashboard conectado directamente a una vista muy compleja que hacia varias uniones de tabla, calculos y comparación de los registros con periodo anterior, esta vista tardaba en ejecutarse aproximadamente 2 minutos y Looker solicitaba la actualización cada 5 minutos, esto estaba afectando al servidor seriamente, aqui la explicación de como afronte este caso:
- Para la demostración vamos a crear una tabla de ejemplo con 1 millón de registros.
—Drop table ventas cascade;
–Llenar una tabla de ejemplo de un millon de registros
create table ventas as
select
‘FE’ as prefijo
,generate_series(1, 1000000, 1) as numero
,(timestamp ‘2019-01-19 23:59:59’ + random() * (timestamp ‘2024-12-18 00:00:01’ – timestamp ‘2019-01-19 23:59:59’))::date as fecha
,floor(((7800000000)*1) * random())::VARCHAR(15) as item
,floor(random()* (30-1 + 1) + 1) as cantidad
,round(floor(random()* (100000-1000 + 1000) + 1000)::integer,-2) as valorunit
, 19 as iva
–CREATE INDEX idx_ventas_fecha ON ventas (fecha);
2. Creamos la vista materializada
create MATERIALIZED view vm_ventas
as select fecha::char(7) as mes,SUM(cantidad*(valorunit/(1+iva/100))) as ventaneta
,now() as ultact –Creamos una columna en la vista materializada que almacene la fecha de la última actualización.
from ventas
where fecha<CURRENT_DATE
group by 1;
2. Creamos una función para actualizar la vista materializada cada 24 horas
CREATE OR REPLACE FUNCTION tg_refresh_vm_ventas()
RETURNS TIMESTAMP AS
$body$
DECLARE
ld_newfec TIMESTAMP;
ld_ultrefresh TIMESTAMP;
ln_horas integer;
BEGIN
–Usamos esa fecha en una función para calcular las horas transcurridas desde la última actualización.
select ultact into ld_ultrefresh from vm_ventas order by ultact desc limit 1 ;
ld_newfec := CURRENT_TIMESTAMP::TIMESTAMP;
ln_horas = (EXTRACT(EPOCH FROM ld_newfec)-EXTRACT(EPOCH FROM ld_ultrefresh)) / 3600;
IF ln_horas>24 THEN
REFRESH MATERIALIZED VIEW vm_ventas;
END IF;
return ld_ultrefresh;
END;
$body$
LANGUAGE ‘plpgsql’
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
3. Consolidamos el movimiento del ultimo dia mas la vista materializada
CREATE or replace VIEW vw_ventas
AS
WITH refresh_matview AS (select tg_refresh_vm_ventas()) –En una vista normal, mediante un CTE, validamos si es necesario actualizar la vista materializada
SELECT * FROM vm_ventas –luego consultamos dicha vista
UNION ALL
select fecha::char(7) as mes,sum(cantidad*(valorunit/(1+iva/100))) as ventaneta,now() as ultact
from ventas where fecha>=current_date group by 1; –Agregando los movimientos del último día.
select * from vw_ventas order by 1 desc –Esto asegura que la vista se actualice automáticamente cada 24 horas