Создание сложных запросов на выборку

SQL используется большинством систем управления реляционными базами данных (СУБД) для управления табличными данными. Это фундаментальный навык, которого ожидают от специалистов по обработке данных и аналитиков.

Вы можете возразить, что это работа инженера по данным, но роли специалистов по анализу данных имеют тенденцию быть полными. Кроме того, как специалист по анализу данных, вы не захотите зависеть от инженера по обработке данных при извлечении данных из базы данных.

Короче говоря, наличие SQL в вашем наборе навыков позволит вам на шаг впереди в поиске и обеспечении работы в области науки о данных.

В этой статье мы рассмотрим 6 примеров избранных запросов. Хотя большая часть синтаксиса SQL одинакова в разных системах управления реляционными базами данных, могут быть некоторые небольшие различия. В этой статье мы будем использовать MySQL.

Пример 1

У нас есть следующая таблица клиентов.

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

mysql> select distinct(f_name) from customer
    -> where left(f_name,1) in ('a','e','i','o','u');
+--------+
| f_name |
+--------+
| Elaine |
| Adam   |
| Alisha |
+--------+

Мы используем функцию unique только для получения уникальных значений в столбце f_name. Предложение where применяет требуемое условие. В этом случае левая функция используется для доступа к первой букве. Мы хотим, чтобы первая буква была в предоставленном списке значений, которые являются гласными.

Пример 2

В приведенной выше таблице клиентов мы хотим получить полное имя (f_name и l_name) и идентификационные номера клиентов на основе следующих двух условий:

  • Полное имя длиннее 10 символов
  • Идентификационный номер четный
mysql> select cust_id, concat(f_name, ' ', l_name) as name
    -> from customer
    -> having char_length(name) > 10 and cust_id % 2 = 0;
+---------+-------------+
| cust_id | name        |
+---------+-------------+
|       4 | Adam Gelvin |
+---------+-------------+

Мы получаем новый столбец, объединяя имя и фамилию, и называем его именем. На этот новый столбец ставится одно из условий. Функция char_length подсчитывает количество символов. Второе условие определяет, четное ли число.

Пример 3

В таблице клиентов, приведенной в первом примере, мы хотим получить клиента с самым длинным полным именем.

mysql> select concat(f_name, ' ', l_name) as name 
    -> from customer
    -> having char_length(name) = (select 
    -> max(char_length(concat(f_name, ' ', l_name))) 
    -> from customer);
+--------------+
| name         |
+--------------+
| Elaine Smith |
+--------------+

Мы выбираем полное имя, объединяя имя и фамилию. Условие ставится на длину полного имени. Значение условия (максимальная длина) определяется дополнительным оператором выбора.

Пример 4

У нас есть следующие таблицы клиентов и покупок.

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

mysql> select customer.f_name, 
    -> sum(purchase.item_qty) as total_item_count 
    -> from customer join purchase 
    -> on customer.cust_id = purchase.cust_id 
    -> group by customer.f_name;
+--------+------------------+
| f_name | total_item_count |
+--------+------------------+
| Adam   |                4 |
| Alisha |                1 |
| Elaine |                6 |
| Jane   |                4 |
| John   |                3 |
| Robert |                1 |
+--------+------------------+

Мы выбираем имя из таблицы клиентов и количество товара из таблицы закупок. Столбец идентификатора клиента - это то, что связывает эти две таблицы, поэтому мы используем его при объединении этих таблиц. Поскольку мы применяем агрегацию к количеству товаров, нам необходимо сгруппировать результаты по именам клиентов.

Пример 5

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

mysql> select distinct(concat(customer.f_name, ' ',   
    -> customer.l_name)) as name 
    -> from customer join purchase 
    -> on customer.cust_id = purchase.cust_id 
    -> where purchase.date = '2020-05-10';
+--------------+
| name         |
+--------------+
| Jane Doe     |
| John Doe     |
| Adam Gelvin  |
| Alisha T.    |
| Elaine Smith |
| Robert Sam   |
+--------------+

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

Пример 6

В таблице покупок мы хотим отсортировать магазины в порядке убывания на основе общего количества товаров, купленных 10 мая 2020 года.

mysql> select store_id, sum(item_qty) as total
    -> from purchase
    -> where date = '2020-05-10'
    -> group by store_id
    -> order by total desc;
+----------+-------+
| store_id | total |
+----------+-------+
|        2 |     9 |
|        1 |     6 |
|        3 |     1 |
+----------+-------+

Выбираем идентификатор магазина и сумму количества товаров. Условие ставится на дату. Количество товаров сгруппировано по номерам магазинов, и сумма рассчитывается для каждой группы. В последней строке результаты сортируются по сумме в порядке убывания.

Заключение

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

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

Спасибо за чтение. Пожалуйста, дайте мне знать, если у вас есть какие-либо отзывы.