Молот данных StatSoft
1 Молоток данных StatSoft Работа с данными является одним из основных видов деятельности каждого аналитика. Введем в STATISTICA 12 новый инструмент для извлечения данных из внешних источников данных, которые обычно представлены в виде систем баз данных. К возможности загрузки данных из баз данных в среду STATISTICA мы привыкли с первых ее версий. Для этого используется инструмент STATISTICA Query, который доступен в программе и сейчас, однако с течением времени этот инструмент может перестать подходить некоторым пользователям. А для вас теперь есть альтернатива в виде нового инструмента Advanced Query Builder (AQB). Что он предлагает и чем он лучше существующего STATISTICA Query? Интерактивность, шепот. Рабочую среду нового инструмента можно разделить на две основные части, одна служит для визуального отображения нашего запроса в виде табличных объектов, их взаимосвязей, ограничений или функций агрегации. Возможность создавать запросы в интерактивном режиме, в удобном графическом интерфейсе, особенно оценят пользователи, не знакомые с синтаксисом языка SQL. Большинство запросов можно щелкнуть мышью, включая вложенные запросы и операции с наборами данных. Но даже те, кто предпочитает письменное изложение вопросов, не проиграют. Вторая часть среды AQB, редактор текстовых запросов, снабжена популярным шептальщиком, который предлагает подходящие выражения для быстрой вставки при написании запроса и может значительно ускорить работу по построению запроса. Все, что мы напишем текстом в этой части, отразится в визуальной части и наоборот. Таким образом, вы можете комбинировать оба метода построения запроса, где мы можем создать часть запроса с помощью мыши, а остальную часть настроить непосредственно в текстовой форме запроса. AQB поддерживает синтаксис большинства известных систем баз данных, таких какMS SQL Server, MySQL, Firebird, Oracle или Postgres. Также доступны стандарты ANSI SQL-89, ANSI SQL-92 и ANSI SQL.По умолчанию установлен универсальный диалект SQL, но он может быть несовместим с некоторыми базами данных, но его можно изменить.
2 Описание среды нового расширенного построителя запросов Как уже указывалось в предыдущем тексте, среда AQB состоит из двух основных частей: части для визуального построения запроса и части для записи запроса в тексте. Они дополняются боковыми разделами слева для отображения дерева структуры запроса и справа для отображения объектов базы данных, как на следующем изображении: Опишем подробные опции каждой части по очереди, желательно на примере. Поэтому давайте практически покажем работу в этом инструменте. Мы будем работать с таблицами STATISTICA, к которым можно создавать запросы, аналогичные таблицам в базе данных. Таблицы STATISTICA можно скачать здесь. Подключение к источнику данных Модуль AQB можно вызвать только из меню, когда отображается лента. В обновленной версии он также будет доступен из классического меню. Но вернемся к ленте. Выбираем вкладку Файл -> Внешние данные -> раздел Расширенный конструктор запросов -> Создавать. Откроется окно модуля AQB. В нем выбираем в верхнем меню Действие -> Соединять. Откроется диалоговое окно Свойства связи с данными для создания соединения с базой данных. Это компонент Windows, который в фоновом режиме создает для нас строку подключения к базе данных. Немного неудобно то, что, в отличие от оригинального инструмента STATISTICA Query, результирующую строку подключения нельзя использовать повторно. Для нового подключения диалоговое окно Data Link Properties должно быть установлено снова.
3 Просто для пояснения в инструменте STATISTICA Query, т.е. в меню Файл -> Внешние данные -> раздел Запрос -> Новые, мы увидим список ранее определенныхсоединение, которое можно использовать немедленно, выбрав соединение и подтвердив кнопку OK: Затем мы вызываем диалоговое окно Data link Properties с помощью кнопки New. После настройки и подтверждения диалога Data link, назовите соединение, отредактируйте строку подключения вручную, если необходимо, и сохраните, подтвердив OK: В обновленной версии STATISTICA планируется также включить диалог уже созданных соединений для AQB . Вернемся к настройке диалогового окна Data link Properties. Во вкладке Посредник выбираем соответствующий драйвер базы данных, к которой мы подключаемся. В нашем случае мы выберем StatSoft OLE DB Provider для электронных таблиц STATISTICA. Это драйвер для таблиц STATISTICA. Переходим на следующую вкладку Connections (или кнопкой Next). Элементы на этой вкладке могут различаться в зависимости от выбранного драйвера. Обычно заполняется имя базы данных (источника данных) и информация для входа. В нашем случае мы выбираем нужный файл, либо всю директорию. Если выбран весь каталог, программа сделает доступными все подходящие таблицы, расположенные в этом каталоге. Две другие вкладки не нужно устанавливать, поэтому мы подтверждаем диалоговое окно OK, и, таким образом, мы должны быть подключены к источнику и иметь возможность создавать запросы. Создание запросов Прежде чем мы перейдем к самой важной части создания запросов, следует отметить, что инструмент AQB поможет нам с построением запросов, но не сделает все за нас. Ожидается, что пользователь имеет хотя бы базовые знания синтаксиса SQL. Перво-наперво,
4, что мы должны сделать, это выбрать соответствующий диалект синтаксиса. Синтаксис языка SQL не одинаков для всех систем баз данных, и вам нужно подумать об этом. Выбор диалекта синтаксиса в AQB уместен по двум причинам. Мы будем уверены, что парсер будет обрабатывать только функции, поддерживаемые базой данных и при этом эти функции будут предлагаться шеппером и их список также будет доступен вредактор выражений. Мы вернемся к этому позже. Делаем выбор в верхнем меню Свойства-> Диалект SQL. Для таблиц STATISTICA мы выберем стандарт ANSI SQL-92. Перед построением самого запроса опишем опции, расположенные внизу окна для визуального построения запроса. Эти параметры отражают, какие объекты используются в запросе: Вывод Если флажок установлен, набор данных (столбец) будет отображаться в результатах запроса. Это только отображение этого столбца в результатах, однако, даже если элемент не отмечен, это все равно повлияет на результат всего запроса. Выражение Содержит имя объекта, представляющего набор данных, обычно имя столбца, но также может содержать другой запрос, суммы столбцов и т. д. Щелкнув правой кнопкой мыши по этому полю (или щелкнув значок с тремя точки), получаем меню для открытия редактора выражений (Expression editor) и вставки следующего запроса (Insert sub-query). Агрегировать Чтобы применить агрегатную функцию к связанному набору данных (столбцу). Количество доступных функций агрегирования зависит от выбранного диалекта синтаксиса SQL. Псевдоним Поле для использования альтернативного именования набора данных. Тип сортировки Поле для параметров сортировки данных в порядке убывания или возрастания. Порядок сортировки При использовании сортировки по нескольким столбцам он указывает порядок, в котором данные последовательно сортируются. Группировка При отмеченном поле используется группировка данного набора данных, т.е. опускание повторяющихся значений отдельных строк. При выборе нескольких столбцов группировка должна применяться ко всем столбцам, или мы должны использовать функцию агрегирования для выбранных столбцов. Критерии для Варианта доступны только при использовании Группировки и связаны с другими вариантами ограничивающих условий (Критериями). При выборе опции Для групп ограничительные условия применяются ко всей группе значений, когда выбрано Для значенийусловия применяются ко всем значениям набора данных. Критерии Поле для ограничивающих условий набора данных. Он также может содержать другой запрос, т.е. другой набор данных также может быть ограничивающими условиями. Нажмите левую кнопку в этом поле, чтобы сделать его доступным для редактирования, а затем правой кнопкой вызовите меню для открытия редактора выражений (Редактор выражений), вставки другого запроса (Вставить подзапрос) или опции редактирования выражения (Редактировать). Редактор выражений также можно вызвать, нажав на иконку с тремя точками в режиме редактирования этого поля. Изначально у нас есть 3 поля для ограничительных условий, однако при использовании последнего автоматически добавляется еще одно поле.
5 Диалоговое окно редактора выражений, в котором легко доступны все ключевые слова, операторы и функции для выбранного диалекта синтаксиса SQL. Он также предлагает быстрый доступ к схеме базы данных, т.е. список всех подключенных объектов базы данных. Среда редактора, аналогичная текстовой части запроса, предлагает шепот и выделенную нотацию синтаксиса SQL: Пример 1: Базовый запрос, агрегация Теперь мы покажем практический пример на нашем образце данных. Мы создадим запрос к исходным финансовым данным, который вернет итоговые данные по отдельным заказам. Для этого воспользуемся функцией агрегации данных. Итак, давайте подключимся к источнику данных через меню Действие -> Соединять. Из списков объектов в правой части выбираем таблицы DEMO_Orders (таблица заказов), DEMO_Order_Items (отдельные позиции заказа) и DEMO_Product_Info (дополнительная информация по позициям заказа). Если первичные и внешние ключи отдельных элементов правильно установлены в базе данных, таблицы также должны быть автоматически связаны и указаны отношения (символы 1, ). В нашем случае приходится связывать таблицы вручную, перетаскивая нужные поля одной таблицыв поле другой таблицы (нажимаем левую кнопку мыши и перетаскиваем курсор на вторую таблицу), с которой мы хотим связать поле. В нашем примере мы подключаем поле ORDER_ID в таблицах DEMO_Orders и DEMO_Order_Items, а поле PRODUCT_ID в таблицах DEMO_Order_Items и DEMO_Product_Info: результат (мы сейчас говорим об одном соединении). Если мы хотим изменить тип JOIN, мы щелкаем правой кнопкой мыши по соединительной линии и выбираем опцию «Свойства». Там мы можем проверить Select All rom Left/Right, что соответствует соединению LEFT/RIGHT JOIN. Мы можем заметить, что в текстовом окне запроса у нас есть запрос, соответствующий тому, что мы сделали до сих пор в визуальной части AQB:
6 Мы, конечно, можем отредактировать это обозначение, что отразится на визуальной части. Попробуем изменить список столбцов, входящих в результат запроса в нотации. После ключевого слова SELECT вместо звездочки добавляем конкретные названия таблиц и соответствующих столбцов: DEMO_Orders.ORDER_ID, DEMO_Order_Items.UNIT_PRICE, DEMO_Order_Items.QUANTITY, DEMO_Product_Info.CATEGORY Схема запроса выглядит следующим образом: Сортируем запрос результат по пункту ORDER_ID: Теперь мы можем видеть результат запроса, то есть предварительный просмотр полученных данных. В верхней части левой панели переключаемся на вкладку Данные: Теперь вернемся к запросу, переключимся на вкладку SQL. Мы улучшим наш запрос. Текущие результаты не имеют правильного формата. Попробуем отобразить суммы отдельных позиций заказа. Во-первых, вам нужно создать столбец общей цены товара, умножив цену за единицу (UNIT_PRICE) и количество (QUANTITY). В средней части AQB мы исключим элементы UNIT_PRICE и QUANTITY из таблицы DEMO_Order_Items. Мы нажимаем вместо этогодо последней пустой строки (если она недоступна, щелкните правой кнопкой мыши в любом месте списка объектов, включенных в запрос, и выберите Вставить пустой элемент), выберите поле Выражение и откройте Редактор выражений. Выбираем объекты QUANTITY и UNIT_PRICE из таблицы DEMO_Order_Items (добавляем их в окно редактора двойным кликом из левого списка объектов)
7 и звездочка между ними указывают на продукт: КОЛИЧЕСТВО * ЦЕНА_ЕД. Подтвердите диалог кнопкой OK. Мы видим, что AQB автоматически добавил имена таблиц перед именами элементов. Получившуюся колонку еще именуем — пишем Sum в колонке Alias (будьте внимательны, диакритические знаки могут вызвать проблемы) и включаем элемент в результаты (проверьте Output). Переносим столбец на второе место в результатах, щелкнув правой кнопкой мыши по нужной строке (не в режиме редактирования) и выбрав Переместить вверх: Смотрим на результат: Теперь достаточно сложить позиции по отдельным заказам. Для этого воспользуемся функцией агрегирования Sum. Мы выберем это в той же строке, т.е. в той, где у нас есть продукты для отдельных позиций. Обратите внимание, что после выбора функции Sum в столбце Aggregation группировка для других переменных выполняется автоматически. Мы упоминали об этом ранее. Функции агрегации (а значит, и группировки), если они применяются в запросе, должны применяться ко всем столбцам в запросе. Наш запрос почти завершен, но результат еще не идеален. В результатах нас смущает пункт КАТЕГОРИЯ, который не одинаков для всех позиций отдельных заказов, не всегда все позиции объединены в одну. Поэтому удаляем этот пункт из запроса — кликаем правой кнопкой мыши по нужному пункту и выбираем Удалить пункт. Результирующий запрос выглядит следующим образом:
8 Последним шагом будет передача данных запроса в среду STATISTICA. Делаем это в верхнем меню Action-> Возврат данных. Это поможет нам пройтив среду STATISTICA и установите необходимые свойства в следующем диалоговом окне: Подтвердите OK, и данные будут загружены в таблицу STATISTICA. Это можно сразу использовать, например, для расчета некоторого анализа: В этом примере мы показали, как создать простой запрос. В то же время мы показали, что можно использовать запросы к таблицам STATISTICA. Однако можно использовать только простые запросы (без вложенных запросов), а скорость обработки запросов намного ниже по сравнению с чистокровными базами данных. Пример 2: Составной запрос, слияние То, что не удалось продемонстрировать на таблицах STATISTICA, мы теперь продемонстрируем на примере файла данных в формате файла .mdb, т.е. базы данных программы Microsoft Access. Для этого требуется драйвер, установленный вместе с MS Office, или его можно скачать по этой ссылке. Снова подключаемся к файлу в AQB в меню Действие -> Подключаемся, выбираем драйвер с пометкой Microsoft Office 12.0 access database engine ole db provider, для старых версий MS Office может быть помечен как Microsoft Jet OLE DB 4.0. на вкладке Connection введите путь к файлу library.mdb (упакованному вместе с таблицами STATISTICA из предыдущего примера) в качестве Источника данных. Мы подтвердим диалог. Теперь мы подключены к базе данных. В дополнение к списку таблиц у нас также есть несколько представлений, доступных здесь: Давайте попробуем создать запрос, который создается путем слияния двух запросов. При этом необходимо соблюдать основное правило при объединении результатов нескольких запросов, мы всегда должны объединять одинаковые наборы данных, т.е. с тем же количеством столбцов, типом данных и порядком. Затем нам нужно отсортировать весь результат, а не отдельные частичные запросы. Выбираем таблицу tblknihy и в ней пункты Название книги и Автор и ограничиваем результат только книгами по автору Коллектив:
9 Условия ограничения значения для текстовых переменных чаще всего вводятся апострофами для баз данных. Теперь мы создадимеще отдельный вопрос. Щелкаем правой кнопкой мыши по значку Q, который находится в правом верхнем углу визуальной части AQB. Мы выбираем опцию подзапроса New union. Иконка теперь обозначает два вопроса, каждый из которых представляет собой отдельный запрос, и, нажимая на эти значки, мы переключаемся между запросами: Вы также можете переключаться между ними в левом дереве структуры запроса, где оба запроса указаны одновременно. уровень. Объект UNIONS над ними — это просто ключевое слово, используемое для объединения нескольких запросов в его текстовой нотации SQL. Для второго запроса мы выбираем ту же таблицу и элементы, что и для первого запроса, только мы ограничиваем автора именем Jiří Kosek: Результирующая таблица представляет собой слияние этих частичных запросов: Тогда вся текстовая запись запроса выглядит следующим образом: SELECT tblknihy.namebook, tblknihy.author FROM tblknihy, WHERE tblknihy .author = ‘kolektiv’ UNION SELECT tblknihy.namebooks, tblknihy.author FROM tblknihy, WHERE tblknihy.autor = ‘Jiří Kosek’ Частичные запросы не только должны быть унифицированы , мы можем создать пересечение или вычесть данные друг из друга. Если мы вернемся к запросу, мы можем щелкнуть правой кнопкой мыши на связи между двумя Q, где у нас есть выбор, что мы хотим делать с наборами: Union объединяет наборы с удалением повторяющихся строк Union All объединяет наборы оставляя повторяющиеся строки. Исключение (разница) возвращает строки первого запроса, которые не появляются во втором запросе. Пересечение (пересечение) возвращает только строки, общие для обоих запросов
.
10 Этот пример, конечно, можно было бы решить проще, используя два условия в одном запросе, но мы хотели продемонстрировать возможность объединения нескольких запросов. Пример 3: Вложенный запрос Наконец, мы покажем, как создать вложенный запрос. Мы выберем все книги, взятые напрокат пользователями из Нове Йичина. Опять же, мы могли бы решить этот вопрос, просто соединив таблицы,но мы пойдем более длинным путем. Мы выбрали пункты тблкних.автор (автор книги), тблкних.назевкних (название книги), тблдатумы.особничисло (личный номер). Нам не нужно включать личный номер в отображение результатов, но он должен быть в выражении: мы добавим критерий для личного номера, щелкнув левой кнопкой мыши, чтобы войти в поле «Критерии», и щелкнув правой кнопкой мыши, чтобы выбрать «Вставить суб». -запрос из вариантов. В верхней части мы добавили еще одну вкладку (Выбрать*) для нашего вложенного запроса: Мы переключаемся на эту вкладку и можем построить вложенный запрос, как и любой другой. Здесь выбираем пункты tblstudenti.osobnicislo, tblstudenti.bydliste (этот пункт не для вывода, нужны только результаты по Личному номеру) и в поле критериев для Личного номера добавляем ограничение для города Новый Йичин: Переключаемся на основной запрос и проверьте оператор вложенного запроса. По умолчанию используется оператор IN, что позволяет ограничиться не только одним значением, но и набором значений: Мы также можем использовать инвертированное выражение NOT IN.
11 Результат в STATISTICA выглядит следующим образом: Повторное использование Большим преимуществом данных, полученных с помощью запросов в форме таблицы STATISTICA, является возможность их обновления без повторного создания запроса. Все необходимое хранится в фоне таблицы, в виде метаданных, и запрос можно просто запустить заново, с открытой и активной таблицей, клавишей F5. Если мы хотели бы изменить запрос, просто перейдите на вкладку меню Файл -> gt; Внешние данные -> раздел Расширенный конструктор запросов -> Изменить запрос. Он снова откроется в инструменте AQB или в старом STATISTICA Query, в зависимости от вашего выбора. Метаданные для запроса можно удалить из таблицы в том же меню, выбрав Удалить. Мы показали, что с помощью инструмента AQB можно создавать не только простые, но и сложные запросы, не будучи экспертом по базам данных.специалисты или хотя бы кто-то такой всегда был под рукой.