BigQuery: Como encontrar o próximo dia útil

Recentemente precisei desenvolver uma tabela agregada para servir a um dashboard e um dos dados a serem calculados era a previsão de entrega de um pedido com base em um SLA.

Até ai tudo tranquilo…

DECLARE data_inicio DATE DEFAULT '2022-09-01';
DECLARE sla INT64 DEFAULT 10;
SELECT DATE_ADD(data_inicio, INTERVAL sla DAY);

Só tinha um detalhe: no cálculo deveria ser considerado apenas os dias úteis.

Utilizando uma lógica parecida com a que foi usada na function de cálculo de dias úteis, resolvi desenvolver uma nova function para encontrar o próximo dia útil dado um intervalo de tempo e uma lista de datas a desconsiderar.

A função é bastante simples, recebe apenas 3 parametros: Data de inicio da contagem, intervalo e a lista de datas a serem desconsideradas no calculo. O resultado foi esse:

CREATE TEMP FUNCTION calc_proxima_data_util(DATA_INICIO DATE, SLA INT64, LISTA_FERIADOS ARRAY<DATE>) AS (
  (
    SELECT MAX(dt) FROM (
    SELECT dt, RANK() OVER(ORDER BY dt) as id
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE_ADD(DATA_INICIO, INTERVAL 1 DAY), DATE_ADD(DATA_INICIO,INTERVAL SLA*10 DAY))) dt 
    WHERE EXTRACT(DAYOFWEEK from dt) between 2 and 6 and dt not in UNNEST(LISTA_FERIADOS)
    QUALIFY id <= SLA)
  )
);

A lógica é bastante simples: é gerado um range de datas partindo da data_inicio (mas não contabilizando-a) e indo até o intervalo * 10. Isso para ter uma margem de segurança e não correr o risco de perder alguma data.

Após a geração do range, com a cláusula where tiramos os finais de semana bem como os feriados.

Do resultado, criei um sequencial para cada linha e com a instrução QUALIFY retornei apenas as linhas com sequencial <= SLA. Precisei fazer dessa forma pois o BigQuery não aceitou que eu usasse o LIMIT passando o valor como um parâmetro… infelizmente =/

Depois só precisei fazer um MAX() na data. Legal né?! =D Eu achei =D.

Segue um exemplo de uso completo:

DECLARE DATA_INICIO DATE DEFAULT '2022-09-01';
DECLARE SLA INT64 DEFAULT 10;
DECLARE lista_feriados ARRAY<DATE> DEFAULT NULL;
SET lista_feriados = [
  DATE('2022-01-01'), 
  DATE('2022-03-01'), 
  DATE('2022-04-15'), 
  DATE('2022-04-17'), 
  DATE('2022-04-21'), 
  DATE('2022-05-01'), 
  DATE('2022-06-16'), 
  DATE('2022-09-07'), 
  DATE('2022-10-12'), 
  DATE('2022-11-02'), 
  DATE('2022-11-15'), 
  DATE('2022-12-25')
];

CREATE TEMP FUNCTION calc_proxima_data_util(DATA_INICIO DATE, SLA INT64, LISTA_FERIADOS ARRAY<DATE>) AS (
  (
    SELECT MAX(dt) FROM (
    SELECT dt, RANK() OVER(ORDER BY dt) as id
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE_ADD(DATA_INICIO, INTERVAL 1 DAY), DATE_ADD(DATA_INICIO,INTERVAL SLA*10 DAY))) dt 
    WHERE EXTRACT(DAYOFWEEK from dt) between 2 and 6 and dt not in UNNEST(LISTA_FERIADOS)
    QUALIFY id <= SLA)
  )
);

SELECT 
  dt_inicio, 
  sla, 
  calc_proxima_data_util(dt_inicio, sla, lista_feriados) as previsao_entrega
FROM (
    SELECT date('2022-09-01') as dt_inicio, 10 as sla union all
    SELECT date('2022-02-25'), 7 union all
    SELECT date('2022-04-07'), 20
);

Abraço!