Что такое код ifx_query

Содержание

Что такое код ifx_query

(PHP 3>= 3.0.3, PHP 4)

ifx_query — отправляет Informix-запрос/query.

Описание

int ifx_query (string query, int link_identifier [, int cursor_type [, mixed blobidarray]])

Возвращает: положительный результирующий идентификатор Informix при успехе или FALSE — при ошибке.

Ресурс «result_id» используется другими функциями для результатов запросов. Устанавливает «affected_rows» для запрашивания функцией ifx_affected_rows() .

ifx_query() отправляет запрос текущей активной базе данных на сервере, ассоциированном со специфицированным идентификатором ссылки.

Выполняет запрос query в соединении conn_id . Для запросов «select» объявляется и открывается курсор. Необязательный параметр cursor_type позволяет создать «scroll» и/или «hold»-курсор. Это битовая маска, которая может быть IFX_SCROLL, IFX_HOLD или и то и другое вместе. Не-select запросы выполняются немедленно. IFX_SCROLL и IFX_HOLD являются символическими константами и как таковые не должны заключаться в кавычки. Если вы опустили этот параметр, курсор является нормальным последовательным курсором.

Для любого типа запроса количество задействованных рядов (приблизительное или реальное) сохраняется для запрашивания функцией by ifx_affected_rows() .

Если у вас в запросе имеются столбцы BLOB (BYTE или TEXT), вы можете добавить параметр blobidarray , содержащий соответствующие «blob ids», и должны заменить эти столбцы знаком «?» в тексте запроса.

Если содержимое столбца TEXT (или BYTE) позволяет, вы можете также использовать «ifx_textasvarchar(1)» и «ifx_byteasvarchar(1)». Это даст возможность рассматривать столбцы TEXT (или BYTE) точно так, как если бы они были простыми (но long) столбцами VARCHAR для select-запросов, и вам не нужно будет беспокоиться о blob id’ах.

При наличии ifx_textasvarchar(0) или ifx_byteasvarchar(0) (ситуация по умолчанию), select-запросы будут возвращать BLOB-столбцы как blob id’ы (целочисленное значение). Вы можете получить значение blob как строку или файл с помощью blob-функций (см. далее).

ifx_query

(PHP 4, PHP ifx_query — Send Informix query

Description

Sends a query to the currently active database on the server that’s associated with the specified link identifier.

For «select-type» queries a cursor is declared and opened. Non-select queries are «execute immediate».

For either query type the number of (estimated or real) affected rows is saved for retrieval by ifx_affected_rows() .

If the contents of the TEXT (or BYTE) column allow it, you can also use ifx_textasvarchar(1) and ifx_byteasvarchar(1). This allows you to treat TEXT (or BYTE) columns just as if they were ordinary (but long) VARCHAR columns for select queries, and you don’t need to bother with blob id’s.

With ifx_textasvarchar(0) or ifx_byteasvarchar(0) (the default situation), select queries will return BLOB columns as blob id’s (integer value). You can get the value of the blob as a string or file with the blob functions (see below).

Parameters

The query string.

The link identifier.

This optional parameter allows you to make this a scroll and/or hold cursor. It’s a bitmask and can be either IFX_SCROLL , IFX_HOLD , or both or’ed together. I you omit this parameter the cursor is a normal sequential cursor.

If you have BLOB (BYTE or TEXT) columns in the query, you can add a blobidarray parameter containing the corresponding «blob ids», and you should replace those columns with a «?» in the query text.

Return Values

Returns valid Informix result identifier on success, or FALSE on errors.

Examples

Example #1 Show all rows of the «orders» table as a HTML table

Example #2 Insert some values into the «catalog» table

// create blob id’s for a byte and text column
$textid = ifx_create_blob ( 0 , 0 , «Text column in memory» );
$byteid = ifx_create_blob ( 1 , 0 , «Byte column in memory» );

// store blob id’s in a blobid array
$blobidarray [] = $textid ;
$blobidarray [] = $byteid ;

// launch query
$query = «insert into catalog (stock_num, manu_code, » .
«cat_descr,cat_picture) values(1,’HRO’. )» ;
$res_id = ifx_query ( $query , $conn_id , $blobidarray );
if (! $res_id ) <
/* . error . */
>

// free result id
ifx_free_result ( $res_id );
?>

Запрос XML-данных при помощи языка XQuery

Возможно, вы уже слышали о новой архитектуре DB2 Viper, поддерживающей как табличную, так и иерархическую структуры данных. Действительно, в предыдущих статьях мы рассматривали новые функции DB2 для работы с XML-данными, рассказывали, как создавать объекты базы данных и заполнять их XML-данными и объясняли, как работать с XML-данными при помощи языков запросов SQL и SQL/XML. В этой статье мы продолжим изучение возможностей работы с XML-данными в программе DB2, фокусируясь, главным образом, на новой особенности программы – поддержке языка запросов XQuery.

В DB2 язык XQuery рассматривается как полноценный язык, что позволяет пользователям сразу записывать выражения XQuery, не требуя внедрения или встраивания выражений XQuery в оболочку предложений SQL. Более того, механизм запросов DB2 обрабатывает запросы XQuery в свойственной этому языку системе команд, это означает, что выражения XQuery анализируются, оцениваются и оптимизируются без скрытой от пользователя трансляции на язык SQL. Конечно, если вы захотите написать «двуязычный» запрос, который содержит одновременно выражения XQuery и SQL, DB2 обработает и оптимизирует и такие запросы.

Как и в статье по SQL/XML «Query DB2 XML Data with SQL» (Запрос XML-данных в среде DB2 при помощи языка SQL)»: в этой статье вы найдете обзор нескольких обычных для запросов задач и увидите, как можно использовать язык XQuery для решения этих задач. Но сначала вкратце рассмотрим отличия языка XQuery от SQL.

Язык XQuery

XQuery во многом отличается от SQL, главным образом, потому, что эти языки разрабатывались для работы с различными моделями данных, имеющими разную структуру. Документ в формате XML содержит иерархии, для него характерен внутренний порядок. Табличные структуры данных, поддерживаемые СУБД на базе SQL, являются однородными и линейными, строки, как таковые, неупорядочены.

Различия между этими моделями данных привели к нескольким принципиальным отличиям в соответствующих им языках запросов. Например, XQuery поддерживает выражения пути, что позволяет программистам перемещаться по иерархической структуре документа XML, в то время как «чистый» SQL (без расширений XML) не допускает такой возможности. XQuery поддерживает как типизированные, так и не типизированные данные, а для данных SQL всегда определен конкретный тип. В XQuery нет нулевых значений, поскольку в документах XML недостающие или неизвестные данные пропускаются, а SQL, как известно, использует нули для представления недостающих или неизвестных значений данных. XQuery возвращает последовательности данных XML; SQL возвращает наборы результатов, состоящие из различных типов данных SQL.

Это только некоторые из принципиальных различий между XQuery и SQL. В рамках этой вводной статьи не приводится исчерпывающий список таких отличий, но они будут подробно рассматриваться в следующем номере журнала IBM Systems Journal. А сейчас давайте перейдем к изучению некоторых основных аспектов языка XQuery и способов его использования для запроса XML-данных в программе DB2 Viper.

База данных для упражнений

Запросы, описанные в этой статье, обращаются к примерам таблиц, создание которых описано в статье «Getting off to a fast start with DB2 Viper» (Начинаем работать с программой DB2 Viper) (developerWorks, March 2006). Коротко: листинг 1 определяет примеры таблиц «items» и «clients».

Листинг 1. Определения таблиц

Примеры XML-данных, включенных в столбец «items comments», показаны на рисунке 1, а примеры XML- данных, включенных в столбец «clients contactinfo» — на рисунке 2. Последующие примеры запросов будут ссылаться на конкретные элементы одного или сразу обоих документов XML.

Рисунок 1. Пример документа XML сохранен в столбце «comments» таблицы «items»
Рисунок 2. Пример документа XML сохранен в столбце «contactinfo» таблицы «clients»

Среда запроса

Все запросы в этой статье разработаны для того, чтобы вы могли повторить их на своем компьютере. Это можно сделать через обработчик командной строки программы DB2 или редактор команд DB2 Command Editor модуля DB2 Control Center. Изображения снимков экрана и инструкции в данной статье относятся ко второму варианту (DB2 Viper поставляется со средой разработчика Developer Workbench на базе платформы Eclipse, что может помочь программисту конструировать запросы наглядным способом. В этой статье проблемы разработки приложений в среде разработки Developer Workbench не рассматриваются).

Для использования редактора команд DB2 Command Editor, откройте Control Center и выберите команду Tools -> Command Editor. На экране появится окно, показанное на рисунке 3 . В верхней панели введите запрос, затем нажмите зеленую стрелку в верхнем левом углу, чтобы выполнить его, и просмотрите полученные результаты в нижней панели или на отдельной вкладке «Query Results».

Рисунок 3. Окно DB2 Command Editor, которое может быть вызвано из DB2 Control Center

Примеры запросов XQuery

Как и в статье «Query DB2 XML Data with SQL» (Запрос XML-данных в среде DB2 при помощи языка SQL): здесь мы рассмотрим несколько распространенных бизнес-сценариев и продемонстрируем, как использовать язык XQuery, чтобы запрос соответствовал требованиям для XML-данных. В статье будут также рассмотрены более сложные ситуации, которые потребуют внедрения в запрос XQuery кода SQL.

В XQuery существует несколько различных видов выражений, которые можно комбинировать в любых сочетаниях. Каждое выражение возвращает список значений, которые могут быть использованы как исходные данные для других выражений. Результат того выражения, в которое вложены все остальные выражения, является результатом всего запроса в целом.

В этой статье описываются два важных типа выражений XQuery: это выражения «FLWOR» и выражения пути. Выражения FLWOR подобны выражениям SELECT-FROM-WHERE в SQL – они используются для выполнения итераций по списку объектов и необязательного возврата значений, вычисленных для каждого объекта. Выражения пути, с другой стороны, перемещаются по иерархии XML-элементов и возвращают те элементы, которые обнаружены в конце пути.

Подобно выражению SELECT-FROM-WHERE языка запросов SQL, выражение XQuery FLWOR может включать несколько предложений, которые начинаются с определенных ключевых слов. Для начала предложений в выражении FLWOR используются следующие ключевые слова:

  • for : выполняет итерацию над последовательностью введенных значений, по очереди связывая переменную с каждым значением
  • let : задает переменную и присваивает ей значение, которое может быть списком, содержащим несколько элементов
  • where : определяет критерии фильтрации результатов запроса
  • order by : определяет порядок сортировки результатов
  • return : определяет результат, который следует отобразить

Выражение пути в XQuery состоит из серии «шагов», разделенных символом «косой черты». В простейшей форме, каждый шаг ведет вниз по иерархии XML, чтобы найти потомка для элемента, возвращенного на предыдущем шаге. Каждый шаг в выражении пути может также содержать предикат, который фильтрует элементы, возвращенные на этом шаге, оставляя только те из них, которые удовлетворяют некоторым условиям. Например, представим, что переменная $clients ограничена списком XML-документов, содержащих элементы , тогда четырехшаговое выражение пути $clients/Client/Address[state = «CA»]/zip возвратит список почтовых индексов клиентов, имеющих калифорнийские адреса.

В большинстве случаев можно написать запрос, используя одно из двух выражений – либо выражение FLWOR, либо выражение пути.

Использование в DB2 XQuery в качестве основного языка запросов

Для выполнения запросов XQuery непосредственно в среде DB2 Viper (в отличие от внедрения их в предложения SQL) следует предпослать запросу ключевое слово xquery. Это сообщает DB2 о необходимости вызвать для обработки запроса анализатор языка XQuery. Обратите внимание, что это нужно делать только в том случае, если вы используете XQuery в качестве верхнеуровневого языка запросов. Если вы включаете выражения XQuery в SQL, нет необходимости предварять их ключевым словом xquery. В этой статье язык XQuery используется в качестве основного языка, поэтому все запросы начинаются с xquery .

При выполнении в качестве основного языка запросов, XQuery требуется источник данных для ввода. Один из методов, который XQuery может использовать, чтобы получить данные для ввода, это вызов функции db2-fn:xmlcolumn с параметром, который определяет имя таблицы и имя столбца в столбце XML таблицы DB2. Функция db2-fn:xmlcolumn возвращает последовательность документов XML, которая хранится в данном столбце. Например, представленный в следующем листинге запрос возвращает последовательность документов XML, содержащих контактную информацию клиентов:

Листинг 2. Простой запрос XQuery, возвращающий контактные данные

Вспомните нашу схему базы данных (см. раздел «База данных для упражнений» ): мы сохраняли такие XML-документы в столбце «contactinfo» таблицы «clients». Обратите внимание, что имена столбца и таблицы в данном случае отображаются в верхнем регистре. Это объясняется тем, что имена таблицы и столбца перед записью во внутренний каталог DB2 обычно переводятся в верхний регистр. Язык XQuery является регистрозависимым, поэтому имена таблиц и столбцов, записанные в нижнем регистре, не соответствовали бы именам в каталоге DB2, записанным в верхнем регистре.

Извлечение определенных элементов XML

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

Листинг 3. Поиск данных о номере факса клиента при помощи выражения FLWOR

В первой строке листинга содержится указание для DB2 о необходимости вызова анализатора языка XQuery. В следующей строке DB2 получает указание выполнить итерацию на вложенных элементах элементов Client, содержащимся в столбце CLIENTS.CONTACTINFO. Каждый элемент fax по очереди связывается с переменной $y. Третья строка показывает, что для каждой итерации возвращается значение переменной $y. Результат представляет собой последовательность элементов XML, показанную в листинге 4:

Листинг 4. Пример вывода данных для предыдущего запроса

Отступив от темы, заметим, что вывод может также содержать некоторую информацию, не представляющую большого интереса для данной статьи: версия XML и кодировка данных, например, , и информация о пространстве имен XML, например, . Чтобы облегчить изучение результата запроса, в этой статье мы опускаем данную информацию. Тем не менее, она может быть важной для некоторых приложений XML. Если для выполнения запроса вы пользуетесь обработчиком командной строки программы DB2, вы можете использовать параметр –d , чтобы скрыть информацию описания XML и параметр –i для вывода результатов на печать в удобном виде.

Запрос из листинга 3 мог бы выглядеть чуть более лаконичным, если бы был решен в форме трехшагового выражения пути, как показано в листинге 5:

Листинг 5. Поиск данных о номере факса клиента при помощи выражения пути

Первым шагом выражения пути осуществляется вызов функции db2-fn:xmlcolumn , что позволяет получить список XML-документов из столбца CONTACTINFO таблицы CLIENTS. Второй шаг возвращает все элементы Client данного документа, а третий шаг – элементы fax, вложенные в элементы Client.

Если вы не заинтересованы в получении по запросу кода XML-фрагментов, и вам нужно только текстовое представление выбранных значений элемента XML, то вы можете включить в предложение return функцию text () , как показано в листинге 6: Листинг 6:

Листинг 6. Два запроса на выборку текстового представления данных о номере факса клиента

Вывод этих запросов может быть примерно таким, как показано в листинге 7:

Листинг 7. Пример вывода возвращаемых данных для предыдущего запроса

Результаты этих учебных запросов относительно просты, потому что элемент fax имеет в основе примитивный тип данных. Безусловно, в основе элементов могут быть и сложные типы, то есть, элементы могут содержать вложенные элементы (или вложенные иерархии). Элемент Address из контактной информации нашего клиента – один из примеров таких данных. В соответствии со схемой, которая приводится в статье «Get off to a fast start with DB2 Viper» (Начинаем работать с программой DB2 Viper) (сайт developerWorks, март 2006 г.), этот элемент может содержать название улицы, номер дома и квартиры, название города, штата и почтовый индекс. Подумаем, какие данные возвращает запрос XQuery в (листинг 8) :

Листинг 8. Запрос на выборку сложного типа данных XML при помощи выражения FLWOR

Если вы решили, что правильный ответ – это последовательность фрагментов XML-данных, содержащая элементы Address и все вложенные элементы, то вы не ошиблись В листинге 9 – пример такого вывода данных:

Листинг 9. Пример вывода возвращаемых данных для предыдущего запроса

Примечание: Данный пример представлен в форматированном виде, чтобы облегчить его чтение. Редактор команд DB2 Command Editor отображает каждую запись адреса клиента отдельной строкой.

Фильтрация значений элементов XML

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

Можно предположить, что предложение where запросов XQuery позволяет выполнить фильтрацию результатов по значению элемента zip в нашем XML-документе. Листинг 10 показывает, как добавить предложение where к предыдущему выражению FLWOR (листинг 8) и получить только те адреса, которые нас интересуют:

Листинг 10. Выражение FLWOR с новым предложением «where»

Добавленное выражение where достаточно просто для понимания. Предложение for связывает переменную $y с каждым возвращаемым адресом по очереди. Предложение where содержит небольшое выражение пути, которое перемещает фокус запроса от каждого элемента address к вложенному в него элементу zip. Предложение where истинно (и адрес остается в результатах запроса) только в том случае, если значение элемента zip равно 95116.

Такой же результат можно получить, добавив к выражению пути предикат, как показано в листинге 11:

Листинг 11. Выражение пути, в которое добавлен фильтрующий предикат

Конечно, вы можете указать в качестве условия фильтрации значение почтового индекса, и тогда будут возвращены элементы независимо от названия улицы и номера дома. Более того, вы также можете выполнить фильтрацию по нескольким значениям элементов XML в одном запросе. Следующий запрос возвращает информацию об адресах электронной почты тех клиентов, чей адрес соответствует конкретному почтовому индексу Нью-Йорка (10011) или любых клиентов проживающих в городе Сан-Хосе.

Листинг 12. Фильтрация результатов запроса по нескольким элементам XML при помощи выражения FLWOR

Обратите внимание, что мы изменили предложение for, и теперь оно связывает переменную $y не с элементами Address, а с элементами Client. Это дает возможность отфильтровать элементы Client по одной части поддерева (Address) и возвратить другую часть поддерева (email). Выражение пути в предложении where и предложение return могут быть написаны для элемента, который связывается с переменной (в нашем случае, $y).

Этот же запрос можно сделать более лаконичным, используя выражение пути:

Листинг 13. Фильтрация результатов запроса по нескольким элементам XML при помощи выражения пути

При рассмотрении этих двух форм запросов не так очевидно то, что любая из них возвращает результаты, которые по двум направлениям существенно отличаются от тех, которые мог бы ожидать программист SQL:

  1. Вы не получите XML-данные на тех отвечающих критериям запроса клиентов, которые не предоставили свой адрес электронной почты. Другими словами, если у вас есть 1000 клиентов, проживающих в Сан-Хосе или имеющих почтовый индекс 10011, а 700 из них предоставили по одному адресу электронной почты, вы получите результат в виде списка этих 700 адресов электронной почты. Причина этого кроется в принципиальном различии между языками XQuery и SQL, о котором мы говорили раньше – Xquery не использует нулевые значения.
  2. Вы не узнаете, какие из адресов электронной почты были извлечены из одного документа XML. Другими словами, если у вас есть 700 клиентов, проживающих в Сан-Хосе или имеющих почтовый индекс 10011, и каждый из них указал по два адреса электронной почты, вы получите результат в виде списка этих 1400 адресов электронной почты. Вы не могли бы получить последовательность из 700 записей, каждая из которых включает два электронных адреса.

Любая из двух ситуаций может быть желательной при одних обстоятельствах и нежелательной при других. Например, если вам нужно отправить по электронной почте уведомление каждому соответствующему критериям запроса зарегистрированному клиенту, то итерация по списку адресов электронной почты клиентов в формате XML может быть выполнена в любом приложении. Однако, если вы хотите послать строго одно уведомление каждому клиенту, включая и тех, кто предоставил вам только почтовый адрес, в этом случае описанный ранее запрос XQuery не обеспечит нужного результата.

Существует несколько способов переделать запрос таким образом, чтобы он возвратил результаты, представляющие собой смешанную информацию в одной форме, c указанием тех случаев, когда несколько адресов электронной почты были извлечены из одной записи о клиенте (то есть, из одного документа XML). Давайте ознакомимся с одним из способов. Но если все, чего вы хотите – это получить список, содержащий один адрес электронной почты для каждого соответствующего критериям запроса клиента, вам следует лишь слегка изменить предложение return в предыдущем запросе:

Листинг 14. Извлечение только первого элемента email для каждого клиента

Этот запрос вынуждает DB2 возвратить первый из адресов электронной почты, который она обнаружит внутри каждого соответствующего критериям поиска XML-документа (записи о контактных данных клиента). Если адрес электронной почты для клиента, соответствующего критериям запроса, не будет обнаружен, то для этого пользователя в результате запроса не будет содержаться никакой информации.

Преобразование вывода XML

Замечательная особенность XQuery – это возможность преобразовать XML-результат запроса из одной формы XML в другую. Например, вы можете использовать запрос XQuery для выборки всех или части хранимых документов XML и преобразовать вывод в формат HTML, который можно просмотреть в веб-браузере. Следующий запрос (листинг 15) осуществляет выборку адресов клиентов с сортировкой результатов по почтовому индексу, и преобразует результат в элементы XML, являющиеся частью неупорядоченного списка HTML:

Листинг 15. Выполнение запроса к DB2 на выборку XML-данных и возвращение результата в формате HTML

Запрос начинается достаточно просто с ключевого слова xquery, которое сообщает синтаксическому анализатору DB2, что язык XQuery используется в качестве основного языка. Вторая строка указывает, что в результаты следует включить HTML-разметку для неупорядоченного списка (

    ). Эта же строка вводит фигурную скобку, первую из двух комплектов, используемых в этом запросе. Фигурные скобки заставляют DB2 проанализировать и обработать вложенное выражение, а не рассматривать его как строку символов..

Третья строка выполняет итерацию на последовательности адресов клиентов, связывая переменную $y по очереди с каждым элементом address. Четвертая строка включает новое предложение order by , определяющее, что результаты должны быть возвращены в порядке возрастания (это порядок по умолчанию) почтовых индексов клиентов (вложенного элемента zip для каждого адреса, связанного с $y). Предложение return сообщает программе, что элементы Address следует заключить в HTML-тэги элементов списка до возвращения результата. А заключительная строка заканчивает формирование запроса и завершает HTML-разметку неупорядоченного списка.

Илон Маск рекомендует:  Как добавить подчёркивание к ссылке

Вывод будет отображен так, как показано в листинге 16:

Листинг 16. Пример HTML-вывода для предыдущего запроса

Давайте обдумаем тему, которая обсуждалась раньше: как написать запрос XQuery, чтобы в возвращенных результатах были показаны пропущенные значения и отмечены случаи, когда один XML-документ (например, одна запись о клиенте) содержит повторяющиеся элементы (такие, как несколько адресов электронной почты). Один из способов – включить возвращаемые данные в новый элемент XML, как показано в следующем запросе в листинге 17:

Листинг 17. Отображение недостающих значений и повторяющихся элементов в результатах запроса XQuery

Выполнив этот запрос, мы получим возвращенные данные в виде последовательности элементов «emailList», причем один элемент будет соответствовать одной записи о клиенте. Каждый элемент emailList будет содержать адрес электронной почты. Если DB2 обнаружит в записи клиента единственный адрес электронной почты, то будет возвращен этот элемент и его значение. При обнаружении несколько адресов будут возвращены все элементы e-mail вместе с их значениями. Наконец, если не будет найдено ни одного адреса электронной почты, то будет возвращен пустой элемент emailList. Таким образом, вывод может выглядеть следующим образом:

Листинг 18. Пример вывода для предыдущего запроса

Использование условной логики

Способность XQuery трансформировать результаты запроса XML можно для снижения сложности кода приложения комбинировать с встроенной в язык поддержкой условной логики . Давайте рассмотрим простой пример. Таблица “items” содержит столбцы XML с комментариями, сделанными клиентами по поводу продукции. Для клиента, который запросил ответ на свои комментарии, вам, возможно, захочется создать новые элементы «action», содержащие идентификатор продукта, идентификатор клиента и сообщение, чтобы эту информацию можно было отправить соответствующему лицу для обработки. Однако комментарии, не требующие ответа, тем не менее, имеют значение для бизнеса, и вы не хотели бы просто игнорировать их. Для этих комментариев создайте элемент «info», включающий только идентификатор продукта и сообщение. В следующем листинге показано, как можно для решения этой задачи использовать выражение XQuery if-then-else :

Листинг 19. Использование выражения «if-then-else» в запросе XQuery

Большая часть аспектов этого запроса должна быть понятна вам уже сейчас, так что давайте сконцентрируемся на условной логике. Предложение if определяет, будет ли значение вложенного элемента ResponseRequested для данного комментария равным «Да».Если будет, то вычисляется предложение then , в результате чего DB2 возвращает новый элемент («action»), который содержит три вложенных элемента: В противном случае, вычисляется предложение else, и DB2 возвращает элемент «info», который содержит только идентификатор продукта и сообщение.

Использование предложения «let»

Мы уже рассмотрели как использовать все части выражения FLWOR кроме одной: речь идет о предложении let. Это предложение используется для присваивания значения (возможно, содержащего список из нескольких объектов) переменной, которая может быть использована в других предложениях выражения FLWOR.

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

Листинг 20. Использование предложения «let»

Функция distinct-values в предложении for возвращает список всех неповторяющихся значений элементов ProductID, найденных среди комментариев в столбце COMMENTS таблицы ITEMS. Предложение for связывает переменную $p по очереди с каждым из этих значений ProductID. Для каждого значения $p предложение let повторно сканирует столбец ITEMS и связывает переменную $pc со списком, содержащим все комментарии, для которых ProductID совпадает с ProductID в переменной $p. Предложение return конструирует новый элемент «product» для каждого из неповторяющихся значений ProductID. Каждый из этих элементов «product» содержит два вложенных элемента: элемент «id», содержащий значение ProductID, и элемент «comments», содержащий число, соответствующее количеству комментариев, полученных по данному продукту.

Результаты запроса могут выглядеть примерно так:

Листинг 21. Пример вывода для предыдущего запроса

Запросы XQuery c вложенным SQL

К этому моменту мы рассмотрели, как составлять запросы XQuery, которые осуществляют выборку фрагментов XML-документа, создают новые формы вывода XML и возвращают различные результаты на основании условий, определенных в самих запросах. Другими словами, вы познакомились с несколькими способами использования языка XQuery для составления запроса на выборку XML-данных, хранимых в DB2.

Конечно, весь язык XQuery нельзя изучить, прочитав такую короткую статью. Но мы не можем пропустить обширную тему, которую до сих пор не рассматривали: как встроить код SQL в запрос XQuery. Это может оказаться полезным, если вам нужно составить запросы, которые осуществляют фильтрацию данных по XML- и не-XML значениям столбцов.

Вспомните статью «Query DB2 XML Data with SQL» (Запрос XML-данных в среде DB2 при помощи языка SQL): в ней шла речь о том, как использовать для решения этой задачи простые выражения XQuery, встроенные в предложения SQL. В этой статье попробуем сделать наоборот: встроим в запрос на языке XQuery код SQL, чтобы ограничить результат по значениям как в традиционном формате данных SQL, так и в специфическом формате элементов XML.

Вместо того, чтобы вызвать функцию db2-fn:xmlcolumn , которая возвращает XML-данные из столбца таблицы, можно вызвать функцию db2-fn:sqlquery , которая выполнит запрос SQL и возвратит только выбранные данные. Запрос SQL, переданный функции db2-fn:sqlquery , должен возвратить XML-данные. Обработка этих XML-данных впоследствии может быть продолжена в среде XQuery.

Запрос, описанный в листинге 22 , осуществляет поиск информации о комментариях, относящихся к продуктам с рекомендуемой розничной ценой (*srp*) более 100 долларов вместе с запросом ответа от клиента. Вспомним, что данные о цене хранятся в десятичном столбце в формате SQL, тогда как комментарии клиентов хранятся как XML-данные. Возвращенные данные, включая идентификатор продукта, идентификатор клиента и сообщение клиента для каждого соответствующего критерию запроса комментария, хранящегося в базе данных, включается в отдельный элемент XML «action».

Листинг 22. Встраивание кода SQL в запрос XQuery

И снова, большая часть аспектов этого запроса должна быть понятна вам уже сейчас, так что давайте сконцентрируемся на новой функции. db2-fn:sqlquery. DB2 обрабатывает предложение SQL SELECT, дополненное этой функцией, чтобы определить, в каких строках содержится информация об объектах стоимостью более 100 долларов. Документы, хранимые в этих строках, служат вводными данными для выражения пути, которое возвращает все вложенные элементы Comments. Последующие части запроса используют предложение XQuery where для дальнейшей фильтрации возвращаемых данных и преобразования частей выбранных комментариев в новые фрагменты XML.

С учетом вышесказанного, давайте подумаем, как можно решить другую задачу, которая немного отличается от первой. Представьте, что вам нужно получить список адресов электронной почты для «особо ценных» клиентов, проживающих в городе Сан-Хосе.Кроме того, если для одного клиента в базе данных имеется несколько адресов электронной почты, то вы хотите включить их все в вывод запроса таким образом, чтобы они были частью отдельной записи о клиенте.. И, наконец, если соответствующий критериям отбора «особо ценный» клиент не предоставил адреса электронной почты, вы хотите получить его обычный почтовый адрес. Листинг 23 иллюстрирует один из способов составления такого запроса:

Листинг 23. Встраивание кода SQL в тело запроса XQuery, который содержит условную логику

Два аспекта этого запроса нуждаются в некотором объяснении. Во-первых, предложение SELECT, встроенное во вторую строку, содержит предикат запроса по столбцу «status», который сравнивает значения столбца VARCHAR со строкой «Gold». В SQL такие строки заключаются в простые кавычки. Обратите внимание на то, что, хотя кажется, что в примере используются двойные кавычки, на самом деле это две простые кавычки до и после критерия сравнения («Gold»). Дополнительные простые кавычки являются escape-символами. Если вы заключите ваш предикат запроса со строковым условием вместо пары простых кавычек в двойные кавычки, вы получите синтаксическую ошибку.

Кроме того, предложение return в этом запросе содержит условную логику, которая позволяет определить, имеется ли элемент email в данной записи о клиенте. Если имеется, то запрос возвратит новый элемент «emailList», содержащий все адреса электронной почты клиента (то есть, все элементы e-mail для этого клиента). В противном случае будет возвращен почтовый адрес клиента (то есть, элемент Address для этого клиента).

Индексирование

И наконец, ничего не стоит научиться создавать специализированные индексы XML для ускорения доступа к данным, хранимым в столбцах XML. Мы не будем рассматривать эту тему в данной статье из-за вводного характера статьи и малого объема данных для упражнений. Однако, в производственной обстановке определение соответствующих индексов может быть критически важным для достижения оптимальной производительности. Чтобы Ресурсы больше узнать о новой технологии индексирования в DB2, обратитесь к источникам из раздела «Ресурсы».

Заключение

Язык XQuery имеет много существенных отличий от языка SQL; некоторые из них были освещены в этой статье. Более детальное изучение этого языка поможет вам определить, в каких случаях можно с выгодой использовать его в работе, а также понять, когда может оказаться полезным совместное использование XQuery и SQL. В следующей статье мы углубимся в еще одну тему, которая может показаться вам интересной: как разработать Java-приложения, использующие возможности работы с XML в DB2. Впрочем, и в этой статье содержится маленький пример, как можно внедрить в запрос XQuery Java-приложение.

Благодарности

Выражаем благодарность Джорджу Лапису (George Lapis), Матиасу Никола (Matthias Nicola) и Гэри Робинсону (Gary Robinson) за рецензирование этой статьи.

Ресурсы для скачивания

Похожие темы

  • Создайте свой проект разработки с помощью пробного ПО IBM, которое можно загрузить непосредственно с сайта developerWorks;
  • Веб-сайт DB2 Viper: более подробная информация о поддержке программой DB2 стандарта XML;
  • What’s new in DB2 Viper: XML to the Core» (Новое в DB2 Viper: XML в центре внимания)(сайт developerWorks, февраль 2006 г.): ознакомьтесь с обзором новых технологий XML, включенных в бета-версию DB2;
  • «Get off to a start start with DB2 Viper» (Начинаем работать с программой DB2 Viper)(сайт developerWorks, март 2006 г.): научитесь вставлять, импортировать и проверять корректность XML-данных;
  • «Query XML Data in DB2 with SQL» (Запрос XML-данных в DB2 при помощи языка SQL)(сайт developerWorks, март 2006 г.): научитесь запрашивать XML-данные в DB2, используя «чистый» SQL и SQL/XML;
  • XQuery from the Experts (XQuery с точки зрения специалистов) (Хауард Кац (Howard Katz), и коллектив авторов, издательство Addison-Wesley, 2003 г.): узнайте больше о языке запросов XQuery. На сайте доступны выдержки из книги ;
  • «Integration of SQL and XQuery in DB2» (Интеграция SQL и XQuery в DB2)(в следующем номере журнала IBM Systems Journal): исследуйте, чем отличаются языки SQL и XQuery и как можно эффективно использовать комбинацию этих двух языков;
  • «Firing up the Hybr >

Комментарии

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

Автоматизация импорта данных в Google BigQuery с помощью Google Cloud Functions

Мы постоянно работаем с Google BigQuery – импортируем данные о пользователях, их заказах и расходах на рекламу из разных источников, чтобы иметь возможность объединять их между собой. Что нам это дает? Например, если у вас интернет-магазин и клиент делает заказ по телефону, а потом авторизовывается на сайте, то с помощью Google BigQuery можно связать все его действия задним числом. Можно отслеживать весь путь клиента по маркетинговой воронке – от первого попадания на сайт до покупки в brick and mortar магазине, и оценивать рекламные кампании с учетом таких офлайн-продаж.

В этой подборке у нас 6 python-скриптов для автоматизации импорта данных в Google BigQuery с помощью Google Cloud функции: FTP; FTPS; HTTP(s); Intercom; MySQL и SFTP. Принцип работы один: с помощью HTTP POST запроса вызывается Cloud-функция, которая получает данные из источника и загружает их в таблицу Google BigQuery. Если таблица уже существует в выбранном датасете, то она будет перезаписана.

Основные требования

Этапы настройки

  • Перейдите в Google Cloud Platform Console и авторизуйтесь с помощью Google аккаунта, или зарегистрируйтесь, если аккаунта еще нет.
  • Перейдите в проект с активированным биллингом или создайте новый биллинг-аккаунт для проекта.
  • Перейдите в раздел Cloud Functions и нажмите «Создать функцию». Обратите внимание, что за использование Cloud-функций взимается плата.
  • Заполните следующие поля:

Название: например, ftp-bq-integration или любое другое подходящее название;

Выделенный объем памяти: 2 ГБ или меньше, в зависимости от размера обрабатываемого файла;

Исходный код: Встроенный редактор;

Среда выполнения: Python 3.X.

  • Скопируйте содержимое файла main.py в встроенный редактор, вкладка main.py.
  • Скопируйте содержимое файла requirements.txt в встроенный редактор, вкладка requirements.txt.
  • В качестве вызываемой функции укажите ftp/ftps/https и так далее, в зависимости от модуля, который вы используете.
  • В дополнительных параметрах увеличьте время ожидания с 60 сек. до 540 сек. или меньшее, в зависимости от размеров обрабатываемого файла.
  • Завершите создание Cloud-функции, нажав на кнопку «Создать».

FTP / FTPS / SFTP

Этот модуль предназначен для передачи файлов с FTP (FTPS, SFTP) — серверов в Google BigQuery с помощью Google Cloud функции. Решение позволяет автоматически выполнять загрузку данных в Google BigQuery из файла, который регулярно обновляется на FTP-сервере.

Файл, который нужно получить с соответствующего сервера, может иметь любое подходящее расширение (.json, .txt, .csv), но должен быть в одном из следующих форматов: JSON (newline-delimited) или Comma-separated values (CSV).

Пример использования

HTTP(s)

Модуль для передачи файлов с HTTPS-серверов в Google BigQuery.

Пример использования

Intercom

Модуль для автоматизации передачи данных из Intercom в Google BigQuery с помощью Google Cloud функции. В настоящее время модуль позволяет импортировать из Intercom такие сущности, как: users, companies, contacts, admins, conversations, teams, tags, segments. При этом модуль не поддерживает custom attributes.

Пример использования

MySQL

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

Пример использования

Более подробную документацию для каждого модуля можно найти в файлах readme в каждом из разделов.

Это только начало, и сейчас мы работаем над скриптами для Bitrix и amoCRM, потому что видим, что они самые востребованные среди наших клиентов. Поделитесь, какие способы вы используете для объединения данных и каких интеграций вам не хватает для этого.

Создание запросов с помощью Microsoft Query

Читайте также:

  1. DocsVision клиент для Microsoft Outlook
  2. I.1.1. Создание основного документа
  3. II.1.2. Позиционирование текста с помощью табуляции
  4. Microsoft
  5. Microsoft
  6. Microsoft Baseline Security Analyzer
  7. Microsoft Excel
  8. Microsoft Excel
  9. Microsoft Excel Поиск решения
  10. MICROSOFT POWERPOINT
  11. Microsoft Project
  12. Microsoft TechNet и Microsoft Developer Network на компакт-дисках

Инструмент Query можно использовать для работы с любым источником данных, для которого в системе установлен соответствующий драйвер ODBC (Open Database Connectivity). Драйверы ODBC это программы, используемые для подключения к конкретной базе данных. Каждая база данных, например Microsoft Access, требует своего драйвера ODBC. Драйверы всех наиболее популярных баз данных включены в состав Microsoft Excel.

Инструмент Microsoft Query может быть вызван из Excel для создания запроса, включающего условия отбора, затем этот же инструмент направляет запрос к внешнему источнику данных в виде SQL (Structured Query Language) инструкции. Вся работа по отбору данных производится вне Excel (например, на сервере баз данных), что позволяет экономить время и память компьютера. Возвращаемые запросом данные могут поступать на рабочий лист в виде списка таблицы или в виде сводной таблицы. Данные из OLAP-источников могут поступать только в виде сводной таблицы.

Первым шагом использования Queryдля импорта данных в таблицы является задание источника данных. Источник данных – это совокупность информации, хранящаяся в файле, с расширением *.DSN, которая позволяет Excel подключаться к внешней базе данных. Обычно в файле источника данных содержится имя БД, ее местонахождение, драйверы подключения и т. п. Файлы источников данных находятся в специальной папке C:\Program Files\’Common Files\ ODBC\ Data Sources\*.dsn и представляют собой простой текст, который можно отредактировать или прочитать в БЛОКНОТЕ.

Для задания источника данных необходимо с помощью меню Данные, команды Импорт внешних данных,Создать запрос открыть диалоговое окно Выбор источника данных (см. рис. 6.1). В этом диалоговом окне отображаются все существующие источники данных, чьи файлы *.dsn сохранены в соответствующей папке, и, обычно, любые доступные реестры DSN, имена которых отмечены звездочками. Реестр DSN – это источник данных, созданный в версии Query, более ранней, чем Query 97.

Рис. 6.1. Диалоговое окно задания источника данных

Чтобы подключиться к существующему источнику данных, выделить его в списке доступных файлов *.dsn и нажать клавишу ОК.

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

1. В диалоговом окне Выбор источника данных дважды щелкнуть мышью по строке вкладки Базы данных.

2. Выполнить три (четвертый не обязателен) последовательных шага открывшегося мастера создания нового источника данных:

1 шаг.Задать имя, присваиваемое новому источнику данных, – любое имя, под которым будет определять источник данных.

2 шаг.В свернутом списке выбрать соответствующий источнику данных драйвер, т. е. указать к какому типу относится наша база данных, на какой платформе (или каким приложением она разработана).

3 шаг.Нажать кнопку Связь и ввести сведения, необходимые выбранному драйверу, в окно диалога «Установка драйвера ODBC для …».

4 шаг.На последнем шаге мастера, если в базе данных несколько таблиц, можно указать имя таблицы, которая будет использоваться по умолчанию; этот шаг является необязательным.

3. После выполнения всех шагов мастера создания источника данных, необходимо нажать кнопку ОК, вернуться в диалоговое окно Выбор источника данных, в которое будет добавлено имя созданного источника.

После того как источник данных определен, можно создавать запрос. Запрос можно создать двумя способами – мастером и непосредственно работая в Microsoft Query. При создании простых запросов обычно бывает достаточно возможностей мастера. Если же необходимо создавать запросы с вычисляемыми условиями отбора или параметрические запросы, необходимо работать непосредственно с Query.

При создании запроса с использованием мастера запросов необходимо, определив источник данных в диалоговом окне Выбор источника данных (см. рис. 6.1), установить флажок Использовать мастер запросов, а затем нажать клавишу ОК.

Работа мастера создания запросов включает четыре шага:

1 шаг.Выбор полей таблиц, которые будут включены в таблицу или сводную таблицу Excel; для просмотра данных выбираемого поля можно выделить это поле в списке Имеющиеся таблицы и столбцы, нажать клавишу Просмотр.

2 шаг.Задание одного или нескольких условий отбора, условие отбора имеет три компонента: имя поля, оператор сравнения и значение, этот шаг является необязательным.

3 шаг.Позволяет сортировать записи по одному или нескольким полям, этот шаг подобно предыдущему является необязательным.

4 шаг.На последнем шаге мастера (см. рис. 6.2) можно сохранить запрос в файле *.dqy, нажав кнопку Сохранить запрос.

Рис. 6.2. Последний шаг мастера создания запроса Query

Если запрос не сохранять, то его можно будет обновлять только из диапазона внешних данных текущего листа, но нельзя будет использовать в других листах.

На последнем шаге мастера можно просмотреть результат выполнения запроса в Query, вызвать мастер создания куба OLAP (см. далее) или вернуть данные в Excel. В последнем случае в диалоговом окне, отображенном на рисунке 6.2, необходимо установить переключатель Вернуть данные в Microsoft Excelи нажать кнопку Готово. После чего на экране появится окно, в котором определяется место размещения данных.

Для того чтобы создать запрос, непосредственно работая в Microsoft Query, необходимо после определения источника данных поступить следующим образом.

1. В диалоговом окне Выбор источника данных (см. рис. 6.1) снять флажок Использовать мастер запросов, а затем нажать клавишу ОК. После чего Query откроет окно нового запроса (см. рис. 6.3) и выведет на экран диалоговое окно Добавление таблицы.

Рис. 6.3. Добавление таблиц в запрос Microsoft Query

2. С помощью диалогового окна Добавление таблицы добавить в запрос таблицы, поля которых используются в запросе, и таблицы, опосредовано через которые первые связаны.

Query автоматически объединяет таблицы, если обнаруживает ключевое поле в одной таблице и поле с таким же именем в другой. Чтобы получить информацию о типе объединения таблиц, можно дважды щелкнуть мышью на линии, соединяющей связанные поля.

3. После отбора всех необходимых таблиц можно работать в окне запроса, которое первоначально разделено на две части:

· верхняя часть представляет собой область таблиц, в которой расположены таблицы, по полям которых составляется отчет; если запрос составляется по нескольким таблицам, то они должны быть связаны между собой;

· нижняя часть представляет область данных, куда Queryвыводит записи, удовлетворяющие условиям;

· кроме того, в окне запроса может присутствовать область условий.

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

5. В случае необходимости строки в таблице можно сортировать без воздействия на порядок записей в исходной базе данных, для этого используется меню Записи, команда Сортировать.

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

Фильтр по выделенному– выбрать любую запись в поле с нужным значением и нажать на панели инструментов кнопку Фильтр по выделенному .

Другим способом фильтрации данных является задание условий с использованием области условий. Для отображения области условий на экране можно использовать команду Условия в меню Вид. В верхней строке области расположены заголовки полей (см. рис. 6.4), а в нижних строчках расположены соответствующие условия.

Рис. 6.4. Использование области условий

Условия, записанные в одну строку, соединены оператором И, а условия, записанные в разные строку, оператором ИЛИ.

Отображение области условий на экране не является обязательным, для создания условий отбора записей можно использовать диалоговое окно Добавление условия (см. рис. 6.5), вызываемое командой Добавить условие, меню Условие.

Рис. 6.5. Диалоговое окно Добавление условия

Чтобы удалить условие отбора, необходимо выделить его заголовок в области условий и нажать клавишу Delete. Для отмены всех условий и снятия фильтров в меню Условие выбирается команда Удалить все условия.

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

После того как запрос полностью создан, его можно сохранить в файле *.dqy, для чего в меню Файл выбирается команда Сохранить запрос. По умолчанию файлы запросов хранятся в папке профиля пользователя в Application Data\Microsoft\Queries. При сохранении запроса в стандартной папке имя файла запроса выводится на вкладке Запросы при открытии нового запроса в Microsoft Query или при выборе команды Создать запрос (меню Данные, подменю Импорт внешних данных) в Microsoft Excel. Если запрос не сохранить, его нельзя будет использовать в других листах книги.

Для возврата таблицы результатов в Microsoft Excel необходимо в меню Файл выбрать команду Вернуть данные в Microsoft Excel и указать место помещения данных: текущий лист, новый лист или отчет сводной таблицы. Прежде чем создать диапазон внешних данных на листе, можно просмотреть свойства запроса, эти же свойства запроса можно просмотреть позже, нажав кнопку Свойства диапазона данных на панели инструментов Внешние данные.

Для редактирования запроса нужно, поставив указатель мыши на любую ячейку данных, в меню Данные вызвать команду Импорт внешних данных, Изменить запрос.

Дата добавления: 2014-11-29 ; Просмотров: 2161 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

Всемогущая функция Query — подробное руководство

Думаю, все слышали о правиле Парето. В любой сфере 20% усилий дают 80% результата. Например, 20% своего гардероба вы носите 80% времени, 20% ваших клиентов приносят 80% дохода. Так же и в Google Таблицах: зная 20% существующих функций, вы сможете решить 80% всех возможных задач.

Я считаю Query одной из наиболее полезных функций Google Таблиц. Но в справке Google она описывается очень поверхностно, и вся мощь данной функции не раскрыта. При более детальном знакомстве становится ясно, что она способна заменить большую часть существующих функций.

Знания возможностей функции Query помогают в построении аналитических инструментов для бизнеса. Если хотите сэкономить своё время, мы можем построить аналитический инструмент для вас:

Илон Маск рекомендует:  Что такое код domxml_open_mem

Для работы с QUERY вам понадобятся базовые знания SQL. Для тех, кто не в курсе: пугаться не надо, функция QUERY на самом деле поддерживает самые простые возможности SQL.

Синтаксис QUERY

  • данные — это диапазон ячеек, который будет служить базой данных для SQL-запроса;
  • запрос — текст SQL-запроса;
  • заголовки — необязательный аргумент, в котором вы можете указать, сколько первых строк массива содержат заголовки.

Для максимального восприятия дальнейшей информации предлагаю открыть и скопировать себе следующую Google Таблицу. Для того, чтобы создать копию, воспользуйтесь меню «Файл» и выберите в нем пункт «Создать копию».

В доксе, копию которого вы только что создали, существует несколько листов. Лист DB — это база данных, к которой мы будет обращаться с помощью функции QUERY. Листы Level содержат примеры, которые мы будем рассматривать в этой статье. C каждым новым уровнем пример будет усложняться.

План SQL-запроса в функции Query

Любой SQL-запрос состоит из отдельных блоков, которые часто называют кляузами. В SQL для функции Query заложен синтаксис языка запросов API визуализации Google, который поддерживает следующие кляузы:

  • select — перечисление полей, которые будут возвращены запросом;
  • where — содержит перечень условий, с помощью которых будет отфильтрован массив данных, обрабатываемый запросом;
  • group by — содержит перечень полей, по которым вы хотите группировать результат;
  • pivot — помогает строить перекрестные таблицы, используя значение одного столбца в качестве названий столбцов финальной таблицы;
  • order by — отвечает за сортировку результатов;
  • limit — с помощью этой части запроса вы можете задать предел количеству строк, возвращаемых запросом;
  • offset — с помощью этой кляузы вы можете задать число первых строк, которые не надо обрабатывать запросом;
  • label — данная кляуза отвечает за название полей, возвращаемых запросом;
  • format — отвечает за формат выводимых данных;
  • options — дает возможность задавать дополнительные параметры вывода данных.

Hello World для функции Query (Select)

Перейдем на лист Level_1 и посмотрим формулу в ячейке A1.

Часть формулы «DB!A1:L1143» отвечает за базу данных, с которой мы будем делать выборку. Вторая часть «select * limit 100» содержит непосредственно текст запроса. Символ «*» в данном случае означает возвращение всех полей, содержащихся в базе данных. С помощью «limit 100» мы ограничиваем вывод данных в 100 строк максимум. Это пример самого простого запроса. Мы выбрали 100 первых строк из базы данных. Это своего рода «Hello world» для функции Query.

Используем фильтры и сортировку (Where, Order by)

Переходим на лист Level_2. Выберем только некоторые нужные нам поля и зададим условия фильтрации и сортировки. Например, используем данные только по кампаниям Campaign_1 и Campaign_2 за период 22-25 октября 2015 года. Отсортируем их в порядке убывания по сумме сеансов. Для фильтра и сортировки в текст запроса необходимо добавить описание кляуз Where и Order. Для вывода в результирующую таблицу описанного выше примера нам понадобятся поля Campaign, Date и Sessions. Именно их и нужно перечислить в кляузе Select.

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

В нашем случае данные, расположенные на листе DB, и обращение к определенным полям прописываются как название столбцов листа. Таким образом, нужные поля располагается в следующих столбцах:

  • поле Date — столбец A;
  • поле Campaign — столбец B;
  • поле Sessions — столбец G.

Соответственно, часть запроса, отвечающая за перечень выводимых в результате данных, будет выглядеть так:

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

В данном случае мы фильтруем данные по названию кампании (Campaign) и дате (Date). Мы используем несколько условий фильтрации. В тексте запроса между всеми условиями должен стоять логический оператор OR или AND. Фильтрация по датам немного отличается от фильтрации по числовым и текстовым значениям, для ее применения необходимо использовать оператор Date.

Часть запроса, отвечающая за фильтрацию данных, будет выглядеть так:

Мы разбили с помощью скобок фильтрацию данных на две логических части: первая фильтрует по датам, вторая — по названию кампании. На данном этапе формула, описывающая данные, которые необходимо выбрать, и условия фильтрации данных, выглядит так:

Вы можете скопировать ее и вставить, например, на новый лист документа, который используется в качестве примера в этом посте, и получите следующий результат:

Помимо обычных логических операторов (=, ) блок WHERE поддерживает дополнительные операторы фильтрации:

  • contains — проверяет содержание определённых символов в строке. Например, WHERE A contains ‘John’ вернёт в фильтр все значения из столбца A, в которых встречается John, например, John Adams, Long John Silver;
  • starts with — фильтрует значения по префиксу, то есть проверяет символы в начале строки. Например, starts with ‘en’ вернёт значения engineering и english;
  • ends with — фильтрует значения по окончанию строки. Например, строка ‘cowboy ’ будет возвращена конструкцией «ends with ‘boy’» или «ends with ‘y’»;
  • matches — соответствует регулярному выражению. Например: where matches ‘.*ia ’ вернёт значения India и Nigeria.
  • like — упрощённая версия регулярных выражений, проверяет соответствия строки заданному выражению с использованиям символов подстановки. На данный момент like поддерживает два символа подстановки: «%» означает любое количество любых символов в строке, и «_» — означает один любой символ. Например, «where name like ‘fre%’» будет соответствовать строкам ‘fre ’, ‘fred ’, и ‘freddy ’.

Запрос уже отфильтровал данные за определенный период и оставил только нужные нам кампании. Остается только отсортировать результат по убыванию в зависимости от количества сеансов. Сортировка в данных запросах осуществляется традиционно для SQL с помощью кляузы Order by. По синтаксису она довольна простая: необходимо только перечислить поля, по которым требуется отсортировать результат, а также указать порядок сортировки. По умолчанию — порядок asc, то есть по возрастанию. Если укажете после название поле параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.

В нашем случае за фильтрацию будет отвечать строчка в тексте запроса:

Соответственно, окончательный результат формулы на листе Level_2, решающий нужную нам задачу, выглядит так:

Теперь вы умеете с помощью простейшего SQL синтаксиса и функции QUERY фильтровать и сортировать данные.

Агрегирующие функции, группировка данных и переименование столбцов (Group by, Label)

Переходим на лист Level_3 и усложняем задачу. В запросах вы можете не только делать выборки, но также проводить различные вычисления и агрегации данных. Для этого в SQL функции Query существует ряд агрегирующих функций и кляуза Group by. Агрегирующие функции:

Функция Описание Поддерживаемый тип данных Возвращаемый тип данных
avg() Возвращает среднее значение для группы Числовой Числовой
count() Возвращает количество значений в группе Любой Числовой
max() Возвращает максимальное значение для группы Любой Аналогичный полю, к которому применяется
min() Возвращает минимальное значение для группы Любой Аналогичный полю, к которому применяется
sum() Возвращает сумму значений в группе Числовой Числовой

Итак, давайте посчитаем данные по каждой кампании:

  • среднесуточное количество сеансов;
  • максимальное количество сеансов за сутки;
  • минимальное количество сеансов за сутки;
  • количество дней, когда по кампании был совершен хотя бы один сеанс;
  • сумма всех сеансов по каждой кампании за весь период.

Для решения этой задачи нам понадобятся данные только из двух полей: Campaign (находится в столбце B) и Sessions (находится в столбце G). Все агрегирующие функции прописываются вместе со списком полей для вывода данных в кляузе Select. В случае применения агрегирующих функций все поля, к которым не применяется этот тип функций, являются группирующими полями. Их необходимо перечислить в кляузе Group by. Агрегирующие функции работают обязательно в паре с Group by. Описание кляузы Select будет следующим:

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

Описание кляузы Group by очень простое:

В кляузе достаточно указать только столбец B, содержащий информацию о названии кампании. Потому что он единственный, к которому мы не применили никакой агрегирующей функции. Наша формула:

Получим следующий результат: В принципе, мы получили желаемый результат, но названия столбцов можно подкорректировать с помощью кляузы Label. Результат будет лучше отображаться, если мы отсортируем отчет по названию кампании. Описание кляузы Order by мы уже рассмотрели выше.

Для нужной сортировки достаточно добавить следующую строку после описания:

Чтобы переименовать столбцы в таблице, которую возвращает запрос, необходимо добавить описание кляузы Label. Синтаксис достаточно прост: сначала указываете выводимый столбец или функцию, которая будет возвращать значения в результирующую таблицу, и далее в одинарных кавычках указываете нужное название.

Это будет выглядеть так:

А результат, возвращаемый формулой, выглядит так:

Все поля названы соответствующим описанию кляузы Label образом. Последнее, что режет глаз в возвращаемой таблице, — формат, в котором выводятся данные в столбце «Среднее». Для корректировки форматов, выводимых запросом данных, требуется описать кляузу Format. Ее описание схоже с описанием Label, но вместо названия поля следует прописать маску вывода данных (также в одинарных кавычках).

Округлим числа в столбце «Среднее» до двух знаков после запятой. Для округления выводимых данных до двух знаков после запятой маска должна выглядеть как ‘0.00’.

Описание кляузы Format

Соответственно, окончательная формула на листе Level_3 выглядит так:

Создание перекрестных таблиц (Pivot, скалярные функции)

Чтобы за считанные секунды с помощью функции QUERY создать перекрестную таблицу, следует добавить в запрос описание кляузы Pivot. Построим отчет, в котором в строках будет номер дня недели, в столбцах ˜— тип устройства, а в качестве выводимых значений рассчитаем показатель отказов. Если вы внимательно изучили структуру базы данных, находящейся на листе DB, то наверняка заметили, что у нас нет поля, содержащего информацию о дне недели, как и поля, содержащего информацию о показателе отказов.

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

Скалярные функции

На момент написания статьи SQL в Google Таблицах поддерживает 14 скалярных функций.

Функция Описание
year() Возвращает номер года из «даты» или «даты и времени». Пример: year(date ‘2009-02-05’) вернет 2009. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
month() Возвращает номер месяца из «даты» или «даты и времени». Но в данном случае январь будет возвращать 0, феврваль 1 и так далее. Началом отсчета для номера месяца является 0. Пример: month(date ‘2009-02-05’) вернет 1. Чтобы функция вернула номер месяца в привычном виде к ее результату прибавьте 1, month(date «2009-02-05»)+1 вернет 2. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
day() Возвращает номер дня в месяце из «даты» или «даты и времени». Пример: day(date ‘2009-02-05’) вернет 5. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
hour() Возвращает номер часа в дне из «даты и времени» или «времени». Пример: hour(timeofday ‘12:03:17′) вернет 12. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
minute() Возвращает номер минуты в часе из «даты и времени» или «времени». Пример: minute(timeofday ‘12:03:17′) вернет 3. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
second() Возвращает номер секунды в минуте из «даты и времени» или «времени». Пример: second(timeofday ‘12:03:17′) вернет 17. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
millisecond() Возвращает номер миллисекунды в секунде из «даты и времени» или «времени». Пример: millisecond(timeofday ‘12:03:17.123′) вернет 123. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
quarter() Возвращает номер квартала в году из «даты и времени» или «времени». Базовым значением или началом отсчета является 1, соответствено, для первого квартала функция вернет значение 1, для второго 2 и так далее. Пример: quarter(date ‘2009-02-05’) вернет 1. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
dayOfWeek() Возвращает номер дня недели в неделе из «даты» или «даты и времени». Началом недели считается воскресенье, для воскресенья функция вернет значение 1, для понедельника 2 и так далее. Пример: dayOfWeek(date ‘2015-11-10’) вернет 3, так как 10 ноября 2015 года — вторник. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных:число.
now() Возвращает текущую дату и время в часовом поясе GTM. Запрашиваемые параметры: не требует ввода параметров. Тип возвращаемых данных: дата и время.
dateDiff() Возвращает разницу в днях между двумя датами. Пример: dateDiff(date ‘2008-03-13’ , date ‘2008-02-12’) вернет 29, так как 10 ноября 2015 года вторник. Запрашиваемые параметры: два параметра с типом «дата» или «дата и время». Тип возвращаемых данных: число.
toDate Возвращает преобразованное в дату значение из «даты» или «даты и времени» или «числа». Пример:
  • toDate(date ‘2008-03-13’) вернет аналогичное значение в формате даты, ‘2008-03-13’.
  • toDate(dateTime‘2013-03-13 11:19:22’) вернет дату ‘2013-03-13’.
  • toDate(1234567890000) вернет дату ‘2009-02-13’.

Запрашиваемые параметры: один параметр с типом дата, дата и время или число. Тип возвращаемых данных: дата.

upper() Преобразует все значения в строке в верхний регистр. Пример: upper( ‘foo’) вернет строку ‘FOO’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.
lower() Преобразует все значения в строке в нижний регистр. Пример: upper( ‘Bar’) вернет строку ‘bar’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.

Арифметические операторы

Оператор Описание
+ Сложение нескольких числовых значений
Разница между числовыми значениями
/ Деление числовых значений
* Умножение числовых значений

Для решения нашей задачи потребуется использовать скалярную функцию dayOfWeek для вычисления дня недели, а также арифметический оператор «/» для подсчета показателя отказов.

Давайте определим поля, которые будем использовать в запросе:

  1. Для вычисления дня недели нам потребуется данные поля Date в столбце A.
  2. Данные о типах устройств хранятся в поле Device category в столбце E.
  3. Для расчета показателя отказов потребуются данные полей Bounces и Sessions — в столбцах H и G.

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

Именно так будет выглядеть описание нужных нам полей. Теперь с помощью кляузы Group by сгруппируем строки по дням недели. Для этого допишем в запрос следующую строку:

Уже видно, как меняется показатель отказов в зависимости от дня недели:

Чтобы построить перекрестную таблицу, достаточно прописать кляузу Pivot с указанием столбца, значения из которого будут добавлены в виде столбцов. В нашем случае это столбец E, потому что именно он содержит информацию о типе устройств. Дописываем строку:

Теперь запрос возвращает результат:

Нам остается только добавить последние штрихи: изменить названия столбцов и формат чисел с помощью пунктов LABEL и FORMAT.

Окончательная формула на листе Level_4:

Строкой LABEL sum(H)/sum(G) ‘’ мы убрали из подписей столбца надпись, содержащую формулу расчета. Строкой FORMAT sum(H)/sum(G) ‘0.00%’ мы передали процентный формат показателю отказов в отчете. В целом, описанного в примерах выше синтаксиса вполне достаточно, чтобы начать активно использовать функцию QUERY, но в завершении статьи хочу показать еще несколько интересных приемов, которые можно взять на вооружение.

Импорт данных с помощью QUERY из другой Google Таблицы

С помощью QUERY вы можете использовать в качестве базы данных другую Google Таблицу. Это можно сделать с помощью сочетания функций ImportRange и QUERY. Я создал новую Google Таблицу, в которую продублировал данные с листа DB из приведенного в начале статьи документа. Чтобы в качестве базы данных использовать данные из другой Google таблицы, в качестве первого аргумента функции Query выступит импортируемый функцией ImportRange диапазон.

Разница в том, что при написании запроса к данным, импортируемым функцией ImportRange, вместо названия столбцов мы указываем их порядковый номер в возвращаемом функцией ImportRange диапазоне. На листе DataImport перепишем запрос, представленный в Level_4 таким образом, чтобы он обращался к данным, находящимся в новой таблице на листе DB_Transfer. Синтаксис функции ImportRange достаточно прост:

Где ключ — часть URL Google Таблицы:

А диапазон — это ссылка на лист и (простите за каламбур) диапазон. В нашем случае диапазоном будет DB_Transfer!A1:L1143. Формула ImportRange:

Именно ее мы должны указать в качестве данных функции Query. Далее остается переписать запрос так, чтобы ссылаться на столбцы базы данных не по названию, а по порядковому номеру столбца. Определим, к каким столбцам мы обращались с помощью запроса на листе Level_4.

Название Содержание Наименование в таблице Порядковый номер
Date Дата A 1
Device type Тип устройства E 5
Sessions Количество сеансов G 7
Bounces Количество отказов H 8

Текст запроса после замены названий столбцов на их порядковый номер:

Как видите, текст запроса практически не изменился, но вместо столбца A мы теперь указываем Col1, вместо столбца E — Col5, вместо G — Col7 и вместо H, соответственно, Col8. Получаем формулу:

В качестве первого аргумента функции Query выступает функция ImportRange с ссылкой на ключ нужной Google таблицы, которую вы можете скопировать из URL Google Таблицы, и ссылки на диапазон, включающий название листа, а также первой и последней ячейки нужного диапазона.

Номера столбцов в запросе идут не со столбца A, а с того, который является первым в указанном вами диапазоне в функции ImportRange. Например, если бы в качестве импортируемого диапазона выступал DB_Transfer!C1:L1143, то данные из столбца C запрашивались ссылкой Col1, поскольку в импортируемом массиве этот столбец — первый.

Окончательную формулу в работе вы можете посмотреть на листе DataImport.

Строим запрос на основе объединения данных из нескольких таблиц с одинаковой структурой

Ещё одна довольно мощная возможность функции QUERY — построение запрос на основе нескольких массивов данных.

Единственное условие для объединения данных — одинаковая структура входящих таблиц.

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

Массив — это виртуальная таблица, которая содержит строки и столбцы.

Массив всегда описывается внутри фигурных скобок, при этом необходимо соблюдать следующую пунктуацию:

  • обратная косая черта « \ » — разделяет столбцы. Например, <1 \ a>. Число 1 будет находится в правой левой ячейке массива, буква «A» в ячейке справа. Так мы описали диапазон, содержащий два столбца и одну строку.
  • точка с запятой «;» используется для перехода на следующую строку. Возьмем <1;A>. Этот массив будет состоять из одного столбца и двух строк, в первой строке будет содержаться значение 1, во второй строке буква «A».

Таким образом вы можете два и более диапазона описать в одном массиве, например:

В данном случае мы обращаемся с запросом к трём диапазонам данных, находящимся на разных листах, объединив их с помощью «;» в массив так, что вторая таблица становится продолжением первой, а третья таблица — продолжением второй.

Посмотрите этот пример по ссылке.

Запрос с динамическими параметрами

Синтаксис запросов в функции QUERY сложен для неподготовленного пользователя. Поэтому вы можете добавить на рабочий лист различные интерактивные элементы в виде выпадающего списка, созданного с помощью функции «Проверка данных».

А в тексте запроса — делать ссылки на ячейки, содержащие нужные данные. Например, мы можем динамически задать диапазон дат, который хотим вывести в динамическую таблицу, либо сделать возможность динамически добавлять и убирать различные поля результирующей таблицы. Посмотреть, как это выглядит наглядно, вы можете на листе DinamicQuery.

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

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

Укажите интересующий вас интервал дат в пределах от 24.09.2015 по 25.10.2015, поскольку данные, сгенерированные для тестовой базы и хранящиеся на листе DB, содержат только этот диапазон.

Далее в конструкторе отчетов вы можете изменить название полей и оно будет отображаться в финальной таблице. Также можете указать, какие поля требуется вывести в отчет. Еще раз напомню, что необходимо указать как минимум одну меру и одно измерение.

Во время изменения каких-либо параметров отчет под конструктором будет изменяться динамически.

Формула, которая изменяет запрос в зависимости от настроенных параметров, выглядит так:

На самом деле функция Query может разрастаться до размеров программы и динамически изменять выводимую информацию в зависимости от указанных на рабочем листе данных.

Надеюсь, у меня получилось объяснить, как пользоваться одной из наиболее сложных и в тоже время полезных функций Google Таблиц.

Готов отвечать на вопросы в комментариях :)

Standard SQL в Google BigQuery: преимущества и примеры использования в маркетинге

Получайте новые статьи на эл. почту

В 2020 году в Google BigQuery появился новый способ общения с таблицами — Standard SQL. До этого времени у сервиса была собственная версия языка структурированных запросов — BigQuery SQL, которая сейчас называется Legacy.

На первый взгляд, между Legacy и Standard SQL нет большой разницы: немного по-другому пишутся названия таблиц, у стандарта чуть жестче требования к грамматике (например, нельзя ставить запятую перед FROM) и больше типов данных. Но если присмотреться, за небольшими отличиями стоят изменения синтаксиса, которые дают маркетологам много преимуществ.

В этой статье вы узнаете:

В чем преимущества Standard SQL перед Legacy SQL

Новые типы данных — массивы и вложенные поля

Стандартный SQL поддерживает новые типы данных — ARRAY и STRUCT (массивы и вложенные поля). Это означает, что в BigQuery стало проще работать с таблицами, загружаемыми из файлов JSON/Avro, данные в которых часто содержат многоуровневые вложения.

Вложенное поле — это мини-таблица внутри большой:

На приведенной схеме голубые и розовые полосы — это строки, в которые вложены мини-таблички. Каждая строка — это одна сессия. У сессии есть общие параметры: дата, уникальный номер, категория устройства пользователя, браузер, операционная система и т.д. Кроме общих параметров для каждой сессии, в строку вложена табличка hits.

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

Если пользователь в течение визита сделал заказ на сайте, в поле hits запишутся характеристики заказа:

  • transactionId — номер.
  • transactionRevenue — доход.
  • transactionShipping — стоимость доставки и др.

Допустим, вы хотите узнать количество заказов от пользователей из Нью-Йорка за последний месяц. Для этого вам нужно посчитать количество уникальных transactionId, обратившись к полю hits. Чтобы добыть данные из таких полей, в Standard SQL есть функция UNNEST:

Если бы параметры заказов записывались не во вложенное поле, а в отдельную табличку, вам пришлось бы с помощью JOIN объединить таблицы о заказах с данными о сессиях, чтобы узнать, в каких сессиях были сделаны заказы.

Больше вариантов подзапросов

Если у вас многоуровневые вложенные поля, чтобы извлечь из них данные, вы можете добавлять подзапросы в SELECT и WHERE. К примеру, в таблицах сессионного стриминга OWOX BI в подтаблицу hits записывается еще одна подтаблица — product. В ней собираются данные о продукте, которые передаются с массивом Enhanced Ecommerce. Если на сайте настроена расширенная электронная торговля и пользователь посмотрел карточку товара, в подтаблицу product запишутся характеристики этого товара.

Чтобы получить характеристики товара, вам понадобится подзапрос внутри основного запроса. Для каждой характеристики товара создается отдельный подзапрос SELECT в круглых скобках:

Благодаря возможностям стандартного SQL проще выстроить логику запроса и написать код. Для сравнения — на Legacy SQL можно писать только вот такую «лесенку»:

Запросы к внешним источникам

С помощью Standard SQL можно обращаться из BigQuery напрямую к таблицам Google Bigtable, Google Cloud Storage, Google Drive и Google Sheets.

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

Больше пользовательских функций (UDF)

Если вам нужно использовать формулу, которой нет в документации, вам помогут пользовательские функции (User Defined Functions). В нашей практике это очень редкий случай, поскольку документация Standard SQL покрывает почти все задачи digital-аналитики.

В стандартном диалекте пользовательские функции можно писать на SQL или JavaScript, а в Legacy поддерживается только JavaScript. В качестве аргументов функции используются колонки, а значения, которые она принимает — это результаты действий с колонками. На стандартном диалекте функции можно писать в том же окошке, что и запросы.

Больше условий в JOIN

В Legacy SQL в качестве условий JOIN можно задавать равенство или название колонок. Стандартный диалект поддерживает также JOIN по неравенству и по произвольному выражению.

Например, для выявления недобросовестных CPA-партнеров мы выбираем сессии, в которых была подмена источника в течение 60 секунд перед транзакцией. Для этого на стандартном диалекте можно добавить неравенство в условие JOIN:

Единственное ограничение стандартного диалекта в отношении JOIN в том, что он не разрешает «половинчатые джойны» (semi-join) с подзапросами вида WHERE column IN (SELECT. ):

Меньше шансов ошибиться

Некоторые функции в Legacy SQL возвращают NULL, если условие задано неверно. Например, если в ваши вычисления закралось деление на ноль, запрос выполнится, а в результирующих строках таблицы появятся записи NULL. Это может маскировать проблемы в запросе или в данных.

Логика стандартного SQL более прямолинейна. Если условие или входящие данные неверны, запрос выдаст ошибку, например «division by zero», и вы сможете быстро поправить текст запроса. В стандарт SQL встроены:

  • Проверка допустимых значений для +, −, ×, SUM, AVG, STDEV.
  • Проверка деления на ноль.

Запросы выполняются быстрее

Запросы с JOIN, написанные на стандартном SQL, выполняются быстрее, чем написанные на Legacy, благодаря предварительной фильтрации входящих данных. Сначала запрос выбирает строки, которые соответствуют условиям JOIN, а затем обрабатывает их.

В дальнейшем Google BigQuery будет работать над повышением скорости и производительности запросов только для Standard SQL.

Таблицы можно редактировать: вставлять и удалять строки, обновлять

Для стандартного диалекта доступны функции Data Manipulation Language (DML). Это означает, что обновлять таблицы, добавлять или удалять из них строки можно через то же окно, через которое вы пишете запросы. Например, с помощью DML можно объединить данные двух таблиц в одну так, чтобы они дополняли друг друга:

Код удобнее читать и править

Теперь сложные запросы можно начинать не только с SELECT, но и с WITH. Такой код проще читать, комментировать и понимать, что хотел сказать автор. А значит, проще предотвратить собственные и исправить чужие ошибки.

С оператором WITH удобно работать, если у вас есть вычисления, которые делаются в несколько этапов. Сначала вы собираете в подзапросах промежуточные метрики, а затем делаете финальные расчеты.

Google Cloud Platform (GCP), в которую входит BigQuery — это платформа полного цикла работы с большими данными, от организации Data Warehouse или Data Cloud до научных экспериментов, предиктивной и прескриптивной аналитики. За счет внедрения стандартного SQL BigQuery расширяет свою аудиторию. Работать с GCP становится интереснее маркетинг-аналитикам, продуктовым аналитикам, Data Scientists и другим командам.

Возможности и примеры использования Standard SQL

Мы в OWOX BI часто работаем с таблицами, собранными при помощи стандартного экспорта Google Analytics 360 в Google BigQuery или через OWOX BI Pipeline. Поэтому в примерах ниже мы рассмотрим особенности SQL-запросов именно к таким данным.

Если вы еще не собираете данные с сайта в BigQuery, вы можете попробовать это бесплатно в trial-версии от OWOX BI.

1. Выбрать данные за интервал времени

В Google BigQuery данные о поведении пользователей на сайте хранятся в wildcard tables (таблицах со звездочкой): за каждый день формируется отдельная таблица. Таблицы по дням называются одинаково — отличается только суффикс в имени. В суффикс записывается дата в формате ГГГГММДД. Например, в таблице owoxbi_sessions_20200301 лежат данные о сессиях за 1 марта 2020 года.

Мы можем в одном запросе обратиться сразу к группе таких таблиц, чтобы получить данные, к примеру, с 1 по 28 февраля 2020 года. Для этого в FROM нужно указать вместо ГГГГММДД значок *, а в WHERE — назвать, какие табличные суффиксы должны войти в интервал времени:

Не всегда конкретные даты, за которые мы хотим собрать данные, нам известны. Например, каждую неделю вам нужно анализировать данные за последние три месяца. Для этого воспользуемся функцией FORMAT_DATE:

После BETWEEN записан первый табличный суффикс. Формулировка CURRENT_DATE(), INTERVAL 3 MONTHS означает «выбери данные за последние 3 месяца от текущей даты». Второй табличный суффикс записан после AND. Он нужен, чтобы обозначить окончание интервала — вчерашний день: CURRENT_DATE(), INTERVAL 1 DAY.

2. Извлечь пользовательские параметры и показатели

Пользовательские параметры и показатели в таблицах Google Analytics Export записываются во вложенное поле hits, в подтаблицы сustomDimensions и customMetrics. Все custom dimensions записываются в две колонки: в одной — номера параметров, которые собираются на сайте, во второй — их значения. Вот как выглядят все параметры, которые передались с одним хитом:

Чтобы их «распаковать» и записать нужные параметры в отдельные колонки, мы пользуемся следующими формулировками в SQL-запросе:

Вот как это выглядит в запросе:

На скриншоте ниже мы выбрали параметры № 1 и № 2 из демо-данных экспорта Google Analytics 360 в Google BigQuery, назвали их page_type и client_id. Каждый параметр записался в отдельную колонку:

3. Посчитать количество сессий в разрезе источника трафика, канала, кампании, города и категории устройства

Такие расчеты полезны, если вы планируете визуализировать данные в Google Data Studio и пользоваться фильтрами по городам и категориям устройств. Это очень просто сделать с оконной функцией COUNT:

4. Объединить одинаковые данные из нескольких таблиц

Предположим, вы собираете данные о выполненных заказах в нескольких таблицах BigQuery: в одной собираются все заказы из магазина А, в другой — из магазина B. Вы хотите сделать из них одну таблицу с такими колонками:

  • client_id — идентификатор покупателя, который сделал заказ.
  • transaction_created — время создания заказа в формате TIMESTAMP.
  • transaction_id — номер заказа.
  • is_approved — подтвержден ли заказ.
  • transaction_revenue — сумма заказа.

В таблицу должны попасть заказы с 1 января 2020 по вчерашний день. Для этого из каждой группы таблиц нужно выбрать подходящие колонки, присвоить им одинаковые имена и объединить результаты с помощью UNION ALL:

5. Создать словарь групп каналов трафика

Когда данные попадают в Google Analytics, система автоматически определяет группу, к которой относится тот или иной переход: Direct (прямой трафик), Organic Search, Paid Search и так далее. Для определения группы каналов Google Analytics «смотрит» на utm-метки переходов, а именно utm_source и utm_medium. Подробнее о группах каналов и о правилах определения, можно почитать в справке Google Analytics.

Если клиенты OWOX BI хотят присвоить собственные названия группам каналов, мы создаем для него словарь, куда какой переход относится. Для этого используем условный оператор CASE и функцию REGEXP_CONTAINS. Эта функция выбирает значения, в которых встречается заданное регулярное выражение.

Пример, как добавить такие условия в тело запроса (названия рекомендуем брать из вашего списка источников в GA):

Как перейти на Standard SQL

Если вы еще не перешли на стандартный SQL, то можете сделать это в любой момент. Главное — не смешивать диалекты в одном запросе.

Вариант 1. Переключатель в интерфейсе Google BigQuery

В старом интерфейсе BigQuery по умолчанию используется Legacy SQL. Для переключения между диалектами нажмите «Show Options» под полем ввода запроса и снимите галочку «Use Legacy SQL» возле пункта «SQL Dialect»:

В новом интерфейсе по умолчанию используется Standard SQL. Здесь переключение диалектов находится во вкладке «More»:

Вариант 2. Написать префикс в начале запроса

Если вы не поставили галочку в настройках запроса, можно начать его с нужного префикса (#standardSQL или #legacySQL):

В этом случае Google BigQuery проигнорирует настройки в интерфейсе и запустит запрос так, как вы написали в префиксе.

Если у вас есть представления или сохраненные запросы, которые запускаются с помощью Apps Script по расписанию, не забудьте поменять в скрипте значение useLegacySql на false:

Вариант 3. Переход на Standard SQL для представлений (Views)

Если вы работаете в Google BigQuery не с таблицами, а с представлениями, к ним нельзя обращаться на разных диалектах. То есть, если ваше представление написано на Legacy SQL, к нему нельзя писать запросы на Standard.

Чтобы перевести представление на стандартный SQL, нужно вручную переписать запрос, которым оно создано. Проще всего это сделать через интерфейс BigQuery.

1. Откройте нужное представление:

2. Нажмите «Details». Когда откроется текст запроса, внизу появится кнопка «Edit Query»:

Теперь запрос можно редактировать по правилам стандартного диалекта. Если вы планируете дальше пользоваться им как представлением, после того, как закончите редактировать, нажмите «Save View».

Совместимость, особенности синтаксиса, операторы, функции

Совместимость

Благодаря внедрению стандартного SQL, непосредственно из BigQuery можно обращаться к данным, которые хранятся в других сервисах:

  • Файлам логов в Google Cloud Storage.
  • Транзакционным записям в Google Bigtable.
  • Другим источникам.

Это позволяет использовать продукты Google Cloud Platform для любых аналитических задач, в том числе для предиктивной и прескриптивной аналитики на базе алгоритмов машинного обучения.

Синтаксис запроса

Структура запроса в стандартном диалекте — почти такая же, как и в Legacy:

Названия таблиц и View пишутся через точку и отбиваются обратным машинописным апострофом: `название_проекта.название_набора данных.название_таблицы`. Например: `bigquery-public-data.samples.natality`.

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

Особенности синтаксиса стандартного SQL:

  • Запятые нужны для перечисления полей в операторе SELECT.
  • Если вы используете оператор UNNEST после FROM , перед UNNEST ставится запятая или JOIN.
  • Нельзя ставить запятую перед FROM.
  • Запятая между двумя запросами приравнивается к CROSS JOIN, поэтому будьте с ней осторожны.
  • JOIN можно делать не только по колонкам или равенствам, но и по произвольным выражениям и неравенствам.
  • Можно писать сложные подзапросы в любой части SQL-выражения (в SELECT, FROM, WHERE). На практике пока что нельзя использовать выражения вида WHERE column_name IN (SELECT. ), как это работает в других базах данных.

Операторы

В стандартном SQL операторы определяют тип данных. Например, массив всегда записывается в стандартных скобках [ ]. Операторы используются для сравнения, поиска соответствий логическому выражению (NOT, OR, AND) и арифметических вычислений.

Функции

Standard SQL поддерживает больше функций, чем Legacy — от традиционных функций агрегирования (сумма, количество, минимум, максимум), математических, строковых и статистических функций до редких форматов — например, HyperLogLog++.

В стандартном диалекте больше функций для работы с датами и TIMESTAMP. Полный список функций приведен в справке Google. Чаще всего используются функции для работы с датами, строками, функции агрегирования и оконные.

1. Функции агрегирования

COUNT(DISTINCT column_name) считает количество уникальных значений в столбце. Например, нам нужно посчитать количество сессий, сделанных с мобильных устройств, за 1 марта 2020 года. Поскольку номер сессии может повторяться в разных строках, мы хотим посчитать только уникальные значения номера сессии:

SUM (column_name) — сумма значений в колонке:

MIN (column_name) | MAX (column_name) — минимальное и максимальное значение в колонке. Эти функции очень удобны для проверки разброса данных в таблице.

2. Оконные (аналитические) функции

Аналитические функции считают значения не по всей таблице, а по определенному «окну» — набору строк, который вам интересен. То есть, сделать внутри таблицы сегменты. Например, вы можете посчитать доход SUM (Revenue) не по всем строкам, а по городам, категориям устройств и так далее. Вы можете превратить в аналитическую функцию SUM, COUNT, AVG и другие функции агрегирования, если добавите к ним условие OVER (PARTITION BY column_name).

К примеру, вам нужно посчитать количество сессий в разрезе источников трафика, каналов и кампаний, городов и категорий устройств. В таком случае используйте выражение:

OVER определяет окно, в котором будут производиться вычисления. PARTITION BY указывает, какие строки нужно сгруппировать для расчета. В некоторых функциях обязательно нужно указывать порядок группировки — ORDER BY.

Полный список оконных функций вы найдете в документации BigQuery.

3. Функции работы со строками

Они полезны, когда вам нужно изменить, отформатировать текст в строке, склеить значения колонок. Например, если вы хотите сформировать уникальный идентификатор сессии из данных стандартного экспорта Google Analytics 360. Рассмотрим самые популярные из них.

SUBSTR вырезает часть строки. В запросе эта функция записывается так: SUBSTR(string_name, 0,4). Первое число обозначает, сколько символов нужно пропустить с начала строки, а второе — сколько цифр вырезать. Например, у вас есть столбец date, куда записываются даты в формате STRING. При этом даты выглядят так: 20200103. Если вы хотите извлечь из этой строки год, вам поможет SUBSTR:

CONCAT (column_name, etc.) — склейка значений. Воспользуемся столбцом date из предыдущего примера. Предположим, вы хотите, чтобы все даты записывались так: 2020-03-01. Для этого используются две строковые функции: сначала вы вырезаете нужные куски строки с помощью SUBSTR, а затем склеиваете их через черточку:

REGEXP_CONTAINS возвращает те значения столбцов, в которых встречается регулярное выражение:

Эту функцию можно использовать и в SELECT, и в WHERE. Например, в WHERE по ней можно выбрать конкретные страницы:

4. Функции работы с датами

Часто даты в таблицах записываются в формате STRING (строка). Если вы планируете визуализировать результаты в Google Data Studio, даты в таблице нужно перевести в формат DATE c помощью функции PARSE_DATE.

PARSE_DATE преобразует строку (STRING) вида 1900-01-01 в формат даты (DATE).
Если в ваших таблицах даты выглядят по-другому (например, 19000101 или 01_01_1900), сначала нужно привести их к указанному виду.

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

SQL-запросы для маркетинговых отчетов

Стандартный диалект позволяет бизнесу извлечь максимум информации из данных — от глубокой сегментации, технического аудита, анализа KPI маркетинга до выявления недобросовестных подрядчиков в CPA-сетях. Вот примеры бизнес-задач, в которых вам помогут SQL-запросы к данным, собранным в Google BigQuery.

1. ROPO-анализ: оцените вклад онлайн-кампаний в офлайн-продажи. Для этого нужно объединить данные о поведении пользователей в онлайне с данными из CRM, системы колл-трекинга, мобильного приложения.

Если в одной и второй базе есть ключ — общий параметр, который уникален для каждого пользователя (например, User ID), вы можете:

  • Проследить, какие пользователи заходили на сайт перед тем, как купить товар в магазине.
  • Как они вели себя на сайте.
  • Как долго принимали решение о покупке.
  • Какие кампании дают наибольший прирост офлайн-покупок.

2. Сделайте сегментацию клиентов по любому сочетанию параметров — от особенностей поведения на сайте (на какие страницы заходили, какие товары смотрели, сколько раз посещали сайт до покупки и т. д.) до номера карты лояльности, купленных товаров и т.д.

Мы подготовили подборку запросов на стандартном диалекте SQL. Если вы уже собираете в Google BigQuery данные с вашего сайта, из рекламных источников и CRM-системы, то сможете использовать эти шаблоны для решения своих бизнес-задач. Просто замените в запросе название проекта, набора данных и таблицы в BigQuery на свои. В подборке вы получите 11 SQL-запросов.

Запросы к данным, собранным с помощью стандартного экспорта из Google Analytics 360 в Google BigQuery:

  • Действия пользователей в разрезе любых параметров.
  • Статистика по ключевым действиям пользователей.
  • Выбор пользователей, которые просмотрели определенные страницы товаров.
  • Действия пользователей, которые купили определенный товар.
  • Настройка воронки с любыми необходимыми шагами.
  • Эффективность работы внутреннего поиска на сайте.

Запросы к данным, собранным в Google BigQuery с помощью OWOX BI:

  • Как изменялся атрибутированный расход по источникам и каналам.
  • Как средняя стоимость привлечения посетителя зависела от города.
  • Как ROAS по валовой прибыли зависел от источника и канала.
  • Как количество заказов в CRM зависело от способа оплаты и способа доставки.
  • Как среднее время доставки зависело от города.

Если у вас есть вопросы к данным Google BigQuery, ответов на которые вы не нашли в наших кейсах, напишите об этом в комментариях. Мы постараемся вам помочь.

query

to raise a query about smth. — задать вопрос о чём-л.

to answer a query — отвечать на вопрос

We have a number of queries regarding delivery. — У нас есть несколько вопросов по доставке.

There was a query as to who actually owned the hotel. — Возникло сомнение относительно того, кто же на самом деле был хозяином отеля.

Put a query against Jack’s name — I’m not sure if he’s coming. — Поставь вопросительный знак напротив имени Джека — я не уверен, что он придёт.

to query smb. about smb./ smth. — расспрашивать кого-л. о ком-л. / чём-л.

I must query your suggestion with the director, it doesn’t seem completely suitable. — Я должен проконсультироваться насчёт вашего предложения с директором — мне кажется, что оно не вполне приемлемо.

Англо-русский современный словарь . 2014 .

Смотреть что такое «query» в других словарях:

Query — Que ry, v. t. [imp. & p. p. ; p. pr. & vb. n. .] 1. To put questions about; to elicit by questioning; to inquire into; as, to query the items or the amount; to query the motive or the fact. [1913 Webster] 2. To address… … The Collaborative International Dictionary of English

Query — Que ry, v. i. 1. To ask questions; to make inquiry. [1913 Webster] Each prompt to query, answer, and debate. Pope. [1913 Webster] 2. To have a doubt; as, I query if he is right. [1913 Webster] … The Collaborative International Dictionary of English

query — index challenge, check (inspect), cross examination, cross examine, cross questioning, dispute (contest) … Law dictionary

query — ► NOUN (pl. queries) 1) a question, especially one expressing doubt. 2) chiefly Printing a question mark. ► VERB (queries, queried) 1) ask a query. 2) N. Amer. put a query or queries to … English terms dictionary

Query — Que ry, n.; pl. . [L. quaere, imperative sing. of quaerere, quaesitum to seek or search for, to ask, inquire. Cf. , , , , .] 1. A question; an inquiry to be answered or solved. [1913 Webster]… … The Collaborative International Dictionary of English

Quéry — Nom surtout porté dans la Somme et dans l Aisne. On le trouve aujourd hui aussi au Canada et en Martinique. On connaît en Bretagne la variante Quérie (56). En Picardie, il s agit de celui qui est originaire de la commune de Quiéry (62). Dans le… … Noms de famille

query — [n] demand for answers concern, doubt, dubiety, inquiry, interrogation, interrogatory, mistrust, objection, problem, question, questioning, reservation, skepticism, suspicion, uncertainty; concepts 21,48,53,662 Ant. answer, reply query [v] ask… … New thesaurus

query — [kwir′ē, kwer′ē] n. pl. queries [< L quaere, 2d pers. sing., imper., of quaerere, to ask, inquire] 1. a question; inquiry 2. a doubt 3. a question mark (?) placed after a question or used to question the accuracy of written or printed matter… … English World dictionary

query — (v.) 1530s, quaere, from L. quaere ask, imperative of quaerere to seek, gain, ask, probably ultimately from PIE *kwo , root forming the stem of relative and interrogative pronouns. Spelling altered c.1600 by influence of inquiry. The noun in the… … Etymology dictionary

query — /ˈkweri, ingl. ˈkwɪərɪ/ [vc. ingl., dall imperat. lat. quaere «chiedi»] s. f. inv. (elab.) interrogazione … Sinonimi e Contrari. Terza edizione

query — vb *ask, question, interrogate, inquire, examine, quiz, catechize … New Dictionary of Synonyms

Что такое код ifx_query

ifx_query — Send Informix query

Description

int ifx_query (string query [, int link_identifier [, int cursor_type [, mixed blobidarray]]])

Returns: A positive Informix result identifier on success, or false on error.

A «result_ >ifx_affected_rows() function.

ifx_query() sends a query to the currently active database on the server that’s associated with the specified link >ifx_connect() was called, and use it.

Executes query on connection conn_id . For «select-type» queries a cursor is declared and opened. The optional cursor_type parameter allows you to make this a «scroll» and/or «hold» cursor. It’s a bitmask and can be either IFX_SCROLL, IFX_HOLD, or both or’ed together. Non-select queries are «execute immediate». IFX_SCROLL and IFX_HOLD are symbolic constants and as such shouldn’t be between quotes. I you omit this parameter the cursor is a normal sequential cursor.

For either query type the number of (estimated or real) affected rows is saved for retrieval by ifx_affected_rows() .

If you have BLOB (BYTE or TEXT) columns in an update query, you can add a blobidarray parameter containing the corresponding «blob ids», and you should replace those columns with a «?» in the query text.

If the contents of the TEXT (or BYTE) column allow it, you can also use «ifx_textasvarchar(1)» and «ifx_byteasvarchar(1)». This allows you to treat TEXT (or BYTE) columns just as if they were ordinary (but long) VARCHAR columns for select queries, and you don’t need to bother with blob id’s.

With ifx_textasvarchar(0) or ifx_byteasvarchar(0) (the default situation), select queries will return BLOB columns as blob id’s (integer value). You can get the value of the blob as a string or file with the blob functions (see below).

Example 1. Show all rows of the «orders» table as a html table

ifx_textasvarchar(1); // use «text mode» for blobs $res_ Can’t select orders : %s\n
%s
\n», ifx_error()); ifx_errormsg(); die; > ifx_htmltbl_result($res_ ); ifx_free_result($res_id);

Example 2. Insert some values into the «catalog» table

Что такое код ifx_query

(PHP 3>= 3.0.3, PHP 4 )

ifx_query — Send Informix query

Description int ifx_query ( string query, int link_identifier [, int cursor_type [, mixed blobidarray]])

Returns: A positive Informix result > FALSE on error.

A «result_ >ifx_affected_rows() function.

ifx_query() sends a query to the currently active database on the server that’s associated with the specified link identifier.

Executes query on connection conn_id . For «select-type» queries a cursor is declared and opened. The optional cursor_type parameter allows you to make this a «scroll» and/or «hold» cursor. It’s a bitmask and can be either IFX_SCROLL, IFX_HOLD, or both or’ed together. Non-select queries are «execute immediate». IFX_SCROLL and IFX_HOLD are symbolic constants and as such shouldn’t be between quotes. I you omit this parameter the cursor is a normal sequential cursor.

For either query type the number of (estimated or real) affected rows is saved for retrieval by ifx_affected_rows() .

If you have BLOB (BYTE or TEXT) columns in an update query, you can add a blobidarray parameter containing the corresponding «blob ids», and you should replace those columns with a «?» in the query text.

If the contents of the TEXT (or BYTE) column allow it, you can also use «ifx_textasvarchar(1)» and «ifx_byteasvarchar(1)». This allows you to treat TEXT (or BYTE) columns just as if they were ordinary (but long) VARCHAR columns for select queries, and you don’t need to bother with blob id’s.

With ifx_textasvarchar(0) or ifx_byteasvarchar(0) (the default situation), select queries will return BLOB columns as blob id’s (integer value). You can get the value of the blob as a string or file with the blob functions (see below).

Example 1. Show all rows of the «orders» table as a html table

ifx_textasvarchar(1); // use «text mode» for blobs $res_ Can’t select orders : %s\n
%s
\n», ifx_error()); ifx_errormsg(); die; > ifx_htmltbl_result($res_ ); ifx_free_result($res_id);

Example 2. Insert some values into the «catalog» table

Илон Маск рекомендует:  Faq как узнать местоположение специальных папок у windows
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL