Mês: Setembro 2022

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!

BigQuery: Contando os dias úteis entre datas

Existem várias formas de fazer esse cálculo, a que vou mostrar nesse post é a mais simples q eu já vi.

Cálculo com datas é algo extremamente comum quando falamos de uma tabela agregada que servirá um relatório ou dashboard, e fazer esses cálculos as vezes pode parecer muito complexo, quando na verdade não é.

Para o exemplo que vou mostrar, criei um script simples, onde simulo algumas datas de inicio e algumas datas fim, e calculo os dias úteis entre elas usando uma UDF temporária

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 total_dias_uteis(DATA_INICIO DATE, DATA_FIM DATE, LISTA_FERIADOS ARRAY<DATE>) AS (
  (
      SELECT COUNT(dt)
      FROM UNNEST(GENERATE_DATE_ARRAY(DATA_INICIO, DATA_FIM)) dt 
      WHERE EXTRACT(DAYOFWEEK from dt) between 2 and 6 and dt not in UNNEST(LISTA_FERIADOS)
  )
);
SELECT dt_inicio, dt_fim, total_dias_uteis(dt_inicio, dt_fim, lista_feriados) as total_dias_uteis
FROM (
    SELECT date('2021-12-25') as dt_inicio, date('2022-01-05') as dt_fim union all
    SELECT date('2022-02-25'), date('2022-03-05') union all
    SELECT date('2022-04-05'), date('2022-04-25')
);

Acima a versão completa pra quem não quer mais explicações rsrsr.

Nesse inicio de código fiz apenas a declaração de uma variável: lista_feriados e logo em seguida inseri os feriados de 2022. Esses feriados poderiam vir de uma tabela, ai vai da imaginação de cada um 😉

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')
];

Logo em seguida criei um UDF temporária para facilitar a vida na hora de usar. O mais interessante aqui é criar uma UDF permanente, nesse caso, basta remover a instrução TEMP, e incluir o id do projeto e do dataset antes do nome da function.

A function recebe 3 parâmetros: data inicio, data fim e uma lista de feriados (criada logo acima).

CREATE TEMP FUNCTION total_dias_uteis(DATA_INICIO DATE, DATA_FIM DATE, LISTA_FERIADOS ARRAY<DATE>) AS (
  (
      SELECT COUNT(dt)
      FROM UNNEST(GENERATE_DATE_ARRAY(DATA_INICIO, DATA_FIM)) dt 
      WHERE EXTRACT(DAYOFWEEK from dt) between 2 and 6 and dt not in UNNEST(LISTA_FERIADOS)
  )
);

E por fim, um select simples simulando algumas datas apenas para mostrar como usar a function, porém se você sabe usar as funções built-in como count, sum, avg… vc sabe usar as UDF’s 🙂

SELECT 
    dt_inicio, 
    dt_fim, 
    total_dias_uteis(dt_inicio, dt_fim, lista_feriados) as total_dias_uteis
FROM (
    SELECT date('2021-12-25') as dt_inicio, date('2022-01-05') as dt_fim union all
    SELECT date('2022-02-25'), date('2022-03-05') union all
    SELECT date('2022-04-05'), date('2022-04-25')
);

Antes de sair… dá uma conferida na série de posts para o Projeto Video Creator que eu postei aqui no blog. Pra quem curte automatizar o trabalho, vai gostar do conteúdo =D.

Gerando videos para o Youtube com linguagem Python
Como extrair o máximo que a sua máquina pode oferecer!

Espero ter ajudado 🙂

Grande abraço!

Exit mobile version