Как обpaбатывать данные из множества таблиц в Google BigQuery
В прошлой статье я рассказывал об оконных функциях в Google BigQuery. Сегодня рассмотрим функции подстановки таблиц. Здесь всё проще.;
Обычно в SQL для объединения данных из нескольких таблиц в одном запросе используют функцию UNION, но в справочнике доступных функций Google BigQuery вы ее не найдете. Чтобы обратиться в одном запросе одновременно к нескольким таблицам, достаточно перечислить их названия через запятую в пункте FROM. Например, у нас в базе есть 5 таблиц:
- Sales_june_2015;
- Sales_july_2015;
- Sales_august_2015;
- Sales_september_2015;
- Sales_october_2015.
Нам необходимо посчитать сумму продаж за третий квартал 2015 года. Информация о продажах в третьем квартале находится в таблицах Sales_july_2015, Sales_august_2015 и Sales_september_2015. Соответственно, нам надо перечислить названия этих таблиц в пункте FROM.
SELECT SUM(Sales) as salesFROM Sales_july_2015, Sales_august_2015, Sales_september_2015; Таким образом, для BigQuery три таблицы при выполнении запроса будут равносильны одной целой таблице о продажах за третий квартал. Важно, чтобы таблицы, которые вы перечисляете через запятую, имели одинаковую структуру данных. Когда в вашем наборе данных всего пять таблиц, вполне можно обойтись описанным выше функционалом. Если же ваши таблицы разделены, например, по дням, то таких таблиц у вас со временем будет сотни, а через пару лет — тысячи. И каждый раз перечислять их через запятую вручную будет уже не так просто. Функции подстановки таблиц (Table wildcard functions) работают по такому же принципу. Используя их, достаточно обозначить диапазон таблиц вместо перечисления всех таблиц через запятую. Рассмотрим три доступные в Google BigQuery функции подстановки таблиц с примерами их использования.
1. Функция TABLE_DATE_RANGE (префикс, начальная дата, конечная дата)
Функция дает возможность обратиться к ряду таблиц, разделенных по дням, задав диапазон дат. Для названия таблицы, в которой описывается, как использовать данную функцию, нужно использовать формат префикс/дата, при этом дата должна быть записана в формате ГГГГММДД. При определении начальной и конечной даты вы можете использовать функции даты и времени, например:
TIMESTAMP(\\\'2012-10-01 02:03:04\\\')DATE_ADD(CURRENT_TIMESTAMP(), -7, \\\'DAY\\\');Предположим, что в нашем наборе данных есть три таблицы:
- mydata.people20140325;
- mydata.people20140326;
- mydata.people20140327.
Для того, чтобы запрос обратился ко всем трем таблицам, необходимо в качестве начальной даты указать TIMESTAMP(\\\'2014-03-25\\\'), а в качестве финальной — TIMESTAMP(\\\'2014-03-27\\\'). Запрос:
SELECTnameFROMTABLE_DATE_RANGE(mydata.people,TIMESTAMP(\\\'2014-03-25\\\'),TIMESTAMP(\\\'2014-03-27\\\'))WHEREage >= 35;Таким образом, в качестве префикса мы указали mydata.people, в качестве начальной даты 2014-03-25, а в качестве конечной — 2014-03-27. Пример обращения к таблицам за 2 предыдущих дня. Предположим, что в проекте myproject-1234 есть следующие таблицы:
- mydata.people20140323;
- mydata.people20140324;
- mydata.people20140325.
Предположим, что сегодня 25 марта 2014 года. Запрос:
SELECTnameFROM(TABLE_DATE_RANGE([myproject-1234:mydata.people], DATE_ADD(CURRENT_TIMESTAMP(), -2, \\\'DAY\\\'), CURRENT_TIMESTAMP()))WHEREage >= 35;В данном примере в качестве начальной даты мы использовали функцию DATE_ADD и указали в качестве точки отсчета текущее время, а также задали параметр отставания в 2 дня. В качестве финальной даты мы указали функцию CURRENT_TIMESTAMP, которая возвращает текущую дату и время. Соответственно, финальной датой будет текущий день.
2. Функция TABLE_DATE_RANGE_STRICT (префикс, начальная дата, конечная дата)
Данная функция — эквивалент TABLE_DATE_RANGE, разница между ними в том, что если в списке таблиц, разбитых по дням, будет отсутствовать таблица хотя бы за одну дату из указанного диапазона, функция TABLE_DATE_RANGE_STRIC вернет ошибку, и сообщение «Таблица (имя таблицы) ошибка». Пример ситуации, в которой функция TABLE_DATE_RANGE_STRICT вернет ошибку (подразумевает наличие в вашем наборе данных следующих таблиц):
- people20140325;
- people20140327.
Запрос:
SELECTnameFROM(TABLE_DATE_RANGE_STRICT(people,TIMESTAMP(\\\'2014-03-25\\\'),TIMESTAMP(\\\'2014-03-27\\\')))WHERE age >= 35;В качестве начальной даты мы задали 2014-03-25, в качестве финальной — 2014-03-25. Запрос в данном случае вернет ошибку «Не найдена таблица people20140326», поскольку ее нет в нашем наборе данных.
3. Функция TABLE_QUERY (набор данных, выражение)
С помощью данной функции вы можете обратиться к таблицам, названия которых соответствуют заданному выражению. Параметр «Выражение» обязательно должен быть в строчном виде. В качестве выражения можно использовать строчные функции, такие как CONTAINS или REGEXP_MATCH. Пример запроса, обращающегося к таблицам с названиями, содержащими «oo» и четыре и более символов (подразумевает присутствие в наборе таблиц со следующими названиями):
- mydata.boo;
- mydata.fork;
- mydata.ooze;
- mydata.spoon.
Запрос:
SELECTspeedFROM (TABLE_QUERY(mydata,\\\'table_id CONTAINS \"oo\" AND length(table_id) >= 4\\\'));Запрос обработает данные из таблиц mydata.ooze и mydata.spoon, так как именно эти две таблицы соответствуют заданному в функции TABLE_QUERY выражению. Пример обращения к таблицам с названиями, соответствующими определенному регулярному выражению. В данном случае название таблицы должно начинаться на «boo» и содержать 3-5 цифр:
- mydata.book4;
- mydata.book418;
- mydata.boom12345;
- mydata.boom123456789;
- mydata.taboo999.
Запрос:
SELECTspeedFROMTABLE_QUERY([myproject-1234:mydata],\\\'REGEXP_MATCH(table_id, r\"^boo[d]{3,5}\")\\\');Поскольку названия этих таблиц соответствуют регулярному выражению, которое мы использовали в запросе «^boo[\d]{3,5}», будут обpaбатываться данные из таблиц:
- mydata.book418;
- mydata.boom12345.
Как видите, обpaбатывать данные с помощью функций подстановки таблиц в Google BigQuery значительно более удобно, чем в классическом SQL. Пользуйтесь с удовольствием.
Комментарии:
Чат-боты упрощают процесс покупки продуктов онлайн. Netflix, Adidas, British Airways и другие крупные компании уже внедрили ботов в свои маркетинговые системы. Так ли это эффективно, если компании начинают отказываться от операторов в пользу ботов?...
19 07 2025 21:45:43
Обучение SEO, PPC, курс по изучению языка R, академии Netpeak Software, Serpstat и другие. Список обновляется....
18 07 2025 23:41:19
Правильная постановка целей, задач и дедлайна — основа успешной работы...
17 07 2025 19:49:16
Как создать онлайн кампанию по продвижению мобильного приложения с максимальной отдачей....
16 07 2025 11:41:54
Новости для наших существующих и потенциальных клиентов....
15 07 2025 22:21:56
Простая инструкция для новичков, как легко создать анимированные баннеры для рекламных кампаний с помощью бесплатного инструмента Google Web Designer. При создании баннера сервис предложит создать файл с нуля либо использовать шаблон. Узнайте обо всех возможностях!...
14 07 2025 22:20:27
Как быстро увеличить количество заинтересованных подписчиков в facebook, twitter, google+ и linkedIn с помощью контактов из рассылки и аккаунтов Facebook....
13 07 2025 23:40:16
Как настроить просто и быстро настроить скрипт в Google ***ytics для отслеживания печати страниц сайта....
12 07 2025 19:47:46
Альтернатива классическому SQL — обработка запросов из множества таблиц с помощью функций подстановки в Google BigQuery....
11 07 2025 17:48:25
Как бесплатно пользоваться преимуществами, особенностями и дополнительными инструментами сервиса...
10 07 2025 9:36:20
Футпринт — это признак, по которому можно спалить PBN-сетку сайтов. Это как родинки. Если взять двух парней с родинкой на щеке, то не факт, что они братья. Но если родинки совпадают на щеке, локте и колене, то тут что-то подозрительное...
09 07 2025 5:25:56
Решили составить список из семи улучшений. Что нового, Netpeak?...
08 07 2025 17:58:46
Стоит ли торопиться с переходом на новый формат объявлений? Эксперимент PPC-специалистов агентства Netpeak....
07 07 2025 0:48:48
При встрече со сложностями, когда мозг говорит вам «стоп, у тебя не получится, не пытайся даже», победа достается тому, кто это предупреждение игнорирует....
06 07 2025 2:27:49
Опыт коллег и их рекомендации — самая ценная валюта в арбитраже. Давайте объединим усилия и составим актуальный рейтинг СРА-сетей Украины....
05 07 2025 12:12:31
Если в сетке сайтов есть интересный домен, его можно использовать для дополнительной монетизации, а не только для ссылок. В таком случае важно знать, как правильно отслеживать трафик таких доменов....
04 07 2025 20:19:30
Контекстная реклама и ее эффективность: как долго длится, как измерить, что бывает, как заканчиваются деньги....
03 07 2025 6:10:38
![Дополнительные ценности Netpeak — что вы получаете при заказе [SEO 2.0] вместе с поисковым продвижением](/marketing-raw/img/751.php.jpg "Дополнительные ценности Netpeak — что вы получаете при заказе [SEO 2.0] вместе с поисковым продвижением")
В коробку [SEO 2.0] вместе с самим поисковым продвижением входят дополнительные ценности Netpeak: унифицированные действия по увеличению продаж, улучшения удобства работы и преимущества бренда Netpeak...
02 07 2025 0:25:44
Красивый слог и сюжет должны быть в интеpaктивной оболочке, соответствующей времени. Проект Gutenbergz идет к созданию именно такой — идеальной истории для людей XXI века. О пути к этой цели, топе App Store и успехе на Kickstarter мы поговорили с руководи...
01 07 2025 15:41:52
Интересные детали интерьера в офисах украинских IT-компаний: от массажного стола и тенниса до звукозаписывающей студии и корпоративной велопарковки. Читать дальше!...
30 06 2025 4:44:28
Чем удивляла, радовала и смешила реклама в социальных сетях в июне 2021 года...
29 06 2025 20:40:53
SEO-специалисты в Киеве получают в два раза больше, чем жители других городов Украины. Middle лидирует среди должностей как PPC, так и SEO-специалистов. Самая высокооплачиваемая должность — Head of PPC $1900. Узнать больше!...
28 06 2025 15:45:58
Глоссарий глупых ошибок в аудите от топовых SEO-агентств...
27 06 2025 20:38:54
Алгоритм E-A-T теперь следит за экспертностью, авторитетностью и достоверностью контента. Что это значит?...
26 06 2025 1:18:10
В арсенале Google Рекламы есть очень ценный инструмент — отслеживание конверсий....
25 06 2025 20:14:53
Упущенная семантика — это ключевые фразы, которые успешно используют в SEO и PPC ваши основные конкуренты, но не используете вы. Как найти упущенную семантику? Читайте кейс сервиса Prodvigator....
24 06 2025 23:42:47
Примеры странной, классной и непонятной рекламы в соцсетях из моей ленты за февраль 2021 года...
23 06 2025 16:58:48
Покажем, как продвигать сайты, продающие детскую обувь...
22 06 2025 2:33:29
Готовые триггерные письма и сценарии позволяют быстро запустить триггеры после реализации технических заданий разработчиком....
21 06 2025 9:11:35
Читать только интернет-маркетологам, SMM, PPC и SEO-специалистам....
20 06 2025 5:40:16
Хорошая история состоит из узнаваемых образов, которые вызывают у пользователя ассоциации с личными переживаниями....
19 06 2025 7:51:59
Помогают ли комментарии повысить эффективность рекламных постов? Единственный способ выяснить наверняка — провести A/B-тестирование в Facebook. Спойлер: тональность комментария также имеет значение в Facebook...
18 06 2025 12:28:49
Влияет ли значение показателя отказов на ранжирование сайта в поисковых системах и стоит ли переживать из-за высоких показателей отказов? Узнать больше....
17 06 2025 16:27:25
Какие рассылки можно отправить покупателю, если у вас есть только данные из карточки товара...
16 06 2025 12:16:23
Зная стоимость клика в вашей тематике или регионе, коэффициент конверсии на сайте, вы можете оценить объем необходимых в рекламу инвестиций и прогнозировать стоимость конверсии. Узнать больше!...
15 06 2025 9:50:58
Chrome Extensions экономят время тех, кто продвигает мобильные приложения...
14 06 2025 15:15:30
Метод настройки с помощью Google Tag Manager. Мануал с подробным описанием каждого шага — показываем на примере, как отслеживать веб-конверсии в рекламных кампаниях LinkedIn. Узнать больше!...
13 06 2025 8:37:22
Как Bigl.ua внедрили AMP и остались очень довольны результатом....
12 06 2025 13:35:20
Как создать популярный и удобный магазин в интернете, какой должна быть его раскрутка и многое другое, прозвучавшее на круглом столе 8P 2017...
11 06 2025 2:21:34
Как понять, что именно нравится подписчикам в вашей рассылке...
10 06 2025 22:41:36
Опции скриншутера, которые упростят вам жизнь....
09 06 2025 23:42:16
Private Blog Network для ниши безопасности, доставки цветов, товаров сантехники, туризма и выпекания. Сетки сайтов работают не всегда и это нормально. Узнать больше!...
08 06 2025 18:34:12
Возможности и перспективы Clubhouse, лайфхаки для пользователей и бонус в конце...
07 06 2025 0:43:10
Данные по 42,5 млн кликов в 25 тематиках и 391 городу...
06 06 2025 13:33:56
Рекламные кампании для брендинга, роста вовлечения пользователей, продаж, лояльности. Какой формат выбрать, чтобы получить необходимое целевое действие. Узнать!...
05 06 2025 19:16:59
Рассказываем, как комaнда Netpeak работала над ростом органики и повышением видимости приоритетных страниц сайта по релевантной семантике....
04 06 2025 5:26:58
Новая инструкция, которую можно применять к любым задачам в Google Таблицах....
03 06 2025 10:27:41
Правила, требования, особенности. А ещё примеры, которые помогут создать правильную, с точки зрения поисковиков, рекламу. Читать!...
02 06 2025 1:15:21
Полезный и развлекательный контент для всех, кто работает в IT, интернет-маркетинге, интересуется фишками тайм-менеджмента, учится управлять комaндой и собой....
01 06 2025 6:21:18
Информация поможет вам в составлении медиапланов. Зная стоимость клика в вашей тематике или регионе, и коэффициент конверсии на вашем сайте, можно оценить объем необходимых инвестиций в рекламу и прогнозировать стоимость конверсии. Узнать больше!...
31 05 2025 23:33:20
Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::
