Илья Леонидович Хохлов "Самоучитель. Курс SQL. Базы данных. ORACLE"

Ничего себе, сколько всего, скажешь ты, как это можно все запомнить и понять?! Каждый блок мы разберем по–отдельности и каждому уделим достаточно внимания!

Но и это еще не все. Есть еще одна возможность команды SELECT – это группировка.

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

Для того чтобы указать по одинаковым значениям в каком столбце необходимо данные группировать, нужно команду SELECT дополнить блоком GROUP BY (с англ. «группировать по») и затем написать имя столбца, по которому необходима группировка. И теперь все строки получаемого набора данных будут группироваться по одинаковому значению в этом столбце.

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

Для решения подобной задачи, вначале, из таблицы «Продаж» мы отберем все сделки за прошедший год. Для выборки данных, согласно этому условию, воспользуемся блоком WHERE. Итак, когда данные будут извлечены из таблицы, за нужный нам промежуток времени, мы можем увидеть, что ни один из единичных заказов не больше 500.000 р. То есть ни в одной полученной строке в столбце «Сумма сделки» значение не больше 500.000 р. Но, если сгруппировать полученные данные по каждому клиенту (иными словами строки с одинаковым значением Клиента слить в одну, подсчитав Сумму сделок по каждому клиенту), то может получиться, что некоторые клиенты, суммарно за год, хорошо превышают этот порог.

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

Например, все выбранные продажи «Клиенту А» сгруппировались в одну строку, подсчитав сумму продаж ему, а все продажи «Клиенту Б» в другую строку, также с итогом по нему. И так по каждому клиенту. Теперь мы видим итоги с суммами продаж за год с группировкой по клиентам. Так как «Клиент А», например, в течение года каждый месяц делал заказы на 100.000 р. Поэтому, после группировки всех сумм его заказов, мы получим 1.200.000 р (100.000 ? 12 месяцев).

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

Место слова GROUP BY в предложении SELECT:

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

HAVING

» блока

GROUP

BY

.

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

На собеседованиях часто можно встретить такой вопрос: в чем разница между WHERE и HAVING? И теперь мы знаем ответ: WHERE выполняет первичный отбор данных из таблицы (таблиц) (в нашем примере, мы сначала отобрали данные продаж только за прошедший год), а HAVING отсеивает уже на основе сгруппированной информации.

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

Конечно, мы можем отбирать строчки из таблицы заказов тех, где «Сумма сделки» больше, например, определенной. Но у нас задача была другая. Нам необходимо было получить клиентов, сумма заказов за год которых превысила 500.000 р. Поэтому мы применили сначала WHERE, для первичного отбора строчек данных из таблицы «Заказов» тех, которые относятся к прошедшему году, и затем воспользовались группировкой GROUP BY по клиентам с подсчетом «Сумм сделок» по каждому их них с опцией HAVING, чтобы на основе сгруппированной (агрегированной, то есть обобщенной) информации сделать еще одну фильтрацию данных.

HAVING следует писать после GROUP BY:

И это уже полная структура одного предложения SELECT. Полный список ключевых слов, которые можно применять при выборке данных. Из всех перечисленных ключевых слов обязательными являются только SELECT и FROM. Запросы могут быть даже без WHERE и без сортировки – ORBER BY. Главное, что всегда нужно указывать, – это какие столбцы отбирать и откуда.

Конечно, мы будем применять еще и кейсы, и подзапросы, но это все будет строиться на основе структуры, которая приведена выше. Поэтому, ее нужно запомнить.

Для Гуру: в СУБД MS SQL Server и MySQL даже FROM не обязателен при выводе данных, но это исключение и применяется при решении специфических задач. Объясню тебе про это на уроке про псевдотаблиц.

4. Написание простых запросов получения данных

4.1. Выборка некоторых или всех столбцов из таблицы

Дорогой читатель, в начале следующей главы мы разберем как установить ORACLE и создать базу данных на своем компьютере. А также, мы познакомимся с программой SQL Developer, одним из самых распространенных средств работы с базами данных ORACLE. Пройдя по ссылке, ты скачаешь скрипт и загрузишь его в свою новую пока пустую базу данных. После прогрузки скрипта, у тебя появятся таблицы с тестовыми (учебными) данными. И все это за несколько простых шагов!

Теперь у тебя будет фактически подготовленное рабочее (учебное) место!

В конце каждой главы для тебя подготовлены практические задачи! Их нужно постараться сделать максимально самостоятельно. К некоторым задачам будут даваться рекомендации к выполнению, к некоторым – нет. Это значит, что их можно будет решить любым способом. Некоторые задачи можно будет решить только комбинацией методов. Большинство задач – это стандартные задачи, которые решат специалисты по SQL, а некоторые – нестандартные. С помощью них, ты научишься нестандартно и более глубоко понимать SQL. Если ты в течение часа не смог решить некоторую задачу, ее можно отложить и попробовать вернуться к ней, например, попозже или завтра! Многие мои ученики иногда так справлялись с достаточно трудными запросами, и, на второй день, почти всегда говорили, что смогли взглянуть на задачу под другим углом.

После списка задач к каждой главе ты найдешь решения к задачам. Мы подробно вместе прорешаем каждую задачу. Но не нужно этим пользоваться сразу, если у тебя не получается решить задачу. Желательно, ответами пользоваться минимум завтра. Ты получишь больше пользы, если сможете решить задачу сам, пусть и дольше.

В предыдущей главе мы рассмотрели общую структуру любого предложения SELECT. Работая постоянно с запросами, через довольно короткое время, мы запомним назначение и расположение каждого блока и еще чуть позже, будем правильно и максимально эффективно их использовать! Теперь, в качестве примеров, составим несколько простых SQL–запросов.

Напишем запрос, выбирающий сотрудников из таблицы Persons, который отображал бы их Фамилию Имя Отчество, Дату рождения и идентификатор филиала, в котором они работают. Фамилия Имя Отчество лежит в колонке NAME, Дата рождения – в колонке BIRTHDATE и идентификатор филиала – в графе FilialID:

Выполняем запрос и получаем результат:

Как видим, вывелись именно запрошенные столбцы из таблицы. И еще, первый столбец, – сквозная нумерация строк возвращаемых данных. Мы его не запрашивали нашей SQL–командой. И на самом деле, это не ORACLE нам его вернул вместе с возвращаемыми запросом данными, а сама программа через которою мы работаем в базе данных (в нашем случае программа SQL Developer) добавила нам его для удобства. Далее не будем обращать на него внимание.

Запрос вернул 21 запись (21 строку). В целях экономии места в книге, мы иногда будем отображать не все возвращаемые данные.

4.2. Использование условий при получении данных. Ключевое слово WHERE

Теперь доработаем запрос, пусть он выведет только сотрудников, где в графе FilialID равно 2:

Как видим из получаемых данных, во втором филиале у нас работает 4 сотрудника!

СУБД ORACLE, выполняя команду SELECT, выбирает для нас такие строчки из всей таблицы Persons, где в колонке FilialID значение равно двум!

Какие символы, помимо знака равно, можно использовать в условиях:

Как мы видим, если в SQL запросе нужно выбрать данные с условием на неравенство, то мы можем написать как <>, так и !=.

Следующей командой выберем сотрудников, работающих не во втором филиале:

Выведутся данные (всего 17 строк, для экономии места вот первых 10):

4.3. Сортировка данных. Блок ORDER BY

Теперь полученные данные мы можем еще и упорядочить по Фамилии Имени Отчеству. Для этого допишем блок ORDER BY.

В блоке ORDER BY (с англ. «упорядочить по») указали графу NAME, так как согласно значению в этом столбце нам необходимо было упорядочить строки. В результате получаем следующую таблицу с данными:

Как видим, строки упорядочены (отсортированы) по Фамилии Имени Отчеству. Точно также, если нам нужно было бы расставить сотрудников не в алфавитном порядке согласно их ФИО, а, например, согласно их дате рождения, то в боке ORDER BY указали бы BIRTHDATE.

Всякий раз, указывая значение столбца, по которому ведется сортировка строк, мы можем сортировать как в прямом порядке, так и в обратном. Для сортировки строк в обратном порядке нужно сразу после имени столбца написать слово DESC. И все!

Выведем сотрудников третьего филиала, упорядоченных по Фамилии Имени Отчеству в обратном порядке:

Получаем: