Курс. Часть 3
Менеджер SQL
Менеджер SQL
Третий раздел курса посвящен детальному изучению Менеджера SQL — ключевого инструмента для работы с реляционными базами данных и файлами в «Слайдер Данные». В рамках этого модуля рассматриваются все аспекты его использования: от создания и редактирования запросов до их выполнения для получения данных из ранее настроенных соединений.
Ключевые возможности Менеджера SQL
В этом разделе вы освоите Менеджер SQL — мощный центр управления данными в «Слайдер Данные».
Мы пройдем путь от основ интерфейса до создания сложных, кросс-системных отчетов.
1. Освоение рабочего пространства: от интерфейса до первого запроса
Это одна из самых мощных функций. Вы научитесь:
В завершение мы проведем детальное сравнение подходов и покажем, почему «Слайдер Данные» предоставляет значительно большую гибкость:
Мы пройдем путь от основ интерфейса до создания сложных, кросс-системных отчетов.
1. Освоение рабочего пространства: от интерфейса до первого запроса
- Интерфейс: Вы познакомитесь со структурой окна, состоящего из боковой панели (с вашими соединениями и переменными) и основной области со списком запросов.
- Упрощенное написание запросов: Научитесь использовать левую панель для просмотра структуры таблиц и полей любого источника данных, что позволяет писать SQL-код без ошибок и необходимости помнить все названия.
- Создание базовых отчетов: На пошаговых примерах мы создадим простые SQL-запросы к различным СУБД (PostgreSQL, MS SQL и др.) и выгрузим результат на лист Р7-Офис.
Это одна из самых мощных функций. Вы научитесь:
- Создавать федеративные запросы, объединяя данные из абсолютно разных источников (например, таблицу из PostgreSQL и локальный CSV-файл) с помощью JOIN или UNION.
- Строить каскады обработки данных, используя одни запросы в качестве источника для других, более сложных.
- Динамические отчеты с помощью переменных: Узнаете, как создавать пользовательские переменные ({имя_переменной}) и использовать их прямо в SQL-коде, чтобы легко менять параметры отчета (например, даты или коды филиалов) без редактирования самого запроса.
- Обработка данных «на лету»: На практических примерах мы разберем, как использовать стандартные SQL-функции для манипуляции данными — от изменения регистра (UPPER) и работы со строками (REPLACE) до агрегации (SUM, GROUP BY) и создания расчетных полей.
В завершение мы проведем детальное сравнение подходов и покажем, почему «Слайдер Данные» предоставляет значительно большую гибкость:
- Прозрачность и полный контроль: Вы работаете с чистым, понятным SQL, который можно редактировать в любой момент, без «магии» и сложностей языка M.
- Централизация: Все соединения и запросы хранятся в одном месте, что упрощает их переиспользование и поддержку.
- Настоящая кросс-системность: Удобная и эффективная работа с множеством разнотипных источников в рамках одного запроса.
1. Запуск и интерфейс Менеджера SQL
Менеджер SQL первого раздела предназначен для формирования SQL-запросов в соединениях, созданных в Менеджере соединений (см. Часть 2 курса).
Для запуска Менеджера SQL
нажмите кнопку Менеджер SQL в меню первого раздела Слайдер Данные для Р7-Офис.
Откроется окно Менеджер SQL.
нажмите кнопку Менеджер SQL в меню первого раздела Слайдер Данные для Р7-Офис.
Откроется окно Менеджер SQL.
Интерфейс окна:
Верхняя часть:
Верхняя часть:
- Кнопка «Скрыть боковую панель» (позволяет расширить область запросов).
- Кнопка «Добавить запрос» (активируется при выборе соединения).
- Список типов запросов – фильтрует все запросы, только гетерогенные или только запросы соединений
Средняя часть:
Левая область (дерево объектов):
- Раздел «Текущий документ» (Переменные пользователя).
- Раздел Гетерогенные источники.
2. Элементы управления редактора
Кнопка Скрыть боковую панель предназначена для того, чтобы убрать левую область. Это позволяет расширить область запросов при необходимости.
После нажатия на кнопку «Скрыть боковую панель» окно Менеджера SQL будет выглядеть так:
Кнопка «Скрыть боковую панель» изменится на кнопку «Показать боковую панель».
При нажатии на неё окно менеджера SQL вернется к исходному виду.
После нажатия на кнопку «Скрыть боковую панель» окно Менеджера SQL будет выглядеть так:
Кнопка «Скрыть боковую панель» изменится на кнопку «Показать боковую панель».
При нажатии на неё окно менеджера SQL вернется к исходному виду.
Кнопка «Добавить запрос», пока не выбрано соединение, неактивна. При выбранном соединении нажатие на кнопку вызывает диалог создания запроса.
Список фильтрации по типу запроса определяет следующий варианты:
Список фильтрации по типу запроса определяет следующий варианты:
- Все запросы – в списке запросов отображаются все созданные запросы
- Гетерогенные запросы – отображение только гетерогенных
- Запросы соединений – можно выбрать фильтрацию по конкретному соединению
3. Левая область Менеджера SQL
3.1. Раздел Открытые документы (Переменные пользователя)
Плагин Слайдер Данные для Р7-Офис позволяет использовать в SQL-запросах пользовательские переменные, хранимые в документе.
Как создать переменную:
Плагин Слайдер Данные для Р7-Офис позволяет использовать в SQL-запросах пользовательские переменные, хранимые в документе.
Как создать переменную:
- Выберите поле Переменные пользователя и появится возможность добавления новой переменной (кнопка «+»)
- При нажатии «+» появится окно создания переменной.
- Введите название (латиница и цифры) и значение. Нажмите кнопку Добавить переменную
Убедившись, что курсор в редакторе запроса стоит в нужной позиции, двойным щелчком на имени переменной в дереве объектов перенесите название переменной в редактор SQL-запросов.
Изменить значение или удалить переменную можно наведением мыши на узле с именем переменной, и выбрав пункт из появившегося меню.
3.2. Список соединений. Структура баз и папок
Список соединений позволяет просматривать структуру баз данных и файловых каталогов.
Если имеется активное подключение к источнику, то в списке соединений значки соединений содержат слева навигационные элементы (серые треугольники).
Если имеется активное подключение к источнику, то в списке соединений значки соединений содержат слева навигационные элементы (серые треугольники).
При нажатии на него у соединения откроется структура данных:
Покажем, как можно посмотреть структуру СУБД на примере СУБД DuckDB.
При нажатии на серый треугольник слева от Tables будет отображен список таблиц СУБД
При нажатии на серый треугольник слева от Tables будет отображен список таблиц СУБД
При раскрытии нужной таблицы , отобразятся поля таблицы с типами полей:
3.3. Раздел Гетерогенные запросы
Гетерогенные или федеративные запросы — это SQL-запросы, которые объединяют данные различных источников, в том числе использующих различные технологии хранения данных (разные СУБД, форматы, протоколы и т.д.) с помощью JOIN или UNION. Пользователь может выбрать любой запрос в качестве гетерогенного источника, к которому потом по названию будет обращаться обобщающий запрос. Запрос можно сделать гетерогенным источником с помощью кнопки «Зарегистрировать как источник HTG» при наведении на заголовок запроса.
Далее запрос попадает в список гетерогенных источников. При раскрытии раздела «Гетерогенные источники» перед именем раздела отобразится список зарегистрированных источников (опубликованных ранее SQL запросов), которые могут быть использованы для создания федеративных запросов.
4. Правая область Менеджера SQL (Область SQL-запросов)
В правой области Менеджера SQL находится список SQL-запросов, сформированных ранее. SQL-запросы сохраняются в памяти прокси-сервера.
При выборе сохраненного запроса открывается область запроса, состоящая из подобласти редактора SQL-запросов и подобласти Предпросмотра.
Такая же область запроса открывается при создании нового SQL-запроса.
4.Создание простых SQL-запросов
Создание SQL-запроса будет показано на примере соединения с СУБД DuckDB.
Для создания нового SQL-запроса выберите соединение, запрос к которому нужно сформировать.
Выберите соединение «test_duckdb_conn» и кнопка «Добавить запрос» станет активной.
Для создания нового SQL-запроса выберите соединение, запрос к которому нужно сформировать.
Выберите соединение «test_duckdb_conn» и кнопка «Добавить запрос» станет активной.
Для удобства ввода запроса следует открыть структуру базы данных, «раскрыв» соединение. Отобразится список таблиц текущей схемы базы данных.
Нажать кнопку «Добавить запрос». Будет вызван диалог создания запроса.
Заполним название как «test_duckdb». Появится окно нового запроса.
Введем запрос, при вводе текста появляется окно автодополнения для помощи пользователю.
После ввода запроса можно посмотреть результат выполнения. Для проверки запроса нажмите на кнопку «Предпросмотр».
В подобласти предпросмотра будет выведена таблица с первыми 10000 строками данных (берется из настроек) или тем количеством строк, которое есть.
В подобласти предпросмотра будет выведена таблица с первыми 10000 строками данных (берется из настроек) или тем количеством строк, которое есть.
5. Подобласти области запросов
5.1. Редактор SQL-запросов
- Кнопка «Предпросмотр» - предназначена для проверки запроса и вывода его результат с ограничением не более количества строк, указанных в настройках как ограничение. При этом, помимо вывода, активируются кнопки для выгрузки в документ.
- Кнопка «Удалить» - для удаления запроса из списка SQL-запросов
- Список соединений – для быстрой смены соединения запроса
5.2. Подобласть предпросмотра
«Выгрузить в документ» - результат документа может быть выгружено в текущий лист либо во внешний файл
После выгрузки выдается информационное окно.
6. Гетерогенные запросы
6.1. Создание гетерогенных запросов
· Для создания гетерогенного запроса выберите любой запрос, например, созданный «test_duckdb».
· При наведении на заголовок появится кнопка
· Для создания гетерогенного запроса выберите любой запрос, например, созданный «test_duckdb».
· При наведении на заголовок появится кнопка
- После нажатия запрос появится в гетерогенных источниках
- Затем мы можем обращаться к этому запросу из другого. Для этого выберем список «Гетерогенные источники»
- Затем нажмите кнопку «Добавить гетерогенный запрос», которая появится после выбора списка
- Введем запрос, который использует название гетерогенного источника и нажмем «Предпросмотр»
6. Визуальный конструктор запросов «Мастер SQL Запросов»
Визуальный конструктор- это инструмент drag-and-drop для создания SQL-запросов без знания синтаксиса. Он вызывается нажатием кнопки «Волшебник» в запросе.
Появится окно Visual SQL Query Builder – визуальный конструктор.
● Выбрать мышью в левой области таблицу ‘date_dim’ и перенести ее на визуальный холст. Отобразится структура таблицы со всеми полями.
- Нажмем «Сгенерировать SQL» и в области «Сгенерированный SQL» появится автоматический запрос, генерируемый по умолчанию:
FROM date_dim
- Нажать кнопку «Сохранить» (Справа-внизу). Мастер закроется и активируется Менеджер SQL. В поле запроса отобразится новый сформированный запрос
Для проверки запроса нажать кнопку Предпросмотр. В разделе предпросмотра должны будут выведены данные результата запроса.
Дополнительно, редактор позволяет
Дополнительно, редактор позволяет
- Удалять все таблицы с холста (кнопка «Очистить»)
- Удалять таблицы с хоста и их связи
- Галочка «Расширенный режим» позволяет работать детально с полями таблиц и задавать детали в запросах для каждого поля
6.2 Создание запроса с JOIN
- Создадим запрос с подключением к MSSQL
- В запросе опять нажать кнопку Волшебник SQL.
- Выбрать мышью в левой области таблицу DimCustomer (Информация о клиентах) и перенести таблицу на Визуальный холст.
- Выбрать мышью в левой области таблицу DimDate (Даты и временные периоды). и перенести таблицу на Визуальный холст.
- Выбрать поле DateFirstPurchase таблицы DimCustomer и перетащить указатель на поле FullDateAlternateKey таблицы DimDate. Будет сформирован запрос в разделе Волшебника Сгенерированный SQL:
FROM dbo.DimCustomer
- INNER JOIN dbo.DimDate ON DimCustomer.DateFirstPurchase = DimDate.FullDateAlternateKey
- Нажать кнопку Ok. Запрос будет перенесен в Менеджер SQL. Нажать кнопку Предпросмотр. Данные результата запроса должны отобразиться в разделе Предпросмотра. Чтобы очистить раздел визуального холста, нажмите кнопку Очистить. Очистится и раздел Сгенерированный SQL
- Выбрать запрос к MS SQL, нажмите «Волшебник SQL». Подтвердите восстановление старого холста
- Нажмите галочку «Расширенный режим». В разделе Поля отобразятся поля выбранной таблицы.
- С помощью столбца Выбор отметить поля CustomerKey, FirstName, LastName, DateFirstPurchase. Запрос автоматически изменится.
SELECT DimCustomer.CustomerKey, DimCustomer.FirstName, DimCustomer.LastName, DimCustomer.DateFirstPurchase
FROM DimCustomer
INNER JOIN DimDate ON DimCustomer.DateFirstPurchase = DimDate.FullDateAlternateKey
Аналогично отметим поля FullDateAlternateKey, CalendarYear для таблицы DimDate. Запрос изменится на
SELECT DimCustomer.CustomerKey, DimCustomer.FirstName, DimCustomer.LastName, DimCustomer.DateFirstPurchase, DimDate.FullDateAlternateKey, DimDate.CalendarYear
FROM DimCustomer
INNER JOIN DimDate ON DimCustomer.DateFirstPurchase = DimDate.FullDateAlternateKey
FROM DimCustomer
INNER JOIN DimDate ON DimCustomer.DateFirstPurchase = DimDate.FullDateAlternateKey
Аналогично отметим поля FullDateAlternateKey, CalendarYear для таблицы DimDate. Запрос изменится на
SELECT DimCustomer.CustomerKey, DimCustomer.FirstName, DimCustomer.LastName, DimCustomer.DateFirstPurchase, DimDate.FullDateAlternateKey, DimDate.CalendarYear
FROM DimCustomer
INNER JOIN DimDate ON DimCustomer.DateFirstPurchase = DimDate.FullDateAlternateKey
- Необходимо нажать кнопку «Сохранить» и запрос будет перенесен в Менеджер SQL. Нажать кнопку Предпросмотр.
Отображаются данные первых покупок клиентов.
- Если для поля DateFirstPurchase в колонке Агрегация выбрать DESC (Сортировка по уменьшению), то автоматически будет изменен запрос на:
FROM DimCustomer
INNER JOIN DimDate ON DimCustomer.DateFirstPurchase = DimDate.FullDateAlternateKey
ORDER BY DimCustomer.DateFirstPurchase DESC
- Нажать кнопку Ok. Запрос будет перенесен в Менеджер SQL.Нажать кнопку Предпросмотр. Данные результата запроса должны отобразиться в разделе Предпросмотра.
- Отображаются данные первых покупок клиентов, отсортированные по годам.
- В разделе Поля выбранной таблицы DimCustomer с помощью столбца «Группировка» отметим поля из SELECT, чтобы протестировать сгруппированные данные. Автоматически изменится запрос:
FROM DimCustomer
INNER JOIN DimDate ON DimCustomer.DateFirstPurchase = DimDate.FullDateAlternateKey
GROUP BY DimCustomer.CustomerKey, DimCustomer.FirstName, DimCustomer.LastName, DimCustomer.DateFirstPurchase, DimDate.FullDateAlternateKey, DimDate.CalendarYear
ORDER BY DimCustomer.DateFirstPurchase DESC
- Далее нужно нажать кнопку «Сохранить». Запрос будет перенесен в Менеджер SQL. Нажать кнопку «Предпросмотр». Данные результата запроса должны отобразиться в разделе Предпросмотра.
7 Сравнение SQL-запросов и в Power Query
Расскажем о работе с Excel Power Query и сравним его с плагином.
Power Query позволяет подключиться к базе данных, и на этапе подключения ввести SQL-запрос. Взаимодействие происходит через модальные окна. Редактирование SQL-запроса в дальнейшем не доступно из интерфейса PQ.
Power Query позволяет подключиться к базе данных, и на этапе подключения ввести SQL-запрос. Взаимодействие происходит через модальные окна. Редактирование SQL-запроса в дальнейшем не доступно из интерфейса PQ.
- Окно подключения Power Query с полем для SQL-запроса:
Этапы подключения:
- «Данные» → «Получить данные» → «Из базы данных».
- Ввод параметров подключения
- Опционально — ввод SQL-запроса вручную.
- После подключения Power Query преобразует результат в таблицу и создаёт M-код. Изменить исходный SQL-запрос через редактор нельзя — только пересоздать подключение. Это снижает гибкость, особенно при частых изменениях запроса.
Таблица сравнения Power Query и SQL данные Р7-Офис
Возможность
Power Query
Плагин SQL Данные Р7-Офис |
Фильтрация
Через UI или SQL при подключении
Прямо в SQL-запросе
Агрегация
Через UI (группировка, сумма)
Прямо в SQL
Предпросмотр
Да, но после импорта
Да, сразу по нажатию кнопки
Повторное использование
Только через дублирование шагов
Сохранение в Менеджере SQL
Множественные источники
Нет (один источник на запрос)
Да (гетерогенные JOIN)
Использование Power Query может приводить к ошибкам и неудобствам
- После подключения SQL-запрос нельзя изменить, только пересоздать.
- M-код чувствителен к русским/английским названиям, особенно при фильтрации по полям с нестандартными символами.
- При внесении изменений Power Query может «переписать» SQL-запрос на свой язык (M), ломая логику.
- M-код чувствителен к русским/английским названиям, особенно при фильтрации по полям с нестандартными символами.
- При внесении изменений Power Query может «переписать» SQL-запрос на свой язык (M), ломая логику.
Дополнительные преимущества Слайдер Данные Р7-Офис при использовании SQL-запросов:
- Имеется журнал выполнения и логирования ошибок. В нем при выполнении SQL-запросов фиксируются параметры запроса, дата выполнения, результат, ошибки (если они есть). Это помогает находить и устранять проблемы, узнавать, кем выполнялся отчет, подтверждать корректность отчетов.
- Плагин Слайдер Данные Р7-Офис не требует установки дополнительных драйверов (ODBC) и прав администратора.
- Все подключения сохраняются в текстовых файлах (.con) и могут быть восстановлены в Менеджере соединений (Часть 2 Курса). Это критично для ИТ-отделов в организациях с ограничениями по установке ПО.
- Имеется поддержка защищенных соединений.
- Работа в Linux.
- Плагин Слайдер Данные Р7-Офис не требует установки дополнительных драйверов (ODBC) и прав администратора.
- Все подключения сохраняются в текстовых файлах (.con) и могут быть восстановлены в Менеджере соединений (Часть 2 Курса). Это критично для ИТ-отделов в организациях с ограничениями по установке ПО.
- Имеется поддержка защищенных соединений.
- Работа в Linux.
Подход Power Query: ограничения интерфейса Merge
Для объединения данных из разных источников Power Query использует визуальный интерфейс
«Объединить запросы» (Merge Queries). Процесс выглядит следующим образом:
«Объединить запросы» (Merge Queries). Процесс выглядит следующим образом:
- Пользователь поочередно загружает в редактор все необходимые таблицы (например, из PostgreSQL и CSV-файла).
- С помощью инструмента Merge он указывает, какие таблицы и по каким полям нужно соединить (аналог JOIN).
- После объединения необходимо вручную «развернуть» вложенную таблицу, выбрав нужные столбцы.
- Неконтролируемые подключения. В Power Query отсутствует централизованное хранилище соединений. Каждый пользователь настраивает доступы самостоятельно, что приводит к дублированию, ошибкам в параметрах и невозможности централизованно управлять доступами. Ситуация усугубляется политиками безопасности (прокси-серверы, запрет на автообновление), которые делают такие децентрализованные подключения нестабильными.
- Сложность и непрозрачность логики. При объединении 3–5 таблиц (например, Продажи + Клиенты + Регионы) пользователь создает запутанную цепочку Merge-запросов. Такая логика быстро становится нечитаемой, а поддержка отчета превращается в проблему: невозможно быстро понять, откуда берется конкретная колонка, а любое изменение в источнике требует ручной перестройки всей цепочки.
- Скрытые риски преобразования типов. Автоматическое определение типов данных в Power Query ненадежно. Инструмент может некорректно распознать дату или преобразовать число в текст, что приводит к трудно обнаруживаемым ошибкам в расчетах и фильтрации.
- Окно Merge предназначено для объединения двух таблиц (например, PostgreSQL и CSV). В нем указываются поля для объединения и выбирается тип соединения (Inner, Left Outer и т.д.).
Ограничения Merge в Power Query
- Данные должны быть уже загружены в модель;
- Нельзя писать SQL JOIN вручную — только через UI;
- Невозможно объединять более двух таблиц одновременно;
- Если источник удалённый, обработка может быть медленной;
- Для сложных объединений нужен переход к языку M, поддержка которого вне Microsoft ограничена.
Сравнение подходов: Power Query против «Слайдер Данные»
- В то время как Power Query предлагает визуальный интерфейс для объединения данных, плагин «Слайдер Данные» предоставляет более гибкий и контролируемый подход, который решает ключевые проблемы, возникающие в корпоративной среде.
Критерий
Power Query
Плагин «Слайдер Данные»
Управление соединениями
Децентрализовано. Каждый пользователь настраивает доступ сам, что ведет к ошибкам и проблемам с безопасностью.
Централизованно. Соединения настраиваются один раз в Менеджере, могут передаваться между сотрудниками (.con файлы) и не зависят от прав пользователя на ПК.
Логика объединения
Цепочка визуальных шагов (Merge). При объединении 3+ таблиц логика становится запутанной и трудной для поддержки.
Прозрачный SQL-запрос. Вся логика объединения содержится в одном, легко читаемом запросе, который полностью контролируется пользователем.
Количество и тип источников
Ограничение на два источника за один шаг Merge. Объединение разных типов источников (например, OLAP и SQL) затруднено.
Без ограничений. В одном SQL-запросе можно объединять любое количество таблиц из разных СУБД, файлов и других источников.
Контроль над типами данных
Ненадежная автоматическая трансформация, которая может приводить к скрытым ошибкам в расчетах.
Полный ручной контроль. Пользователь сам определяет типы данных с помощью стандартных SQL-функций, исключая неожиданные преобразования.
Поддержка и изменения
При изменении источника или логики часто требуется переделывать всю цепочку шагов.
Гибкость. Достаточно обновить одно соединение или изменить часть SQL-запроса. Запросы можно сохранять и переиспользовать в других отчетах.
Выводы: когда и какой инструмент выбрать
Power Query — хороший выбор для новичков и решения простых задач. Его визуальный интерфейс позволяет быстро объединить две-три таблицы из схожих источников без написания кода.
Плагин «Слайдер Данные» — это профессиональный инструмент для сложных сценариев. Он незаменим, когда требуются:
Плагин «Слайдер Данные» — это профессиональный инструмент для сложных сценариев. Он незаменим, когда требуются:
- Прозрачность: четкое понимание, откуда берутся данные.
- Надежность: полный контроль над типами данных и логи
Приложение А. Примеры простых SQL-запросов
Список предустановленных соединений, необходимых для примеров.
А.1. СУБД PostgresSQL
В менеджере SQL выберите соединение pgl - PostgreSQL. При отсутствии создайте его (Часть 2 Курса). Раскройте структуру таблиц, если нужно потренироваться в использовании автоматического переноса имени таблицы в запрос.
Нажмите кнопку Добавить запрос. Создайте запрос pg0 с регистрацией его, как источника для гетерогенных запросов.
В подобласти редактора введите запрос:
select * from "person"."address"
Примечание: Можно скопировать его отсюда в редактор SQL-запросов.
Нажмите кнопку Предпросмотр. Получим вывод первых 200 строк из 19614 в нашем случае.
В подобласти редактора введите запрос:
select * from "person"."address"
Примечание: Можно скопировать его отсюда в редактор SQL-запросов.
Нажмите кнопку Предпросмотр. Получим вывод первых 200 строк из 19614 в нашем случае.
В строке с именем запроса pgl : pg0 должен отображаться значок Скрепки, показывающий готовность отчета к формированию гетерогенного запроса.
При желании можно выгрузить данные на лист Р7-Офис, нажав на кнопку Выгрузить в документ и закрыть (Название отчета любое, например, tpg0). На новом листе будут выведены все строки.
Начало таблицы (отчета):
При желании можно выгрузить данные на лист Р7-Офис, нажав на кнопку Выгрузить в документ и закрыть (Название отчета любое, например, tpg0). На новом листе будут выведены все строки.
Начало таблицы (отчета):
Конец отчета (19615 строк – автоматически добавлена строка заголовка):
A.2. СУБД MS SQL
В менеджере SQL выберите соединение mssl – MS SQL. Раскройте структуру его таблиц.
Нажмите кнопку Добавить запрос. Создайте запрос mss0, например:
select * from DimCustomer
Можно скопировать его отсюда в редактор SQL-запросов.
Нажмите кнопку Предпросмотр. Отобразятся первые 200 строк из 18484.
select * from DimCustomer
Можно скопировать его отсюда в редактор SQL-запросов.
Нажмите кнопку Предпросмотр. Отобразятся первые 200 строк из 18484.
В строке с именем запроса mssl : mss0 должен отображаться значок Скрепки, показывающий готовность отчета к формированию гетерогенного запроса.
При желании можно выгрузить данные на лист Р7-Офис, нажав кнопку Выгрузить в документ и закрыть (Название отчета любое, например, tmss0). На новом листе будут выведены все строки.
Начало таблицы (отчета):
При желании можно выгрузить данные на лист Р7-Офис, нажав кнопку Выгрузить в документ и закрыть (Название отчета любое, например, tmss0). На новом листе будут выведены все строки.
Начало таблицы (отчета):
Конец отчета (18485 строк – автоматически добавлена строка заголовка):
A.3. СУБД My SQL
В менеджере SQL выберите соединение mysl – My SQL.
Раскройте структуру его таблиц.
Раскройте структуру его таблиц.
Нажмите кнопку Добавить запрос. Создайте запрос mys0, например:
select * from adventureworks.contactcreditcard
Нажмите кнопку Предпросмотр. Получим вывод первых 200 строк из 19118.
select * from adventureworks.contactcreditcard
Нажмите кнопку Предпросмотр. Получим вывод первых 200 строк из 19118.
Конец отчета (19119 строк – автоматически добавлена строка заголовка):
A.4. СУБД Oracle
Пример создания запроса соединения с СУБД Oracle приведен в п.4. Добавим выгрузку в документ.
Запрос:
select * from "DIMACCOUNT"
После нажатия на кнопку Предпросмотр в редакторе запросов:
Запрос:
select * from "DIMACCOUNT"
После нажатия на кнопку Предпросмотр в редакторе запросов:
Для выгрузки данных на лист Р7-Офис, нажмите кнопку Выгрузить в документ и закрыть (Название отчета любое, например, tora0). На новом листе будут выведены все строки.
Начало таблицы (отчета):
Начало таблицы (отчета):
Конец отчета (100 строк – автоматически добавлена строка заголовка):
Приложение B. SQL-запросы с формулами
В редакторе SQL-запросов можно задавать более сложные запросы. Покажем примеры таких запросов на примере СУБД Oracle (п.7.4.).
B.1. Изменение регистра букв
Исходный запрос: select * from "DIMACCOUNT"
При нажатии на кнопку Предпросмотр будет выведено:
При нажатии на кнопку Предпросмотр будет выведено:
Изменим SQL-запрос на:
select upper(ACCOUNTDESCRIPTION) as UpperDesc from "DIMACCOUNT" limit 5
После нажатия на кнопку Предпросмотр будут выведены данные столбца ACCOUNTDESCRIPTION в верхнем регистре :
select upper(ACCOUNTDESCRIPTION) as UpperDesc from "DIMACCOUNT" limit 5
После нажатия на кнопку Предпросмотр будут выведены данные столбца ACCOUNTDESCRIPTION в верхнем регистре :
Заменим функцию upper() на lower() и имя столбца на LOWDESC, получим
Выходные данные соответствуют ожидаемым (столбец ACCOUNTDESCRIPTION в нижнем регистре).
B.2. Удаление пробелов в строковых полях
Изменим запрос на
select replace(ACCOUNTDESCRIPTION, ' ', '') as ADesc from "DIMACCOUNT" limit 5
После нажатия на кнопку Предпросмотр должны получить
select replace(ACCOUNTDESCRIPTION, ' ', '') as ADesc from "DIMACCOUNT" limit 5
После нажатия на кнопку Предпросмотр должны получить
В столбце ACCOUNTDESCRIPTION будут удалены пробелы.
B.3. Разбиение строкового поля на столбцы по первому пробелу
Изменим запрос:
select ACCOUNTDESCRIPTION as Adsc,
SUBSTR(ACCOUNTDESCRIPTION , 1, INSTR(ACCOUNTDESCRIPTION , ' ') - 1) AS first_part,
SUBSTR(ACCOUNTDESCRIPTION , INSTR(ACCOUNTDESCRIPTION , ' ') + 1) AS second_part
from "DIMACCOUNT"
WHERE INSTR(ACCOUNTDESCRIPTION , ' ') > 0;
Получим:
select ACCOUNTDESCRIPTION as Adsc,
SUBSTR(ACCOUNTDESCRIPTION , 1, INSTR(ACCOUNTDESCRIPTION , ' ') - 1) AS first_part,
SUBSTR(ACCOUNTDESCRIPTION , INSTR(ACCOUNTDESCRIPTION , ' ') + 1) AS second_part
from "DIMACCOUNT"
WHERE INSTR(ACCOUNTDESCRIPTION , ' ') > 0;
Получим:
Будут выведены данные столбца ACCOUNTDESCRIPTION в первом столбце и добавятся два столбца с частями строк до и после пробела.
B.4. Объединение строкового и числового поля
Изменим запрос:
select ACCOUNTDESCRIPTION || ' # ' || TO_CHAR(ACCOUNTKEY) as cnt
from "DIMACCOUNT"
Получим:
select ACCOUNTDESCRIPTION || ' # ' || TO_CHAR(ACCOUNTKEY) as cnt
from "DIMACCOUNT"
Получим:
Будет создан столбец с объединенными данными из столбца ACCOUNTDESCRIPTION, символа решетка (#) и числовыми данными из столбца ACCOUNTKEY, преобразованного в строку.
B.5. Группировка по текстовому полю, усреднение и сортировка
Изменим запрос:
SELECT
ACCOUNTDESCRIPTION,
ROUND(AVG(PARENTACCOUNTCODEALTERNATEKEY), 2) AS AVERAGE_PARENT_CODE
FROM
DIMACCOUNT
GROUP BY
ACCOUNTDESCRIPTION
order by AVERAGE_PARENT_CODE
Получим:
SELECT
ACCOUNTDESCRIPTION,
ROUND(AVG(PARENTACCOUNTCODEALTERNATEKEY), 2) AS AVERAGE_PARENT_CODE
FROM
DIMACCOUNT
GROUP BY
ACCOUNTDESCRIPTION
order by AVERAGE_PARENT_CODE
Получим:
Данные в столбце PARENTACCOUNTCODEALTERNATEKEY будут усреднены и сгруппированы по столбцу ACCOUNTDESCRIPTION.
Приложение С . Примеры сложных гетерогенных запросов c файловыми каталогами
В п.6 описывалось создание гетерогенного запроса.
В этом примере опишем процесс создания нескольких более сложных гетерогенных запросов.
Подготовим SQL-запросы к соединениям с файловыми каталогами (пп.9.1-9.4).
В этом примере опишем процесс создания нескольких более сложных гетерогенных запросов.
Подготовим SQL-запросы к соединениям с файловыми каталогами (пп.9.1-9.4).
- fs_csv_p – Продукция (товары), CSV-файл
- fs_xlsx_sal – Продажи, файл Excel
- fs_csv_cli – Клиенты, CSV-файл
С.1. SQL-Запрос продукции (товаров) в Менеджере SQL
Выделите соединение fs_csv_p.
Нажмите кнопку Добавить запрос. Введите имя запроса: fs_csv_prod. Отметьте чек-бокс Зарегистрировать как источник. Нажмите кнопку Добавить новый SQL.
Будет сформирован новый SQL-запрос.
Нажмите кнопку Добавить запрос. Введите имя запроса: fs_csv_prod. Отметьте чек-бокс Зарегистрировать как источник. Нажмите кнопку Добавить новый SQL.
Будет сформирован новый SQL-запрос.
В редакторе введите SQL-запрос:
select * from products_csv
Нажмите кнопку Предпросмотр.
select * from products_csv
Нажмите кнопку Предпросмотр.
С.2. SQL-Запрос клиентов в Менеджере SQL
Выделите соединение fs_csv_cli.
Нажмите кнопку Добавить запрос. Введите имя запроса: fs_csv_clients. Отметьте чек-бокс Зарегистрировать как источник. Нажмите кнопку Добавить новый SQL. Будет сформирован новый SQL-запрос.
Нажмите кнопку Добавить запрос. Введите имя запроса: fs_csv_clients. Отметьте чек-бокс Зарегистрировать как источник. Нажмите кнопку Добавить новый SQL. Будет сформирован новый SQL-запрос.
В редакторе введите SQL-запрос: select * from clients_csv
Нажмите кнопку Предпросмотр.
Нажмите кнопку Предпросмотр.
С.3. SQL-Запрос продаж в Менеджере SQL
Выделите соединение fs_xls_sal.
Нажмите кнопку Добавить запрос. Введите имя запроса: xlsx_sal. Отметьте чек-бокс Зарегистрировать как источник. Нажмите кнопку Добавить новый SQL. Будет сформирован новый SQL-запрос.
В редакторе введите SQL-запрос:
select s."идентификатор клиента",s."идентификатор товара",
s."идентификатор продажи",s."количество проданного товара",
('1899-12-30'::DATE + s."дата продажи") AS "дата продажи" from sales10048_xlsxs
Нажмите кнопку Предпросмотр.
Нажмите кнопку Добавить запрос. Введите имя запроса: xlsx_sal. Отметьте чек-бокс Зарегистрировать как источник. Нажмите кнопку Добавить новый SQL. Будет сформирован новый SQL-запрос.
В редакторе введите SQL-запрос:
select s."идентификатор клиента",s."идентификатор товара",
s."идентификатор продажи",s."количество проданного товара",
('1899-12-30'::DATE + s."дата продажи") AS "дата продажи" from sales10048_xlsxs
Нажмите кнопку Предпросмотр.
С.4. Удаление дубликатов в результатах запросов
Для удаления дубликатов необходимо добавить в SQL-запросы команду distinct.
Товары.
Измените SQL-запрос на:
select distinct * from products_csv
Клиенты.
Измените SQL-запрос на:
select distinct * from clients_csv
Продажи.
Измените SQL-запрос на:
select distincts."идентификатор клиента",s."идентификатор товара",
s."идентификатор продажи",s."количество проданного товара",
('1899-12-30'::DATE + s."дата продажи") AS "дата продажи" from sales_xlsx s
Для использования этих запросов в гетерогенных запросах и для проверки необходимо повторно нажать кнопку Предпросмотр в каждом SQL-запросе.
Товары.
Измените SQL-запрос на:
select distinct * from products_csv
Клиенты.
Измените SQL-запрос на:
select distinct * from clients_csv
Продажи.
Измените SQL-запрос на:
select distincts."идентификатор клиента",s."идентификатор товара",
s."идентификатор продажи",s."количество проданного товара",
('1899-12-30'::DATE + s."дата продажи") AS "дата продажи" from sales_xlsx s
Для использования этих запросов в гетерогенных запросах и для проверки необходимо повторно нажать кнопку Предпросмотр в каждом SQL-запросе.
С.5. Гетерогенный запрос по объединению данных в общую таблицу
Объединение данных (JOIN, ВПР) производится по идентификаторам в источниках. Данные получим из запросов, подготовленных в пп.9.1-9.4.
Выберите в Менеджере SQL Гетерогенные запросы и нажмите кнопку Добавить Запрос.
Выберите в Менеджере SQL Гетерогенные запросы и нажмите кнопку Добавить Запрос.
Введите имя запроса get_sal. Чек-бокc можно не выделять. Нажмите кнопку Добавить новый SQL.
SELECT
c."имя клиента",
p."наименование товара",
p."цена товара",
s."количество проданного товара",
s."дата продажи"
FROM
xlsx_sal s
JOIN
fs_csv_clientsc ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
ORDER BY
s."дата продажи" DESC;
Нажмите кнопку Предпросмотр. Получим:
SELECT
c."имя клиента",
p."наименование товара",
p."цена товара",
s."количество проданного товара",
s."дата продажи"
FROM
xlsx_sal s
JOIN
fs_csv_clientsc ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
ORDER BY
s."дата продажи" DESC;
Нажмите кнопку Предпросмотр. Получим:
С.6. Гетерогенный запрос с расчетным столбцом Сумма продаж
Добавим в общей таблице п.9.5. столбец «Сумма продажи», равный цене товара, умноженной на количество проданного товара в транзакции.
Чтобы добавить столбец, измените SQL-запрос. в редакторе:
SELECT
c."имя клиента",
p."наименование товара",
p."цена товара",
s."количество проданного товара",
s."дата продажи",
(p."цена товара" * s."количество проданного товара") AS "Сумма продажи"
FROM
xlsx_sal s
JOIN
fs_csv_clients c ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
ORDER BY
s."дата продажи" DESC;
Нажмите кнопку Предпросмотр. Получим:
Чтобы добавить столбец, измените SQL-запрос. в редакторе:
SELECT
c."имя клиента",
p."наименование товара",
p."цена товара",
s."количество проданного товара",
s."дата продажи",
(p."цена товара" * s."количество проданного товара") AS "Сумма продажи"
FROM
xlsx_sal s
JOIN
fs_csv_clients c ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
ORDER BY
s."дата продажи" DESC;
Нажмите кнопку Предпросмотр. Получим:
С.7. Гетерогенный запрос с суммарными продажами по месяцам
Создайте новый гетерогенный запрос getmonth.
SELECT
month(s."дата продажи") as mn,
SUM(p."цена товара" * s."количество проданного товара") AS "Сумма продаж"
FROM
xlsx_sal s
JOIN
fs_csv_clients c ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
GROUP BY
month(s."дата продажи")
order by mn
Нажмите кнопку Предпросмотр. Получим:
SELECT
month(s."дата продажи") as mn,
SUM(p."цена товара" * s."количество проданного товара") AS "Сумма продаж"
FROM
xlsx_sal s
JOIN
fs_csv_clients c ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
GROUP BY
month(s."дата продажи")
order by mn
Нажмите кнопку Предпросмотр. Получим:
С.8. Гетерогенный запрос с суммарными продажами по клиентам
Создайте новый гетерогенный запрос getclient.
SELECT
c."имя клиента",
SUM(p."цена товара" * s."количество проданного товара") AS "Сумма продаж"
FROM
xlsx_sal s
JOIN
fs_csv_clients c ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
GROUP BY
c."имя клиента"
order by "Сумма продаж" desc
Нажмите кнопку Предпросмотр. Получим:
SELECT
c."имя клиента",
SUM(p."цена товара" * s."количество проданного товара") AS "Сумма продаж"
FROM
xlsx_sal s
JOIN
fs_csv_clients c ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
GROUP BY
c."имя клиента"
order by "Сумма продаж" desc
Нажмите кнопку Предпросмотр. Получим:
С.9. Гетерогенный запрос с суммарными продажами по товарам
Создайте новый гетерогенный запрос getprod.
SELECT
p."наименование товара",
SUM(p."цена товара" * s."количество проданного товара") AS "Сумма продаж"
FROM
xlsx_sal s
JOIN
fs_csv_clients c ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
GROUP BY
p."наименование товара"
order by "Сумма продаж" desc
Нажмите кнопку Предпросмотр. Получим:
SELECT
p."наименование товара",
SUM(p."цена товара" * s."количество проданного товара") AS "Сумма продаж"
FROM
xlsx_sal s
JOIN
fs_csv_clients c ON s."идентификатор клиента" = c."идентификатор клиента"
JOIN
fs_csv_prod p ON s."идентификатор товара" = p."идентификатор товара"
GROUP BY
p."наименование товара"
order by "Сумма продаж" desc
Нажмите кнопку Предпросмотр. Получим:
