Знайте эти проблемы в деталях и разбирайтесь в подходах к их легкому решению.
Отличные навыки работы с SQL являются преимуществом для любого Data Scientist. Как изучение новых концепций, так и их тщательный пересмотр могут быть достигнуты путем выбора и решения некоторых общих проблем, возникающих в предметной области.
В этой статье я расскажу о 5 таких проблемах, которые я выбрал из LeetCode, и опишу, что они хотят, и изложу подходы к их решению. С этими проблемами я также надеюсь охватить некоторые из основных концепций, которые вы должны знать (и пересмотреть), а также некоторые продвинутые подходы к построению запросов, которые могут быть полезны для запоминания при подготовке к собеседованию или просто для расширения ваших знаний SQL.
Начнем 👇
1. Объедините две таблицы
Вопрос. Вам даны две таблицы — Person и Address, и вас просят написать запрос SQL, чтобы сообщить имя, фамилию, город и штат каждого человека в таблице Person.
(Если адрес personId отсутствует в таблице Address, вместо этого укажите null.)
Your Output: +-----------+----------+---------------+----------+ | firstName | lastName | city | state | +-----------+----------+---------------+----------+ | Allen | Wang | Null | Null | | Bob | Alice | New York City | New York | +-----------+----------+---------------+----------+
Подходы:
Самый простой подход к решению — использование концепции JOINS. Однако вам может потребоваться решить, какое соединение с вами следует использовать здесь.
Помните — поскольку нам нужно сообщать значения столбцов города и штата как нулевые для строки, в которой их нет, мы должны интуитивно понять, что здесь требуется левое соединение.
Итак, выполнив простое левое соединение, мы можем составить в MySQL следующий запрос:
select a.firstName, a.lastName, b.city, b.state from Person a left join Address b on a.personId = b.personId;
Обратите внимание, как нам нужно сопоставить внешний ключ из таблицы Address с таблицей Person, чтобы получить столбцы города и штата каждого человека.
2. Клиенты, которые никогда не заказывают
Вопрос. Рассматриваются две таблицы — Customers и Orders. Вам нужно написать SQL-запрос, чтобы сообщить обо всех клиентах, которые никогда ничего не заказывали.
Your output: +-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
Подходы:
№ 1: использование theWHERE NOT EXISTSclause:
Во-первых, в качестве подзапроса мы хотим выбрать все строки из таблицы «Заказы», в которых идентификаторы клиентов из таблицы «Клиенты» совпадают с идентификаторами клиентов из таблицы «Заказы». Это означает, что — мы отбираем всех клиентов, которые сделали заказ хотя бы один раз.
Теперь в основном запросе мы можем просто не выбирать этих клиентов (строки) для создания выходных данных. Это делается с помощью предложения NOT EXISTS.
select c.name as 'Customers'
from Customers c
where not exists
(
select null
from Orders o
where c.id = o.customerId
);
Нет 2: использование theNOT IN keywords:
Существует, хотя и более медленный способ сделать то же самое, в котором мы можем легко выбрать все идентификаторы клиентов из таблицы «Заказы» и выбрать всех клиентов из таблицы «Клиенты», которых нет в результате подзапроса.
select c.name as 'Customers'
from Customers c
where c.id not in
(
select customerId from Orders
);
3. Вторая по величине зарплата
Вопрос.Нам нужно найти вторую по величине зарплату для таблицы Employee. Если нет второй по величине зарплаты, запрос должен вывести null.
Your output: +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
Подходы:
№1. Самый простой способ решить эту проблему — использовать ключевые слова limit и offset.
Мы выберем все различные зарплаты из таблицы, упорядочим их в порядке убывания и ограничим вывод на 1, чтобы получить только 1 зарплату, и, наконец, мы сместим убывающий порядок на 1, что означает, что мы выбираем не самую высокую, а вторую по величине зарплату. Что-то вроде этого:
select distinct e.salary as SecondHighestSalary from Employee e order by e.salary desc limit 1 offset 1
№ 2.Еще один способ решить эту проблему — использовать функцию Max() из SQL.
В подзапросе мы выбираем максимальную зарплату из таблицы Employee. Наконец, мы выбираем максимальную заработную плату из вывода этого подзапроса и представляем ее как окончательный вывод, который теперь является второй по величине заработной платой.
select max(e.salary) as SecondHighestSalary from Employee e where e.salary < (select max(salary) from Employee );
4. Дублирование писем
Вопрос.Нас просят найти все повторяющиеся электронные письма, указанные в таблице Person.
Ссылка на проблему.
Your output: +---------+ | Email | +---------+ | [email protected] | +---------+
Подходы:
Мы можем подойти к этой проблеме, убедившись, что первое, что мы понимаем, это то, что для выбора дубликатов наше количество электронных писем любого типа должно быть больше 1. Сказав это, мы хотим представить только электронные письма в качестве вывода, и лучший способ сделать это — использовать ключевые слова «Group by» и «Having».
select p.email from Person p group by email having count(p.email) > 1;
5. Повышение температуры
Вопрос. Мы хотим написать запрос SQL, чтобы найти все даты с более высокой температурой по сравнению с предыдущими датами, учитывая таблицу Weather.
Your output: +----+ | id | +----+ | 2 | | 4 | +----+
Подходы:
Суть проблемы заключается в том, чтобы убедиться, что соседние даты не совпадают с датами с возрастающей температурой. Итак, чтобы решить эту проблему, мы можем использовать функцию Datediff(date1, date2).
Обратите внимание, что нам нужно показать только более высокий dateId в нашей выходной таблице? Таким образом, в нашем запросе мы обязательно выберем только дату с более высокой температурой, например, w2, как мы делаем это здесь:
select w2.id from Weather w1, Weather w2 where w2.temperature > w1.temperature and datediff(w2.recordDate, w1.recordDate) = 1;
Затем мы хотим сравнить температуру w2 с экземпляром, который находится непосредственно перед ним, поэтому мы берем для этого другую переменную w1. Затем мы применяем два Where условия: во-первых, мы убеждаемся, что температура w2 выше, чем температура w1. А во-вторых, мы следим за тем, чтобы разница в датах составляла всего 1 день. Итак, мы успешно построили наш запрос!
Несколько напутственных слов…
Вот и все — список из 5 проблем, о которых очень важно знать, и я надеюсь, что он помог вам понять или пересмотреть некоторые основные концепции написания лучших запросов для решения проблем.
В будущем я вернусь и напишу еще несколько статей по SQL. Так что следуй за мной и будь в курсе!
Я также рекомендую стать участником Medium, чтобы не пропустить ни одной статьи по науке о данных, которую я публикую каждую неделю. Присоединяйтесь сюда 👇
Присоединиться!
Подпишитесь на меня в Твиттере.
Проверьте полный репозиторий кода всех моих сообщений о науке о данных!
Еще пара моих статей, которые могут быть вам интересны:
