Как Получить Год и Месяц из Даты в BigQuery? Пошаговая Инструкция с Примерами

06.12.2025
67296

4 мин
46

В этой статье мы подробно рассмотрим, как извлечь год и месяц из поля с датой в Google BigQuery. Это распространенная задача при анализе данных, требующая знания специальных функций SQL, предоставляемых BigQuery. Мы рассмотрим различные подходы, оптимизацию запросов и примеры использования для решения этой задачи, с фокусом на bigquery получить год месяц из даты.

Обзор Функций для Извлечения Года и Месяца в BigQuery

BigQuery предлагает несколько функций для работы с датами, позволяющих извлекать год и месяц. Наиболее часто используемые – EXTRACT, DATE_PART и FORMAT_DATE. Каждая из них имеет свои особенности и подходит для разных сценариев.

Основные функции BigQuery для работы с датами: EXTRACT, DATE_PART, FORMAT_DATE

EXTRACT: Позволяет извлечь определенную часть даты или времени (например, год, месяц, день) в виде целого числа.
DATE_PART: Функция, аналогичная EXTRACT, но может быть не так широко распространена в BigQuery.
FORMAT_DATE: Форматирует дату в соответствии с указанным шаблоном, что позволяет получить год и месяц в нужном виде (текстовом или числовом).

Сравнение и выбор подходящей функции в зависимости от задачи

EXTRACT идеально подходит, когда вам нужны год и месяц как числовые значения для дальнейших вычислений или агрегаций.
FORMAT_DATE полезен, когда требуется представить год и месяц в определенном текстовом формате (например, "Январь 2025").
DATE_PART выполняет те же задачи, что и EXTRACT, но EXTRACT является более предпочтительным вариантом.

Выбор между этими функциями зависит от ваших конкретных потребностей и требуемого формата результата.

Использование Функции EXTRACT для Года и Месяца

Функция EXTRACT – один из наиболее эффективных и простых способов извлечения года и месяца из даты в BigQuery.

Синтаксис и параметры функции EXTRACT

Синтаксис функции EXTRACT выглядит следующим образом:

EXTRACT(part FROM date_expression)

Где:

part: Часть даты, которую нужно извлечь (YEAR, MONTH, DAY, HOUR, MINUTE и т.д.).
date_expression: Выражение, представляющее дату или timestamp. Это может быть имя столбца, функция, возвращающая дату, или литерал даты.

Примеры извлечения года и месяца с использованием EXTRACT: от простого к сложному

Пример 1: Извлечение года из столбца order_date таблицы orders:

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

Пример 2: Извлечение месяца из столбца order_date:

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

Пример 3: Извлечение года и месяца в одном запросе:

SELECT
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

Пример 4: Группировка данных по году и месяцу:

SELECT
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  COUNT(*) AS order_count
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

Применение Функции FORMAT_DATE для Получения Года и Месяца

Функция FORMAT_DATE позволяет форматировать дату в соответствии с заданным шаблоном, что может быть полезно для получения года и месяца в определенном текстовом представлении. Это альтернативный подход к EXTRACT.

Форматирование даты с помощью FORMAT_DATE: обзор параметров

Синтаксис функции FORMAT_DATE:

FORMAT_DATE(format_string, date_expression)

Где:

format_string: Строка, определяющая формат даты. Поддерживает различные спецификаторы, такие как %Y (год), %m (месяц в числовом формате), %B (полное название месяца) и другие.
date_expression: Выражение, представляющее дату.

Примеры использования FORMAT_DATE для извлечения года и месяца в различных форматах

Пример 1: Получение года и месяца в формате "YYYY-MM":

SELECT FORMAT_DATE('%Y-%m', order_date) AS order_year_month
FROM orders;

Пример 2: Получение месяца в виде полного названия и года:

SELECT FORMAT_DATE('%B %Y', order_date) AS order_month_year
FROM orders;

Пример 3: Получение месяца в сокращенном формате и года:

SELECT FORMAT_DATE('%b %Y', order_date) AS order_month_year
FROM orders;

Продвинутые Примеры и Оптимизация Запросов

Рассмотрим более сложные сценарии и способы оптимизации запросов при работе с датами в BigQuery.

Преобразование строки в дату и извлечение года/месяца

Часто дата хранится в виде строки. В этом случае необходимо сначала преобразовать строку в дату, а затем извлечь год и месяц. Используйте функцию PARSE_DATE для преобразования строки в дату.

Пример:

SELECT
  EXTRACT(YEAR FROM PARSE_DATE('%Y-%m-%d', date_string)) AS order_year,
  EXTRACT(MONTH FROM PARSE_DATE('%Y-%m-%d', date_string)) AS order_month
FROM (SELECT '2023-10-26' AS date_string) ;

Оптимизация запросов при работе с большими объемами данных: partition pruning, indexing

При работе с большими таблицами в BigQuery важно оптимизировать запросы для снижения затрат и повышения производительности.

Partition Pruning: Если ваша таблица секционирована по дате, BigQuery может отфильтровать разделы, не содержащие данные за нужный период, что значительно ускоряет выполнение запроса. Убедитесь, что фильтры в вашем запросе используют столбцы секционирования.
Clustering: Кластеризация таблицы по столбцам, используемым для фильтрации (например, год и месяц), также может повысить производительность запросов. Кластеризация упорядочивает данные в таблице, что позволяет BigQuery быстрее находить нужные данные.

Пример запроса с использованием partition pruning:

Предположим, таблица orders секционирована по столбцу order_date. Для извлечения данных за определенный год и месяц можно использовать следующий запрос:

SELECT
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  COUNT(*) AS order_count
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 1, 2
ORDER BY 1, 2;

В этом случае BigQuery будет сканировать только те разделы таблицы, которые содержат данные за 2023 год.

Заключение

В этой статье мы рассмотрели различные способы извлечения года и месяца из даты в Google BigQuery. Мы изучили функции EXTRACT и FORMAT_DATE, рассмотрели примеры их использования и обсудили методы оптимизации запросов. Правильное использование этих техник позволит вам эффективно работать с датами в BigQuery и получать нужные данные для анализа. Понимание того, как bigquery получить год месяц из даты, является ключевым навыком для аналитика данных, работающего с этой платформой.