Как извлечь год из даты в Google BigQuery: подробное руководство по функциям?

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

Саммари статьи

ChatGPT

Google AI

Grok

Perplexity

Ключевая функция для извлечения года: EXTRACT

Основной и наиболее прямой способ получить год из даты или временной метки в BigQuery — это использование функции EXTRACT.

Синтаксис EXTRACT(YEAR FROM…) и его применение

Функция EXTRACT позволяет извлекать определенную часть даты или времени из выражения типа DATE, DATETIME, TIMESTAMP или TIME. Ее общий синтаксис выглядит следующим образом:

EXTRACT(date_part FROM expression)

Где:

• date_part — это часть даты или времени, которую необходимо извлечь. Для получения года используется значение YEAR.

• expression — это выражение типа DATE, DATETIME или TIMESTAMP, из которого извлекается год. BigQuery функции даты поддерживают различные форматы, что делает EXTRACT универсальным инструментом.

Использование EXTRACT(YEAR FROM...) возвращает целое число, представляющее год. Это особенно удобно для дальнейших математических операций, фильтрации или группировки данных по году.

Примеры использования с типами DATE, DATETIME и TIMESTAMP

Рассмотрим, как функция EXTRACT работает с различными типами данных даты и времени в BigQuery SQL.

• Извлечение года из типа DATE:

  SELECT
    EXTRACT(YEAR FROM DATE '2023-04-15') AS year_from_date,
    EXTRACT(YEAR FROM CURRENT_DATE()) AS current_year;
  

  В этом примере year_from_date вернет 2023, а current_year вернет 2026 (на основе текущей даты 2026-03-31).

• Извлечение года из типа DATETIME:

  SELECT
    EXTRACT(YEAR FROM DATETIME '2026-01-20 10:30:00') AS year_from_datetime;
  

  Результатом будет 2026.

• Извлечение года из типа TIMESTAMP:

  При работе с TIMESTAMP важно учитывать часовые пояса. TIMESTAMP хранит момент времени в UTC, но при извлечении года BigQuery может применять часовой пояс сессии или явно указанный часовой пояс.

  SELECT
    EXTRACT(YEAR FROM TIMESTAMP '2025-07-01 15:00:00 UTC') AS year_from_utc_timestamp,
    EXTRACT(YEAR FROM TIMESTAMP '2025-07-01 15:00:00 America/New_York') AS year_from_ny_timestamp;
  

  Оба запроса вернут 2025, так как извлечение года не зависит от смещения внутри одного дня. Однако, если TIMESTAMP находится на границе года в разных часовых поясах, результат может отличаться.

Другие полезные функции для работы с годом в BigQuery

Помимо EXTRACT, BigQuery предлагает и другие функции, которые могут быть полезны при работе с годом.

Функция DATE_TRUNC: округление до начала года

Функция DATE_TRUNC позволяет "обрезать" дату или временную метку до начала указанной части даты. Это полезно, когда требуется получить не просто числовое значение года, а саму дату, соответствующую началу года.

Синтаксис:

DATE_TRUNC(expression, date_part)

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

SELECT
  DATE_TRUNC(DATE '2023-04-15', YEAR) AS start_of_year_date,
  DATE_TRUNC(DATETIME '2026-08-20 14:00:00', YEAR) AS start_of_year_datetime;

Результаты:

• start_of_year_date: 2023-01-01

• start_of_year_datetime: 2026-01-01 00:00:00

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

FORMAT_DATE и CURRENT_DATE: форматирование и получение текущего года

• FORMAT_DATE: Эта функция позволяет форматировать объект DATE в строку в соответствии с заданным форматом. Для получения года в виде строки можно использовать спецификатор %Y.

  SELECT
    FORMAT_DATE('%Y', DATE '2023-04-15') AS formatted_year_string;
  

  Результат: '2023'.

  Хотя EXTRACT предпочтительнее для числовых операций, FORMAT_DATE полезна для вывода года в отчетах или для конкатенации со строками.

• CURRENT_DATE: Возвращает текущую дату. В сочетании с EXTRACT или FORMAT_DATE позволяет легко получить текущий год.

  SELECT
    EXTRACT(YEAR FROM CURRENT_DATE()) AS current_year_numeric,
    FORMAT_DATE('%Y', CURRENT_DATE()) AS current_year_string;
  

  Оба запроса вернут 2026 (числовое) и '2026' (строковое) соответственно, основываясь на текущей дате 2026-03-31.

  Реклама

  Аналогично, CURRENT_DATETIME() и CURRENT_TIMESTAMP() могут быть использованы для получения текущего года из соответствующих типов данных.

Практическое применение и особенности работы с годом

Эффективная работа с годом в BigQuery выходит за рамки простого извлечения значения. Она включает в себя фильтрацию, группировку и оптимизацию запросов.

Фильтрация и группировка данных по году

Извлеченный год часто используется для аналитических задач, таких как фильтрация данных за определенный год или агрегация метрик по годам. Это фундаментальные операции в анализе данных BigQuery.

• Фильтрация данных по году:

  SELECT
    order_id, order_date, total_amount
  FROM
    your_dataset.orders
  WHERE
    EXTRACT(YEAR FROM order_date) = 2023;
  

  Этот запрос отфильтрует все заказы, сделанные в 2023 году. Для повышения производительности, особенно на больших таблицах, рекомендуется использовать предикаты, которые могут использовать партиционирование по дате. Если таблица orders партиционирована по order_date, BigQuery автоматически отсканирует только нужные партиции.

• Группировка данных по году:

  SELECT
    EXTRACT(YEAR FROM transaction_timestamp) AS transaction_year,
    SUM(amount) AS total_annual_amount,
    COUNT(DISTINCT user_id) AS distinct_users_annual
  FROM
    your_dataset.transactions
  GROUP BY
    transaction_year
  ORDER BY
    transaction_year;
  

  Этот пример демонстрирует, как получить годовые итоги по сумме транзакций и количеству уникальных пользователей. SQL EXTRACT YEAR в GROUP BY является стандартной практикой для построения годовых отчетов.

Оптимизация запросов и учет часовых поясов

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

• Оптимизация запросов:

  • Партиционирование: Если ваша таблица партиционирована по столбцу DATE или TIMESTAMP, использование EXTRACT(YEAR FROM date_column) в предикате WHERE может быть неоптимальным, если BigQuery не может "протолкнуть" эту функцию до уровня партиций. Лучше использовать прямые сравнения с партициями, например, WHERE _PARTITIONTIME BETWEEN '2023-01-01' AND '2023-12-31' или WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31'. Однако, если таблица партиционирована по году (например, с помощью DATE_TRUNC(date_column, YEAR)), то EXTRACT(YEAR FROM date_column) = 2023 будет эффективным.

  • Кластеризация: Кластеризация таблицы по столбцу даты или по извлеченному году может значительно улучшить производительность запросов, которые фильтруют или группируют данные по году, уменьшая объем сканируемых данных.

  • Избегайте функций в WHERE на непартиционированных столбцах: Применение функций к столбцам в условии WHERE может помешать BigQuery использовать индексы или партиции, что приведет к полному сканированию таблицы и увеличению стоимости запроса. Всегда стремитесь к тому, чтобы предикаты WHERE были "sargable" (использующими индексы/партиции).

  • Материализованные представления: Для часто используемых агрегаций по году рассмотрите создание материализованных представлений, которые предварительно вычисляют эти агрегации.

• Учет часовых поясов:

  • Тип TIMESTAMP в BigQuery всегда хранится в UTC. При извлечении года из TIMESTAMP без явного указания часового пояса, BigQuery использует часовой пояс сессии (обычно UTC). Если ваши данные относятся к определенному локальному времени, и год должен быть извлечен относительно этого времени, используйте AT TIME ZONE:

  SELECT
    EXTRACT(YEAR FROM TIMESTAMP '2025-12-31 23:00:00 UTC' AT TIME ZONE 'America/New_York') AS year_ny,
    EXTRACT(YEAR FROM TIMESTAMP '2025-12-31 23:00:00 UTC' AT TIME ZONE 'Europe/Moscow') AS year_moscow;
  

  В данном случае year_ny вернет 2025, а year_moscow вернет 2026, так как 23:00 UTC 31 декабря 2025 года уже будет 02:00 1 января 2026 года в Москве (+3 часа).

  • Всегда стандартизируйте обработку часовых поясов в ваших ETL/ELT процессах, чтобы избежать несоответствий в анализе данных BigQuery.

Заключение

Извлечение года из даты является базовой, но мощной операцией в Google BigQuery. Функция EXTRACT(YEAR FROM...) предоставляет наиболее прямой способ получить год, в то время как DATE_TRUNC, FORMAT_DATE и CURRENT_DATE расширяют возможности для более сложных сценариев работы с датами BigQuery. Понимание этих функций, а также применение лучших практик по оптимизации запросов и учету часовых поясов, позволит вам эффективно выполнять анализ данных, строить точные отчеты и принимать обоснованные решения на основе ваших данных в BigQuery. Работа с датами BigQuery становится интуитивно понятной и высокопроизводительной при правильном подходе.