Técnicas avançadas para extrair partes de datas usando a função EXTRACT() no SQL

A função EXTRACT() do SQL é uma ferramenta poderosa para extrair elementos específicos de uma data ou hora. Neste artigo, começaremos com o uso básico, abordando como extrair elementos como ano, mês, dia, hora, entre outros. Além disso, exploraremos exemplos avançados como a extração de trimestres e dias da semana, a combinação com consultas complexas e dicas de otimização de desempenho. Isso permitirá que você manipule dados de data de forma eficaz, proporcionando grande conveniência em análises de dados e criação de relatórios.

Uso básico da função EXTRACT()

A função EXTRACT() é usada no SQL para extrair elementos específicos de uma data ou hora. A sintaxe básica é a seguinte:

EXTRACT(elemento FROM data)

Por exemplo, suponha que uma tabela orders contenha uma coluna order_date com a data do pedido. Para extrair o ano, você escreveria o seguinte:

SELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

Essa consulta extrai o ano de cada data de pedido na tabela orders e exibe o resultado na coluna order_year. Outros elementos podem ser extraídos da mesma forma. Por exemplo, para extrair o mês, substitua YEAR por MONTH.

Técnicas para extrair ano, mês e dia

Vamos explorar em detalhes como usar a função EXTRACT() para extrair o ano, mês e dia de dados de data.

Extraindo o ano

Para extrair o ano, especifique YEAR. Aqui está um exemplo que extrai o ano de order_date na tabela orders:

SELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;

Essa consulta obtém o ano de cada data de pedido e exibe como order_year.

Extraindo o mês

Para extrair o mês, especifique MONTH. O exemplo a seguir extrai o mês de order_date:

SELECT EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

Essa consulta obtém o mês de cada data de pedido e exibe como order_month.

Extraindo o dia

Para extrair o dia, especifique DAY. O exemplo a seguir extrai o dia de order_date:

SELECT EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

Essa consulta obtém o dia de cada data de pedido e exibe como order_day.

Técnicas para extrair hora, minuto e segundo

Veja como usar a função EXTRACT() para extrair a hora, minuto e segundo de dados de data e hora.

Extraindo a hora

Para extrair a hora, especifique HOUR. Aqui está um exemplo que extrai a hora de order_time na tabela orders:

SELECT EXTRACT(HOUR FROM order_time) AS order_hour
FROM orders;

Essa consulta obtém a hora de cada horário de pedido e exibe como order_hour.

Extraindo o minuto

Para extrair o minuto, especifique MINUTE. O exemplo a seguir extrai o minuto de order_time:

SELECT EXTRACT(MINUTE FROM order_time) AS order_minute
FROM orders;

Essa consulta obtém o minuto de cada horário de pedido e exibe como order_minute.

Extraindo o segundo

Para extrair o segundo, especifique SECOND. O exemplo a seguir extrai o segundo de order_time:

SELECT EXTRACT(SECOND FROM order_time) AS order_second
FROM orders;

Essa consulta obtém o segundo de cada horário de pedido e exibe como order_second.

Exemplos avançados de extração de trimestre e dia da semana

A função EXTRACT() pode ser usada para extrair não apenas o ano, mês e dia, mas também informações como trimestres e dias da semana. Abaixo, mostramos como extrair trimestres e dias da semana.

Extraindo o trimestre

Para extrair o trimestre, especifique QUARTER. O exemplo a seguir extrai o trimestre de order_date na tabela orders:

SELECT EXTRACT(QUARTER FROM order_date) AS order_quarter
FROM orders;

Essa consulta obtém o trimestre de cada data de pedido e exibe como order_quarter. Os trimestres são definidos como: 1º trimestre de janeiro a março, 2º trimestre de abril a junho, 3º trimestre de julho a setembro e 4º trimestre de outubro a dezembro.

Extraindo o dia da semana

Para extrair o dia da semana, especifique DOW (Day of Week). O exemplo a seguir extrai o dia da semana de order_date:

SELECT EXTRACT(DOW FROM order_date) AS order_day_of_week
FROM orders;

Essa consulta obtém o dia da semana de cada data de pedido e exibe como order_day_of_week. DOW usa valores de 0 a 6 para representar os dias da semana, sendo 0 para domingo, 1 para segunda-feira, 2 para terça-feira, e assim por diante.

Extração de dados com base em períodos específicos

Ao extrair dados com base em períodos específicos, a função EXTRACT() pode ser combinada com várias condições para obter dados de um intervalo ou período específico de forma eficiente.

Extração de dados com base no ano fiscal

Ao extrair dados com base no ano fiscal, muitas vezes o mês de início não é janeiro. Por exemplo, se o ano fiscal começar em abril, a extração pode ser feita da seguinte maneira:

SELECT *
FROM orders
WHERE (EXTRACT(YEAR FROM order_date) = 2023 AND EXTRACT(MONTH FROM order_date) >= 4)
   OR (EXTRACT(YEAR FROM order_date) = 2024 AND EXTRACT(MONTH FROM order_date) <= 3);

Essa consulta extrai os pedidos de abril de 2023 a março de 2024.

Extração de dados com base em dias úteis

Para extrair dados apenas dos dias úteis, geralmente são excluídos os fins de semana e feriados. No exemplo abaixo, os dados dos dias úteis são extraídos, excluindo sábados e domingos:

SELECT *
FROM orders
WHERE EXTRACT(DOW FROM order_date) NOT IN (0, 6);

Essa consulta extrai os dados dos pedidos realizados de segunda a sexta-feira. Para excluir feriados, é necessário preparar uma lista de feriados e adicionar uma condição para excluí-los.

Extração de dados com base em períodos de tempo específicos

Para extrair dados com base em um período de tempo específico, como durante o horário comercial (das 9h às 18h), a consulta seria escrita da seguinte forma:

SELECT *
FROM orders
WHERE EXTRACT(HOUR FROM order_time) BETWEEN 9 AND 18;

Essa consulta extrai os dados dos pedidos realizados entre 9h e 18h.

Consultas complexas combinando a função EXTRACT()

A função EXTRACT() pode ser combinada com outras funções SQL para realizar extrações de dados ainda mais avançadas. Abaixo estão alguns exemplos.

Consulta para calcular o total de vendas mensais

Para calcular o total de vendas mensais, combine a função EXTRACT() com funções de agregação.

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       SUM(order_amount) AS total_sales
FROM orders
GROUP BY order_year, order_month
ORDER BY order_year, order_month;

Essa consulta calcula o total de vendas para cada ano e mês, agrupando os resultados por order_year e order_month.

Consulta para calcular a venda média com base em um dia da semana específico

Para calcular a venda média com base em um dia da semana específico, como sexta-feira, escreva a consulta da seguinte forma:

SELECT EXTRACT(DOW FROM order_date) AS day_of_week,
       AVG(order_amount) AS average_sales
FROM orders
WHERE EXTRACT(DOW FROM order_date) = 5
GROUP BY day_of_week;

Essa consulta filtra os dados para considerar apenas os pedidos realizados na sexta-feira (5) e calcula a venda média para esses dias.

Consulta para comparar vendas trimestrais

Para comparar vendas trimestrais, combine a função EXTRACT() com a cláusula CASE.

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(QUARTER FROM order_date) AS order_quarter,
       SUM(order_amount) AS total_sales
FROM orders
GROUP BY order_year, order_quarter
ORDER BY order_year, order_quarter;

Essa consulta calcula o total de vendas para cada ano e trimestre, agrupando os resultados por order_year e order_quarter.

Consulta combinando múltiplas condições de período

Para extrair dados com base em múltiplas condições de período, combine várias funções EXTRACT(). Por exemplo, para extrair dados com base em meses específicos (janeiro e fevereiro) e em um período de tempo específico (das 9h às 18h), a consulta seria escrita assim:

SELECT *
FROM orders
WHERE EXTRACT(MONTH FROM order_date) IN (1, 2)
  AND EXTRACT(HOUR FROM order_time) BETWEEN 9 AND 18;

Essa consulta extrai os dados de pedidos realizados em janeiro e fevereiro entre 9h e 18h.

Dicas para otimização de desempenho

Veja algumas maneiras de otimizar o desempenho das consultas que usam a função EXTRACT(). Quando lidamos com grandes volumes de dados, a eficiência das consultas é essencial.

Uso de índices

Para melhorar o desempenho das consultas baseadas em datas ou horários, configure índices nas colunas relacionadas. Por exemplo, crie um índice na coluna order_date.

CREATE INDEX idx_order_date ON orders(order_date);

O uso de índices acelera a pesquisa baseada em datas. No entanto, a adição de índices pode afetar o desempenho das atualizações no banco de dados, então é importante definir índices apenas nas colunas necessárias.

Uso de índices parciais

Para otimizar buscas com base em condições específicas, use índices parciais. Por exemplo, se você costuma buscar dados de um ano ou mês específico, crie um índice parcial baseado nessas condições.

CREATE INDEX idx_order_date_partial ON orders(order_date)
WHERE EXTRACT(YEAR FROM order_date) = 2023;

Esse índice acelera a pesquisa de dados do ano de 2023.

Dicas para escrever consultas eficientes

A forma como você escreve suas consultas pode melhorar o desempenho. Evite cálculos e conversões desnecessárias e use condições diretas sempre que possível.

-- Consulta ineficiente
SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

-- Consulta eficiente
SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

A segunda consulta usa índices de forma mais eficaz, melhorando o desempenho.

Uso de visualizações (views)

Consultas complexas usadas com frequência podem ser transformadas em visualizações, tornando-as reutilizáveis. O uso de visualizações melhora a legibilidade e a gestão das consultas.

CREATE VIEW monthly_sales AS
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month,
       SUM(order_amount) AS total_sales
FROM orders
GROUP BY order_year, order_month;

Ao criar visualizações como esta, você pode obter os dados necessários de forma mais simples e melhorar o desempenho das consultas.

Conclusão

Neste artigo, abordamos várias maneiras de usar a função EXTRACT() no SQL para extrair elementos específicos de dados de data e hora. Desde o uso básico para extrair ano, mês, dia, hora, minuto e segundo, até a extração de trimestres e dias da semana, extração de dados com base em períodos específicos, aplicação em consultas complexas e dicas de otimização de desempenho, cobrimos uma ampla gama de técnicas. Ao utilizar a função EXTRACT() de forma eficaz, você pode aumentar significativamente a eficiência na análise de dados e criação de relatórios. Aplicando essas técnicas, o processamento de dados usando SQL se tornará mais poderoso e flexível.