Category: Базы данных

28
Апр
2022

🗄️ ✔️ 10 лучших практик написания SQL-запросов

Делимся рекомендациями по решению распространенных проблем с SQL, с которыми часто сталкиваются специалисты по работе с данными.

Статья является переводом. Оригинал доступен по ссылке.

Я работаю с данными уже 3 года, и меня до сих пор удивляет, что есть люди, которые хотят стать аналитиками, практически не зная SQL. Хочу особо подчеркнуть, что SQL является фундаментальным языком независимо от того, кем вы будете работать в сфере анализа данных.

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

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

Для примеров я буду использовать базу данных SQLite: sql-practice.com

1. Проверка уникальных значений в таблице

        SELECT count(*), count(distinct patient_id) FROM patients

    

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

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

Однако становится немного сложнее, когда у вас есть несколько столбцов, которые создают первичный ключ. Чтобы решить эту проблему, просто объедините столбцы, составляющие первичный ключ, после ключевого слова DISTINCT. Простой пример — объединение имени и фамилии для создания первичного ключа.

        SELECT count(*), count(distinct first_name || last_name) FROM patients
    

2. Поиск повторяющихся записей

        SELECT 
    first_name 
    , count(*) as ct
    
FROM patients
GROUP BY
    first_name
HAVING
    count(*) > 1
ORDER BY 
    COUNT(*) DESC
;
    

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

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

Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

3. Обработка NULL с DISTINCT

        with new_table as (
select patient_id from patients
UNION
select null
)

select 
    count(*)
  , count(distinct patient_id)
  , count(patient_id) 

from new_table
    

Результатом запроса будет значение 4531 для столбца COUNT(*) и 4530 для двух оставшихся столбцов. Когда вы указываете столбец, ключевое слово COUNT исключает нулевые значения. Однако, при использовании звездочки в подсчет включаются значения NULL. Это может сбивать с толку при проверке, является ли столбец первичным ключом, поэтому я посчитал нужным упомянуть об этом.

4. CTE > Подзапросы

        -- Use of CTE
with combined_table as (
select
  *
 
FROM patients p
JOIN admissions a 
  on p.patient_id = a.patient_id
)

, name_most_admissions as (
select
    first_name || ' ' || last_name as full_name
  , count(*)                       as admission_ct
  
FROM combined_table
)

select * from name_most_admissions
;

-- Use of sub-queries :(
select * from 
   (select
        first_name || ' ' || last_name as full_name
      , count(*)                       as admission_ct
  
    FROM (select
             *
 
          FROM patients p
          JOIN admissions a 
              on p.patient_id = a.patient_id
          ) combined_table
    ) name_most_admissions
;
    

Когда я впервые начал работать аналитиком данных 3 года назад, я писал SQL-запросы с большим количеством подзапросов, чем это было необходимо. Я быстро понял, что это не приводит к читабельному коду. В большинстве ситуаций вы хотите использовать общее табличное выражение вместо подзапроса. Зарезервируйте подзапросы для однострочников, которые вы хотите использовать.

5. Использование SUM и CASE WHEN вместе

        select 
     sum(case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end) as allergies_burl
   , sum(case when allergies = 'Penicillin' and city = 'Oakville' then 1 else 0 end)   as allergies_oak
  
from patients
    

Предложение WHERE может работать, если вы хотите суммировать количество пациентов, отвечающих определенным условиям. Но если вы хотите проверить несколько условий, вы можете использовать ключевые слова SUM и CASE WHEN вместе. Это делает код лаконичным и легко читаемым.

Данную комбинацию также можно использовать в выражении WHERE, как в примере ниже.

        select
  * 
FROM patients
WHERE TRUE
  and 1 = (case when allergies = 'Penicillin' and city = 'Burlington' then 1 else 0 end)
    

6. Будьте осторожны с датами

        with new_table as (
select
    patient_id
  , first_name
  , last_name
  , time(birth_date, '+1 second') as birth_date

from patients
where TRUE
   and patient_id = 1

UNION
  
select
    patient_id
  , first_name
  , last_name
  , birth_date 

from patients
WHERE TRUE
  and patient_id != 1
)

select 
  birth_date 
  
from new_table 
where TRUE 
  and birth_date between '1953-12-05' and '1953-12-06'
    

В этой базе данных все даты сокращены до дня. Это означает, что все значения времени столбца Birthday_date в этом примере равны 00:00:00. Однако в реальных наборах данных это обычно не так.

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

В приведенном выше примере я искусственно добавил секунду к пациенту №1. Как видите, этой 1-й секунды было достаточно, чтобы исключить пациента из результатов при использовании ключевого слова BETWEEN.

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

7. Не забывайте об оконных функциях

        select
    p.*
  , MAX(weight) over (partition by city) as maxwt_by_city
   
 from patients p
    

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

Я видел, как некоторые аналитики пробовали обходные пути, когда оконная функция делала код короче и читабельнее и, скорее всего, также экономила им время.

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

8. По возможности избегайте DISTINCT

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

Это ошибка. Если вы не можете с самого начала объяснить, почему в данных есть дубликаты, возможно, вы исключили из своего анализа какую-то полезную информацию. Вы всегда должны быть в состоянии объяснить, почему вы помещаете distinct в таблицу и почему есть дубликаты. Использование WHERE обычно предпочтительнее, так как вы можете увидеть то, что исключается.

9. Форматирование SQL

Об этом сказано довольно много, но стоит повторить. Обязательно отформатируйте SQL. Лучше создать больше строк с хорошим форматированием, чем пытаться сжать весь код всего в несколько строк. Это позволит ускорить разработку.

Вы можете заметить, что в примерах я использовал TRUE в WHERE выражении. Это было сделано для того, чтобы все аргументы в выражении WHERE начинались с AND. Таким образом, аргументы начинаются с одного и того же места.

Еще один быстрый совет — добавить запятые в начале столбца в выражении SELECT. Это позволяет легко найти пропущенные запятые, поскольку все они будут упорядочены.

10. Совет по отладке

Некоторые SQL-запросы могут быть очень сложными для отладки. Что мне больше всего помогло, когда я сталкивался с этим в прошлом, так это то, что я очень усердно документировал свои шаги.

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

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

***

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

Материалы по теме

14
Апр
2022

📜 Основные SQL-команды и запросы с примерами, которые должен знать каждый разработчик

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

Кратко об SQL

Хранить информацию в базах данных (БД) – обычная практика для среднего и крупного бизнеса. Такой подход позволяет при необходимости легко получить доступ к нужным сведениям. Чтобы работать с такими базами, необходимо изучить SQL — структурированный язык запросов, используемый для извлечения и обработки хранящейся информации.

Запросы языка и непосредственно сам SQL можно разделить на несколько категорий.

1. Язык определения данных

Язык определения данных или DDL позволяет создавать БД, описывать их структуру, а также устанавливать правила размещения в них данных.

Команды DDL:

  1. ALTER — добавить, изменить или удалить столбцы.
  2. COLLATE — оператор сопоставления.
  3. CREATE — создать таблицы.
  4. DROP — оператор удаления объектов из базы данных.
  5. DISABLE TRIGGER — отключить триггер.
  6. ENABLE TRIGGER — включить триггер.
  7. RENAME — переименовать.
  8. UPDATE STATISTICS — обновить статистику.

2. Язык обработки данных DML

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

Запросы DML:

  1. BULK INSERT — для загрузки данных из файла в таблицу.
  2. SELECT — оператор запроса, возвращающий нам необходимые сведения.
  3. DELETE — удаляет строки из таблиц.
  4. UPDATE — изменяет табличные данные.
  5. INSERT — вставляет новые записи.
  6. UPDATETEXT — обновляет текст.
  7. MERGE — смесь операторов UPDATE и INSERT.
  8. WRITETEXT — запись текста.
  9. READTEXT — чтение текста.

3. Язык управления данными или DCL

Язык управления данными или DCL позволяет настроить работу с базой данных путем установки необходимых разрешений и прав СУБД.

DCL -запросы:

  1. GRANT — управление доступом к базе данных.
  2. REVOKE — отмена выданных привилегий.
  3. DENY — отнимает у пользователя возможность выполнять действия.

4. Язык управления транзакциями TCL

Язык управления транзакциями TCL служит для управления изменениями, происходящими в результате DML-запросов, и объединяет эти DML-запросы в наборы транзакций.

TCL команды:

  1. BEGIN — определяем начало транзакции.
  2. COMMIT — фиксируем изменения.
  3. ROLLBACK — откатывает все изменения.
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

5. Основные команды SQL: Select

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

Давайте разберем по пунктам практические примеры применения таких запросов на примере созданной нами в БД таблицы Clients с информацией о покупателях условного магазина.

Cousomer_id First_name Last_name Age Country
1 Абрам Рабинович 31 Россия
2 Денис Петров 22 Россия
3 Иван Сидоров 22 Латвия
4 Денис Кузнецов 25 Латвия
5 Сара Рабинович 28 Беларусь

Итак, что же можно делать с загруженными в базу сведениями при помощи рассматриваемой команды?

1. Запрашивать фамилии и имена всех клиентов. Оператор FROM в этой конструкции указывает, из какой именно таблицы вы хотите ее (эту информацию) взять.

Например:

        SELECT first_name, last_name
FROM Clients;
    

2. Для выбора всех столбцов из таблицы используем символ *.

        SELECT *
FROM Clients;

    

3. Команда SELECT может иметь необязательный оператор WHERE, позволяющий извлекать записи, соответствующие определенным условиям. Здесь мы выбираем из таблицы всех клиентов по фамилии Рабинович:

        SELECT *
FROM Clients;
WHERE last_name = 'Рабинович';

    

4. Здесь с помощью Select мы хотим получить всех клиентов из России:

        SELECT age
FROM Clients;
WHERE country = 'Россия';

    
Примечание
В SQL мы должны заключать текстовые данные в одинарные или двойные кавычки.

5. Также для поиска достаточно часто используются условные операторы. В следующем примере мы извлекаем из таблицы всех клиентов, возраст которых превышает 25 лет:

        SELECT *
FROM Clients;
WHERE age > 25;

    

6. А тут мы выбираем всех клиентов, по фамилии Рабинович проживающих в России.

        SELECT *
FROM Clients;
WHERE last_name = Рабинович' AND country = 'Россия';

    

7. Здесь SELECT выведет результат, если все условия, разделенные оператором AND, будут истинны. В данном случае команда выберет имена всех клиентов по фамилии Петров из России:

        SELECT first_name
FROM Clients;
WHERE country = 'Россия' AND last_name = 'Петров';

    

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

8. Оператор SQL NOT выбирает строку, если заданное условие равно FALSE. Здесь мы выбираем имена и фамилии клиентов, проживающих во всех странах, кроме Латвии.

        SELECT first_name, last_name
FROM Clients;
WHERE NOT country = 'Латвия

    

9. Также можно комбинировать несколько операторов AND, OR и NOT в одном запросе. В этом примере мы хотим выбрать клиентов из России или Беларуси, возраст которых меньше 26 .

        SELECT *
FROM Clients;
WHERE (country = 'Россия' OR country = 'Беларусь') AND age < 26;

    

10. Оператор SQL SELECT DISTINCT выбирает уникальные, отличные от других, строки из таблицы. Здесь мы выводим неодинаковые страны проживания клиентов.

        SELECT DISTINCT country
FROM Clients;

    

11. А здесь мы выбираем неодинаковые комбинации параметров страна + имя.

        SELECT DISTINCT country, first_name
FROM Clients;

    

12. Если нам нужно подсчитать количество уникальных строк, мы можем использовать функцию COUNT() в совокупности с оператором DISTINCT. Здесь команда SELECT возвращает количество уникальных стран:

        SELECT COUNT(DISTINCT country)
FROM Clients;

    

13. Ключевое слово AS используется для присвоения столбцам или всей таблице временного имени, которое позже можно использовать для его или ее идентификации соответственно. Здесь переименовываем столбец first_name на name.

        SELECT first_name AS name
FROM Clients;

    

14. Оператор TOP используется для выбора фиксированного количества строк из базы данных. В примере ниже мы выбираем первые 2 строки из таблицы.

        SELECT TOP 2 *
FROM Clients;

    

15. Ключевое слово LIMIT используется со следующими системами баз данных: MySQL, PostgreSQL, SQLite. Здесь мы ограничиваем вывод имени и возраста клиента двумя первыми строками таблицы.

        SELECT first_name, age
FROM Clients;
LIMIT 2;

    

16. Оператор IN используется с предложением WHERE для сопоставления значений в списке. Здесь мы выбираем имена клиентов, проживающих либо в России, либо в Латвии.

        SELECT first_name, country
FROM Clients;
WHERE country IN ('Россия', 'Латвия');

    

17. Оператор IN также можно использовать для выбора строк с определенным значением. Здесь выбираются строки со значением Россия в поле country.

        SELECT first_name, country
FROM Clients;
WHERE 'Россия' in (country);

    

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

        SELECT *
FROM Clients;
ORDER BY first_name;

    

19. Чтобы явно отсортировать выбранные записи в порядке возрастания, используем ключевое слово ASC. В примере мы выбираем всех клиентов, а затем сортируем их в порядке возрастания по возрасту.

        SELECT *
FROM Clients;
ORDER BY age ASC;

    

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

20. Также мы можем использовать ORDER BY при работе с несколькими столбцами. В конструкции ниже мы выбираем все записи, а затем сортируем их по имени. Если имя повторяется более одного раза, SQL сортирует данные по возрасту.

        SELECT *
FROM Clients;
ORDER BY first_name, age;

    

21. Можем использовать ORDER BY вместе с ключевым словом WHERE. В примере мы сначала выбираем данные о фамилии и возрасте клиентов не из России. Затем выбранные записи сортируем в порядке убывания по фамилии:

        SELECT last_name, age
FROM Clients;
WHERE NOT country = 'UK'
ORDER BY last_name DESC;

    

22. Здесь мы группируем строки по параметру country и подсчитываем количество повторяющихся стран с помощью функции COUNT().

        SELECT country, COUNT(*) AS number
FROM Clients;
GROUP BY country;

    
***

Это далеко не полный список вариантов использования команды SELECT при работе с базами данных. Комбинаций довольно много. Однако, важно понять базовый принцип написания таких запросов. По большому счету, язык запросов не так сложен и основные команды при должной усидчивости можно освоить за неделю. Ну а дальше – практика, а практический опыт накапливается долго. Удачи в обучении.

Материалы по теме

13
Мар
2022

🐘 Руководство по SQL для начинающих. Часть 3: усложняем запросы, именуем вложенные запросы, анализируем скорость запроса

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

Самостоятельное объединение (self join)

Иногда возникает ситуация, когда для получения необходимых данных нам понадобится объединить таблицу саму с собой. Наглядный пример вы можете посмотреть по этой ссылке. Суть в том, что, если строки в таблице содержат данные, которые могут быть связаны с данными из других строк этой же таблицы (например id), мы может сделать join таблицы к ней самой.


Давайте для начала добавим поле с идентификатором лучшего друга best_friend_id в таблицу учеников и вставим в него некоторые данные.

        ALTER TABLE students
ADD best_friend_id INT;

UPDATE students
SET best_friend_id = 5
WHERE id = 1;

UPDATE students
SET best_friend_id = 4
WHERE id = 2;

UPDATE students
SET best_friend_id = 2
WHERE id = 3;

UPDATE students
SET best_friend_id = 2
WHERE id = 4;

UPDATE students
SET best_friend_id = 1
WHERE id = 5;

SELECT * FROM students;

/*
 id  | name     | classroom_id | best_friend_id
 --- | -------- | ------------ | --------------
   1 | Adam     |            1 |              5
   2 | Betty    |            1 |              4
   3 | Caroline |            2 |              2
   4 | Dina     |       [null] |              2
   5 | Evan     |       [null] |              1
*/
    

Итак, в таблице мы храним id лучшего друга для каждого ученика. Это эффективно, но не очень читабельно. Чтобы определить, кто же является лучшим другом, нам придётся соединить таблицу саму с собой. Возьмем таблицу students и сделаем соединение (join) снова с таблицей students, в качестве ключей для связи у нас будет id студента и id лучшего друга (best_friend_id). Определим псевдонимы таблиц x и y для улучшения понимания кода.

        SELECT
    x.name,
    y.name AS best_friend
FROM
    students AS x
INNER JOIN
    students AS y
    ON y.id = x.best_friend_id;

/*
 name     | best_friend
 -------- | -----------
 Adam     | Evan
 Betty    | Dina
 Caroline | Betty
 Dina     | Betty
 Evan     | Adam
*/
    

Оконные функции (Window functions)

Оконные функции очень похожи на агрегатные функции (group by) тем, что они применяются для вычислений сгруппированных наборов данных. Но в отличие от агрегатных функций, оконные не уменьшают количество строк в результатах запроса.

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

        SELECT
    s.name,
    g.score,
    AVG(g.score) OVER (
        PARTITION BY s.name
    )
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id;

/*
 name  | score | avg
 ------| ----- | ----------
 Adam  |    82 | 80.8000...
 Adam  |    82 | 80.8000...
 Adam  |    80 | 80.8000...
 Adam  |    75 | 80.8000...
 Adam  |    85 | 80.8000...
 Betty |    74 | 70.4000...
 Betty |    75 | 70.4000...
 ...   |   ... |        ...
*/
    

Для агрегирующих операторов AVG, MIN, или MAX, каждая строка в группировке PARTITION BY будет отображать одинаковое значение. Возможно, для определенных видов анализа это и понадобится, но на самом деле, сила оконных функций в другом.

Возможность ранжирования данных более полезный случай. Попробуем сделать это с оценками наших учеников. Чтобы ранжировать оценки всех учеников мы будем использовать оператор RANK() OVER, в который мы передадим нужный нам столбец.

        SELECT
    s.name,
    g.score,
    RANK() OVER (
        ORDER BY g.score
    )
FROM
    grades AS g
INNER JOIN
    students AS s
    ON s.id = g.student_id;

/*
 name  | score | rank
 ----- | ----- | ----
 Betty |    64 |    1
 Dina  |    64 |    1
 Evan  |    67 |    3
 ...   |   ... |  ...
*/
    

Добавим всего одну строку в наш запрос, а именно PARTITION BY s.name к оператору OVER. В итоге мы получили ранжирование в рамках одного студента.

        SELECT
    s.name,
    g.score,
    RANK() OVER (
        PARTITION BY s.name  -- ranks by student
        ORDER BY g.score
    )
FROM
    grades AS g
INNER JOIN
    students AS s
    ON s.id = g.student_id;

/*
 name     | score | rank
 -------- | ----- | ----
 Adam     |    75 |    1
 Adam     |    80 |    2
 Adam     |    82 |    3
 Adam     |    82 |    3
 Adam     |    85 |    5
 Betty    |    64 |    1
 Betty    |    69 |    2
 Betty    |    70 |    3
 Betty    |    74 |    4
 Betty    |    75 |    5
 Caroline |    90 |    1
 Caroline |    92 |    2
 ...      |   ... |  ...
*/
    

У оконных функций есть еще несколько интересных возможностей. Например, функции распределения cumulative distribution, функции dense и percent ranks. dense_rank – функция возвращает ранг каждой строки, но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий. С функциями lag и lead вы можете более подробно ознакомиться по ссылке.


Оператор WITH

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

Например, мы хотим сравнить оценку ученика (колонка grades) с его средним баллом. Сделать это в одном запросе вроде бы несложно, нам сначала нужно вычислить средний балл с помощью оператора GROUP BY, а затем написать что-то вроде g.score > avg. Начнем с агрегации GROUP BY.

        SELECT
    s.name,
    ROUND(AVG(g.score),1) AS avg
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    s.name;

/*
 name     | avg
 -------- | ----
 Dina     | 79.6
 Evan     | 83.4
 Betty    | 70.4
 Caroline | 94.6
 Adam     | 80.8
*/
    

Это было просто. Но как же нам сравнить каждую оценку со средним баллом? Взгляните на примеры запросов ниже, все они выдадут ошибку.

        SELECT
    s.name,
    ROUND(AVG(g.score),1) AS avg,
    g.score > avg
    ...
    -- ERROR: column "avg" does not exist

SELECT
    s.name,
    ROUND(AVG(g.score),1) AS avg,
    g.score > ROUND(AVG(g.score),1)
    ...
    -- ERROR: column "g.score" must appear in the GROUP BY
    -- clause or be used in an aggregate function
    

Мы можем дважды использовать оконные функции, но это выглядит как минимум не очень читабельно.

        SELECT
    s.name,
    AVG(g.score) OVER (PARTITION BY s.name),
    g.score > AVG(g.score) OVER (PARTITION BY s.name)
    ...
    

Чтобы наш запрос был читабельным и масштабируемым, будем использовать WITH. Разделим наш запрос на два подзапроса: первый будет считать средний балл, а второй добавит записи с индивидуальными оценками grades.

        WITH averages AS (
    SELECT
        s.id,
        ROUND(AVG(g.score),1) AS avg_score
    FROM
        students AS s
    INNER JOIN
        grades AS g
        ON s.id = g.student_id
    GROUP BY
        s.id
)
SELECT
    s.name,
    g.score,
    a.avg_score,
    g.score > a.avg_score AS above_avg
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    averages AS a
    ON a.id = s.id;

/*
 name  | score | avg_score | above_avg
 ----- | ----- | --------- | ---------
 Adam  |    82 |      80.8 | true
 Adam  |    82 |      80.8 | true
 Adam  |    80 |      80.8 | false
 Adam  |    75 |      80.8 | false
 Adam  |    85 |      80.8 | true
 Betty |    74 |      70.4 | true
 Betty |    75 |      70.4 | true
*/
    

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

Запросы могут быть чудовищно длинные. Например в компании Meta (Facebook), мне встречался запрос, содержащий в себе 1000 строк и вызывал сразу 25 таблиц. Этот запрос был бы совершенно нечитаемым без применения оператора WITH, который разграничивает отдельные участки кода и присваивает им псевдонимы.

Работая с большим объемом данных, мы не имеем таких роскошных возможностей как последовательное выполнение подзапросов, сохранение данных в CSV формат, затем объединение полученных данных и анализа их с помощью Python. Все взаимодействия с базой данных должны происходить за один раз.


Давайте разберем еще один вариант. Предположим, в нашей школе уволили директора, и система оценок претерпела изменения. У нас появился такой показатель как средневзвешенный балл, который вычисляется как оценка (grades) умноженная на коэффициент сложности (weight). Теперь для сдачи экзамена необходимо иметь: средневзвешенный балл по всем предметам не ниже 85% или этот же балл не ниже 70% по собственному проекту. Объединение данной логики в один блок CASE WHEN достаточно сложно, но, если мы разделим запрос с помощью WITH всё станет гораздо проще.

Давайте начнем с того, что выявим учеников, у которых средневзвешенный балл больше 85%.

        SELECT
    s.name
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
GROUP BY
    s.name
HAVING
    SUM(g.score * a.weight) > 85;

/*
 name
 --------
 Caroline
*/
    

Прекрасно. Теперь узнаем у кого из учеников больше 70% по персональному проекту.

        SELECT
    s.name
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    a.name = 'biography'
    AND g.score > 70

/*
 name
 --------
 Adam
 Caroline
 Evan
*/
    

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

        SELECT DISTINCT
    name
FROM
    students
WHERE
    name IN <people_who_passed_final>
    OR name IN <people_who_passed_project>;
    

Это очень просто с использованием WITH. Мы присвоим псевдонимы нашим запросам weighted_pass и project_pass, а потом объединим их.

        WITH weighted_pass AS (
    SELECT
        s.name
    FROM
        students AS s
    INNER JOIN
        grades AS g
        ON s.id = g.student_id
    INNER JOIN
        assignments AS a
        ON a.id = g.assignment_id
    GROUP BY
        s.name
    HAVING
        SUM(g.score * a.weight) > 85
),
project_pass AS (
    SELECT
        s.name
    FROM
        students AS s
    INNER JOIN
        grades AS g
        ON s.id = g.student_id
    INNER JOIN
        assignments AS a
        ON a.id = g.assignment_id
    WHERE
        a.name = 'biography'
        AND g.score > 70
)
SELECT DISTINCT
    name
FROM
    students
WHERE
    name IN (SELECT name FROM weighted_pass)
    OR name IN (SELECT name FROM project_pass);

/*
 name
 --------
 Evan
 Caroline
 Adam
*/
    

Смотрим вглубь – EXPLAIN

В завершении данной статьи, давайте разберем еще одну важную тему. Чем больше мы изучаем SQL, тем больше способов построения сложных запросов нам известно. Когда лучше использовать EXCEPT, а когда NOT IN? Нужно ли нам использовать несколько JOIN для объединения таблиц, либо лучше применить WITH и UNION ALL?

И в итоге, как нам понять, что один запрос более эффективен, чем другой?

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

        EXPLAIN
SELECT
    s.id AS student_id,
    g.score
FROM
    students AS s
LEFT JOIN
    grades AS g
    ON s.id = g.student_id
WHERE
    g.score > 90
ORDER BY
    g.score DESC;
    
/*
 QUERY PLAN
 ----------
 Sort (cost=80.34..81.88 rows=617 width=8)
 [...] Sort Key: g.score DESC
 [...] -> Hash Join (cost=16.98..51.74 rows=617 width=8)
 [...] Hash Cond: (g.student_id = s.id)
 [...] -> Seq Scan on grades g (cost=0.00..33.13 rows=617 width=8)
 [...] Filter: (score > 90)
 [...] -> Hash (cost=13.10..13.10 rows=310 width=4)
 [...] -> Seq Scan on students s (cost=0.00..13.20 rows=320 width=4)
*/
    

Можно пойти еще дальше и использовать оператор EXPLAIN ANALYZE, который отобразит еще более детальную информацию (например, время выполнения каждой части запроса и используемую память).

        EXPLAIN ANALYZE
SELECT
    s.id AS student_id,
    g.score
FROM
    students AS s
LEFT JOIN
    grades AS g
    ON s.id = g.student_id
WHERE
    g.score > 90
ORDER BY
    g.score DESC;
    
/*
 QUERY PLAN
 ----------
 Sort (cost=80.34..81.88 rows=617 width=8)
   (actual tiem=0.169..0.171 rows=6 loops=1)
 [...] Sort Key: g.score DESC
 [...] Sort Method: quicksort Memory: 25kB
 [...] -> Hash Join (cost=16.98..51.74 rows=617 width=8)
   (actual time=0.115..0.145 rows=6 loops=1)
 [...] Hash Cond: (g.student_id = s.id)
 [...] -> Seq Scan on grades g (cost=0.00..33.13 rows=617 width=8)
   (actual time=0.045..0.052 rows=6 loops=1)
 [...] Filter: (score > 90)
   Rows removed by Filter: 19
 [...] -> Hash (cost=13.10..13.10 rows=310 width=4)
   (actual time=0.059..0.060 rows=5 loops=1)
 [...] Buckets: 1024 Batches: 1 Memory Usage: 9kB
 [...] -> Seq Scan on students s (cost=0.00..13.10 rows=310 width=4)
    (actual time=0.022..0.027 rows=5 loops=1)
 Planning Time: 0.379 ms
 Execution Time: 0.227 ms
*/
    

Например в результате выше можно увидеть, что Postgres последовательно сканирует (Seq Scan) таблицы grades и students, потому что они не индексированы. Иначе говоря, Postgres не знает что строка внизу таблицы имеет id меньше или больше чем строка наверху таблицы. В нашем случае, это не имеет особого значения ввиду размеров нашей базы данных, но если бы мы работали с миллионами строк данных, то нам определенно потребовалось бы найти и устранить такие узкие места в производительности.

***

Данная статья была обзором тех навыков в SQL, которые вам точно понадобятся сразу после получения базовых знаний. Мы начали с установки Postgres и pgAdmin, тем самым получив возможность экспериментировать с базой данных на своём компьютере.

Затем мы узнали про полезные синтаксические конструкции, которые позволили нам составлять более сложные запросы. Мы начали с фильтрации данных и узнали, чем отличается WHERE от HAVING. Далее мы познакомились с условными конструкциями, узнали как сегментировать данные с помощью CASE WHEN и обрабатывать null, используя COALESCE. Мы перешли от горизонтального к вертикальному объединению таблиц с помощью операций над множествами, разобрав как UNION, UNION ALL, INTERSECT, и EXCEPT работают с повторяющимися строками в таблицах. И в конце второй части статьи узнали как создавать массивы и работать с ними.

В финальной части мы разобрали, как составлять еще более сложные запросы, такие как объединение таблицы с самой собой, оконные функции для сравнения данных, и оператор WITH для именования вложенных запросов. И в конце мы узнали, что с помощью EXPLAIN и EXPLAIN ANALYZE можно оценить производительность наших запросов и узнать в каких местах их можно оптимизировать.

И конечно же это далеко не всё. Есть еще много функций, про которые стоит знать, например CAST (для приведения одного типа данных к другому, например float к integer), или функции, определяемые пользователем (user-defined functions), которые можно использовать для дальнейшего упрощения кода. Всё это безусловно очень полезно, но я бы рекомендовал всегда думать об оптимизации запросов. Даже в FAANG компаниях с практически неограниченными вычислительными ресурсами, запросы могут завершаться с ошибкой в том случае, если они используют больше памяти, чем может предоставить сервер. Выбор правильного подхода к написанию запроса, упрощает работу с потоком данных, что снижает шанс получить гневный звонок в полночь.

Дополнение

1. CASCADE

Что же на самом деле происходит когда мы используем CASCADE при удалении таблицы?

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

        SELECT
    s.name,
    s.classroom_id,
    c.teacher
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | classroom_id | teacher
 -------- | ------------ | -------
 Adam     |            1 | Mary
 Betty    |            1 | Mary
 Caroline |            2 | Jonah
 Dina     |       [null] | [null]
 Evan     |       [null] | [null]
*/

DROP TABLE classrooms CASCADE;

/*
DROP TABLE
Query returned successfully in 71 msec.
*/

SELECT * FROM students;

/*
 id | name     | classroom_id | best_friend_id
 -- | -------- | ------------ | --------------
  1 | Adam     |            1 |              5
  2 | Betty    |            1 |              4
  3 | Caroline |            2 |              2
  4 | Dina     |       [null] |              2
  5 | Evan     |       [null] |              1
*/
    

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

        CREATE TABLE classrooms (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    teacher VARCHAR(100)
);

/*
CREATE TABLE
Query returned successfully in 139 msec.
*/

INSERT INTO classrooms
    (teacher)
VALUES
    ('Dr. Random'),
    ('Alien Banana');

/*
INSERT 0 2
Query returned successfully in 99 msec.
*/

SELECT
    s.name,
    s.classroom_id,
    c.teacher
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | classroom_id | teacher
 -------- | ------------ | -----------
 Adam     |            1 | Dr. Random
 Betty    |            1 | Dr. Random
 Caroline |            2 | Alien Banana
 Dina     |       [null] | [null]
 Evan     |       [null] | [null]
*/
    

Так произошло, потому что CASCADE удалил внешний ключ в таблице students. Мы можем это проверить. Изменим значение classroom_id в таблице students, поскольку оно больше не является внешним ключом и не связано с таблицей classrooms, запрос выполнится успешно. Но если мы попробуем сделать то же самое с полем student_id в таблице grades, мы получим ошибку, потому что student_idэто внешний ключ.

        UPDATE students
SET classroom_id = 10
WHERE id = 1;

/*
UPDATE 1
Query returned successfully in 37 msec.
*/

UPDATE grades
SET student_id = 10
WHERE id = 1;
/*
ERROR:  insert or update on table "grades" violates foreign key
    constraint "fk_students"
DETAIL:  Key (student_id)=(10) is not present in table
    "students".
SQL state: 23503
*/
    

И еще одно замечание по поводу CASCADE. Если мы используем ON DELETE CASCADE при создании внешнего ключа в таблице students, а потом удаляем запись в classrooms, у нас также исчезнут связанные строки в таблице students. Такой способ может быть полезен при работе с личными данными пользователей, например мы хотим удалить все данные о клиенте или о сотруднике.

        DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100),
    classroom_id INT,
    CONSTRAINT fk_classrooms
        FOREIGN KEY(classroom_id)
        REFERENCES classrooms(id) ON DELETE CASCADE
);

INSERT INTO students
    (name, classroom_id)
 VALUES
    ('Adam', 1),
    ('Betty', 1),
    ('Caroline', 2);

SELECT * FROM students;
/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
*/

DELETE FROM classrooms
WHERE id = 1;

SELECT * FROM students;
/*
 id | name     | classroom_id
 -- | -------- | ------------
  3 | Caroline |            2
*/
    

2. Сохранность данных

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

3. Длина строк

Мы установили, что тип данных в колонке учитель – это строка с максимальной длиной в 100 символов, так как не предполагаем, что будут значения длиннее. Но на самом ли деле мы экономим таким образом место на диске?

С технической точки зрения, в Postgres не имеет значения, какую длину строки мы определили (10, 100 или 500 символов). Указание длины строки – это скорее хороший способ коммуникации между разработчиками, потому что таким образом вы указываете, что вы ожидаете получить в этом поле.

Но, например, в MySQL длина строк имеет значение: временные таблицы и таблицы MEMORY будут хранить строки одинаковой длины и дополнять их до максимального значения. Это означает, что, если вы указали тип поля VARCHAR(1000), то все данные в этой колонке будут дополняться до указанной длины, даже если строка состоит из 100 символов.

4. Оператор If

Если вам интересно, ниже пример того, как в Postgres выглядит синтаксис с оператором if.

        DO $$

BEGIN
    IF
        (SELECT COUNT(*) FROM grades) >
        (SELECT COUNT(*) FROM students)
    THEN
        RAISE NOTICE 'More grades than students.';
    ELSE
        RAISE NOTICE 'Equal or more students than grades.';
    END IF;

END $$;

/*
NOTICE: More grades than students.
*/
    

5. Индексы

При увеличении базы данных, установка индексов становится критически важной. Для примера, давайте создадим индекс на поле с оценками в таблице grades:

        CREATE INDEX
    score_index
ON
    grades(score);
    

Тем не менее, если сейчас мы применим оператор EXPLAIN ANALYZE, мы увидим, что Postgres по-прежнему выполняет последовательное сканирование. Так происходит потому, что Postgres уже достаточно неплохо оптимизирован. Если количество записей в таблице невелико, быстрее выполнить последовательное сканирование, нежели использовать индексы. То есть Postgres сам выбрал наиболее быстрый путь.

***

Материалы по теме

16
Фев
2022

🐘 Руководство по SQL для начинающих. Часть 2: фильтрация данных, запрос внутри запроса, работа с массивами

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

Фильтрация данных с помощью операторов WHERE и HAVING

Скорее всего, вы уже знакомы с оператором для фильтрации данных WHERE и, возможно, слышали об операторе HAVING. Но чем же конкретно они отличаются? Давайте сделаем несколько запросов к таблице успеваемости (grades), чтобы в этом разобраться. Воспользуемся оператором ORDER BY RANDOM(), чтобы выбрать произвольные данные, затем LIMIT 5, чтобы запрос выдал только 5 записей. Упорядочивать все строки только ради примера достаточно неэффективно, но если таблица небольшая – это допустимо.

        SELECT
    *
FROM
    grades
ORDER BY
    RANDOM()
LIMIT
    5;

/*
 id | assignment_id | score | student_id
 -- | ------------- | ----- | ----------
 14 |             4 |   100 |          3
 22 |             2 |    91 |          5
 23 |             3 |    85 |          5
 16 |             1 |    81 |          4
  9 |             4 |    64 |          2
*/
    

Каждая строка отображает оценку ученика по определенному предмету. Давайте узнаем средний балл каждого ученика. Для этого нам понадобится:

  1. GROUP BYдля группировки по ученикам.
  2. AVG(score)для вычисления среднего значения.
  3. ROUNDдля округления полученных значений.
        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
ORDER BY
    student_id;

/*
 student_id | avg_score
 ---------- | ---------
          1 |      80.8
          2 |      70.4
          3 |      94.6
          4 |      79.6
          5 |      83.4
*/
    

Теперь давайте представим, что из предыдущего запроса, нам нужны только те строки, где средний балл (avg_score) больше, чем 50 и меньше, чем 75. То есть запрос должен отобразить только ученика со student_id=2. Что произойдет при использовании оператора WHERE?

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
WHERE
    score BETWEEN 50 AND 75
GROUP BY
    student_id
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          1 |      75.0
          2 |      70.4
          3 |      64.0
          4 |      67.0
*/
    

Результаты выглядят совершенно неверными. Ученик с id=5 не отображается в результате запроса, а ученики с id 1, 3 и 4 на месте. К тому же их средний балл (avg_score) изменился. А что если бы это были данные какого-нибудь важного отчета? Есть вероятность как минимум растеряться.

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

Теперь посмотрим, что изменится при использовании оператора HAVING.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          2 |      70.4
*/
    

Эти два запроса выдают совершенно разные результаты, потому что операторы WHERE и HAVING фильтруют данные на разных этапах агрегации. WHERE обрабатывает данные перед агрегацией, а HAVING после, и фильтрует уже результаты.


Результат агрегации в запросе с оператором WHERE изменился, потому что мы изменили входные данные для подсчета среднего балла каждого ученика. У ученика с id=5 нет оценок в диапазоне с 50 по 75, поэтому он был исключен из запроса. В то время как оператор HAVING отфильтровал результаты уже после подсчета.

Когда вы освоите применение операторов WHERE и HAVING по отдельности, можете попробовать использовать их вместе. Например: мы хотим отобразить учеников, чей средний балл только по домашним работам не меньше 50 и не больше 75 баллов.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades AS g
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    a.category = 'homework'
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75;

/*
 student_id | avg_score
 ---------- | ---------
          2 |      74.5
*/
    
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

Условные операторы: CASE WHEN и COALESCE

