Я много лет страстно увлечен базами данных и программированием на языке SQL. Поверьте, это просто и увлекательно, к тому же высокооплачиваемое умение.
Отвлекитесь ненадолго и полистайте мои практические беседы без теоретической шелухи. Пусть вас не удивляет нумерация разделов - я постарался изложить материал в наиболее полезной последовательности.
Содержание беседы #3:
Тема 2. Язык структурированных запросов
Раздел 2.1 Понятие Стандарта ANSI-SQL
Пункт 2.1.2 Основные команды языка
(Роль оператора Not в логических условиях)
Раздел 2.2 Виды запроса (в том числе понятие «подзапроса»)
Тема 4. Анализ данных
Раздел 4.1. Соотнесение условий задачи с видом запроса
Роль оператора Not в логических условиях.
Когда нам нужно произвести сравнение на неравенство, то обычно достаточно использовать знак <>. Однако в некоторых случаях удобно или даже необходимо использовать логическое отрицание. Речь идет об одном из трех базовых, основных, классических операторов: And, Or, Not. Подробное рассмотрение свойств этих и других Булевых операторов – тема отдельной статьи по математической логике, поэтому здесь мы не будем вдаваться в подробности. Нам, хипстерам, это не к чему: мы же не знаем устройство микросхем смартфона, но прекрасно умеем им пользоваться.
Ниже приведены практические причины, в порядке предполагаемой частоты использования оператора Not на практике SQL аналитика или программиста:
1) При работе со списком, ведь когда весь список большой, а несколько объектов из него НЕ требуется искать, то проще перечислить именно их, а затем взять операцию логического отрицания:
CoLoR Not In (‘Чёрный’, ‘Белый’)
В результате вернется всё множество цветов, кроме двух указанных. При этом не требуется перечислять их все: ‘Красный’, ‘Желтый’, ‘Зеленый’…
2) При использовании оператора подзапроса Exists(…). Здесь, как и в предыдущем примере, мы выполняем отрицание сформированного подзапросом списка значений. Только не для того, чтобы меньше значений перечислять, а чтобы проще логически построить запрос.
Select …
From Table1
Where Not Exists(Select 1
From Table2
Where Table2.Column1 = Table1.Column1)
Обращаю внимание, что в подзапросах на проверку «существования» я специально ставлю «Select 1», вместо привычного «Select *», чтобы показать отсутствие логической нагрузки в подзапросе, ибо вся суть в данном случае в истинности или неистинности логических условий в разделе Where внутри подзапроса.
3) При отрицании условий, в которых задействован оператор Or. Здесь тот случай, когда использование Or не является необходимостью, но сильно упрощает понимание логики написанного запроса.
Вот запрос покупателя «хочу смартфон Самсунг, но чтобы был недорогой (меньше 10 тысяч рублей), ну или пусть хотя бы камера у него будет классная – на 8 мегапикселей»:
Where ModeL = ‘Samsung’ And ( ( PhotoCamMPs = 8 ) Or ( Price < 10000 ) )
Так как Самсунги все дешевые и из-за цены почти все они попадут в список, то нам хотелось бы отбросить только дорогие модели со слабыми камерами, убрать их с витрины, а все остальные модели предложить покупателю (в предыдущий запрос подставляем Not):
Where ModeL = ‘Samsung’ And Not ( ( PhotoCamMPs = 8 ) Or ( Price < 10000 ) )
Вот эти модели нам нужно убрать, а вовсе не браться за написание нового запроса:
Where ModeL = ‘Samsung’ And ( ( PhotoCamMPs < 8 ) Or ( Price >= 10000 ) )
Последние два запроса логически вернут одинаковый результат.
4) В отрицании Is Null
Выбрать всех клиентов, у которых еще не указан ИНН:
Where INN is Null
Where INN = Null писать неправильно!
Выбрать всех клиентов, с заполненным полем ИНН
Where INN is Not Null
Where INN <> Null писать неправильно!
Нужно понимать, что Not – это часть алгебры логики (прошу прощения за повторное упоминания её в суе). Применяться может только внутри условий раздела Where и в более редких случаях в разделе Having. Синтаксически верно также использовать его в разделе From, если потребуется связать таблицы каким-то хитрым способом, но семантически и с точки зрения оптимизации – это будет неверно и неэффективно! Итак, Not только внутри Where и иногда в Having.
Виды запроса (в том числе понятие «подзапроса»).
Прежде, чем рассмотреть основные внешние виды запросов необходимо сделать целый ряд замечаний и пояснений. Не спешите с бросанием в меня тухлых помидоров - я сконцентрировал знания о подзапросах всего в нескольких абзацах.
Теперь, когда мы знаем, что структура команды именуемой запрос SQL логически разбита на несколько разделов, может показаться, что все запросы должны походить один на другой, как две капли воды, только количество таблиц и логических условий будет разные. Однако это не так. Многообразие внешнего вида запросов на SQL поражает. Уже два десятилетия ведутся работы по созданию автоматических систем по генерации SQL запросов на основании анализа постановки задачи на человеческом языке. В общем виде задача не решена. Есть две стороны причины: семантическая и структурно-логическая.
Семантическая причина кроется в неоднозначности формулирования заказчиком, постановщиком задачи и даже аналитиком условий и характера поиска. Всегда требуется внимание и понимание программиста конечного результата запроса.
Структурно-логическая причина заключается в возможности большой вложенности логических условий друг в друга: сложный подзапрос с помощью Exists() превращается в часть запроса верхнего уровня, который может стать подзапросом внутри From или Where или Having в запросе еще большего уровня. Дополнительный оператор проверки альтернатив
Case When <логическое условие> Then … Else … End
может быть вставлен В ЛЮБОЙ раздел запроса. При этом внутри логических условий может также находиться сложный подзапрос.
Чтобы научиться строить различные запросы на заданную задачу, сначала необходимо понять что можно, а что нет. Т.е. где пределы возможностей.
Важно для понимания. Фактически, высокая сложность запроса может говорить о том, что задачу необходимо решать по-другому, иными средствами, языками, методами. Однако в реальности оказывается, что: во-первых аналитик может не обладать широким перечнем методов и языков программирования; во-вторых время на решение задачи может быть весьма коротким, вплоть до нескольких минут, а за такое время много технологических операций не произвести. Поэтому приходится решать задачу только с помощью SQL.
Поверьте, после того как однажды вы, не напрягаясь, достанете вашему руководителю бесценные и срочно ожидаемые данные из базы за 1-2 минуты, он разрешит вам кататься по офису на лонгборде.
Техническая особенность языка: лучше не применять более чем тройной вложенности как в разделе Where, так и в разделе From – это может привести к очень медленному исполнению запроса, а на версии сервера MS SQL Server 2000 (кое где используемого и поныне) это даже приводит к логическим ошибкам и сообщениям о невозможности эффективно исполнить запрос из-за его логической сложности.
Еще будет полезно дать описание термина Подзапрос – это обычный запрос любого вида, начинающийся на «Select», но который окружен скобками «(» и «)» и вставлен в другой запрос:
· вместо таблицы ( «(Select *…)» или «(Select Column1, Column2, …)» )
· вместо списка значений ( «(Select Column1 From…)» )
· или вместо константы ( «(Select max(Column1) From … )» )
Теперь суть. Опираясь на свой пятнадцатилетний опыт написания запросов, я составил некую классификацию наиболее часто используемых видов запросов (приведены в порядке от наиболее часто используемых к наименее):
1) Типичный вид запроса, без подзапросов.
Select …
From …
Where …
2) Подзапрос используется в разделе Where.
Select …
From …
Where Exists(Select…
From…
Where…
)
3) Вид запроса с простейшей группировкой (агрегат: max, min, count, sum, avg).
Select …, <агрегат>
From …
Where …
Group By …
4) Подзапрос используется в разделе From.
Select …
From … (Select…
From…
Where…
) as …
Where …
5) Подзапрос используется в разделе Select.
Select …, (Select…
From…
Where…
)
From …
Where …
6) Подзапрос используется в разделе Having.
Select …
From …
Where …
Group By …
Having… (Select <агрегат>
From …
Where …
)
Если попытаться сделать прогноз, оценивая опыт написания запросов, то можно сказать, что для решения рабочих задач: в 50% задач из 100% вы будете писать запрос первого вида; еще в 30% задач – запрос второго вида; в 10% задач – запрос третьего вида, в оставшихся 10% задач примените один из более редких видов.
Соотнесение условий задачи с видом запроса.
Выше я подарил вам замок, а теперь дарю от него ключ.
В реальной работе приходится анализировать слабо структурированные запросы от заказчиков и руководства. Возникает нетривиальный вопрос: какой вид запроса в каком случае применять?
Это одна из первых ступенек в профессиональном вопросе анализа данных. Поскольку сначала нужно проанализировать саму постановку задачи.
Попытаемся соотнести ключевые слова из словесной постановки задачи с типами запросов, рассмотренных в Теме 2, Разделе 2.2.
Для начала нам понадобится создать некий словарик терминов для анализа входящего запроса. Вы можете работать в любой предметной области, с любыми объектами от торговли хлебом до ракетной промышленности, но суть запросов не изменится, если запросить количество произведенного хлеба или ракет за период.
Объект, который является основной сущностью (булка хлеба или ракета) и, скорее всего, выделен в отдельную таблицу с говорящим названием будем называть Предметом. Если необходимо посчитать только хлеб 1 сорта или только ядерные ракеты, нам понадобится термин Характеристика. Оперирование характеристиками сводится к составлению списка логических условий (те, что стоят в разделе Where). Их сложность на начальном этапе нам не важна, поэтому мы можем назвать их – Условие.
Как правило, в результате запроса мы должны: либо предоставить список, т.е. Выбрать данные, либо сообщить некие числа (количества, максимальные или другие типы значений), т.е. Посчитать данные.
Итак, словарик терминов, подобие которого есть в голове у каждого SQL-аналитика:
Предмет – основной объект, о котором идет речь в задаче.
Характеристика – любое поле таблицы, в которой хранятся данные о предмете.
Условие – набор любых логических условий.
Выбрать – вывести списком, без сокращения набора строк агрегатом.
Посчитать – использовать агрегат.
Теперь мы можем соотнести виды запроса с их словесным описанием:
1) Выбрать Предметы, где Характеристика №1 такая, а Характеристика №2 такая.
2) Выбрать Предметы №1, где Характеристика №1 такая, но при этом не существует Условие с Предметом №2, где Характеристика №2 у Предмета №2 такая.
3) Для каждого Предмета Посчитать что-то (с помощью агрегата).
4) Выбрать Предметы №1, где Характеристика №1 такая, а также для каждого предмета Выбрать Предметы №2, где Характеристика №2 предмета №2 такая.
5) Выбрать Предметы №1 и их дополнительные Характеристики из Предмета №2 (возможно, эти Характеристики будут получены из агрегата).
6) Только для тех Предметов №1, которые подходят под Условия, которые Посчитаны из Предметов №2, Посчитать что-то (с помощью агрегата).
Если вы усвоили третью беседу, то можете смело общаться о предметной области на одном языке с любым народом в IT-компании - от заказчиков и бизнес-аналитиков до директора и ведущих программистов хипстеров. Хотите большего - читайте следующие беседы.
Спасибо, что дочитали до конца! Любите друг друга... и базы данных)
Автор: Щукин Андрей (vk.com/faustonly), Санкт-Петербург. Дата публикации: 22.02.2018
