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.

Espero ter ajudado 🙂

Grande abraço!