Иногда, к данным в колонке нужно применить некое условие наподобие if-else. Возможно у вас есть таблица для прогнозирования поведения модели и вам нужно вывести данные в виде положительных и отрицательных значений, основываясь на каком-то условии.

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

        SELECT
    score,
    CASE
        WHEN score < 60 THEN 'F'
        WHEN score < 70 THEN 'D'
        WHEN score < 80 THEN 'C'
        WHEN score < 90 THEN 'B'
        ELSE 'A'
    END AS letter
FROM
    grades;

/*
 score | letter
 ----- | ------
    82 | B
    82 | B
    80 | B
    75 | C
   ... | ...
*/
    

Логика, которую мы передаем в блок CASE WHEN может охватывать сразу несколько колонок. Давайте выведем в результат запроса колонку instructor, за основу возьмем таблицу students. Например, если ученику назначен учитель, то в эту колонку запишем имя учителя, если нет, то имя ученика.

        SELECT
    name,
    teacher,
    CASE
        WHEN teacher IS NOT NULL THEN teacher
        ELSE name
    END AS instructor
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Если мы работаем с данными, которые могут не иметь значения, то есть являются null, оператор COALESCE – лучший выбор. COALESCE – проверка на null, то есть при передаче в него параметров null, он вернет первое значение, не являющееся null. Перепишем предыдущий запрос.

        SELECT
    name,
    teacher,
    COALESCE(teacher, name)
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Четвертая строка в этом запросе, делает то же самое, что и строки с четвертой по седьмую в предыдущем. То есть, если значение в колонке teacher не null, возвращаем имя учителя, если null, возвращаем имя ученика.

COALESCE будет пропускать все переданные ему аргументы, пока не дойдет до элемента со значением не null. Если же все аргументы имеют значение null, то в возвращаемом значении тоже будет null.

        SELECT
    COALESCE(NULL, NULL, NULL, 4);
/*
 coalesce
 --------
        4
 */

 SELECT
    COALESCE(NULL);
/*
 coalesce
 --------
 [null]
 */
    

И наконец, в Postgres есть еще оператор условия IF , однако он используется для управления несколькими запросами сразу, но не одним. Даже если вы data scientist или data engineer маловероятно, что вы будете им пользоваться. Если вы хотите освоить работу с данным оператором, попробуйте воспользоваться платформой Airflow.

Операции над множествами: UNION, INTERSECT, и EXCEPT

При использовании оператора JOIN мы к одной таблице горизонтально присоединяем другую таблицу. В запросе ниже видно, мы получили данные об ученике с именем Adam из трех таблиц students, grades и assignments. В качестве ключа для связки таблиц использовали поле id.

        SELECT
    s.name,
    g.score,
    a.category
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    s.name = 'Adam';

/*
 name | score | category
 ---- | ----- | --------
 Adam |    82 | homework
 Adam |    82 | homework
 Adam |    80 | exam
 Adam |    75 | project
 Adam |    85 | exam
*/
    

В большинстве случаев использование оператора JOIN полностью покрывает наши потребности. Но что если мы захотим объединить таблицы вертикально. Например, у нас есть таблица и снизу к ней мы хотим добавить другую таблицу.

Давайте представим, что с системой успеваемости в нашей школе произошли очень странные изменения. Теперь для определения того, сдал ученик экзамен или нет используются не оценки. Ученик сдаст экзамен если: его имя начинается с буквы А или В или же в его имени только 5 букв. Мы можем написать 2 запроса, которые покажут нам всех учеников, подходящих под каждое условие, а затем используем оператор UNION ALL, чтобы объединить результаты в одну таблицу.

        SELECT
    *
FROM (
    SELECT
        name,
        'Name starts with A/B' as reason
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION ALL

SELECT
    *
FROM (
    SELECT
        name,
        'Name is 5 letters long' AS reason
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name  | reason
 ----  | ------
 Adam  | Name starts with A/B
 Betty | Name starts with A/B
 Betty | Name is 5 letters long
*/
    

Здесь, на строках с 4 по 11 и с 18 по 24 мы впервые увидели вложенные запросы (или подзапросы). Обратите внимание, что таким запросам необходимо присваивать имена (в нашем случае x и y) чтобы работал оператор UNION ALL.

Также вы могли обратить внимание, что мы использовали оператор UNION ALL, а не просто UNION. Дело в том, что UNION ALL возвращает все строки, в то время как UNION убирает дубли. То есть если в двух подзапросах будут две одинаковые строки, UNION покажет в результатах только одну. Именно для этого запроса ничего не изменится, потому что ученик Betty удовлетворяет обоим условиям, а вот если не выводить колонку reason в результате мы увидим имя Betty только один раз.

        SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION  -- <- UNION, а не UNION ALL

SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name
 -----
 Adam
 Betty   <- Только одна запись, потому что использовали оператор UNION
*/
    

При выборе UNION или UNION ALL подумайте, нужны ли вам повторяющиеся значения. При написании сложных запросов я предпочитаю использовать UNION ALL, чтобы убедиться, что в результирующей таблице то количество строк, которое нужно, и, если есть дубли, значит, где-то раньше я, скорее, всего ошибся с соединениями таблиц (JOIN). Чем раньше вы будете фильтровать данные в запросе, тем эффективнее он будет работать.


Операторы UNION и UNION ALL возвращают все строки из подзапросов (в случае с UNION без дублей). Два других оператора INTERSECT и EXCEPT, позволяют нам вернуть только те строки, которые соответствуют определенным критериям. INTERSECT (пересечение) вернет только те строки, которые присутствуют в обоих запросах, а EXCEPT (исключение) вернет строки из подзапроса А, которых нет в подзапросе Б.


В следующем запросе попробуем работу оператора INTERSECT, который отобразит строки, присутствующие в обоих подзапросах (id 2 и id 3). В отличие от UNION нам не нужно присваивать имена вложенным запросам.

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

INTERSECT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

А теперь изменим запрос и применим оператор EXCEPT, который выведет строки из подзапроса А, которых нет в подзапросе Б (в нашем случае строка с id=1).

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

EXCEPT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
*/
    

Операции над множествами дают нам возможность комбинировать результаты запросов (UNION), просматривать пересекающиеся записи (INTERSECT) и извлекать отличающиеся данные (EXCEPT). Больше не нужно сравнивать результаты запросов вручную.

Функции для работы с массивами

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

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

        SELECT
    name,
    ARRAY_AGG(score) AS scores
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;
    
/*
 name     | scores
 -------- | ------
 Adam     | {82,82,80,75,85}
 Betty    | {74,75,70,64,69}
 Caroline | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}
*/
    

Также в нашем арсенале есть следующие функции:

  1. CARDINALITY – выводит количество элементов в массиве.
  2. ARRAY_REPLACE – заменяет указанные элементы.
  3. ARRAY_REMOVE – удаляет указанные элемент.
        SELECT
    name,
    ARRAY_AGG(score) AS scores,
    CARDINALITY(ARRAY_AGG(score)) AS length,
    ARRAY_REPLACE(ARRAY_AGG(score), 82, NULL) AS replaced
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;

/*
 name     | scores            | length | replaced
 -------- | ----------------- | ------ | --------------------
 Adam     | {82,82,80,75,85}  |      5 | {NULL,NULL,80,75,85}
 Betty    | {74,75,70,64,69}  |      5 | {74,75,70,64,69}
 Caroline | {96,92,90,100,95} |      5 | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}  |      5 | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}  |      5 | {67,91,85,93,81}
*/
    

UNNESTеще одна функция, которая может вам пригодиться. Её действие противоположно функции ARRAY_AGG, то есть она позволяет разделить массив на отдельные строки.

        SELECT
    'name' AS name,
    UNNEST(ARRAY[1, 2, 3]);
    
/*
 name  | unnest
 ----  | ------
 name  |      1
 name  |      2
 name  |      3
*/
    

В этой части статьи мы с вами разобрали:

  • фильтрацию данных с помощью операторов WHERE и HAVING;
  • условные операторы CASE WHEN и COALESCE;
  • операции над множествами;
  • функции для работы с массивами.

В финальной части статьи разберем:

  • присоединение таблицы к самой себе (self join);
  • оконные функции (window function);
  • посмотрим вглубь запросов (explain).
***

Материалы по теме

16
Фев
2022

🐘 Руководство по SQL для начинающих. Часть 2: фильтрация данных, запрос внутри запроса, работа с массивами

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

Фильтрация данных с помощью операторов WHERE и HAVING

Скорее всего, вы уже знакомы с оператором для фильтрации данных WHERE и, возможно, слышали об операторе HAVING. Но чем же конкретно они отличаются? Давайте сделаем несколько запросов к таблице успеваемости (grades), чтобы в этом разобраться. Воспользуемся оператором ORDER BY RANDOM(), чтобы выбрать произвольные данные, затем LIMIT 5, чтобы запрос выдал только 5 записей. Упорядочивать все строки только ради примера достаточно неэффективно, но если таблица небольшая – это допустимо.

        SELECT
    *
FROM
    grades
ORDER BY
    RANDOM()
LIMIT
    5;

/*
 id | assignment_id | score | student_id
 -- | ------------- | ----- | ----------
 14 |             4 |   100 |          3
 22 |             2 |    91 |          5
 23 |             3 |    85 |          5
 16 |             1 |    81 |          4
  9 |             4 |    64 |          2
*/
    

Каждая строка отображает оценку ученика по определенному предмету. Давайте узнаем средний балл каждого ученика. Для этого нам понадобится:

  1. GROUP BYдля группировки по ученикам.
  2. AVG(score)для вычисления среднего значения.
  3. ROUNDдля округления полученных значений.
        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
ORDER BY
    student_id;

/*
 student_id | avg_score
 ---------- | ---------
          1 |      80.8
          2 |      70.4
          3 |      94.6
          4 |      79.6
          5 |      83.4
*/
    

Теперь давайте представим, что из предыдущего запроса, нам нужны только те строки, где средний балл (avg_score) больше, чем 50 и меньше, чем 75. То есть запрос должен отобразить только ученика со student_id=2. Что произойдет при использовании оператора WHERE?

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
WHERE
    score BETWEEN 50 AND 75
GROUP BY
    student_id
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          1 |      75.0
          2 |      70.4
          3 |      64.0
          4 |      67.0
*/
    

Результаты выглядят совершенно неверными. Ученик с id=5 не отображается в результате запроса, а ученики с id 1, 3 и 4 на месте. К тому же их средний балл (avg_score) изменился. А что если бы это были данные какого-нибудь важного отчета? Есть вероятность как минимум растеряться.

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

Теперь посмотрим, что изменится при использовании оператора HAVING.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          2 |      70.4
*/
    

Эти два запроса выдают совершенно разные результаты, потому что операторы WHERE и HAVING фильтруют данные на разных этапах агрегации. WHERE обрабатывает данные перед агрегацией, а HAVING после, и фильтрует уже результаты.


Результат агрегации в запросе с оператором WHERE изменился, потому что мы изменили входные данные для подсчета среднего балла каждого ученика. У ученика с id=5 нет оценок в диапазоне с 50 по 75, поэтому он был исключен из запроса. В то время как оператор HAVING отфильтровал результаты уже после подсчета.

Когда вы освоите применение операторов WHERE и HAVING по отдельности, можете попробовать использовать их вместе. Например: мы хотим отобразить учеников, чей средний балл только по домашним работам не меньше 50 и не больше 75 баллов.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades AS g
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    a.category = 'homework'
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75;

/*
 student_id | avg_score
 ---------- | ---------
          2 |      74.5
*/
    
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

Условные операторы: CASE WHEN и COALESCE

Иногда, к данным в колонке нужно применить некое условие наподобие if-else. Возможно у вас есть таблица для прогнозирования поведения модели и вам нужно вывести данные в виде положительных и отрицательных значений, основываясь на каком-то условии.

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

        SELECT
    score,
    CASE
        WHEN score < 60 THEN 'F'
        WHEN score < 70 THEN 'D'
        WHEN score < 80 THEN 'C'
        WHEN score < 90 THEN 'B'
        ELSE 'A'
    END AS letter
FROM
    grades;

/*
 score | letter
 ----- | ------
    82 | B
    82 | B
    80 | B
    75 | C
   ... | ...
*/
    

Логика, которую мы передаем в блок CASE WHEN может охватывать сразу несколько колонок. Давайте выведем в результат запроса колонку instructor, за основу возьмем таблицу students. Например, если ученику назначен учитель, то в эту колонку запишем имя учителя, если нет, то имя ученика.

        SELECT
    name,
    teacher,
    CASE
        WHEN teacher IS NOT NULL THEN teacher
        ELSE name
    END AS instructor
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Если мы работаем с данными, которые могут не иметь значения, то есть являются null, оператор COALESCE – лучший выбор. COALESCE – проверка на null, то есть при передаче в него параметров null, он вернет первое значение, не являющееся null. Перепишем предыдущий запрос.

        SELECT
    name,
    teacher,
    COALESCE(teacher, name)
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Четвертая строка в этом запросе, делает то же самое, что и строки с четвертой по седьмую в предыдущем. То есть, если значение в колонке teacher не null, возвращаем имя учителя, если null, возвращаем имя ученика.

COALESCE будет пропускать все переданные ему аргументы, пока не дойдет до элемента со значением не null. Если же все аргументы имеют значение null, то в возвращаемом значении тоже будет null.

        SELECT
    COALESCE(NULL, NULL, NULL, 4);
/*
 coalesce
 --------
        4
 */

 SELECT
    COALESCE(NULL);
/*
 coalesce
 --------
 [null]
 */
    

И наконец, в Postgres есть еще оператор условия IF , однако он используется для управления несколькими запросами сразу, но не одним. Даже если вы data scientist или data engineer маловероятно, что вы будете им пользоваться. Если вы хотите освоить работу с данным оператором, попробуйте воспользоваться платформой Airflow.

Операции над множествами: UNION, INTERSECT, и EXCEPT

При использовании оператора JOIN мы к одной таблице горизонтально присоединяем другую таблицу. В запросе ниже видно, мы получили данные об ученике с именем Adam из трех таблиц students, grades и assignments. В качестве ключа для связки таблиц использовали поле id.

        SELECT
    s.name,
    g.score,
    a.category
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    s.name = 'Adam';

/*
 name | score | category
 ---- | ----- | --------
 Adam |    82 | homework
 Adam |    82 | homework
 Adam |    80 | exam
 Adam |    75 | project
 Adam |    85 | exam
*/
    

В большинстве случаев использование оператора JOIN полностью покрывает наши потребности. Но что если мы захотим объединить таблицы вертикально. Например, у нас есть таблица и снизу к ней мы хотим добавить другую таблицу.

Давайте представим, что с системой успеваемости в нашей школе произошли очень странные изменения. Теперь для определения того, сдал ученик экзамен или нет используются не оценки. Ученик сдаст экзамен если: его имя начинается с буквы А или В или же в его имени только 5 букв. Мы можем написать 2 запроса, которые покажут нам всех учеников, подходящих под каждое условие, а затем используем оператор UNION ALL, чтобы объединить результаты в одну таблицу.

        SELECT
    *
FROM (
    SELECT
        name,
        'Name starts with A/B' as reason
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION ALL

SELECT
    *
FROM (
    SELECT
        name,
        'Name is 5 letters long' AS reason
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name  | reason
 ----  | ------
 Adam  | Name starts with A/B
 Betty | Name starts with A/B
 Betty | Name is 5 letters long
*/
    

Здесь, на строках с 4 по 11 и с 18 по 24 мы впервые увидели вложенные запросы (или подзапросы). Обратите внимание, что таким запросам необходимо присваивать имена (в нашем случае x и y) чтобы работал оператор UNION ALL.

Также вы могли обратить внимание, что мы использовали оператор UNION ALL, а не просто UNION. Дело в том, что UNION ALL возвращает все строки, в то время как UNION убирает дубли. То есть если в двух подзапросах будут две одинаковые строки, UNION покажет в результатах только одну. Именно для этого запроса ничего не изменится, потому что ученик Betty удовлетворяет обоим условиям, а вот если не выводить колонку reason в результате мы увидим имя Betty только один раз.

        SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION  -- <- UNION, а не UNION ALL

SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name
 -----
 Adam
 Betty   <- Только одна запись, потому что использовали оператор UNION
*/
    

При выборе UNION или UNION ALL подумайте, нужны ли вам повторяющиеся значения. При написании сложных запросов я предпочитаю использовать UNION ALL, чтобы убедиться, что в результирующей таблице то количество строк, которое нужно, и, если есть дубли, значит, где-то раньше я, скорее, всего ошибся с соединениями таблиц (JOIN). Чем раньше вы будете фильтровать данные в запросе, тем эффективнее он будет работать.


Операторы UNION и UNION ALL возвращают все строки из подзапросов (в случае с UNION без дублей). Два других оператора INTERSECT и EXCEPT, позволяют нам вернуть только те строки, которые соответствуют определенным критериям. INTERSECT (пересечение) вернет только те строки, которые присутствуют в обоих запросах, а EXCEPT (исключение) вернет строки из подзапроса А, которых нет в подзапросе Б.


В следующем запросе попробуем работу оператора INTERSECT, который отобразит строки, присутствующие в обоих подзапросах (id 2 и id 3). В отличие от UNION нам не нужно присваивать имена вложенным запросам.

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

INTERSECT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

А теперь изменим запрос и применим оператор EXCEPT, который выведет строки из подзапроса А, которых нет в подзапросе Б (в нашем случае строка с id=1).

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

EXCEPT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
*/
    

Операции над множествами дают нам возможность комбинировать результаты запросов (UNION), просматривать пересекающиеся записи (INTERSECT) и извлекать отличающиеся данные (EXCEPT). Больше не нужно сравнивать результаты запросов вручную.

Функции для работы с массивами

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

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

        SELECT
    name,
    ARRAY_AGG(score) AS scores
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;
    
/*
 name     | scores
 -------- | ------
 Adam     | {82,82,80,75,85}
 Betty    | {74,75,70,64,69}
 Caroline | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}
*/
    

Также в нашем арсенале есть следующие функции:

  1. CARDINALITY – выводит количество элементов в массиве.
  2. ARRAY_REPLACE – заменяет указанные элементы.
  3. ARRAY_REMOVE – удаляет указанные элемент.
        SELECT
    name,
    ARRAY_AGG(score) AS scores,
    CARDINALITY(ARRAY_AGG(score)) AS length,
    ARRAY_REPLACE(ARRAY_AGG(score), 82, NULL) AS replaced
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;

/*
 name     | scores            | length | replaced
 -------- | ----------------- | ------ | --------------------
 Adam     | {82,82,80,75,85}  |      5 | {NULL,NULL,80,75,85}
 Betty    | {74,75,70,64,69}  |      5 | {74,75,70,64,69}
 Caroline | {96,92,90,100,95} |      5 | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}  |      5 | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}  |      5 | {67,91,85,93,81}
*/
    

UNNESTеще одна функция, которая может вам пригодиться. Её действие противоположно функции ARRAY_AGG, то есть она позволяет разделить массив на отдельные строки.

        SELECT
    'name' AS name,
    UNNEST(ARRAY[1, 2, 3]);
    
/*
 name  | unnest
 ----  | ------
 name  |      1
 name  |      2
 name  |      3
*/
    

В этой части статьи мы с вами разобрали:

  • фильтрацию данных с помощью операторов WHERE и HAVING;
  • условные операторы CASE WHEN и COALESCE;
  • операции над множествами;
  • функции для работы с массивами.

В финальной части статьи разберем:

  • присоединение таблицы к самой себе (self join);
  • оконные функции (window function);
  • посмотрим вглубь запросов (explain).
***

Материалы по теме

16
Фев
2022

🐘 Руководство по SQL для начинающих. Часть 2: фильтрация данных, запрос внутри запроса, работа с массивами

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

Фильтрация данных с помощью операторов WHERE и HAVING

Скорее всего, вы уже знакомы с оператором для фильтрации данных WHERE и, возможно, слышали об операторе HAVING. Но чем же конкретно они отличаются? Давайте сделаем несколько запросов к таблице успеваемости (grades), чтобы в этом разобраться. Воспользуемся оператором ORDER BY RANDOM(), чтобы выбрать произвольные данные, затем LIMIT 5, чтобы запрос выдал только 5 записей. Упорядочивать все строки только ради примера достаточно неэффективно, но если таблица небольшая – это допустимо.

        SELECT
    *
FROM
    grades
ORDER BY
    RANDOM()
LIMIT
    5;

/*
 id | assignment_id | score | student_id
 -- | ------------- | ----- | ----------
 14 |             4 |   100 |          3
 22 |             2 |    91 |          5
 23 |             3 |    85 |          5
 16 |             1 |    81 |          4
  9 |             4 |    64 |          2
*/
    

Каждая строка отображает оценку ученика по определенному предмету. Давайте узнаем средний балл каждого ученика. Для этого нам понадобится:

  1. GROUP BYдля группировки по ученикам.
  2. AVG(score)для вычисления среднего значения.
  3. ROUNDдля округления полученных значений.
        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
ORDER BY
    student_id;

/*
 student_id | avg_score
 ---------- | ---------
          1 |      80.8
          2 |      70.4
          3 |      94.6
          4 |      79.6
          5 |      83.4
*/
    

Теперь давайте представим, что из предыдущего запроса, нам нужны только те строки, где средний балл (avg_score) больше, чем 50 и меньше, чем 75. То есть запрос должен отобразить только ученика со student_id=2. Что произойдет при использовании оператора WHERE?

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
WHERE
    score BETWEEN 50 AND 75
GROUP BY
    student_id
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          1 |      75.0
          2 |      70.4
          3 |      64.0
          4 |      67.0
*/
    

Результаты выглядят совершенно неверными. Ученик с id=5 не отображается в результате запроса, а ученики с id 1, 3 и 4 на месте. К тому же их средний балл (avg_score) изменился. А что если бы это были данные какого-нибудь важного отчета? Есть вероятность как минимум растеряться.

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

Теперь посмотрим, что изменится при использовании оператора HAVING.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75
ORDER BY
    student_id;
    
/*
 student_id | avg_score
 ---------- | ---------
          2 |      70.4
*/
    

Эти два запроса выдают совершенно разные результаты, потому что операторы WHERE и HAVING фильтруют данные на разных этапах агрегации. WHERE обрабатывает данные перед агрегацией, а HAVING после, и фильтрует уже результаты.


Результат агрегации в запросе с оператором WHERE изменился, потому что мы изменили входные данные для подсчета среднего балла каждого ученика. У ученика с id=5 нет оценок в диапазоне с 50 по 75, поэтому он был исключен из запроса. В то время как оператор HAVING отфильтровал результаты уже после подсчета.

Когда вы освоите применение операторов WHERE и HAVING по отдельности, можете попробовать использовать их вместе. Например: мы хотим отобразить учеников, чей средний балл только по домашним работам не меньше 50 и не больше 75 баллов.

        SELECT
    student_id,
    ROUND(AVG(score),1) AS avg_score
FROM
    grades AS g
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    a.category = 'homework'
GROUP BY
    student_id
HAVING
    ROUND(AVG(score),1) BETWEEN 50 AND 75;

/*
 student_id | avg_score
 ---------- | ---------
          2 |      74.5
*/
    
Больше полезных материалов вы найдете на нашем телеграм-канале «Библиотека программиста»

Условные операторы: CASE WHEN и COALESCE

Иногда, к данным в колонке нужно применить некое условие наподобие if-else. Возможно у вас есть таблица для прогнозирования поведения модели и вам нужно вывести данные в виде положительных и отрицательных значений, основываясь на каком-то условии.

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

        SELECT
    score,
    CASE
        WHEN score < 60 THEN 'F'
        WHEN score < 70 THEN 'D'
        WHEN score < 80 THEN 'C'
        WHEN score < 90 THEN 'B'
        ELSE 'A'
    END AS letter
FROM
    grades;

/*
 score | letter
 ----- | ------
    82 | B
    82 | B
    80 | B
    75 | C
   ... | ...
*/
    

Логика, которую мы передаем в блок CASE WHEN может охватывать сразу несколько колонок. Давайте выведем в результат запроса колонку instructor, за основу возьмем таблицу students. Например, если ученику назначен учитель, то в эту колонку запишем имя учителя, если нет, то имя ученика.

        SELECT
    name,
    teacher,
    CASE
        WHEN teacher IS NOT NULL THEN teacher
        ELSE name
    END AS instructor
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Если мы работаем с данными, которые могут не иметь значения, то есть являются null, оператор COALESCE – лучший выбор. COALESCE – проверка на null, то есть при передаче в него параметров null, он вернет первое значение, не являющееся null. Перепишем предыдущий запрос.

        SELECT
    name,
    teacher,
    COALESCE(teacher, name)
FROM
    students AS s
LEFT JOIN
    classrooms AS c
    ON c.id = s.classroom_id;

/*
 name     | teacher | instructor
 -------- | ------- | ----------
 Adam     | Mary    | Mary
 Betty    | Mary    | Mary
 Caroline | Jonah   | Jonah
 Dina     | [null]  | Dina
 Evan     | [null]  | Evan
*/
    

Четвертая строка в этом запросе, делает то же самое, что и строки с четвертой по седьмую в предыдущем. То есть, если значение в колонке teacher не null, возвращаем имя учителя, если null, возвращаем имя ученика.

COALESCE будет пропускать все переданные ему аргументы, пока не дойдет до элемента со значением не null. Если же все аргументы имеют значение null, то в возвращаемом значении тоже будет null.

        SELECT
    COALESCE(NULL, NULL, NULL, 4);
/*
 coalesce
 --------
        4
 */

 SELECT
    COALESCE(NULL);
/*
 coalesce
 --------
 [null]
 */
    

И наконец, в Postgres есть еще оператор условия IF , однако он используется для управления несколькими запросами сразу, но не одним. Даже если вы data scientist или data engineer маловероятно, что вы будете им пользоваться. Если вы хотите освоить работу с данным оператором, попробуйте воспользоваться платформой Airflow.

Операции над множествами: UNION, INTERSECT, и EXCEPT

При использовании оператора JOIN мы к одной таблице горизонтально присоединяем другую таблицу. В запросе ниже видно, мы получили данные об ученике с именем Adam из трех таблиц students, grades и assignments. В качестве ключа для связки таблиц использовали поле id.

        SELECT
    s.name,
    g.score,
    a.category
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
INNER JOIN
    assignments AS a
    ON a.id = g.assignment_id
WHERE
    s.name = 'Adam';

/*
 name | score | category
 ---- | ----- | --------
 Adam |    82 | homework
 Adam |    82 | homework
 Adam |    80 | exam
 Adam |    75 | project
 Adam |    85 | exam
*/
    

В большинстве случаев использование оператора JOIN полностью покрывает наши потребности. Но что если мы захотим объединить таблицы вертикально. Например, у нас есть таблица и снизу к ней мы хотим добавить другую таблицу.

Давайте представим, что с системой успеваемости в нашей школе произошли очень странные изменения. Теперь для определения того, сдал ученик экзамен или нет используются не оценки. Ученик сдаст экзамен если: его имя начинается с буквы А или В или же в его имени только 5 букв. Мы можем написать 2 запроса, которые покажут нам всех учеников, подходящих под каждое условие, а затем используем оператор UNION ALL, чтобы объединить результаты в одну таблицу.

        SELECT
    *
FROM (
    SELECT
        name,
        'Name starts with A/B' as reason
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION ALL

SELECT
    *
FROM (
    SELECT
        name,
        'Name is 5 letters long' AS reason
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name  | reason
 ----  | ------
 Adam  | Name starts with A/B
 Betty | Name starts with A/B
 Betty | Name is 5 letters long
*/
    

Здесь, на строках с 4 по 11 и с 18 по 24 мы впервые увидели вложенные запросы (или подзапросы). Обратите внимание, что таким запросам необходимо присваивать имена (в нашем случае x и y) чтобы работал оператор UNION ALL.

Также вы могли обратить внимание, что мы использовали оператор UNION ALL, а не просто UNION. Дело в том, что UNION ALL возвращает все строки, в то время как UNION убирает дубли. То есть если в двух подзапросах будут две одинаковые строки, UNION покажет в результатах только одну. Именно для этого запроса ничего не изменится, потому что ученик Betty удовлетворяет обоим условиям, а вот если не выводить колонку reason в результате мы увидим имя Betty только один раз.

        SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LEFT(name,1) IN ('A', 'B')
) AS x

UNION  -- <- UNION, а не UNION ALL

SELECT
    *
FROM (
    SELECT
        name  -- <- нет колонки `reason`
    FROM
        students
    WHERE
        LENGTH(name) = 5
) AS y;

/*
 name
 -----
 Adam
 Betty   <- Только одна запись, потому что использовали оператор UNION
*/
    

При выборе UNION или UNION ALL подумайте, нужны ли вам повторяющиеся значения. При написании сложных запросов я предпочитаю использовать UNION ALL, чтобы убедиться, что в результирующей таблице то количество строк, которое нужно, и, если есть дубли, значит, где-то раньше я, скорее, всего ошибся с соединениями таблиц (JOIN). Чем раньше вы будете фильтровать данные в запросе, тем эффективнее он будет работать.


Операторы UNION и UNION ALL возвращают все строки из подзапросов (в случае с UNION без дублей). Два других оператора INTERSECT и EXCEPT, позволяют нам вернуть только те строки, которые соответствуют определенным критериям. INTERSECT (пересечение) вернет только те строки, которые присутствуют в обоих запросах, а EXCEPT (исключение) вернет строки из подзапроса А, которых нет в подзапросе Б.


В следующем запросе попробуем работу оператора INTERSECT, который отобразит строки, присутствующие в обоих подзапросах (id 2 и id 3). В отличие от UNION нам не нужно присваивать имена вложенным запросам.

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

INTERSECT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

А теперь изменим запрос и применим оператор EXCEPT, который выведет строки из подзапроса А, которых нет в подзапросе Б (в нашем случае строка с id=1).

        SELECT
    *
FROM
    students
WHERE
    id IN (1,2,3)

EXCEPT

SELECT
    *
FROM
    students
WHERE
    id IN (2,3,4);

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
*/
    

Операции над множествами дают нам возможность комбинировать результаты запросов (UNION), просматривать пересекающиеся записи (INTERSECT) и извлекать отличающиеся данные (EXCEPT). Больше не нужно сравнивать результаты запросов вручную.

Функции для работы с массивами

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

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

        SELECT
    name,
    ARRAY_AGG(score) AS scores
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;
    
/*
 name     | scores
 -------- | ------
 Adam     | {82,82,80,75,85}
 Betty    | {74,75,70,64,69}
 Caroline | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}
*/
    

Также в нашем арсенале есть следующие функции:

  1. CARDINALITY – выводит количество элементов в массиве.
  2. ARRAY_REPLACE – заменяет указанные элементы.
  3. ARRAY_REMOVE – удаляет указанные элемент.
        SELECT
    name,
    ARRAY_AGG(score) AS scores,
    CARDINALITY(ARRAY_AGG(score)) AS length,
    ARRAY_REPLACE(ARRAY_AGG(score), 82, NULL) AS replaced
FROM
    students AS s
INNER JOIN
    grades AS g
    ON s.id = g.student_id
GROUP BY
    name
ORDER BY
    name;

/*
 name     | scores            | length | replaced
 -------- | ----------------- | ------ | --------------------
 Adam     | {82,82,80,75,85}  |      5 | {NULL,NULL,80,75,85}
 Betty    | {74,75,70,64,69}  |      5 | {74,75,70,64,69}
 Caroline | {96,92,90,100,95} |      5 | {96,92,90,100,95}
 Dina     | {81,80,84,64,89}  |      5 | {81,80,84,64,89}
 Evan     | {67,91,85,93,81}  |      5 | {67,91,85,93,81}
*/
    

UNNESTеще одна функция, которая может вам пригодиться. Её действие противоположно функции ARRAY_AGG, то есть она позволяет разделить массив на отдельные строки.

        SELECT
    'name' AS name,
    UNNEST(ARRAY[1, 2, 3]);
    
/*
 name  | unnest
 ----  | ------
 name  |      1
 name  |      2
 name  |      3
*/
    

В этой части статьи мы с вами разобрали:

  • фильтрацию данных с помощью операторов WHERE и HAVING;
  • условные операторы CASE WHEN и COALESCE;
  • операции над множествами;
  • функции для работы с массивами.

В финальной части статьи разберем:

  • присоединение таблицы к самой себе (self join);
  • оконные функции (window function);
  • посмотрим вглубь запросов (explain).
***

Материалы по теме

07
Фев
2022

🐘 Руководство по SQL для начинающих. Часть 1: создание базы данных, таблиц и установка связей между таблицами

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

Установка

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

Первый шаг – установить SQL

Мы будем использовать PostgreSQL (Postgres) достаточно распространенный SQL диалект. Для этого откроем страницу загрузки, выберем операционную систему (в моем случае Windows), и запустим установку. Если вы установите пароль для вашей базы данных, постарайтесь сразу не забыть его, он нам дальше понадобится. Поскольку наша база будет локальной, можете использовать простой пароль, например: admin.

Следующий шаг – установка pgAdmin

pgAdmin – это графический интерфейс пользователя (GUI – graphical user interface), который упрощает взаимодействие с базой данных PostgreSQL. Перейдите на страницу загрузки, выберите вашу операционную систему и следуйте указаниям (в статье используется Postgres 14 и pgAdmin 4 v6.3.).

После установки обоих компонентов открываем pgAdmin и нажимаем Add new server. На этом шаге установится соединение с существующим сервером, именно поэтому необходимо сначала установить Postgres. Я назвал свой сервер home и использовал пароль, указанный при установке.

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

Мы можем создать таблицы напрямую в pgAdmin, но вместо этого мы напишем код, который можно будет использовать в дальнейшем, например, для пересоздания таблиц. Для создания запроса, который создаст наши таблицы, нажимаем правой кнопкой мыши на postgres (пункт расположен в меню слева home Databases (1) postgres и далее выбираем Query Tool.


Начнем с создания таблицы классов (classrooms). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса (query tool) и запустите (run или F5).

        DROP TABLE IF EXISTS classrooms CASCADE;

CREATE TABLE classrooms (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    teacher VARCHAR(100)
);
    

В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms, если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение (constraint) в конце строки добавлен оператор CASCADE. CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.

Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.

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

Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом (primary key), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY, этот приём является альтернативой синтаксису последовательности (CREATE SEQUENCE). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.

И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу (alter table).

Теперь давайте создадим таблицу учеников (students). Новая таблица будет содержать: уникальный идентификатор (id), имя ученика (name), и внешний ключ (foreign key), который будет указывать (references) на таблицу классов.

        DROP TABLE IF EXISTS students CASCADE;

CREATE TABLE students (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(100),
    classroom_id INT,
    CONSTRAINT fk_classrooms
        FOREIGN KEY(classroom_id)
        REFERENCES classrooms(id)
);
    

И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id, которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (classroom_id), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов (classrooms).

Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (students). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (students), в которой указан идентификатор класса (classroom_id), не существующий в таблице classrooms. Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.

Невозможно вставить данные, поскольку в таблице классов нет записи с id = 1
        INSERT INTO students
    (name, classroom_id)
VALUES
    ('Matt', 1);

/*
ERROR: insert or update on table "students" violates foreign
    key constraint "fk_classrooms"
DETAIL: Key (classroom_id)=(1) is not present in table
    "classrooms".
SQL state: 23503
*/
    

Теперь давайте добавим немного данных в таблицу классов (classrooms). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.

        INSERT INTO classrooms
    (teacher)
VALUES
    ('Mary'),
    ('Jonah');

SELECT * FROM classrooms;

/*
 id | teacher
 -- | -------
  1 | Mary
  2 | Jonah
*/
    

Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные связи (с таблицей классов).

        INSERT INTO students
    (name, classroom_id)
 VALUES
    ('Adam', 1),
    ('Betty', 1),
    ('Caroline', 2);

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
*/
    

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

Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса (classroom_id) передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.

        -- явно определим значение NULL
INSERT INTO students
    (name, classroom_id)
VALUES
    ('Dina', NULL);

-- неявно определим значение NULL
INSERT INTO students
    (name)
VALUES
    ('Evan');

SELECT * FROM students;

/*
 id | name     | classroom_id
 -- | -------- | ------------
  1 | Adam     |            1
  2 | Betty    |            1
  3 | Caroline |            2
  4 | Dina     |       [null]
  5 | Evan     |       [null]
*/
    

И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (assignments), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (grades), в которой мы будем хранить данные о том, как ученик выполнил эти задания.

        DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS grades CASCADE;

CREATE TABLE assignments (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    category VARCHAR(20),
    name VARCHAR(200),
    due_date DATE,
    weight FLOAT
);

CREATE TABLE grades (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    assignment_id INT,
    score INT,
    student_id INT,
    CONSTRAINT fk_assignments
        FOREIGN KEY(assignment_id)
        REFERENCES assignments(id),
    CONSTRAINT fk_students
        FOREIGN KEY(student_id)
        REFERENCES students(id)
);
    

Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data).

        COPY assignments(category, name, due_date, weight)
FROM 'C:/Users/mgsosna/Desktop/db_data/assignments.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 5
Query returned successfully in 118 msec.
*/

COPY grades(assignment_id, score, student_id)
FROM 'C:/Users/mgsosna/Desktop/db_data/grades.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 25
Query returned successfully in 64 msec.
*/
    

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

        SELECT
    c.teacher,
    a.category,
    ROUND(AVG(g.score), 1) AS avg_score
FROM
    students AS s
INNER JOIN classrooms AS c
    ON c.id = s.classroom_id
INNER JOIN grades AS g
    ON s.id = g.student_id
INNER JOIN assignments AS a
    ON a.id = g.assignment_id
GROUP BY
    1,
    2
ORDER BY
    3 DESC;

/*
 teacher | category  | avg_score
 ------- | --------- | ---------
 Jonah   |  project  |     100.0
 Jonah   |  homework |      94.0
 Jonah   |  exam     |      92.5
 Mary    |  homework |      78.3
 Mary    |  exam     |      76.0
 Mary    |  project  |      69.5
*/
    

Отлично! Мы установили, настроили и наполнили базу данных.

***

Итак, в этой статье мы научились:

  • создавать базу данных;
  • создавать таблицы;
  • наполнять таблицы данными;
  • устанавливать связи между таблицами;

Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц (JOIN) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.

В следующей части мы разберем:

  • виды фильтраций в запросах;
  • запросы с условиями типа if-else;
  • новые виды соединений таблиц;
  • функции для работы с массивами;

Материалы по теме

20
Дек
2021

Курс «Python для пентестера»

Изучите тонкости работы с языком программирования Python и его использование в сфере кибербезопасности. Максимум практики и поддержка инструктора при выполнении заданий.
— Читать дальше «Курс «Python для пентестера»»

09
Дек
2021

Факультет Java-разработки

Освойте один из самых распространенных языков программирования и получите востребованную профессию. После обучения сможете создавать сетевые чаты, приложения, мобильные игры и другие сервисы.
— Читать дальше «Факультет Java-разработки»

10
Ноя
2021

📱 Выбираем хостинг для мобильного приложения. Часть вторая: базы данных и системы кеширования

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

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

Этот цикл статей написан совместно с нашим партнером – компанией Selectel.

Другие статьи цикла:

Базы данных для мобильных приложений

Начнем обзор с классических реляционных СУБД, плавно перейдем к NoSQL и завершим обзор решениями для кэширования. Я не поклонник MySQL и ничего хорошего или плохого я об этой базе данных не скажу. Есть причины по которым предпочитаю ей не пользоваться. Поэтому сразу перейдем к PostgreSQL.


PostgreSQL

PostgreSQL – это надежная реляционная СУБД корпоративного уровня с открытым исходным кодом и многолетней историей разработки. Все, что вы когда-либо хотели бы получить от реляционной базы данных, присутствует в PostgreSQL. Если вас беспокоит совместимость, обслуживание тысяч запросов из сотен таблиц, и максимальное использование SQL, правильно настроенный PostgreSQL отлично справится с этой задачей.

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

PostgreSQL поддерживает расширяемость множеством способов, такие как хранимые функции и процедуры, использование процедурных языков PL/PGSQL, Perl, Python и др.. Многие расширения предоставляют дополнительные функции, включая PostGIS – модуль для геопространственного анализа.

Если у вас уже есть модель структурированных данных, то PostgreSQL будет лучшим вариантом.


MongoDB

MongoDB – это система управления базами данных, ориентированная на документы, и в настоящее время она является самым популярным на рынке решением NoSQL.

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

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

PostgreSQL vs MongoDB по-честному

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

Можно посмотреть различные бенчмарки сравнивающие эти две базы данных, и каждый хвалит свое решение. Если же подойти в вопросу с технической стороны, то PostgreSQL использует бинарные деревья для индексирования, которые отлично работают на HDD. В тоже время большинство современных баз данных (RocksDB, MongoDB, Tarantool и т.д.) используют LSM-дерево (Log-structured merge-tree – журнально структурированное дерево со слиянием) вместо классического B-Tree.

Бинарное дерево ориентировано на операции чтения и поиск, дерево LSM ориентировано на доступ к часто изменяемым данным и работу с SDD.

Index Clients TPS – Транзакций в секунду
Inclusive B-Tree 1 9387
Inclusive B-Tree 10 18761
RocksDB FDW 1 138350
RocksDB FDW 10 122369
RocksDB 1 166333
RocksDB 10 141482

[https://postgrespro.ru/list/thread-id/2503727] пруф от независимого разработчика, поэтому этот источник, вызывает больше доверия, чем рекламные материалы проектов.

Как видите, разница примерно в 10 раз.

Система управления базами данных Postgres в 4–15 раз быстрее MongoDB при тестировании производительности транзакций, проведенном OnGres – компанией, специализирующейся на предоставлении программного обеспечения и услуг для баз данных и спонсируемой EnterpriseDB.

С одной стороны, Postgres умеет вставлять записи в таблицы без индексов практически с линейной скоростью записи на диск (сотни мегабайт в секунду). Однако если таблица содержит индексы, а вставленные ключи имеют случайные значения, тогда мы наблюдаем резкое снижение производительности.

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

PostgreSQL представляет классический подход к хранению структурированных, данных в виде таблиц. С другой стороны, MongoDB требует от разработчика переосмысления подхода к хранению данных. Конечно, эта СУБД не дает полной свободы действий, но предоставляет дополнительный инструмент, повышающий производительность базы данных – коллекции.

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

Инструменты кэширования для мобильных приложений

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


Redis

Redis позиционирует себя как система управления базами данных класса NoSQL, работающая с парами ключ-значение. К базовым характеристикам можно отнести то, что Redis хранит данные в оперативной памяти (In Memory), при этом обеспечивает постоянное хранение и на дисковых накопителях (персистентность). Одно из ключевых отличий Redis от других решений заключается в поддержке различных типов данных: строки, списки, множества, хеш-таблицы, упорядоченные множества. Еще одним важным отличием является реализация системы обмена сообщениями по модели «издатель-подписчик» (PubSub). C его помощью вы сможете создать шину данных приложения, открывать каналы, подписываться и публиковать сообщения.

Redis часто используется для хранения идентификаторов сетевых сессий, но это не ограничивает варианты его применения. Например, мы можем кешировать наиболее часто запрашиваемые пользователями данные, такие как первые страницы категорий товаров или таблиц лидеров.

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

Когда Redis используется в качестве кеша, ему можно дать указание автоматически удалять старые данные при добавлении новых. Это поведение известно сообществу разработчиков как алгоритм LRU, и применяется по умолчанию в популярной системе memcached. LRU – фактически только один из поддерживаемых алгоритмов освобождения данных.

Начиная с Redis 4.0 доступен новый режим освобождения наименее часто используемых данных – LFU. Этот режим может обеспечивать лучшую эффективность в определенных ситуациях, поскольку в этом случае Redis отслеживает частоту доступа. Данные, которые используются редко, удаляются, в то время как наиболее используемые имеют более высокий шанс остаться в памяти.

Итог

Выбор базы данных для вашего приложения во многом зависит от специфики проекта и уровня ваших разработчиков. Если использование PostgreSQL можно считать начальным уровнем, то разработка на основе MongoDB уже требует знаний сравнимых с уровнем Intermediate.

Дальнейшее увеличение производительности базы данных возможно при использовании движков хранилищ данных (встраиваемых key-value хранилищ). Здесь стоит обратить внимание на такие решения, как RocksDB и BadgerDB. Однако они требуют от разработчика еще более серьезных познаний в системах управления базами данных, сопоставимых с уровнем Senior.

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

Естественно, Redis не является единственным решением. Для осознания потребности в реализации собственной системы кэширования, нужно понимать процесс сериализации и десериализации происходящий при каждом запросе к данным, отправке сообщения по шине данных или взаимодействии с Rest API.

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

***
Чтобы ускорить работу системы, выберите провайдера инфраструктуры, который наилучшим образом соответствует требованиям вашего ресурса: объем накопителя, процессор и количество ядер, объем оперативной памяти и ежемесячного трафика, наличие резервного копирования, быстрая техническая поддержка. Эта статья написана совместно с нашим партнером – компанией Selectel.

Selectel предлагает серверы, оснащенные 4-768 ГБ ОЗУ, 2-72 ядрами ЦП, а также с возможностью подключить графический ускоритель и выбрать в качестве сервера даже Raspberry Pi 4 (4/64 ГБ) и Mac mini для iOS-разработчиков.

08
Окт
2021

Конференция HighLoad++ 2021

Крупнейшая в Европе IT-конференция для разработчиков высоконагруженных систем. В программе более 130 докладов, персональные консультации и полезные знакомства.
— Читать дальше «Конференция HighLoad++ 2021»

31
Авг
2021

📊 Путеводитель по Big Data для начинающих: методы и техники анализа больших данных

Методы и техники анализа Big Data: Machine Learning, Data mining, краудсорсинг, нейросети, предиктивный и статистический анализ, визуализация, смешение и интеграция данных, имитационные модели. Как разобраться во множестве названий и аббревиатур? Читайте наш путеводитель.

Machine Learning

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

Как методы машинного обучения применяют при анализе больших данных:

  • Осуществление маркетинговых исследований и сегментации информации. Это дает возможность предприятию понимать рынок, узнавая потребности целевой аудитории. В СМИ машинное обучение помогает определить, какой контент нравится аудитории, а какой нет.
  • Изучение поведения клиентов.
В особенности преимущества машинного обучения в Big Data видны в процессе построения IoT-систем. Технологию используют Tesla Motors и Nest, а также IoT-платформы: AWS IoT Greengrass ML Inference, SageMaker, Google Cloud IoT.
В особенности преимущества машинного обучения в Big Data видны в процессе построения IoT-систем. Технологию используют Tesla Motors и Nest, а также IoT-платформы: AWS IoT Greengrass ML Inference, SageMaker, Google Cloud IoT.

Нейронные сети и распознавание образов

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

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

Вариант использования нейросетей с распознаванием образов – различать фото мужчин и женщин.

Для этого потребуется:

  • Построить нейронную сеть, т.е. искусственные нейроны нужно запрограммировать воспринимать данные на входе и выстраивать связи.
  • Отправить нейросети выборку очищенного потока информации – базу фотографий с отметками женских и мужских лиц. Это необходимо для обучения нейросети, чтобы она в дальнейшем понимала, по каким критериям отличаются лица.
  • Запустить тест нейросети, для этого отправить новую очищенную выборку с лицами, но без отметок. В ходе тестирования можно определить частоту возникновения ошибок.

Data mining

Термин Data mining появился благодаря математику Григорию Пятецкому-Шапиро в 1989 году. Добыча данных – это интеллектуальный анализ, необходимый для выявления закономерностей в массиве разнородной информации.

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

Основные задачи, решаемые Data mining:

  • Анализ отклонений выявляет резко отличающиеся данные, нетипичные для потока.
  • Ассоциации предназначены для поиска повторяющейся информации из различных источников.
  • Регрессионный анализ позволяет найти факторы, которые влияют на какой-то заданный параметр.
  • Классификация предназначена для разделения сведений по группам, где находятся схожие элементы.
  • Кластеризация выполняет распределение полученных записей по ранее составленным классам.

Краудсорсинг

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

Для разового опроса или сбора/обработки отзывов покупателей невыгодно разрабатывать систему искусственного интеллекта. Однако для регулярной обработки данных и их анализа стоит использовать системы, которые основаны на машинном обучении или Data Mining. Машины способны выполнять сложный анализ, который основан на математических методах (имитационное моделирование или статистика).

Предиктивная аналитика

Прогнозная, предсказательная или предиктивная аналитика составляет прогноз на основании накопившейся информации, отвечая на вопрос «Что может произойти?». Данные получают методами моделирования, математической статистики, машинного обучения, Data mining и т.д.


Предприятия могут использовать предиктивную аналитику для контроля технологических или каких-то иных бизнес-процессов. На основании прошлых и текущих показаний можно, например, заранее определить время профилактического ремонта оборудования, чтобы избежать поломки техники. Также метод применяется в сфере продаж (по результатам предыдущих периодов можно получить прогноз по продажам на следующий); трейдерами на бирже; в логистике для расчета времени доставки; для выявления мошенничества в страховой и банковской сферах; для прогноза роста компании и финансовых показателей.

Принцип работы Predictive analytics:

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

Имитационное моделирование

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

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

Статистический анализ

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

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

К статистике относят анализ временных рядов и А/В тестирование. A/B testing или split testing – это маркетинговый метод исследования, при котором сравнивают контрольную группу элементов с наборами тестовых групп с измененными параметрами, чтобы определить, какие факторы улучшают целевые показатели.
К статистике относят анализ временных рядов и А/В тестирование. A/B testing или split testing – это маркетинговый метод исследования, при котором сравнивают контрольную группу элементов с наборами тестовых групп с измененными параметрами, чтобы определить, какие факторы улучшают целевые показатели.

Методы для получения статистических результатов:

  • Корреляционный анализ для выявления взаимосвязей и того, как изменение одних показателей влияет на другие.
  • Подсчет процентного соотношения результатов исследования.
  • Динамические ряды для оценивания частоты и интенсивности изменений условий на протяжении времени.
  • Вычисление среднего показателя.

Визуализация аналитических данных

Для упрощения процесса анализа информации используют метод визуализации данных. Выполнить визуализацию Big Data можно при помощи средств виртуальной реальности и «больших экранов». Визуальные образы воспринимаются лучше текста, поскольку 90% информации человек получает через зрение.

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

Результаты выводят в виде диаграмм, карт, графиков, гистограмм, 3-D моделей, либо пиктограмм. Инструменты для визуализации больших данных: Qlik, Microsoft (Excel, Power BI), Tableau (tableau desktop, tableau public), Orange и Microstrategy.
Результаты выводят в виде диаграмм, карт, графиков, гистограмм, 3-D моделей, либо пиктограмм. Инструменты для визуализации больших данных: Qlik, Microsoft (Excel, Power BI), Tableau (tableau desktop, tableau public), Orange и Microstrategy.

Смешение и интеграция данных

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

Методы, благодаря которым можно использовать записи из разных источников:

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

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

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

***

Если вы только начинаете путь в профессии, обратите внимание на Факультет аналитики Big Data образовательной онлайн-платформы GeekBrains. Вы научитесь собирать и анализировать данные, извлекать полезную информацию и находить закономерности. После обучения сможете проверять гипотезы и помогать бизнесу принимать взвешенные решения. Занятия под руководством опытных наставников и поддержка опытных HR помогут вам продвинуться по карьерной лестнице. Специализированный опыт не потребуется: программа предполагает освоение профессиональных навыков с нуля.

31
Авг
2021

📊 С чего начать погружение в Big Data?

Как стать специалистом по Big Data? Какое направление выбрать: Data Scientist, Data Analyst или Data Engineer? С чего начать изучение этого направления, разберем информацию для начинающих IT-шников.

Генерация и обмен большими данными между устройствами происходит практически в каждой социальной сфере. С Big Data работают такие гиганты Google, Uber, IBM, Amazon, оптимизируя работу с клиентами, снижая риск мошенничества и угроз безопасности данных. Специалисты по Big Data необходимы в сферах: маркетинга, поисковых технологий, ритейла, социальных сетях, играх, персонализации, речевых технологиях, финансовых учреждениях и в рекомендательных системах.

Вакансии Data Scientist, Data Engineer и Data Analyst все чаще встречаются в объявлениях с привлекательно высокими зарплатами. С чего начать погружение в Big Data? Чтобы влиться в это направление, рассмотрим основные знания, навыки и технологии, которые стоит изучить новичку для поиска работы.


Направления в Big Data

Существует три основных направления специалистов по Big Data: Data Science, Data Engineer, Data Analyst. Всем им желательно иметь высшее образование (бакалавр, магистр) по техническим специальностям: компьютерные системы, информационные технологии, экономическая кибернетика или подобным.

Разберем направления работы экспертов по большим данным:

  • Аналитикой занимаются Data Scientist и Data Analyst, в их обязанности входит формирование гипотез, поиск закономерностей в наборах данных (dataset), визуализация информации, подготовка данных к моделированию, разработка алгоритмов Machine Learning (машинного обучения), интерпретация полученных данных, а также изучение предметной области или бизнес-процесса.
  • Инженерия относится к профессиям Data Engineer и администратор. Такие специалисты занимаются поддержкой, созданием и настройкой программной и аппаратной инфраструктуры системы сбора, хранения и обработки информации, а также аналитикой массивов и информационных потоков, в том числе конфигурированием облачных (Cloud) и локальных кластеров.

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

Для работы с большими данными, необходимо иметь хотя бы базовые знания:


Что нужно знать Data Scientist?

Исследователь, ученый по данных (Data Scientist) в основном занимается извлечением полезной информации из массивов сведений.

Основные знания, которыми должен обладать специалист Data Scientist:

  • Математика, а точнее теория вероятностей, статистика и дискретная математика. Знания математики необходимы и для машинного обучения, что предполагает также использование линейной алгебры.
  • Различные информационные технологии, средства и методы для интеллектуального анализа данных (Data Mining): языки программирования (Haskell, R, Julia, Python), среды для статического анализа данных (MatLab, R-Studio, Jupyter Notebook), структуры и алгоритмы данных, машинное обучение и иные ветви искусственного интеллекта (генетические алгоритмы, искусственные нейронные сети, deep learning).
  • Понимание предметной области.

Что следует изучать Data Engineer?

Роли в Data Engineering:

  • Database Administrator должен обладать обширными знаниями по базам данным, уметь настраивать СУБД и уровни доступа пользователей, обслуживать и обеспечивать бесперебойную работу системы.
  • Data Architect создает основу для управления данными, их прием из разных источников, интеграцию и обслуживание. Для этого стоит использовать Apache Pig, SQL, Apache Spark, Hive, XML, Apache Zookeeper, Apache Kafka и тому подобные инструменты.
  • Data Engineer должен уметь пользоваться инструментами для работы с базами данных, знать языки программирования (Python или Java), уметь пользоваться системами AWS, HDFS (Hadoop S3 Buckets, MapReduce).

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


Какой базой должен обладать Data Analyst

Для аналитика не обязательно высшее образование в области информационных технологий. Однако Data Analyst должен разбираться в бизнес-процессах, понимать статистику, выполнять машинное обучение, уметь работать с инструментами.

Типа анализа данных:

  • Описательный – для сбора характеристик, обработки полученной информации.
  • Прогнозный направлен на прогнозирование будущих результатов.
  • Диагностический помогает обнаружить ошибки в данных.
  • Предписательный включает перечисленные выше типы анализа информации.
В обязанности аналитика также входят задачи по Business Inteligence (BI) и оптимизации процессов на производстве. Специалист должен знать методы анализа бизнес-процессов: SWOT, ABC, IDEF, BPMN, ССП, PDCA, EPC и прочие.

Базовые навыки Data Analyst:

  • Умение извлекать данные из различных источников (Hadoop, MS SQL, MySQL и др.).
  • Обработка информации с использованием Scala, R, Python или Java.
  • Визуализация структурированных данных с применением Qlik, Plotly или Tableau.
  • Формирование исследования, которое соответствует категории бизнес-задачи.
  • Предоставление гипотез в соответствии с задачами бизнеса.

Дополнительно аналитик может использовать Apache Storm, Apache Kinesis, Apache Spark Streaming.

Специалистам по Big Data нужно уметь строить графические модели, используя байесовские и нейронные сети, кластеризацию и виды анализа. Data Scientist, Data Analyst или Data Engineer должны обладать навыками работы с Data Lakes (озерами данных), а также разбираться в вопросах безопасности и управления данными (Data Governance). Стать экспертом поможет углубленная проработка каждого из навыков.

***

Если вы только начинаете путь в профессии, обратите внимание на Факультет аналитики Big Data образовательной онлайн-платформы GeekBrains. Вы научитесь собирать и анализировать данные, извлекать полезную информацию и находить закономерности. После обучения сможете проверять гипотезы и помогать бизнесу принимать взвешенные решения. Занятия под руководством опытных наставников и поддержка опытных HR помогут вам продвинуться по карьерной лестнице. Специализированный опыт не потребуется: программа предполагает освоение профессиональных навыков с нуля.

25
Июл
2021

📊 Как стать дата-инженером в 2021 году?

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

Мир науки о данных быстро меняется, и это открыло новые возможности и вакансии в области инженерии данных. Давно хотели разобраться, как стать дата-инженером, но не знали с чего начать? Тогда эта статья для вас.

Типичные обязанности дата-инженера:

  • Исследовательский анализ данных.
  • Извлечение данных из массивов разнородной информации.
  • Оценка и очистка наборов данных.
  • Подготовка и написание логики ETL (процессов обработки/преобразования информации).
  • Построение конвейеров данных для распределения по нескольким серверам.
  • Сбор и запрос данных.
  • Объединение данных.
  • Создание хранилищ данных.
  • Оптимизация доставки данных.
  • Нормализация и моделирование данных.
  • Перепроектирование инфраструктуры данных для масштабируемости.
  • Использование инструментов для обслуживания данных.
  • Помощь специалистам по исследованию данных в оптимизации продуктов.

Главные технические компетенции дата-инженера:

  • Языки программирования Python, C++, Java и Scala.
  • Знание алгоритмов и структур данных.
  • Системы управления базами данных (SQL и NoSQL).
  • Инфраструктура – облачные вычисления.
  • Потоковая передача данных – Apache Beam.

Как стать успешным дата-инженером в 2021 году?


Изучите языки программирования

Требования индустрии вращаются вокруг Python и Scala. Чтобы создавать качественное ПО, вам потребуется подтянуть навыки владения этими языками и получить практический опыт работы с инструментами для инженерии данных. Также желательно владеть такими языками, как Java и C++.

Освойте SQL

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


Изучите облачные технологии

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

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

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

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

Обзор ресурсов для изучения

Алгоритмы и структуры данных

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

Python

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

SQL

Облачные технологии

  • Hadoop. Система для обработки больших объемов данных. Курс посвящен методам обработки больших объемов данных с помощью системы Hadoop. Завершив обучение, вы получите базовые знания об основных способах хранения и методов обработки больших объемов данных, а также поймете принципы работы распределенных систем в контексте фреймворка Hadoop.
Из более продвинутых курсов хочется отметить англоязычный GCP: Complete Google Data Engineer and Cloud Architect Guide на Udemy, где 28 часов контента дополняются 25 статьями и 48 ресурсами для скачивания. Программа обучения дает полное представление о дисциплине.

Итог

Если вам нравится работать в спокойной обстановке и проводить чистку необработанных данных, карьера в области дата-инженерии – правильный выбор. Самое трудное – создать портфолио и накопить опыт для получения первой работы. Значительное увеличение объема облачных услуг стало одной из основных причин столь высокого спроса на инженеров по обработке данных.

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

***

Если вы только начинаете путь в профессии, обратите внимание на курс Data Engineering образовательной онлайн-платформы GeekBrains. Занятия под руководством опытных наставников и поддержка опытных HR помогут вам продвинуться по карьерной лестнице. Вы научитесь собирать и обрабатывать данные, проектировать хранилища и работать с инфраструктурой. Специализированный опыт для этого не потребуется: программа предполагает освоение профессиональных навыков с нуля.

12
Июл
2021

Интенсив ASP.NET Core Fundamentals

.NET-разработчик и тренер Microsoft проведёт 11 онлайн-сессий по работе с платформой ASP.NET Core и научит создавать современные кроссплатформенные приложения.
— Читать дальше «Интенсив ASP.NET Core Fundamentals»

10
Июн
2021

Конференция SECON 2021

В программе конференции доклады от экспертов, общение с коллегами, новые знакомства, выставка и развлечения в атмосфере летнего фестиваля.
— Читать дальше «Конференция SECON 2021»

04
Июн
2021

Курс «Профессия Data Scientist» с трудоустройством

За 2 года освоите профессию Data Science: научитесь программировать на Python и R, применять алгоритмы машинного обучения и визуализировать данные.
— Читать дальше «Курс «Профессия Data Scientist» с трудоустройством»

31
Май
2021

Курс «Backend-разработка на Node.js»

За 4 месяца станете fullstack-разработчиком, будете создавать бэкенд веб-приложений на Node.js и расширите возможности ваших проектов на JavaScript с помощью языка TypeScript.
— Читать дальше «Курс «Backend-разработка на Node.js»»

26
Май
2021

Курс «Fullstack-разработчик на JavaScript»

За полтора года с нуля научитесь программировать на JavaScript и TypeScript и освоите передовые технологии — React, Node.js, Docker, Express.js, Nest.js, Firebase, MongoDB.
— Читать дальше «Курс «Fullstack-разработчик на JavaScript»»

12
Май
2021

Java. Backend-практикум

Бесплатный онлайн-курс для начинающих Java-разработчиков. За 8 недель получите систематизированную базу знаний и создадите свой проект для портфолио.
— Читать дальше «Java. Backend-практикум»

06
Май
2021

Конференция Highload++ 2021

Большая конференция для разработчиков высоконагруженных систем. Доклады, митапы, нетворкинг, уникальный опыт и ноу-хау от экспертов, задающих тренды.
— Читать дальше «Конференция Highload++ 2021»

28
Апр
2021

Установка пробной версии Microsoft Access в 2021 году бесплатно

Для учебных целей нам потребовалась установка пробной бесплатной версии Microsoft Access, но в реальности это оказалось нетривиальной задачей. К сожалению, ссылку на установку персональной или семейной версии найти не удалось, поэтому нужно ставить При…

14
Апр
2021

Курс «PHP + MySQL за 1,5 месяца»

12 занятий, практика на каждом уроке, сертификат об окончании курса и возможность попасть в команду BrainForce. Старт в любой день.
— Читать дальше «Курс «PHP + MySQL за 1,5 месяца»»

25
Мар
2021

Менеджер баз данных DBeaver

В этой статье поговорим об инструменте, который нужен всем, кто работает с базами данных: программистам, сисадминам, аналитикам и т.д. Это универсальный менеджер баз данных DBeaver, который поддерживает более 80 баз данных. Преимущества DBeaver Менедже…

10
Мар
2021

Факультет игровой аналитики

Научитесь создавать игры, используя знания маркетинга, гейм-дизайна и статистики. Курс подойдёт начинающим аналитикам, гейм-дизайнерам и даже простым геймерам, которые хотят превратить хобби в работу мечты.
— Читать дальше «Факультет игровой аналитики»…