Язык SQL
8.13.3 Правила построения запросов
Работа с заданиями на естественном языке
Задание на построение запроса обычно представляется в виде одной или нескольких фраз на естественном, для нас русском, языке. Может показаться, что составление запроса по заданию —это аналог обычного для лингвистов перевода, только проще, уже потому что язык перевода SQL устроен проще естественных языков.
Всё так, у нас не перевод художественного текста (как там, на английском "Немь лукает луком немным в закричальности зари"). Но, все-таки, мы исходим из текста на естественном языке. Конечно, никто не станет начинать задание на запрос так: "Не будет ли Вам благоугодно предоставить сведения о . . . ". Но, тем не менее, подмножество естественного языка, достаточное для написания заданий на составление инструкций SQL, и не требующее изучения человеком никто не определил.
Конечно, можно потребовать обязательного пользования некоторой тер-миносистемой (кто бы её создал) отсутствия во фразе задания метафоричности, расплывчатости, неоправданного использования синонимов и т.д. Но, по-видимому, нельзя запретить задание несколькими фразами, использование названий объектов базы на естественном языке, использование умолчаний, чрезмерно общих понятий ("выбрать сотрудников, у которых . . . ") и прочие прелести.
Трудность написания запросов ещё в том, что бизнес и база данных это два существенно различных Мира, в то время как естественные языки представляют в общих чертах один Мир. Во-первых, модель бизнеса может отображаться частями на модель базы данных, пользовательский интерфейс и модель сервера приложений. Во-вторых, некоторые конструкции, например, соединения, могут не называться в задании. Их необходимо "додумать".
Попытаемся выполнить такое задание: "Начислить заработную плату за январь 1982 года". Если вспомнить, что процесс начисления заработной платы требует исходить из отсутствующих у нас (в схеме scott) документов, подтверждающих выполнение работы и зависящих от принятого способа оплаты, что перед выдачей ведомости на оплате необходимо рассчитать подоходный налог, и т.д., то задачу следует признать неразрешимой в имеющейся у нас базе. Теперь уместно спросить, а вообще, какой смысл имеет таблица emp. Очевидно, это всего лишь набор записей о приёме на работу. Запись об увольнении сделать невозможно. Нет соответствующего столбца, какого-нибудь firedate. Поэтому работник, если верить таблице emp, никогда не увольняется, даже в случае смерти. Эдакий список лиц допущенных к работе посмертно. Правда, можно просто стереть запись об уволенном сотруднике. Но как тогда ответить на вопрос: "Работал ли X в марте 1981 года?". А что тогда означает уникальность табельного номера? Если сотру дника уволить, стерев запись о нём, то при повторном приёме, кто вспомнит его табельный номер, который, кстати, может быть уже занят. Невозможно перевести сотрудника на другую должность, так как нет столбца "Дата перевода".
Мы не ратуем за максимальное усложнение задач для начинающих. Но, начиная с некоторого уровня, когда необходимые ремесленные навыки уже достигнуты, следует больше интересоваться семантикой данных. Без осознания смыслов искажается подлинная сущность базы, а написание сложных запросов может превратиться в трудно разрешимую проблему.
Выяснение семантики всегда требует значительного объёма работы и хорошего знания бизнеса.
Если задание состоит из нескольких фраз, необходимо установить связи между ними. Лингвисты говорят об анафоре, когда существует связь вперед и катафоре для связи назад.
Примеры заданий:
- Выбрать фамилии и должности сотрудников, работающих в отделах 20 и 30. Зарплата выбранных сотрудников должна быть выше средней по своему отделу.
- В каждом отделе есть сотрудники, зарплата которых выше средней по своему отделу. Выбрать таких сотрудников в отделах 20 и 30.
Очевидно, оба задания должны быть трансформированы к следующему виду:
"Выбрать фамилии и должности сотрудников, удовлетворяющих следующим условиям: место работы — отделы 20 и 30, зарплата выше средней по своему отделу."
После приведения к одной фразе, может быть имеющей сложную структуру, необходимо определить шаблон SQL, в котором может быть записан транслированный запрос.
Свойства шаблонов
Для того, чтобы различать шаблоны построим систему их свойств, обладающую тремя свойствами:
- значение каждого свойства можно установить, анализируя задание и, может быть, семантику базы данных; при этом не все задания могут быть выполнимыми;
- свойства образуют расслоенное пространство, причём свойства верхнего уровня могут уточняться через свойства нижних уровней;
- координатная система заданная набором свойств должна, по крайней мере для некоторых заданий, определять шаблон однозначно.
На верхнем уровне классификации мы уже выделили три класса шаблонов:
- запросы без подзапросов;
- запросы с подзапросами;
- запросы с учётом вложенных структур.
Существование вложенных структур можно выявить из спецификации семантики таблиц и по некоторым особенностям задания. Например, для иерархических структур это указания на подчинённость. Работа со структурой, не предусмотренной семантикой данных возможна, но бессмысленна. Пример: иерархия образующая дерево по столбцам "рост" и "вес" в таблице "пациенты".
Если запрос содержит подзапросы, то в задании должна существовать по крайней мере, одна подфраза, которую следует понимать как требование вычислить некоторые величины по данным базы. Поскольку эти данные могут меняться, то предвычисление этих величин до исполнения запроса невозможно.
Вспомним, что к запросам без подзапросов мы отнесли объединения нескольких запросов с помощью операций над множествами (UNION и др.). В задании для таких запросов должно быть одно из двух:
- или указание на два и более источника данных (таблиц), к которым выполняются независимые запросы, и результаты этих запросов собираются в одну таблицу;
- или указание минимум двух способов группировки данных из одной таблицы, или набора таблиц, результаты которых должны быть объединены (минимальный вариант: данные без группировки и с одной группой).
Для объединения запросов следует проверить необходимые условия объединения: одно и то же число столбцов, попарное совпадение их типов и возможность совмещения семантики столбцов. Поясним последнее: типы данных столбцов "рост" и "вес" позволяют объединить их, но семантика слишком различна чтобы итоговый столбец был полезен.
Если же во всех объединяемых запросах таблица одна, столбцы совпадают и нет группировок, то эта конструкция сводится к одному запросу со сложным условием во фразе WHERE.
Перечисленных свойств достаточно для выделения классов верхнего уровня.
Теперь разберёмся с одиночным запросом к одной таблице. Простейший шаблон SF реализуется, если необходимо выбрать некоторые столбцы для всех строк таблицы. Если же в задании обнаружено условие отбора строк, используется шаблон SFW.
Если необходимо упорядочить результат по каким-то его столбцам, переходим к шаблону SFO или SFWO.
Если необходимо считать итоговые результаты по группам, то группы выделяются фразой GROUP BY, а во фразе SELECT используются многострочные функции. Группирование и упорядочение могут существовать в одном запросе.
Осталось разобраться с одиночными запросами к нескольким таблицам. В них следует убедиться, что подзапросов нет, но данные выбираются более, чем из одной таблицы. Запись такого запроса с условиями соединения или с операторами соединений — это чисто технические детали. Необходимо только разобраться, является ли соединение внутренним или внешним. Во внутренних соединениях каждой строке одной таблицы соответствует минимум одна строка второй. Во внешнем левом или правом соединении в одной таблице нет строк, соответствующих строкам второй. В полном внешнем соединении строки каждой таблицы могут не иметь соответствующих строк второй таблицы.
Переходим к подзапросам. Их главный признак —необходимость получить данные, которые могут быть найдены только с помощью другого запроса по информации этой же или других таблиц. Мнемоническим признаком может быть удобство записи основного запроса с заглушками, которые можно расшифровать только с помощью подзапроса.
Признак коррелированного подзапроса: данные подзапроса должны быть свои для каждой строки основного запроса.
Признак обычного подзапроса: данные подзапроса пригодны для всех строк основного запроса.
Следует помнить, что обычные подзапросы следует использовать во фразах FROM, WHERE и HAVING, а коррелированные только во фразах SELECT, WHERE и HAVING. Дело в том, что обработка основного запроса начинается с фразы FROM и повторно вернуться к ней уже нельзя.
Приступаем к написанию запроса
Прежде чем писать запросы познакомьтесь с используемой схемой базы, не пренебрегая семантикой, которая обычно описывается недостаточно полно. Полезно хорошее знакомство с предметной областью, для которой создана база данных.
Попытаемся, идя сверху вниз, сначала установить класс, к которому запрос относится. Если это удастся, получим общий шаблон запроса, и на следующих этапах будем его уточнять и заполнять деталями.
На первом этапе, используя описанную выше систему признаков, выясняем, к какому из трёх основных классов относится запрос: без подзапросов, с подзапросами или запрос с учётом вложенных структур. Не забываем, что запросы без подзапросов включают в себя одиночные запросы к одной или нескольким таблицам и объединения результатов нескольких запросов как множеств.
На следующем этапе уточняем выбранный шаблон. Пусть оказалось, что мы пишем запрос к одной таблице, не содержащий подзапросов. Такой запрос включает обязательные составляющие SELECT и FROM. Нужны ли остальные фразы, выясним, выявляя их признаки описанные ранее.
Если же на первом этапе установлено, что запрос содержит подзапросы, следует выделить части задания определяющие содержание подзапросов и места их прикрепления к основному шаблону или вложенным шаблонам (подзапросам). После этого можно детализировать подзапросы.
Насколько мне удалось выяснить, в практике работы со сложными информационными системами глубина вложенности подзапросов больше трех не применяется. Число вложенных подзапросов на один основной запрос обычно не превышает 5-7.
Что делать, если не удаётся пройти этот путь до конца? Останавливайтесь и пытайтесь выяснить любые подробности. Впоследствии они вам пригодятся. Затем, уже с уточнённым восприятием задания, пытайтесь продолжить работу.
К сожалению, мы освоили только небольшую часть современного языка SQL. Мы не изучали великое множество функций, в том числе аналитических. Фраза GROUP BY нами освоена в простейшем варианте. Существуют опции GROUP BY CUBE, GROUP BY ROLLUP, используются множества группирования (GROUPING SETS) и т.д. Мы не изучали фразу WITH, позволяющую вынести подзапросы в отдельную секцию помещаемую перед фразой SELECT. Многомерная модель нами только намечена.
Тем не менее, предложенный подход к написанию запросов распространяется на весь язык SQL.
8.14 На каких моделях построен SQL
Вернёмся к первому эпиграфу восьмой главы и постараемся понять, так ли достойны сожаления отступления SQL от реляционной модели.
Шаблон запроса, в точности соответствующего запросу исчисления на кортежах, и не содержащего никаких функций, рассмотрен в разделе 8.13.1. Даже используя результат запроса как новую таблицу, получаем слишком узкий класс запросов. Так что мир исчисления на кортежах вплетён в табличный мир, в котором можно включать в запрос процедурные элементы, использовать регулярные выражения, упорядочивать строки, группировать их, вычисляя агрегатные функции. Можно использовать аналитические функции, в таблицах можно хранить какие-то структуры данных, можно работать с широким классом подзапросов, не выразимых в реляционном мире, и т.д.
Мы прикоснулись к части мира многомерных данных (в разделе 8.12), сплетённого с табличным миром и поняли принципиальную возможность создания других подобных миров.
В заключительной "Семантика баз данных" мы погрузим табличный мир в мир семантических баз данных, в котором существенно расширена допустимая семантика, и покажем, что в табличной и даже реляционной модели можно организовать дедуктивную систему.
Так что не стоит жалеть об упущенном реляционном счастье.
Что же касается дальнейших расширений, то мы подозреваем их пришествие, но по совету Яджнявалкьи из Брихадараньяка-упанишады не говорим о них слишком много.