Порядок выполнения SQL-операций
Мы привыкли, что компьютер выполняет команды программиста последовательно, в том порядке, который указал автор кода. Однако SQL относится к декларативным языкам, то есть SQL-запрос описывает ожидаемый результат, а не способ его получения.
Давайте разберём, в какой последовательности выполняются шесть операций в SQL: SELECT, FROM, WHERE, GROUP BY, HAVING и ORDER BY.
База данных выполняет команды в строгой очерёдности, о которой полезно знать любому разработчику. Залог оптимального запроса тот же, что и залог успеха в приготовлении вкусного блюда: важно знать не только ингредиенты, но и когда каждый из них должен попасть в блюдо. Если база данных отойдет от стандартного сценария выполнения команд, то ее производительность может сильно пострадать.
База данных сотрудников
В этой статье мы поработаем с типичной базой сотрудников, относящихся к разным отделам. По каждому сотруднику известны его ID, имя, фамилия, зарплата и отдел:
| EMPLOYEE_ID | FIRST_NAME | LAST_NAME | SALARY | DEPARTMENT |
|---|---|---|---|---|
| 100 | James | Smith | 78,000 | ACCOUNTING |
| 101 | Mary | Sexton | 82,000 | IT |
| 102 | Chun | Yen | 80,500 | ACCOUNTING |
| 103 | Agnes | Miller | 95,000 | IT |
| 104 | Dmitry | Komer | 120,000 | SALES |
EMPLOYEE
5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях
Хотя составление SQL-запросов — это не самое интересное в работе дата-сайентистов, хорошее понимание SQL чрезвычайно важно для того, кто хочет преуспеть в любом занятии, связанном с обработкой данных. Дело тут в том, что SQL — это не только SELECT , FROM и WHERE . Чем больше SQL-конструкций знает специалист — тем легче ему будет создавать запросы на получение из баз данных всего, что ему может понадобиться.
Автор статьи, перевод которой мы сегодня публикуем, говорит, что она направлена на решение двух задач:
- Изучение механизмов, которые выходят за пределы базового знания SQL.
- Рассмотрение нескольких практических задач по работе с SQL.
Вопрос №1: второе место по зарплате
Напишите SQL-запрос для получения из таблицы со сведениями о заработной плате сотрудников ( Employee ) записи, содержащей вторую по размеру заработную плату.
Например, такой запрос, выполненный для таблицы, представленной ниже, должен вернуть 200 . Если в таблице нет значения, меньшего, чем самая высокая зарплата — запрос должен вернуть null .
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+▍Решение А: использование IFNULL и OFFSET
Вот основные механизмы, которые будут использованы в данном варианте решения задачи:
- IFNULL(expression, alt) : эта функция возвращает свой аргумент expression в том случае, если он не равен null . В противном случае возвращается аргумент alt . Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.
- OFFSET : этот оператор используется с выражением ORDER BY для того чтобы отбросить первые n строк. Это нам пригодится по той причине, что нас интересует вторая строка результата (то есть — вторая по величине зарплата, данные о которой есть в таблице).
SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 ), null) as SecondHighestSalary FROM Employee LIMIT 1▍Решение B: использование MAX
В запросе, представленном ниже, используется функция MAX . Здесь выбирается самое большое значение заработной платы, не равное максимальной заработной плате, полученной по всей таблице. В результате мы и получаем то, что нам нужно — вторую по величине заработную плату.
SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary != (SELECT MAX(salary) FROM Employee)Вопрос №2: дублирующиеся адреса электронной почты
Напишите SQL-запрос, который обнаружит в таблице Person все дублирующиеся адреса электронной почты.
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+▍Решение А: COUNT в подзапросе
Сначала мы создаём подзапрос, в котором выясняется частота появления каждого адреса в таблице. Затем результат, возвращаемый подзапросом, фильтруется с использованием инструкции WHERE count > 1 . Запрос вернёт сведения об адресах, встречающихся в исходной таблице больше одного раза.
SELECT Email FROM ( SELECT Email, count(Email) AS count FROM Person GROUP BY Email ) as email_count WHERE count > 1▍Решение B: выражение HAVING
- HAVING : это выражение, которое позволяет использовать инструкцию WHERE вместе с выражением GROUP BY .
SELECT Email FROM Person GROUP BY Email HAVING count(Email) > 1Вопрос №3: растущая температура
Напишите SQL-запрос, который находит в таблице Weather все даты (идентификаторы дат), когда температура была бы выше температуры на предшествующие им даты. То есть, нас интересуют даты, в которые «сегодняшняя» температура выше «вчерашней».
+---------+------------------+------------------+ | Id(INT) | RecordDate(DATE) | Temperature(INT) | +---------+------------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------------+------------------+▍Решение: DATEDIFF
- DATEDIFF : эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.
SELECT DISTINCT a.Id FROM Weather a, Weather b WHERE a.Temperature > b.Temperature AND DATEDIFF(a.Recorddate, b.Recorddate) = 1Вопрос №4: самая высокая зарплата в подразделении
В таблице Employee хранятся сведения о сотрудниках компании. В каждой записи этой таблицы содержатся сведения об идентификаторе ( Id ) сотрудника, о его имени ( Name ), о зарплате ( Salary ) и о подразделении компании, где он работает ( Department ).
+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+В таблице Department содержатся сведения о подразделениях компании.
+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+Напишите SQL-запрос, который находит в каждом из подразделений сотрудников с максимальной заработной платой. Например, для вышеприведённых таблиц подобный запрос должен возвращать результаты, представленные следующей таблицей (при этом порядок строк в таблице значения не имеет):
+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Jim | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+▍Решение: команда IN
Команда IN позволяет задавать в инструкции WHERE условия, соответствующие использованию нескольких команд OR . Например, две следующие конструкции идентичны:
WHERE country = ‘Canada’ OR country = ‘USA’ WHERE country IN (‘Canada’, ’USA’).Здесь мы хотим получить таблицу, содержащую название подразделения ( Department ), имя сотрудника ( Employee ) и его заработную плату ( Salary ). Для этого мы формируем таблицу, в которой содержатся сведения об идентификаторе подразделения ( DepartmentID ) и о максимальной зарплате по этому подразделению. Далее мы объединяем две таблицы по условию, в соответствии с которым записи в результирующую таблицу попадают только в том случае, если DepartmentID и Salary есть в ранее сформированной таблице.
SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary FROM Employee INNER JOIN Department ON Employee.DepartmentId = Department.Id WHERE (DepartmentId , Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId )Вопрос №5: пересаживание учеников
Мэри — учительница в средней школе. У неё есть таблица seat , хранящая имена учеников и сведениях об их местах в классе. Значение id в этой таблице постоянно возрастает. Мэри хочет поменять местами соседних учеников.
Вот таблица исходного размещения учеников:
+---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +---------+---------+Вот что должно получиться после пересаживания соседних учеников:
+---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +---------+---------+Напишите запрос, который позволит учительнице решить вышеописанную задачу.
Обратите внимание на то, что если количество учеников является нечётным — последнего ученика никуда пересаживать не надо.
▍Решение: использование оператора WHEN
SQL-конструкцию CASE WHEN THEN можно рассматривать как оператор if в программировании.
В нашем случае первый оператор WHEN используется для проверки того, назначен ли последней строке в таблице нечётный идентификатор. Если это так — строка не подвергается изменениям. Второй оператор WHEN отвечает за добавление 1 к каждому нечётному идентификатору (например — 1, 3, 5 превращается в 2, 4, 6) и за вычитание 1 из каждого чётного идентификатора (2, 4, 6 превращаются в 1, 3, 5).
SELECT CASE WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND MAX(id) FROM seat) THEN id WHEN id%2 = 1 THEN id + 1 ELSE id - 1 END AS id, student FROM seat ORDER BY idИтоги
Мы разобрали несколько задач по SQL, попутно обсудив некоторые продвинутые средства, которые можно использовать при составлении SQL-запросов. Надеемся, то, что вы сегодня узнали, пригодится вам при прохождении собеседований по SQL и окажется полезным в повседневной работе.
P.S. В нашем маркетплейсе есть Docker-образ с SQL Server Express, который устанавливается в один клик. Вы можете проверить работу контейнеров на VPS. Всем новым клиентам бесплатно предоставляются 3 дня для тестирования.
Уважаемые читатели! Что вы можете посоветовать тем, кто хочет освоить искусство создания SQL-запросов?
- Блог компании RUVDS.com
- Занимательные задачки
- SQL
- Карьера в IT-индустрии
SQL запросы быстро. Часть 1
Язык SQL очень прочно влился в жизнь бизнес-аналитиков и требования к кандидатам благодаря простоте, удобству и распространенности. Из собственного опыта могу сказать, что наиболее часто SQL используется для формирования выгрузок, витрин (с последующим построением отчетов на основе этих витрин) и администрирования баз данных. И поскольку повседневная работа аналитика неизбежно связана с выгрузками данных и витринами, навык написания SQL запросов может стать фактором, из-за которого кандидат или получит преимущество, или будет отсеян. Печальная новость в том, что не каждый может рассчитывать получить его на студенческой скамье. Хорошая новость в том, что в изучении SQL нет ничего сложного, это быстро, а синтаксис запросов прост и понятен. Особенно это касается тех, кому уже доводилось сталкиваться с более сложными языками.
Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join’ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.
Практика
Введение в синтаксис будет рассмотрено на примере открытой базы данных, предназначенной специально для практики SQL. Чтобы твое обучение прошло максимально эффективно, открой ссылку ниже в новой вкладке и сразу запускай приведенные примеры, это позволит тебе лучше закрепить материал и самостоятельно поработать с синтаксисом.
Кликнуть здесь
После перехода по ссылке можно будет увидеть сам редактор запросов и вывод данных в центральной части экрана, список таблиц базы данных находится в правой части.
Структура sql-запросов
Общая структура запроса выглядит следующим образом:
SELECT ('столбцы или * для выбора всех столбцов; обязательно') FROM ('таблица; обязательно') WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно') GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно') HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно') ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS’ом.
SELECT, FROM
SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.
Выбрать все (обозначается как *) из таблицы Customers:
SELECT * FROM CustomersВыбрать столбцы CustomerID, CustomerName из таблицы Customers:
SELECT CustomerID, CustomerName FROM CustomersWHERE
WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.
Фильтрация по одному условию и одному значению:
select * from Customers WHERE City = 'London'Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):
select * from Customers where City IN ('London', 'Berlin')select * from Customers where City NOT IN ('Madrid', 'Berlin','Bern')Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:
select * from Customers where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15select * from Customers where City in ('London', 'Berlin') OR CustomerID > 4GROUP BY
GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).
При использовании GROUP BY обязательно:
- перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
- агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.
select City, count(CustomerID) from Customers GROUP BY CityГруппировка количества клиентов по стране и городу:
select Country, City, count(CustomerID) from Customers GROUP BY Country, CityГруппировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:
select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails GROUP BY ProductID
Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:
select City, count(CustomerID) from Customers WHERE Country = 'Germany' GROUP BY City
Переименование столбца с агрегацией с помощью оператора AS. По умолчанию название столбца с агрегацией равно примененной агрегатной функции, что далее может быть не очень удобно для восприятия.
select City, count(CustomerID) AS Number_of_clients from Customers group by CityHAVING
HAVING — необязательный элемент запроса, который отвечает за фильтрацию на уровне сгруппированных данных (по сути, WHERE, но только на уровень выше).
Фильтрация агрегированной таблицы с количеством клиентов по городам, в данном случае оставляем в выгрузке только те города, в которых не менее 5 клиентов:
select City, count(CustomerID) from Customers group by City HAVING count(CustomerID) >= 5
В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:
select City, count(CustomerID) as number_of_clients from Customers group by City HAVING number_of_clients >= 5
Пример запроса, содержащего WHERE и HAVING. В данном запросе сначала фильтруется исходная таблица по пользователям, рассчитывается количество клиентов по городам и остаются только те города, где количество клиентов не менее 5:
select City, count(CustomerID) as number_of_clients from Customers WHERE CustomerName not in ('Around the Horn','Drachenblut Delikatessend') group by City HAVING number_of_clients >= 5
ORDER BY
ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.
Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:
select * from Customers ORDER BY City
Осуществлять сортировку можно и по нескольким столбцам, в этом случае сортировка происходит по порядку указанных столбцов:
select * from Customers ORDER BY Country, City
По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:
select * from Customers order by CustomerID DESC
Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:
select * from Customers order by Country DESC, CityJOIN
JOIN — необязательный элемент, используется для объединения таблиц по ключу, который присутствует в обеих таблицах. Перед ключом ставится оператор ON.
Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:
select * from Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerIDНередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,
select * from Orders join Customers on Orders.CustomerID = Customers.CustomerID where Customers.CustomerID >10
Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:
Другие типы JOIN’ов можно увидеть на замечательной картинке ниже:
В следующей части подробнее поговорим о типах JOIN’ов и вложенных запросах.
При возникновении вопросов/пожеланий, всегда прошу обращаться!
Порядок выполнения операторов sql запроса
Пожалуйста, дайте порядок выполнения операторов sql запроса или дайте ссылку, где прочитать об этом: что за чем выполняется. Например (с потолка):
SELECT t1.id AS id, t1.name AS name, count(*) AS cnt FROM tbl_name1 t1 INNER JOIN tbl_name2 t2 ON t1.id = t2.id WHERE t1.id > 10 GROUP BY t1.name HAVING cnt > 10 ORDER BY t1.id Сначала GROUP BY , потом? Последним ORDER BY . Нигде не могу найти нормальной информации об этом :((
Отслеживать
user177221
задан 13 авг 2011 в 6:19
466 1 1 золотой знак 4 4 серебряных знака 10 10 бронзовых знаков
4 ответа 4
Сортировка: Сброс на вариант по умолчанию
Для SQL Server смотри ссылку.
Логический порядок обработки инструкции SELECT:
- FROM
- ON
- JOIN
- where
- GROUP BY
- WITH CUBE или WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP